1. Preconditions for configuration
- It is known that server A IP=192.168.0.11, server B IP=192.168.0.12
- It is recommended to use the same version of MySQL. If the small version is generally compatible, such as the MySQL version of A is 5.7.1, and the MySQL version of B is 5.7.2;
- Before configuration, it is necessary to ensure that the data of the two databases are consistent. It is recommended to synchronize database A to database B to ensure that the data of databases A and B are consistent before configuration;
- Ensure that the network ports of the two databases are interconnected, such as: 3306;
- No new data can be written during configuration;
2. Configuration
2.1 Configure server B MySQL synchronization server MySQL data
2.1.1 Modify the MySQL configuration file of server A and add the following content
[mysqld] #master A slave config #The server id of host A needs to be inconsistent with host Bserver-id=1 #Binary log file namelog-bin=master-a-bin #Binary log formatbinlog-format=ROW #Set the maximum log to 1Gmax_binlog_size=1024M #Set the maximum number of days for binlog files to be retained for 30 daysexpire_logs_days=30 #After the database is down and restored, the missing data will be automatically synchronizedrelay_log=mysql-relay-bin relay_log_recovery=1 #Set the initial value of the auto-increment id to 1, and the increment is 2 for each timeauto_increment_offset=1 auto_increment_increment=2 #Enable gtidgtid_mode=ON enforce_gtid_consistency=1 #The database that needs to be synchronized, such as test1 and test2, is configured according to the actual situationbinlog-do-db=test1 binlog-do-db=test2 #Don't sync the databasebinlog-ignore-db=mysql,information_schema,performance_schema,sys #Don't sync tables#replicate-ignore-table=Library name.Table name
After the configuration is complete, save and restart MySQL
2.1.2 Log in to server A and create a data synchronization account for B
Synchronize account: repl_b, synchronize password: 123456, the specific command steps are as follows
mysql> grant replication slave on *.* to 'Sync account'@'Host B' identified by 'Sync Password'; #Login to server A, MySQL, and create B's data synchronization accountmysql> flush privileges; #Refresh permissionsmysql> reset master; #Reset master information#Note: Do not write data before synchronization begins, otherwise it will affect the bin-log log file.mysql> flush tables with read lock; #Lock table command, use unlock table command after the synchronization configuration is completed: unlock tables;mysql> show master status; #View and record the current situationbin-logFile name and location，Recordbing-logfile name：master-a-bin.000001，Log location：154
2.1.3 Log in to the B server to modify the MySQL configuration file, set A to B master, and add the following
#master B slave config #The server id of host B is inconsistent with the id of host Aserver-id=2 #Binary log file namelog-bin=master-b-bin #Binary log formatbinlog-format=ROW #Set the maximum log to 1Gmax_binlog_size=1024M #Set the maximum number of days for binlog files to be retained for 30 daysexpire_logs_days=30 #After the database is down and restored, the missing data will be automatically synchronizedrelay_log=mysql-relay-bin relay_log_recovery=1 #Set the initial value of the auto-increment id to 1, and the increment is 2 for each timeauto_increment_offset=2 auto_increment_increment=2 #Enable gtidgtid_mode=ON enforce_gtid_consistency=1 #The database that needs to be synchronized, such as test1 and test2, is configured according to the actual situationbinlog-do-db=test1 binlog-do-db=test2 #Don't sync the databasebinlog-ignore-db=mysql,information_schema,performance_schema,sys #Don't sync tables#replicate-ignore-table=Library name.Table name
After the configuration is completed, save and restart the following MySQL
Log in to MySQL on host B and set A to B master
PS: If the global transaction id (gtid_mode=ON) is enabled, you can remove this parameter without specifying the master_log_pos parameter.
#Back up the database that needs to be synchronized, and import the backup database on host B#Login mysql of host B, set A to B master#stop B's slavemysql> stop slave; #Set the master of B to A, and synchronize the data of A through repl_bmysql> change master to master_host='Host A',master_user='Sync account',master_password='Sync Password',master_log_file='bing-log file name',master_log_pos=Log location; #Open B's slavemysql> start slave; #Determine whether B's slave is synchronized successfullymysql> show slave status\G; #View the output resultSlave_IO_RunningandSlave_SQL_RunningAll forYEShour，Indicates success
At this point, the MySQL synchronization server A data has been completed. Next, set up the MySQL of A synchronization server B.
2.2 Configure server A and MySQL synchronization server B and MySQL data
2.2.1 Log in to MySQL on Server B, create MySQL synchronization account on Server A, synchronous account: repl_a, password: 123456
#Login B's mysqlmysql> grant replication slave on *.* to 'Synchronize your account'@'HostAofIP' identified by 'Synchronize password'; mysql> flush privileges; #Note: Before synchronization begins, it is recommended not to write data, otherwise it will affect the bin-log log file.#Reset master informationmysql> reset master; #Lock table command, use unlock table command after synchronization is completed: unlock tablesmysql> flush tables with read lock; #View and record the file name and location of the current bin-log, record the bing-log file name: master-b-bin.000001, log location: 154mysql> show master status;
2.2.2. Log in to MySQL server A and set B to A's master
#Login host A and stop slavemysql> stop slave; #Set the master of A to Bmysql> change master to master_host='Host B',master_user='Sync account',master_password='Sync Password',master_log_file='master-b-mysql-bin.000001',master_log_pos=154; #Start slavemysql> start slave; mysql> show slave status\G; #View the output resultSlave_IO_RunningandSlave_SQL_RunningAll forYEShour，Indicates success
3. Unlock the database table lock
Execute the unlock command in MySQL on server A and server B: unlock tables
mysql> unlock tables;
4. Perform database reading and writing synchronization test
The data changed (insert, modify, delete) in A will be synchronized to B's database in real time, and the data changed by B will be synchronized to A in real time.
This is the end of this article about the project practice of MySQL dual-main configuration. For more related content on MySQL dual-main configuration, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!