Consistent backup of large DB --single-transaction 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?
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 &gt; 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.