13

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?

Improve this question
1
  • 2
    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, 2011 at 19:06

2 Answers 2

Reset to default
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.

Improve this answer
4
  • 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, 2011 at 16:46
  • Looks right to me, too !!! +1 !!!
    – RolandoMySQLDBA
    Sep 8, 2011 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, 2019 at 10:25
  • Why is any of this necessary? If a MySQL process is acting as a slave, why doesn't the shutdown process do the necessary things? This seems prone to issue to require this.
    – Ryan Griffith
    Apr 5 at 21:35
1

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
Improve this answer
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, 2020 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, 2020 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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