CODEWITHSUNDEEP
mysqlmariadbdatabase-replication
glglgl
glglgl

Reputation: 91059

MySQL/MariaDB replication: Can I interrupt the process?

I have a replication setup here where data get replicated from a stationary host to a notebook.

Replication happens in two steps: the copying of the relay files, which is quite fast, and the application of the relay log events to the database, which tends to be slow.

Now my question: Suppose the slave has gotten all data from the master, but the "import process" still runs. Can I safely shut down the slave host and resume the still pending part of the replication without disturbing the process in any way?

So I am connected to the host, say "stop slave", shut down the notebook, go home and then "start slave" again without having a connection to the host. Can I expect the slave instance to resume the import process again?

Upvotes: 0

Views: 707

Answers (2)

Rick James
Rick James

Reputation: 142296

Your laptop is permanently a Slave to the other machine, correct? You are just breaking the network connection to the Master every night?

There are two threads on the Slave. The I/O thread is responsible for pulling data from the binlog on the Master and putting the stuff into the "relay-log" on the Slave. If (when) the network goes away, this thread repeatedly retries. There are settings that say how frequently and when to eventually give up. Consider tuning them.

The SQL thread is responsible for applying whatever is in the relay-log. Effectively, the SQL thread can run all the time. It's quite happy to "do nothing" when there is nothing to do.

The I/O thread creates new relay-log files as needed; the SQL thread deletes a log as it finishes with it.

I have dealt with dozens of slaves over the years; I don't recall any issue with network or power failures. You are essentially causing at least a network failure every night. If you are also powering down the laptop, do it gracefully. InnoDB (but not MyISAM) recovers nicely from power failures, but don't push your luck.

STOP/START SLAVE seems unnecessary, but won't hurt. Things should "resume" and eventually "catch up".

Your quote talks about the Master purging binlogs. Well, there is an issue here. The Master does not keep track of what Slaves exist, so it can't tell if your Slave is un-connected for longer than the Master is keeping the binlogs.

See expires_logs_days. Suggest you set that to higher than the number of vacation days you might ever take.

My experience with Slaves predates GTIDs, Galera, etc.; will you be using such?

Upvotes: 1

glglgl
glglgl

Reputation: 91059

I partially have found the answer to my question:

The MySQL documentation says:

If the slave stops before the SQL thread has executed all the fetched statements, the I/O thread has at least fetched everything so that a safe copy of the statements is stored locally in the slave's relay logs, ready for execution the next time that the slave starts. This enables the master server to purge its binary logs sooner because it no longer needs to wait for the slave to fetch their contents.

This indicates that it is perfectly possible to resume the import process (execution of the statements), however, it still remains unclear

  1. if I need to start slave before the described things happen and
  2. what happens if the slave doesn't find its master if I do start slave.

Upvotes: 0

Related Questions