musqldump (on slave) with --single-transaction option (for InnoDB)

 I got the following question from one of our customers.


”I have a question regarding mysqldump.

I am using the following scenario:

Replication: one master and one slave, default engine is InnoDB.

If I execute musqldump (on slave) with --single-transaction option (for InnoDB) do I have to stop the replication on slave or it does not metter?


 Thank you.”



When using --single-transaction the SQL statements are wrapped by START TRANSACTION and COMMIT. In this case when all tables are Innodb there is no actual need to stop the replication.


Please note the following regarding --single-transaction

1.       You have to make sure that all of the tables you dump are innodb

2.       It recommended to combine --quick with --single-transaction for large tables

3.       You cannot combine  --single-transaction --lock-tables because LOCK TABLE STATEMENT implicitly commit and open a new transaction

4.       No other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE


With all that said I think that the all point of backing up from slave is to avoid suck dilemmas and just STOP the SQL thread and backup in a sterile environment.