MySql Replication - slave lagging behind master

Question

I have a master/slave replication on my MySql DB.

my slave DB was down for a few hours and is back up again (master was up all the time), when issuing show slave status I can see that the slave is X seconds behind the master.

the problem is that the slave dont seem to catch up with the master, the X seconds behind master dont seem to drop...

any ideas on how I can help the slave catch up?

Answer 1

Here is an idea

In order for you to know that MySQL is fully processing the SQL from the relay logs. Try the following:

STOP SLAVE IO_THREAD;

This will stop replication from downloading new entries from the master into its relay logs.

The other thread, known as the SQL thread, will continue processing the SQL statements it downloaded from the master.

When you run SHOW SLAVE STATUS\G, keep your eye on Exec_Master_Log_Pos. Run SHOW SLAVE STATUS\G again. If Exec_Master_Log_Pos does not move after a minute, you can go ahead run START SLAVE IO_THREAD;. This may reduce the number of Seconds_Behind_Master.

Other than that, there is really nothing you can do except to:

• Trust Replication
• Monitor Seconds_Behind_Master
• Monitor Exec_Master_Log_Pos
• Run SHOW PROCESSLIST;, take note of the SQL thread to see if it is processing long running queries.

BTW Keep in mind that when you run SHOW PROCESSLIST; with replication running, there should be two DB Connections whose user name is system user. One of those DB Connections will have the current SQL statement being processed by replication. As long as a different SQL statement is visible each time you run SHOW PROCESSLIST;, you can trust mysql is still replicating properly.