Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
This is a classic situation many encounter but don't really investigate. Thank you for asking, +1 for you, and Welcome to the DBA StackExchange. –  RolandoMySQLDBA Jul 26 '12 at 19:17
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
Thanks 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. –  vinhboy Jul 25 '12 at 5:59
1  
Do 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. –  randy melder Jul 25 '12 at 14:45
add comment

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.

share|improve this answer
add comment

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.

PROLOGUE

  • 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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.