Troubleshooting Common MySQL Replication Errors

MySQL master-slave replication problems are common in connection exceptions, data inconsistencies, GTID or binlog errors, and replication delays. 1. Check whether the master-slave connection is normal, ensure that the network connection, permission pairs, and account passwords are correct; 2. Troubleshoot replication failures caused by inconsistencies in data, check error logs, skip errors if necessary and use tools to verify consistency; 3. Handle GTID or binlog issues, ensure that the master library has not cleaned the required transaction logs, and correctly configure the GTID mode; 4. Optimize replication delay, improve slave library performance, enable parallel replication, and reduce slave library load. When encountering problems, you should prioritize the SHOW SLAVE STATUS output and analyze the root cause of log location.

MySQL master-slave replication problems are actually quite common. Sometimes the data is out of sync, the delay is high, or the error is directly reported and interrupted, which makes it a headache to troubleshoot. But in most cases, these problems have fixed routines to solve. As long as you master a few key points, many errors can be quickly located and fixed.

The following situations basically cover the most important places you should check when encountering a copy exception.

1. Check whether the connection between the master and slave server is normal

If the slave library cannot connect to the main library, copying will naturally not be possible. This type of problem usually manifests as Last_IO_Error error message, such as:

• Can't connect to MySQL server on 'master_host'
• Access denied for user 'repl_user'@'slave_ip'

Handling suggestions:

• Make sure the master library allows slave library IP access (check firewall or security group settings)
• Confirm that the copy account permission is correct (at least REPLICATION SLAVE permission)
• Check whether the username, password, and port used from the library are configured correctly
• Try testing network connectivity with telnet master_ip 3306 in the library

If you find that it is a permission issue, you can reauthorize and refresh on the main library:

 GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

2. Data inconsistency leads to replication failure

This is a relatively hidden but common situation. For example, if you execute a delete statement in the main library, but the corresponding record does not exist in the slave library, it will cause a copy interruption and an error is reported similar:

Could not execute Delete_rows event on table...

Solution:

• View Last_SQL_Error in SHOW SLAVE STATUS\G

• If you confirm that it is a problem caused by a statement, you can skip this error:

   SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
  START SLAVE;

• A safer approach is to use tools to compare master-slave data consistency, such as pt-table-checksum and pt-table-sync

However, it should be noted that skipping errors is only a temporary solution. It is essential to figure out why data inconsistencies occur, such as whether there is manual write to the slave library, or whether the previous copy has been interrupted and not processed in time.

---

3. GTID or binlog related errors

In GTID mode, if the master library cannot find the transaction number requested by the slave library, the following error will appear:

The slave is connecting using CHANGE MASTER TO with a replication stream that is not present on the master...

This is usually because the GTID requested from the library has been cleaned up in the main library (binlog expires or is purged).

Solution:

• You can try to do a full backup and restore from the library again
• If mysqldump is used, remember to add --master-data=2 --single-transaction --dump-slave=1 parameter
• Check the expire_logs_days settings of the main library to avoid binlog being cleaned too early

In addition, it is also possible that you did not specify the GTID mode correctly when building the copy. At this time, you must confirm gtid_mode=ON and enforce_gtid_consistency = ON .

---

4. The replication delay is too high

Although it is not an error, if the replication delay is severe, it will affect the real-time data. It can be judged by Seconds_Behind_Master in SHOW SLAVE STATUS\G .

Possible reasons:

• The main library has a lot of pressure to write, and the performance of the slave library cannot keep up
• There is a slow query in serial execution from the library
• Row-based copy was used, the update volume was too large

Optimization suggestions:

• Upgrade the library hardware configuration, especially IO performance
• Turn on parallel replication (multi-threaded replication), adjust slave_parallel_workers and slave_parallel_type
• For read and write separation scenarios, appropriately reduce the query pressure borne by the library

If some tables are particularly hot, you can also consider dividing the database and detaching the table or introducing a cache layer to alleviate it.

---

Basically that's it. MySQL replication problems are caused by connections, permissions, data inconsistencies, or configurations. When you encounter problems, first look at the output of SHOW SLAVE STATUS , and then combine it with log analysis to find the direction.

The above is the detailed content of Troubleshooting Common MySQL Replication Errors. For more information, please follow other related articles on the PHP Chinese website!