Windows MySQL Bidirectional Synchronization setup method detail article _mysql

1.1 Environment Construction

Prepare two Windows NT hosts, install the Ikey Server Windows version separately, make sure that the MySQL service starts properly, and make sure that the two hosts are on the same LAN. Determine which station is the main, standby machine, assuming a is the host, B for standby, assuming a host IP address:192.168.1.101,b host IP address is 192.168.1.102

1.2 Create a synchronization account

login MySQL database on A and B nodes , create synchronization account and give synchronization permissions as follows:

A node Operation:

Run cmd, the CD into the Ikey version of the installation directory under the ikey\mysql\bin , and then perform mysql–uroot–p123456 login MySQL database execution:

Mysql>grant REPLICATION SLAVE on *.* to "yw[J1] " @ "192.168.1.10[J2] 2" identified by "ym [J3] ';

Mysql>flush privileges;

The B -node operation steps as above, the permissions are given as follows:

Mysql>grant REPLICATION SLAVE on *.* to "yw[J4] " @ "192.168.1.10[J5] 1" identified By ' ym[J6] ';

Mysql>flush privileges;

After doing so, run net stop MySQL, and stop the two node database separately .

1.3 Configure data synchronization options

1.3.1 a host Operation

After the installed version of Ikey Server Windows , a my_master.cnf configuration file is generated under the Ikey\mysql\backup folder in the installation directory , A Host Copy the my_master.cnf file to the Ikey\mysql\bin folder under the installation directory and rename it to my.cnf, before renaming, back up the original my.cnf file, the following is a description of the changes my.cnf made:

Log-bin=mysql-bin # Sync Event log file

BINLOG-DO-DB=IKEY_DB # provides database logs for data Synchronization Services

Binlog-do-db=ikey_log # provides database logs for data Synchronization Services

Server-id=1
master-host=192.168.1.102 # host B 's IP address
Master-user=ym # Sync Account
Master-password=ym # Sync account password
master-port=3306 # Port, MYSQL Port of host
master-connect-retry=60 # retry interval of seconds
replicate-do-db=ikey_db # synchronized database

Replicate-do-db=ikey_log # synchronized database

1.3.2 B host Operation

after a version of Ikey Server Windows is installed , a my_slave.cnf is generated under the Ikey\mysql\backup folder under the installation directory configuration file, copy the my_slave.cnf file to the Ikey\mysql\bin folder under the installation directory on the B standby and rename it to my.cnf. Before renaming, please back up the original my.cnf file, modify the same host A, different configuration is the following:

server-id=2
master-host=192.168.1.101 # address of host A

The above need to note is, to define the Master-host host IP address, please modify according to the actual situation.

Make sure that the synchronization user, host IP, and Sync account passwords are the same as the settings that you set .

1.3.3 Verifying data Synchronization

When the above configuration is completed, reboot host A and Standby B database, run net start MySQL,

To view the synchronization configuration , login to the MySQL database.

To view Master information on a node:

Mysql> Show master status;

+------------------+----------+------------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+------------------+------------------+

| mysql-bin.000001 | Ikey_db,ikey_log |                   |

+------------------+----------+------------------+------------------+

1 row in Set (0.00 sec)

To view Slave information on the B node:

Mysql> show Slave status\g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

Master_host:192.168.1.101

Master_user:ym

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

Read_master_log_pos:98

relay_log_file:testbbb-relay-bin.000002

relay_log_pos:235

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

Replicate_do_db:ikey_db,ikey_log

By looking at the sync state on the B node, we can clearly see the synchronization configuration information set and the current synchronization status.

You can also view master information on the B node and View slave information on node A .

Add data on a host, and test the data on the B standby for synchronization and reverse testing.

1.4 Synchronous Maintenance

When you need to change the node IP , theMysql synchronization configuration also needs to be modified, before changing the node IP , we do the following:

execute mysql> stop slave on A and B nodes respectively ; stops the current sync state.

If A node IP is changed to 192.168.1.103 at this point , you need to do the following:

1.4.1 A node:

Mysql> Show master status;

+------------------+----------+------------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+------------------+------------------+

| mysql-bin.000002 | 118 | ikey_db,ikey_log |                   |

+------------------+----------+------------------+------------------+

1 row in Set (0.00 sec)

1.4.2 B node:

Change account permissions for b--à-direction synchronization

Mysql>show grants for YM@192.168.1.101 ( formerly A node IP);

To view the permissions originally assigned to the 192.168.1.101 connection, at this point we remove the synchronization account and reassign the 192.168.1.103 connection permissions as follows:

Mysql>drop user ym@192.168.1.101;

Mysql>flush privileges;

Mysql>grant replication Slave on *.* to ym@192.168.1.103 identified by ' ym ';

Mysql>flush privileges;

Then modify the A-àB Direction Synchronization configuration entry:

Mysql>change MASTER to

-> master_host= ' 192.168.1.103 ', # MASTER server address

-> master_user= ' ym ',

->master_password= ' ym ', ->->master_log_file= ' mysql-bin.000002 ', # just now we record A Node database to perform a replicated log file.

-> master_log_pos=118; # The log file location that you just recorded for the A node database to replicate.

Query OK, 0 rows affected (0.02 sec)

At the same time, you need to view Master Information for node B , sync log logs and POS locations

Mysql> Show master status;

+------------------+----------+------------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+------------------+------------------+

| mysql-bin.000003 | Ikey_db,ikey_log |                   |

+------------------+----------+------------------+------------------+

1 row in Set (0.00 sec)

Also, modify the B -node MySQL master configuration file

master-host=192.168.1.103 # host A 's IP address

1.4.3 then a node:

To modifythe B -à-A Direction synchronization configuration entry:

Mysql>change MASTER to

->master_log_file= ' mysql-bin.000003 ', # just now we record the B-node database to perform the replicated log file.

-> master_log_pos=98; # The log file location to replicate for the B-node database just recorded.

Query OK, 0 rows affected (0.02 sec)

Then Start the synchronization state for A and B nodes and run the start slave respectively; then look at the sync status to ensure that show slave status is viewed on each machine ; Draw

Slave_io_running:yes

Slave_sql_running:yes

is in the normal sync state.

The above is for a single node IP Modification of the operation, if A,B node IP needs to be modified, refer to the above configuration to operate, preferably first for a single node IP to modify, and ensure that one-way synchronization is not a problem, then the second node IP Modification and configuration.

[J1] the user who performs synchronization permissions

[J2] IP address of host B

[J3] Sync account password

[J4] the user who performs synchronization permissions

[J5] IP address of host A

 [j6] sync account password