Is it okay to run mysqldump --single-transaction
on Slave DB without stopping replication (stop slave;
)? This is for regular daily backup so we can restore Master DB in case of a storage disaster. Most of the tables are InnoDB, only a couple of them are MyISAM.
Official documentation says that:
"You should stop replication on the slave before starting the dump
process to ensure that the dump contains a consistent set of data".
What does it mean consistent data
? Does it mean like latest data?
I know --single-transaction
means that it takes data from the time it was issued, so new further changes while the dump was happening will not be recorded to the dump. Am I correct?
Do not want to run the backup on master due to lag it creates on our application.
Best Answer
Performing a mysqldump creates a consistent and logical dump of the data which can be point-in-time recoverable.
There are two ways you can stop replication partway
METHOD #1 : mysqldump option --dump-slave
When you perform a mysqldump with --dump-slave, it will stop only the SQL thread. The I/O thread continues collecting binary log events from the Master. Note how this is explained in the MySQL Documentation:
METHOD 2 : Stop the SQL Thread Yourself
Did you know you can manually stop the SQL thread ? According to the MySQL Documentation on
STOP SLAVE;
, you can run the following:The I/O thread continues receiving binlog events from the Master.
After running
STOP SLAVE SQL_THREAD;
, you can then run your mysqldump. You could probably do an LVM snapshot or anything else to the data files on the Slave.When done with your backup, then run
or
This will reactivate the SQL thread.
GIVE IT A TRY !!!
Warning: Stopping the SQL thread, which terminates a local DB Connection, could make tables created with
CREATE TEMPORARY TABLE
disappear. If you are using statement based replication, please do additional research on your App to make sure transactions don't rollback when the SQL thread stops.WHAT ABOUT --single-transaction ???
You can mysqldump using --single-transaction ??? without --dump-slave. However, there are many commands that break running transactions. See the list of those commands in my old post Transactional DDL workflow for MySQL.
UPDATE 2018-02-13 13:07 EST
You just asked me :
These backups are for restoring the master DB in case of a storage disaster. Not for slave. So is --dump-slave still valid?
If you use the dump as is (without creating another slave with it and without attempting point-in-time recovery with replay of binary logs), then yes --dump-slave is OK..
Please keep in mind that if you launch a mysqldump at midnight, that's the dump's point-in-time.
Here is an additional benefit to using --dump-slave : the mysqldump will include the Master's log file and position between lines 23 and 25. That way, if you wish to create another slave, you have the Master's replication coordinates. If you wish to attempt point-in-time recovery and you can still retrieve the binary logs from the old Master, you can do so.