windows mysql主主配置_Windows mysql 主从服务器配置

参考资料

Configure on both servers a different server-id, and activate the binary log on the master. If it is not already done, the most typical way to do that is modify the my.ini file and restart the service (net restart mysql):

[mysqld]

log-bin

server-id = 1

Make also sure that the master is accesible from the slave node and it is not bound to localhost (bind-address = 127.0.0.1)

Create a user on the master for the slave to connect with REPLICATION SLAVE privileges (use MySQL-command line for this):

mysql master> CREATE USER 'user_name'@'ip.of.the.slave' IDENTIFIED BY 'password';

mysql master> GRANT REPLICATION SLAVE ON *.* TO 'user_name'@'ip.of.the.slave';

Make a backup of the master and apply it to the slave. There are several ways to do that (stopping the server and copying the files works), but the easiest would be to se mysqldump (navigate to the bin directory of your sql-server installation (f.e. C:\Program Files\MySQL\MySQL Server 5.7\bin). Also you may need to provide credentials by appending this to both commands: -uroot -pPassword)

(on the master windows command line) mysqldump --all-databases --master-data > dump.sql

(on the slave windows command line ) mysql < dump.sql

At the beginning the dump.sql you will find a line like this:

-- CHANGE MASTER TO MASTER_LOG_FILE = 'file_name', MASTER_LOG_POS = file_pos;

Use those parameters to configure the replication on the slave:

mysql slave> CHANGE MASTER TO MASTER_HOST = 'ip.of.the.master',

MASTER_USER = 'user_name_you_just_created',

MASTER_PASSWORD = 'password_you_just_defined',

MASTER_LOG_FILE = 'file_name',

MASTER_LOG_POS = file_pos;

(beware, no quotes for integers, like MASTER_LOG_POS).

If everything is ok until now, just start replication on the slave:

mysql slave> START SLAVE;

To check that it is running, execute:

mysql slave> SHOW SLAVE STATUS;

And look for:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

GTID-based replication is a bit different, and I have not mentioned it.