Handling the problem of out-of-synchronization of slave data in Mysql8.0 version
Situation 1: Error during synchronization
During the synchronization process of the slave database, the master database deletes data.
1. The slave database does not synchronize the data of the master database
Cause analysis: Caused by data synchronization error.
Operation from library
show slave status\G
View Slave_IO_Running, Slave_SQL_Running
If both are NO, or if one is NO, there will be an out-of-sync situation.
Last_SQL_Error Use this field to view error issues and handle them
2. Solution (repair)
Handle the error sql in one
implement
start slave sql_thread; show slave status\G Slave_IO_Running, Slave_SQL_Running changes to YES; Seconds_Behind_Master is the time the slave fell behind the master. When it is 0, it means it has caught up.
3. Solution (overall)
Clear all data (slave database) and resynchronize
1. Operation from database -- Stop slave stop slave -- Reset slave reset slave all --Delete synchronized data drop database db* 2. Main library operation -- Check status show master status Check the value of position. If there is a change after multiple queries, it means that the data has been manipulated. -- Reset master reset master -- Lock table read-only flush tables with read lock At this time, perform a backup and restore the slave database 3. Operation from database -- Re-establish the relationship. Just check the master status with Zihou's two parameters and keep them consistent with the main library. change master to master_host = '192.168.22.22', master_user = 'user', master_port=3306, master_password='pwd', master_log_file = 'mysqld-bin.000001', master_log_pos=1234; -- Start slave start slave 4. Unlock the main library unlock tables 5. Check the status of the slave library -- Check slave status show slave status Check the values of Slave_IO_Running and Slave_SQL_Running. If both are YES, it means there is no problem. Just write data to the main database for testing.
Scenario 2:
View from library
mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: No The master is fine, but the slave is out of sync.
Here are two solutions:
Method 1: Ignore the error and continue synchronization
This method is suitable for situations where the data in the master-slave database are not very different, or the data does not need to be completely unified, or the data requirements are not strict.
solve:
mysql> stop slave; # means skipping a step error, the following number is variable mysql> set global sql_slave_skip_counter =1; mysql> start slave; # Use later mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes
OK, now the master-slave synchronization status is normal. . .
Method 2: Specify to skip the error code and continue synchronization
Error codes such as primary key conflicts and table already exist such as 1062, 1032, 1060, etc. You can specify in the mysql main configuration file to skip such exceptions and continue with the next SQL synchronization. This can also avoid many abnormal interruptions of master-slave synchronization.
[mysqld] slave-skip-errors = 1062,1032,1060 Restart mysql service mysqld restart \t# or\t /etc/init.d/mysqld #Start the command according to the actual situation Then use mysql> show slave status\G to view: Slave_IO_Running: Yes Slave_SQL_Running: Yes
Detailed error handling
‘Could not find first log file name in binary log index file’ solution
Database master-slave error: Slave_IO_Running: No On the one hand, the reason is because of network communication problems or log reading errors. Here are the solutions to log error issues: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file' Solution: Stop slave from machine mysql> slave stop; Log in to mysql on the master machine: Record the location of the master's bin, for example: mysql> show mster status; + ------------------- + ---------- + --------------- + --- ---------------------------------------- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + ------------------- + ---------- + --------------- + --- ---------------------------------------- + | mysqld-bin.000010 | 106 | | information_schema,mysql | + ------------------- + ---------- + --------------- + --- ---------------------------------------- + The log is mysqld-bin.000010 Flush logs: mysql> flush logs; Because the position of the refresh log file will be + 1, that is, the File becomes: mysqld-bin.000011 Immediately go to slave to execute mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000011',MASTER_LOG_POS=106; mysql> slave start; mysql> show slave status\G;
================================================== ================================
Mysql master-slave synchronization show slave status\G
Next, let’s talk about the specific meaning of the parameters displayed by the show slave status command.
*1) Display format based on binary log replication*
mysql> show slave status\G
*************************** 1. row ********************* *******
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.18.16.22
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.010362
Read_Master_Log_Pos: 555176471
Relay_Log_File: mysqld-relay-bin.004136
Relay_Log_Pos: 502564
Relay_Master_Log_File: mysql-bin.010327
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: blog
Replicate_Ignore_DB:
Replicate_Do_Table: blog.archives
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 555176471
Relay_Log_Space: 3642164873
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: 1042
Master_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: 1622
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Explanation of the specific meaning of parameters:
Slave_IO_State This refers to the state of the Slave connection to the Master, which is the state of the current IO thread and the state transition of the MySQL master-slave replication thread. Master_User This is a user on the Master who is responsible for master-slave replication. It is created when creating master-slave replication (with replication slave permissions). Master_Port The port of the Master server is usually 3306. Connect_Retry The number of connection attempts. When using change master, you can use the master-connect-retry option to specify the current value. Master_Log_File Displays the name of the main server binary log file currently being read by the current I/O thread. The above display is mysql-bin.010362. Read_Master_Log_Pos Displays the offset currently synchronized to the binary log on the main server, the position that the I/O thread has read, in bytes. The above example shows the offset position of 555176471 currently synchronized to mysql-bin.010362, that is, it has been The contents of 529MB (555176471/1024/1024) in the binary log of mysql-bin.010362 are synchronized. Relay_Log_File Displays the name of the relay log file currently being read and executed by the slave's SQL thread. Relay_Log_Pos Displays the offset of the relay log that the slave's SQL thread has read and executed in the current relay log. Relay_Master_Log_File Displays the binary log file synchronized from the Slave relay log to the Master, which is mysql-bin.010327 in this example. Slave_IO_Running Displays whether the I/O thread is started and successfully connected to the main server. If successful, it is Yes, otherwise it is No. Slave_SQL_Running Displays whether the SQL thread is started. Start is Yes, otherwise it is No. Replicate_Do_DB Replicate_Ignore_DB Replicate_Do_Table Replicate_Ignore_Table Replicate_Wild_Do_Table Replicate_Wild_Ignore_Table These parameters are used to indicate which libraries or tables should not be synchronized to the standby database during replication. However, be careful when using these parameters, because problems may occur when used across libraries. In addition, when only ignoring or allowing only multiple libraries or tables, the ignore statement must be used multiple times. Last_Errno,Last_Error Displays the error number and error message of the Slave's SQL thread reading the log parameters. An error number of 0 and an empty string for the message indicates no errors; if the Last_Error value is not empty, it will also be included as a message in the slave server's error log. show. Skip_Counter Displays the most recently used value for SQL_SLAVE_SKIP_COUNTER, which is used to skip Slave errors. Exec_Master_Log_Pos Indicates the position of the Relay log that has been executed by the SQL thread relative to the main library binary log offset. Relay_Log_Space Represents the total size of all original relay logs combined, the value specified in the UNTIL clause of the START SLAVE statement, Until_Condition has the following values: Until_Condition, Until_Log_File, Until_Log_Pos. Until_Condition If the UNTIL clause is not specified, there is no value. The value is Master if the slave is reading until the given position of the master's binary log is reached, and Relay if the slave is reading until the given position of its relay log is reached. Until_Log_File Until_Log_Pos Until_Log_File and Until_Log_Pos are used to indicate the log file name and position value. The log file name and position value define the point at which the SQL thread terminates execution. Master_SSL_Allowed Shows whether the slave server uses SSL to connect to the master server. If SSL connections to the master server are allowed, the value is Yes; if SSL connections to the master server are not allowed, the value is No; if SSL connections are allowed, but the slave server does not have SSL support enabled, the value is Ignored. Master_SSL_CA_File Master_SSL_CA_Path Master_SSL_Cert Master_SSL_Cipher Master_SSL_Key If the slave uses SSL to connect to the master server, the corresponding certificate and private key information will be displayed here. The options related to SSL using CHANGE MASTER are: –master-ca, –master-capath, –master-cert, –master-cipher and –master-key, etc. Seconds_Behind_Master Indicates the delay time between master and slave, in seconds. It is the difference between the timestamp in the binlog currently executed by the SQL thread (actually the relay log) and the latest timestamp of the IO thread. Essentially, this field calculates the time difference (in seconds) between the Slave SQL thread and the Slave I/O thread. If the network connection between the master and the slave is fast, the slave i/o thread is very close to the master, so this field is a good approximation of comparing the slave SQL thread to the master. If the network is slow, this is not a good approximation; the slave SQL thread may often be trapped by the slave I/O thread, so Seconds_Behind_Master will usually show a value of 0, even if the I/O thread is much slower than the master. In other words, this column only applies to fast networks. Master_SSL_Verify_Server_Cert Displays whether to authenticate the Master certificate. Master_Server_Id Displays the Server_id of the primary server. Using_Gtid Indicates whether Gtid-based replication is enabled. If enabled, it is Yes, otherwise it is No. Gtid_IO_Pos If Gtid-based replication is enabled, the currently executed transaction ID will be displayed here.
*2) Display format based on GTIDs copy*
mysql> show slave status\G
*************************** 1. row ********************* *******
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.30.65
Master_User: mysql_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 790
Relay_Log_File: relay-log.000008
Relay_Log_Pos: 1003
Relay_Master_Log_File: mysql-bin.000003
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: 790
Relay_Log_Space: 2591
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: 0
Master_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: 1
Master_UUID: 5f0b7791-a499-11e6-901c-44a84227448b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 5f0b7791-a499-11e6-901c-44a84227448b:2-11
Executed_Gtid_Set: 3ec6f284-a4a8-11e6-a3fe-44a84220797c:1-4,5f0b7791-a499-11e6-901c-44a84227448b:1-11
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_UUID Record the UUID of the Master. Master_Info_File Record the storage location of Master info information. SQL_Delay Record the time the Slave sets to delay replication, 0 means no delay. SQL_Remaining_Delay Slave_SQL_Running_State Record the status of the SQL thread. Last_IO_Error_Timestamp Record the timestamp of the most recent IO thread error. Last_SQL_Error_Timestamp Records the timestamp of the most recent SQL thread error. Retrieved_Gtid_Set The received binary log collection corresponds to the IO thread. execute_Gtid_Set Executed binary log collection, corresponding to the SQL thread. Auto_Position Record whether automatic transaction verification is enabled in GTID mode. Channel_Name Under multi-source replication (supported in 5.7), there can be multiple replication channel names.