Mysql – Two-way primary/secondary replication

Mysql installation (documentation self check)

Mysql server A 193.169.200.241:3306 Synchronization account repl Server B: 193.169.200.240:3306 synchronization account repl is installed on two serversCopy the code

Mysql file configuration (add configuration to mysql installation base configuration)

/etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
bind_address=0.0.0.0
server-id=1  
log_bin=mysql-bin     
binlog_format=mixed
binlog_expire_logs_seconds=604800
sync-binlog=1
innodb_flush_log_at_trx_commit=1
binlog-do-db=test    
auto-increment-increment=2
auto-increment-offset=2
replicate-do-db=test    
relay_log=relay-bin 

Copy the code
Service mysql restartCopy the code
Remark: Default installation path (default data location /var/lib/mysql) 2. Bind_address External access address 3. Server-id Service ID, an integer greater than 0 4. The logs are stored in the mysql-bin.000001 format in /var/lib/mysql.data file. 5. Binlog_format Log format, which is a mixed log format Sync-binlog Will be reported to binlog for every transaction committed. The difference between 0 and 1 is 5 times that of row read/write performance or higher 8. Binlog-do-db Specifies the database that needs to be synchronized. If there are multiple databases that need to be synchronized, the data cannot be written in a single row. 10. Auto-increment-offset = 1,3,5, or 2,4, or 6 There are two devices, so one is set to 1 and the other is set to 2. Replicate-do-db Specifies the database that needs to be synchronized from another serverCopy the code

Mysql synchronization account

CREATE USER 'repl'@'193.169.200.241' IDENTIFIED BY 'ZAQ! 2wsx'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'193.169.200.241'; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS\G *************************** 1. row *************************** File: mysql-bin.000001 Position: 689 Binlog_Do_DB: test Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 SEC) Result Create a MASTER/slave replication configuration information on server B and run the CHANGE MASTER TO command MASTER_HOST = '193.169.200.240, MASTER_USER =' repl ', MASTER_PASSWORD = 'ZAQ! 2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=689;Copy the code
CREATE USER 'repl'@'193.169.200.240' IDENTIFIED BY 'ZAQ! 2wsx'; GRANT REPLICATION SLAVE ON *.* TO 'repl'@'193.169.200.240'; FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS\G Create A MASTER/slave replication message on server A and run CHANGE MASTER TO MASTER_HOST = '193.169.200.241, MASTER_USER =' repl ', MASTER_PASSWORD = 'ZAQ! 2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=5336;Copy the code

Mysql primary/secondary replication

1. Ensure that the tables in the test library are consistent at the beginning. Run the enable master/slave command on server AB. 3. Show slave status\G Check master/slave information Check the printed information.Copy the code

Problem summary and solution

1. When data is written to server A, the process becomes stuck. Show processList Check whether the mysql process has A waiting processCopy the code
Flush tables with read lock If an error log is displayed in the show slave status\G command, an exception occurs during synchronization. Stop slave; Set global sql_slave_skip_counter =1; # set to skip this error, if not a start slave several more times; Unlock tables; Master/slave synchronization, complete synchronization (master/slave data is too different to be resolved by ignoring the error above) 1. 240 Flush tables with read lock on master; Mysqldump -uroot -p -hlocalhost --all-databases > mysql. SQL (--all-databases indicates all databases [email protected]: ~ / app. 2. Execute stop slave on library 241; Source ~/app/mysql.sql 3. Run SHOW MASTER STATUS\G on 240 TO rewrite the result into the following command, and run CHANGE MASTER TO on 241 MASTER_HOST = '193.169.200.240, MASTER_USER =' repl ', MASTER_PASSWORD = 'ZAQ! 2wsx',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=689; The value MASTER_LOG_POS replaces the start slave based on the actual value. show slave status\G; Slave_IO_Running slave_SQL_Running Yes UNLOCK tables; Unlock ps: In actual development, if it is found that machine A and machine B are not synchronized, data may be written on both machines. In this case, $>stop slave should be executed on both machines again. $>SHOW MASTER STATUS\G Then run $> CHANGE MASTER TO MASTER_HOST='A/B',MASTER_USER='repl',MASTER_PASSWORD='ZAQ! 2wsx',MASTER_LOG_FILE='mysql-bin.00000?? ',MASTER_LOG_POS=???Copy the code
Can't execute the given command because you have active locked tables or an active transaction 1. View the process shwo ProcessList; unlock tables; Unlock the account. 2. Run the stop salve commandCopy the code