What matters should be paid attention to in Mysql master-slave replication

Mysql master-slave replication needs to pay attention to what matters, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

First, report an error error connecting to master 'xroomx.x.x.xbank x'-retry-time: 60 retries: 86400

1. Check the username and password of master-slave replication

two。 Check MASTER_LOG_FILE and MASTER_LOG_POS.

Remember the command to configure the slave library, and these parameters should refer to the configuration of the master library:

Mysql > CHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=3308, MASTER_USER='root', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=797

Query synchronization results:

Mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 3308 Connect_Retry: 60 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 797 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 797 Relay_Log_Space: 409 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL _ CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 3308

The synchronization results can be seen in three items: Slave_IO_Running (Ishock O thread state), Slave_SQL_Running (SQL thread state), and Seconds_Behind_Master (the number of seconds delayed by the slave database than the master database during replication).

Second, there is a conflict from the library and cannot be copied. You can move the synchronization pointer down one, and repeat if you are out of sync multiple times.

Mysql > stop slave;Query OK, 0 rows affected (0.02 sec) mysql > set global sql_slave_skip_counter=1;Query OK, 0 rows affected (0.00 sec) mysql > start slave;Query OK, 0 rows affected (0.01 sec)

Third, at present, the slave library also serves as the master library of other slave libraries. Add the following parameters to the my.cnf of the library, and modify the configuration of server_id and log-bin (uncomment, modify the default configuration), and restart the service.

Log-slave-updateslog-bin = / data/3309/data/mysql-bin # according to the actual situation, writing expire_logs_days = 7 # is equivalent to find / data/3309/data-type f-name "mysql-bin.000*-mtime + 7 | xargs rm-f". After reading the above, have you mastered what matters need to be paid attention to in Mysql master-slave replication? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!