mysqldump on slave (without stopping replication)

Question

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.

score 5 · Answer 1 · edited Jun 15 '20 at 09:05

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:

This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.

--dump-slave causes the coordinates from the master to be used rather than those of the dumped server, as is done by the --master-data option. In addition, specfiying this option causes the --master-data option to be overridden, if used, and effectively ignored.

The option value is handled the same way as for --master-data (setting no value or 1 causes a CHANGE MASTER TO statement to be written to the dump, setting 2 causes the statement to be written but encased in SQL comments) and has the same effect as --master-data in terms of enabling or disabling other options and in how locking is handled.

This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.

In conjunction with --dump-slave, the --apply-slave-statements and --include-master-host-port options can also be used.

This option was added in MySQL 5.5.3.

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:

mysql> STOP SLAVE SQL_THREAD;

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

mysql> START SLAVE SQL_THREAD;

or

mysql> START SLAVE;

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.