Windows mysql bidirectional synchronization setup method
- 2020-05-10 23:00:50
- OfStack
1.1 environment setup
Prepare two Windows NT hosts, install iKEY Server windows version respectively, make sure the versions are correct, make sure mysql service starts normally, make sure the two hosts are in the same LAN, and make sure which one is the main and standby machine. Assume A is the host and B is the standby machine, assume A IP address is 192.168.1.101, B IP address is 192.168.1.102
1.2 create a synchronous account
Log in mysql database on A and B nodes respectively, create a synchronous account and grant synchronization rights, as follows:
A node operation:
Run cmd, cd enter iKEY\mysql\bin under iKEY version installation directory, then execute mysql, uroot, p123456 and log in mysql database to execute:
mysql > GRANT REPLICATION SLAVE ON *.* TO "yw[j1] "@"192.168.1.10[j2] 2" IDENTIFIED BY 'ym[j3] ';
mysql > flush privileges;
The operation steps of B node are 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 this, run net stop mysql and stop the two node databases, respectively.
1.3 configuration data synchronization option
1.3.1 A host operation
After the iKEY Server windows version is installed, an my_
.cnf configuration file will be generated in the iKEY\mysql\backup folder in the installation directory. The my_master.cnf file will be copied to the iKEY\mysql\bin folder in the installation directory on the A host. Rename it my.cnf. Please backup the original my.cnf file before renaming it. The following are the modifications made by my.cnf:
log-bin = mysql-bin # log file for synchronized events
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 IP address
master-user =ym # sync account
master-password =ym # synchronize account passwords
master-port =3306 # port, MYSQL port of the host
master-connect-retry =60 # retry interval 60 seconds
replicate-do-db =ikey_db # synchronized database
replicate-do-db =ikey_log # synchronized database
1.3.2 B host operation
After the iKEY Server windows version is installed, one my_slave.cnf configuration file will be generated in the iKEY\mysql\backup folder in the installation directory. The my_slave.cnf file will be copied to the iKEY\mysql\bin folder in the installation directory on the B backup machine. Rename it my.cnf. Please backup the original my.cnf file before renaming it. The modification is the same as the A host.
server-id=2
master-host =192.168.1.101 # host A address
Note above that to define the host IP address for master-host, please modify it according to the actual situation.
Make sure the above sync user, host IP, and sync account passwords are the same as configured.
1.3.3 verify data synchronization
When the above configuration is completed, restart the host A and standby B databases, respectively, and run net start mysql,
To view the synchronization configuration, log in the mysql database.
View Master information on A node:
mysql > show master status;
+------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000001 | 98 | ikey_db,ikey_log | |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
View Slave information on 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 viewing the synchronization status 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 slave information on the A node.
Add the data on the A host, test whether the data on the B standby is synchronized, and reverse test.
1.4 synchronization maintenance
When the node IP needs to be changed, the synchronous configuration of Mysql also needs to be modified accordingly. Before changing the node IP, we will do the following:
mysql is executed on A and B nodes, respectively > stop slave; Stop the current synchronization state.
If the A node IP is changed to 192.168.1.103 at this time, the following operations are required:
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 B-- aA directional sync account permissions
mysql > show grants for ym@192.168.1.101(original A node IP);
After checking the original permission granted to connect from 192.168.1.101, we delete this synchronization account and grant the permission to connect from 192.168.1.103 again, 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-aB directional sync configuration item:
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 ', # we just recorded the log file for the A node database to perform replication.
- > MASTER_LOG_POS = 118; # just recorded the location of the log file to be copied from the A node database.
Query OK, 0 rows affected (0.02 sec)
At the same time, you need to check the Master information of the B node and synchronize the LOG log and POS location
mysql > show master status;
+------------------+----------+------------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+------------------+------------------+
| mysql-bin.000003 | 98 | ikey_db,ikey_log | |
+------------------+----------+------------------+------------------+
1 row in set (0.00 sec)
Also modify the B node mysql main configuration file
master-host =192.168.1.103 # host A IP address
1.4.3 followed by A node:
Modify the B-aA directional sync configuration item:
mysql > CHANGE MASTER TO
- > MASTER_LOG_FILE=' mysql-bin.000003 ', # we just recorded the log file for the B node database to perform replication.
- > MASTER_LOG_POS = 98; # just recorded the location of the log file to be copied from the B node database.
Query OK, 0 rows affected (0.02 sec)
Then start the synchronization state for A and B nodes, and run start slave respectively. Then check the synchronization status to make sure you check show slave status on each machine; conclusion
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
It's in normal sync.
If A and B IP both need to be modified, it is better to modify IP for a single node first, and then modify and configure IP for the second node after ensuring that there is no problem with one-way synchronization.
cript">[j1] user who performs synchronization permissions
cript">[j2] IP address for host B
cript">[j3] synchronize account passwords
cript">[j4] user who performs synchronization permissions
cript">[j5] IP address for host A
cript">[j6] synchronize account passwords