Mysql slave synchronization error solution, mysqlslave

Mysql slave synchronization error solution, mysqlslave
Knowledge points involved

Mysql master-slave synchronization, refer to: MySQL database settings master-slave Synchronization

For more information about mysqlbin log, see binlog of MySQL.

To solve slave errors, refer:

Backup stopped working !! Slave_ SQL _Running: No

Mysql database binlog truncation error, leading to mysql Master/Slave synchronization failure troubleshooting

Analysis and Solution of slave database failure, and manual synchronization based on binlog modification position

Problem

The customer reports that lack recent data.

Analysis and Solution

The master database provides services, and the slave database is used as the Report Server. The master database is synchronized through the mysqlbin log.

From the report data point of view, there is a lack of data from 10/28 to now (11/18), compared to the master, slave database main table data, found:

The data in the slave database after 10/28 is missing and the data is not synchronized.

Follow the yellow field in the figure:

Slave_IO_Running: Yes
Slave_ SQL _Running: No --- indicates that the SQL process is not working, and the problem lies in this.

Pink background in the figure, Last_Error :....

'Duplicate entry '000000' for key 'primary' 'on query. Default database:'. Query: 'insert into user...

This error is simple because it violates the unique primary key constraint.

2. the mysql Error Log uses my. conf to identify the Error Log File. view the vi file and search for the following according to log 151028:

From the log, we can see that mysql was shut down abnormally at 1:28:55, 10/28; after, recovery started. 1: 29: 16 s after I/O errror; 1: 30: 19 s SQL error, slave SQL thread aborted (stop working ).

Mysql also provides a solution:

Fix the problem, and restart the slave SQL thread with "SLAVE START".   We stopped at log 'mysql-bin.000274' position 504869752

Restart and tell the binlog file executed by SQL to set the position. The restart slave. Error still exists, as shown below:

Mysql> change master to MASTER_LOG_FILE = 'mysql-bin.000274 ', MASTER_LOG_POS = 504873114; ERROR 1198 (HY000): This operation cannot be performed Med with a running slave; run stop slave firstmysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> change master to MASTER_LOG_FILE = 'mysql-bin.000274 ', MASTER_LOG_POS = 504873114; Query OK, 0 rows affected (1.98 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status \ G ***************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: export Master_User: cns Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000281 Read_Master_Log_Pos: 361947072 Relay_Log_File: app3-relay-bin.000002 Relay_Log_Pos: 253 rows: mysql-bin.000274 Slave_IO_Running: Yes Slave_ SQL _Running:View Code

 

The cause of the error is still

Last_SQL_Error: Error 'Duplicate entry '1169595' for key 'PRIMARY'' on query. Default database: ''. Query: 'insert into user (type,lang,ipAddr,activityStatus,extUserId,endpoint,createTime, email, userName, mobile, storageSize, tuner
        )values ('normal','zh-xx','xxxx','active','913151000777430','xxx',now(),null,null,null,0,0)'

At this point, although the cause of the problem must be solved. To learn more about the mysql synchronization mechanism and bin log, the principle is that slave obtains the binlog of the master and executes the command. If an error is reported, the database already has this record, possibly because the position in the log is incorrect, you can only start with binlog analysis,

For details about viewing binlog, see binlog of MySQL.

The second method is used for viewing:

Mysql> show binlog events [IN 'Log _ name'] [FROM pos] [LIMIT [offset,] row_count]; Option parsing: IN 'Log _ name' specifies the binlog file name to be queried (if not specified, it is the first binlog file) FROM pos specifies the start point of the pos (not specified is counted FROM the first pos point of the entire file) LIMIT [offset,] offset (not specified is 0) total number of row_count queries (not specified as all rows)

Log_name and pos error logs are known. The query is as follows:

Binlog includes the SQL statements for all database operations. Each record contains one database operation.

In the bin log, it is easy to find the wrong statement. The problem is: Find the position to which the slave SQL thread executes. here, you can only use the most stupid method to view the data in the slave database based on SQL statements, such:

Query | 1 | 504873619 | replace into content_preference(userId,contentId,playRecordId,status,createTime) 
 values (587658,15308,1544691,0,now())  

This SQL statement inserts a record into content_preference. In the salve database, check whether there are records with id = 587658 and contentId = 15308 IN THE content_preference table. If yes, this statement has been executed.

Keep searching down, find the records of SQL statements that are not executed, and find the position.

At this point, you can determine the position of the binlog executed in the slave database, reset the binlog position of the slave database, start the slave, view the execution status, and running, as shown below:

mysql> stop slave;Query OK, 0 rows affected (0.00 sec)mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000274',MASTER_LOG_POS=504873114;Query OK, 0 rows affected (1.98 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Queueing master event to the relay log                  Master_Host: 172.17.128.15                  Master_User: xxx                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000274          Read_Master_Log_Pos: 693913486               Relay_Log_File: app3-relay-bin.000002                Relay_Log_Pos: 1819098        Relay_Master_Log_File: mysql-bin.000274             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

 

After the backup is completed, the data exported from the report is normal and the problem is solved.

Reflection

Mysql was restarted on the seventh day because the online service encountered an exception and could not be solved. The machine was restarted. At that time, the report database was not taken into account and the database backup was not checked after the restart, the problem is not found until the user uses it.

Mysql restarts unexpectedly, although the exception information and the binlog file and location of the backup are recorded after the next restart. If binlog SQL is being executed, but mysql closes unexpectedly, the position of the record is older, resulting in the next startup,

Some binlog logs are repeatedly executed. This problem cannot be said to be a mysql bug. This mechanism ensures that slave data is not lost, but you only need to manually find the posion.

Follow-up:

1. You can manually execute stop slave when the machine is restarted. After mysql is shut down normally and restarted, manually start slave and the synchronization will be normal.

2. Be careful when restarting the online system. After the restart, check whether the related modules are started. You can add mysql slave monitoring.