12

For the first time since it was set up I need to reboot a read-only MySQL replication slave.

I found this article about downing a slave for maintenance (albeit he's just describing stopping the mysql daemon):

How to Safetly[sic] Restart Your MySQL Slave Server

In summary the procedure is:

In mysql client:

STOP SLAVE;
FLUSH TABLES;

From OS:

/etc/init.d/mysql stop

I would reboot at this point and then after the system has booted:

In mysql client (the mysql daemon is configured to start on boot):

START SLAVE;

Does this look about right? Is there anything else I should be doing?

1
  • 1
    Would you have a recommendation for automating this? In other words, if the server is ever rebooted, you'd like to have this happen: STOP SLAVE; FLUSH TABLES;
    – user3707
    Oct 3 '11 at 19:06
9

This looks right. The slave will pick up where it left off when it starts back up.

I will note that unless you supply the --skip-slave-start option, the slave should start automatically.

3
  • Just gave it a go and it works fine (also see my edit note on the question about the misspelling of "safely"). Thanks.
    – Kev
    Sep 8 '11 at 16:46
  • Looks right to me, too !!! +1 !!! Sep 8 '11 at 17:05
  • I keep having slave failures (key not found, duplicate key) when restarting my slave. I now see this, and realize I'm not stopping the slave with stop slave, before restarting the mysql daemon. Could that be the cause ?
    – nl-x
    Jun 24 '19 at 10:25
0

In our case, we're using AWS RDS Mysql Aurora (5.6). We are migrating to 5.7 and setup replication on a new cluster of 5.7 from a snapshot of 5.6.

The engine size needed to be updated, and this question and its answer became the catalyst for the solution to reboot without losing replication progress (at the point of issuing these commands the slave was hours behind master).

  1. on the slave call this procedure: CALL mysql.rds_stop_replication;
  2. in rds, make the changes necessary and reboot the instance
  3. on the slave startup replication: CALL mysql.rds_start_replication;

It is worth noting that after completing this process, the slave did later completely catch up as evidenced by show slave status Master_Log_File finally matching that of show master status, and the show slave status Seconds_Behind_Master achieving a zero value.

For comparison purposes, here are a few stats of this db:

  • VolumeBytesUsed: 9.208 Terrabytes
  • Tables in DB: 115
  • Queries per second (5 min avg): 5,000
2
  • This answer seems to be AWS RDS-specific; do you think it's useful on a question that asks about the on-prem MySQL?
    – mustaccio
    Nov 19 '20 at 14:29
  • @mustaccio i asked myself that as well. determining that it would be useful was a process of considering the facts. i was searching for an aws rds specific answer, and this post was (1) at the top of the search list and (2) enough info for me to do other leg work to solve it. i'm simply sharing the other leg work in case others can benefit from our situation. all mysql are not rds mysql, but all rds mysql are mysql.
    – WEBjuju
    Nov 19 '20 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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