My mysql_slave got stuck on an error and built up to being 200,000+ Seconds_Behind_Master. Should I rebuild it and start from scratch, or let it catch up by itself?
-
2This is a classic situation many encounter but don't really investigate. Thank you for asking, +1 for you, and Welcome to the DBA StackExchange.– RolandoMySQLDBAJul 26, 2012 at 19:17
Add a comment
|
4 Answers
IMHO
Let it catch up.
The Seconds_Behind_Master reading is not what I rely on to see if catching up is possible or worth it. Don't be afraid at the number reported.
You have to look at the server's setting for expire_logs_days. If your slave is so far behind that the binary log file has been deleted, then you need to re-image.
-
1Thanks for the tip. I checked my expire_logs_days, and it's set to 10. I know that my replication has failed for at least 30 days now. Should I be concerned? I did a quick check and everything seems to be correct. Do you know a sure fire way for me to double check? Thanks again.– vinhboyJul 25, 2012 at 5:59
-
2Do a "show slave status\G" on the SLAVE. See what bin log file is current; something like bin.00041. That file should be found on the MASTER in the binary log folder. If it is not, then of course, your slave will not start. Now, you need to look at what's making your slave fail and fix the root cause of that problem. Look at the error message and do a deep dive.– randomxJul 25, 2012 at 14:45
-
I have an interesting replication issue related to this. I'm using master-master replication, and one of the servers ('main') is far more powerful than the other ('backup'). Replication from 'backup' to 'main' is instantaneous and Seconds_Behind_Master is always zero. The reverse, however, is not true. What happens is that, although the Master_Log_File and Position are exactly the same, Seconds_Behind_Master is usually a few days off. 'Backup' usually stops with a lot of duplicate keys; it requires deleting those, starting the slave again, and do that every day... Sep 10, 2016 at 12:59
-
... and yes, I really meant days and not seconds or minutes... it's a 6-digit figure. Eventually, after several weeks, it might catch up. What is interesting is that both the log file and the position are always in sync. My only guess is that 'main' knows that 'backup' is lagging behind and is only feeding it the amount of data it can safely process, even though it is several days old. In many cases, from the duplicate keys, I do actually remember the database entries (they are WordPress), and yes, they are a few days old. It's weird. Sep 10, 2016 at 13:01
Seconds_Behind_Master is really a double-edged sword
When Seconds_Behind_Master significantly increases, there are two scenarios to look into with regards to the output of SHOW SLAVE STATUS\G:
SCENARIO #1
If the Relay_Log_Space is under 1G, this is a tell-tale sign that the Slave has issues reading its Master's binlog entries through the IO Thread. The timestamps recorded is the relay logs would appear to be skewed because the Master may have recorded its binlog quickly, but gap between the Slave's current time and the timestamp in the its relay logs increase. Look for things like these:
- Server load on the Master
- Long running queries from the Master
- A series of DML statements that ran fine in parallel on Master but then serialize on Slave
- Network Latency over the Slave's IO Thread
SCENARIO #2
If the Relay_Log_Space starts exceeding 2G, immediately look at Slave_IO_Running and Slave_SQL_Running. Chances are, replication is simply broken. In most cases, if Replication breaks, the SQL Thread is dead and the IO Thread keeping running (Slave_IO_Running is Yes, Slave_SQL_Running is No), Because the IO Thread is still up, it can catch new SQL commands that the Master shipped over to the Slave's relay logs. That can grow. If left unchecked, it could grow to the point of filling up a disk. If your alerting scheme simply checks Seconds_Behind_Master being NULL, this should prompt you to quickly address the SQL error. If Seconds_Behind_Master is just numerically increasing, this will occur due to a series of DML statements that ran fine in parallel on Master but then serialize on Slave (just like in SCENARIO #1). In essence, SQL statements from the IO Thread are being collected faster than the SQL thread can process them.
EPILOGUE
- SCENARIO #1 requires more troubleshooting and root cause analysis.
- SCENARIO #2 is just an indication of a heavy-write Master sending its transactions to a Slave to process SQL one at time. In that case, you have one of three(3) choices
- Let replication catchup
- Shutdown mysql on the Slave, zap all data (except mysql folder) and do a fresh reload of MySQL
- Shutdown mysql on the Slave,
RESET MASTER;on the Master, rsync /var/lib/mysql from Master to Slave, start mysql on the Slave, setup replication - Use XtraBackup to perform live copy of the Master and restores to Slave
Here are my posts that may help with these steps
- MySQL Community Edition: Can I replicate my entire server with another server in one go? (Oct 14, 2011)
- What is the difference between XtraBackup and rsync? (Jun 17, 2011)
- How can I move a database from one server to another? (May 23, 2011)
- How do you re-sync the Master MySQL DB with Slave DB changes if the Master goes offline? (May 15, 2011)
answered Jul 26, 2012 at 19:14
You should watch seconds behind master periodically, it should decrease it's value.
Also Make sure that Slave I/O state should be Waiting for Master to send event and Slave I/O running and Slave SQL running shows status as YES.
Seconds_Behind_Master(SBM) will represent the difference between the timestamp of the latest transaction processed by the SQL Thread and the timestamp of the same transaction when it was processed on the master. If you see a 200,000+ SBM on the slave - it means that the transaction the slave executed now, was executed 200,000 seconds back on the master. However, it does not mean that the slave will need the same amount of time to catch up. It could be more or less depending the execution speed of the slave and actual number of transactions the master is ahead of. Check out this post:https://scalegrid.io/blog/mysql-tutorial-understanding-the-seconds-behind-master-value/ that explains how to understand various values of Seconds_Behind_Master
