How to troubleshoot a replication problem in MySQL?
As with any other aspect of database management, replication doesn’t always proceed as expected. The Troubleshooting Replication section of the MySQL Reference Manual instructs you to check for messages in your error log when something goes wrong with replication.
Why is MySQL replication not working in MariaDB?
If you have set up MySQL replication, you probably know this problem: sometimes there are invalid MySQL queries which cause the replication to not work anymore. In this short guide, I explain how you can repair the replication on the MySQL slave without the need to set it up from scratch again. This guide is for MySQL and MariaDB.
Why does MySQL replication need to be single threaded?
Since (traditional) MySQL replication is single threaded, any long running query in the replication may stall the whole ring. Also if any of the servers would go down, the ring would be broken and currently there is no failover software that can repair ring structures.
Why does MySQL replication stall the replication stream?
This has to do with the fact that a DDL change will most of the time lock a table and only release this lock once the DDL change has been applied. It even gets worse once you start replicating these DDL changes through MySQL replication, where it will in addition stall the replication stream.
How to check if a replication thread is connecting to master?
Use SHOW PROCESSLIST, find the I/O and SQL threads and check their State column to see what they display. See Section 17.2.3, “Replication Threads”. If the I/O thread state says Connecting to master , check the following: Verify the privileges for the replication user on the source.
What does show Master status on MySQL mean?
The first step is to run show master status or show master status\\G on the master database to get the correct values for the slave. The slave status above indicates the slave is connected to the master and awaiting log events. Synching the correct log file position should restore copying to the slave.