Consistent backup of large DB --single-transaction

Question

I need to mysqldump a large DB (330 GB) in order to set up a slave for replication.

• Does --single-transaction allow me to dump the entire DB without shutting mysql down and keeping it down for the duration?
• If I put a read lock on, can I get my bin information but then can release the lock and dump using --single-transaction so there is minimal downtime to users?

Answer 1

If you want the binlog file and position, just include --master-data.

According to the MySQL Documentation on mysqldump's --master-data option

Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. 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 server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.

If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1.

This option requires the RELOAD privilege and the binary log must be enabled.

The --master-data option automatically turns off --lock-tables. It also turns on --lock-all-tables, unless --single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for --single-transaction). In all cases, any action on logs happens at the exact moment of the dump.

It is also possible to set up a slave by dumping an existing slave of the master. In MySQL 5.5.3 and later, you can create such a dump using the --dump-slave option, which overrides --master-data and causes it to be ignored if both options are used.

Here is a sample script

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=2 --all-databases"
mysqldump --all-databases  | gzip > dumpfile.sql.gz

You said the the data is 330GB, so the gzip may be 30-40GB. No worries. If you run

less dumpfile.sql.gz

The CHANGE MASTER TO command with the log file and log position will be located on line 23.

Please keep one thing in mind: You cannot run any DDL against InnoDB tables during the mysqldump or else the tables being dumped after that DDL will no longer be point-in-time copies.

BTW there will be no downtime. Just make sure the binary log in the dump is still in existence on the Master when you load the Slave and setup replication.