Troubleshooting MySQL Master-Slave Replication

There are many factors that cause the MySQL master-slave replication structure to be interrupted or to report errors. Here are several common troubleshooting methods.

1. New - slave violates the uniqueness constraint
For example, the primary key constraint is violated:

Error'Duplicate entry '26' for key'PRIMARY''

Possible causes: The primary key [id=26] is written to the slave library first, and the primary key [id=26] is written after the master library, and then synchronized to the slave library, so the slave library cannot write.
There are many solutions:

For example, it can be solved by deleting the slave library [id=26].
Modify the data of slave database [id=26], skip this execution statement from the library:

stop slave;
set global sql_slave_skip_counter=1;
start slave;

The specific operation depends on the situation.

2. Delete - slave cannot find the record
The master successfully deleted a record or dropped a temporary table, the record or temporary table does not exist on the slave
The error is reported as follows:

Last_SQL_Error: Could not execute Delete_rows event on table hcy.t1;
Can't find record in't1',
Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log mysql-bin.000006, end_log_pos 254

Delete the non-existent record, the slave can skip this statement and execute the same as above.

3. Update - salve can't find the record
Compare master, manually make up for lost data, and slave skips this statement.

4. Binary file (binlog) is missing
The binary file was deleted by mistake, or the master-slave replication was interrupted for too long due to unknown reasons, and the binary file was cleared by the system. The binlog cannot be downloaded from the library.

Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:'Could not find first log file name in binary log index file'
The first three points are that the slave SQL execution thread reports an error, and the fourth point is that the slave IO thread reports an error and cannot download the binlog file.
There are two ways to deal with:

Ignore the missing files, check the master log file, and synchronize from the last one.

First stop syncing from the library
mysql> stop slave;

View the main library log file and location
mysql> show master logs;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.001530 | 1079859590 |
| mysql-bin.001531 | 1073742419 |
| mysql-bin.001532 | 1073743077 |
| mysql-bin.001533 | 1073743130 |
| mysql-bin.001534 | 1074996301 |
+------------------+------------+
5 rows in set (0.00 sec)

Back to the slave library, so that the log file and location correspond to the master library
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001530',MASTER_LOG_POS=1079859590;

Finally, start the slave library:
mysql> start slave;
show slave status\G;

5. The relay log is damaged
Solution: Find the synchronized binlog and POS points, and then re-synchronize, so that you can have a new relay daily value.

mysql> show slave status\G;
*************************** 1. row ******************** *******
              Master_Log_File: mysql-bin.000010
          Read_Master_Log_Pos: 1191
               Relay_Log_File: vm02-relay-bin.000005
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1593
                   Last_Error: Error initializing relay log position: I/O error reading the header from the binary log
                 Skip_Counter: 1
          Exec_Master_Log_Pos: 821


Slave_IO_Running: Receive master's binlog information
                   Master_Log_File
                   Read_Master_Log_Pos
Slave_SQL_Running: Perform write operation
                   Relay_Master_Log_File
                   Exec_Master_Log_Pos

The binlog and POS points to be executed shall prevail.

Relay_Master_Log_File: mysql-bin.000010
Exec_Master_Log_Pos: 821

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010',MASTER_LOG_POS=821;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

6.Ibbackup
All kinds of big tricks are used, but helpless slave data is lost too much, ibbackup (requires silver) should be your debut.
Ibbackup hot backup tool is paid. xtrabackup is free and functionally the same.
Ibbackup does not lock the table during the backup. A transaction is started during the backup (equivalent to taking a snapshot), and then a point is recorded. After that, the data changes are saved in the ibbackup_logfile file, and the data changed by the ibbackup_logfile is written to ibdata during recovery.
Ibbackup only backs up data (ibdata, .ibd ), table structure .frm does not back up.

7. The ultimate move-rebuild the slave