Two-way synchronization configuration steps for MySQL database under Windows

Recently encountered in the project to achieve two-way synchronization of MySQL data on the server, on the Internet to find a lot of information, but most of them are configured under the Liux system,

and are reproduced each other, no detailed steps, so decided to write a Windows system under the full configuration, share with you, if there are shortcomings,

Please correct us.

Tools/Materials

• Two servers, operating system are WINDOW2012 Datacenter Edition, one server for A,IP address: 192.168.1.2, another for B,IP address: 192.168.1.3

• MySQL version best consistent, large version must be consistent, small update does not affect, such as 5.6.10,5.6.11 two version even if the same, if one is 5.5, one is 5.6 not

Method/Step

Add a sync account. Add an account to MySQL on two servers, enter the MySQL command interface and enter the password.

Example: Execute MySQL command,mysql> Grant replication Slave,replication Client on *. * to repl@ ' 192.168.1.% ' identified by ' 123456 ';

Description: Repl is the account name, 123456 is the corresponding password, 192.168.1.% is accessible within the LAN, you can also designate a server, the% of the corresponding IP can be replaced. Note: The above commands are executed on both A and B servers.

Configure a server.

Locate the MySQL configuration file My.ini, and add the following configuration under [Mysqld]:

Log_bin=mysql-bin #说明: Log_bin: Specifying the location and naming of binary log files
Server_id=1 #server_id: MySQL server flag, must be guaranteed unique
Replicate_do_db=ftest #replicate_do_db: The name of the database to be synchronized, multiple separated by commas, this item is not configurable
Sync_binlog=1 #sync_binlog: Whether to sync binary log files to disk, greater than 0 for account opening
Log_slave_updates=1 #将事件自动写到填制日志中注

Note: After saving the file, to restart the MySQL service, then the same operation on the B server to operate again. (note here that server_id must never be the same, and if the same will go wrong)

Once the two servers are configured, the replication feature is turned on on the B server. This step does not need to modify the My.ini file, just to execute the MySQL command, the following is the command:

mysql> Change Master to master_host= ' 192.168.1.2 ',

Master_user= ' Repl ',

-master_password= ' 123456 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=0;

Description: IP address of the MASTER_HOST:A server

Master_user: The sync account we created in the first step

Master_password: The corresponding account password

Master_log_file: The binary log file name, not necessarily the name, can be viewed with the command

Query name and POS with show Master status\g;

Master_log_pos: Use the above command to find out this value

Turn on the copy function

With show slave status\g; To check if replication is working properly, if slave_io_state is empty, slave_io_running, slave_io_running is no,

The copy function is not running and we use start slave to start the replication function.

Again with the command show slave status\g; View, we see slave_io_state as: Waiting for Master to send Eventslave_io_running,

Slave_sql_running yes to indicate that the configuration has been successful and that the replication function is functioning properly. Then configure a server, the same steps as the configuration B server.

Note: Must be careful to configure the parameters inside, to the corresponding master_host, Master_log_file, master_log_pos configuration is correct

Test data synchronization is successful, on a server to modify the Ftest database of a table data, and then on the B server to see if the data changes, if changed,

Description has been configured successfully. Then modify the data of the B server, then go to a server to view, if the data also changed, bidirectional synchronization of the data synchronization function succeeded.

Two-way synchronization configuration steps for MySQL database under Windows