Mysqldump --single-transaction option
--single-transaction
says "no changes that occur to InnoDB tables during the dump will be included in the dump". So, effectively, the dump is a snapshot of the databases at the instant the dump started, regardless of how long the dump takes.
Because of the ways in which locks interact, this will slow down tables, possibly even halt writes. Think, for example, of running ALTER TABLE
or DROP TABLE
during the dump, together with other actions on the same table.
--lock-tables
is useful for MyISAM tables.
--opt
is usually advised. I think it is independent of the above options.
Related videos on Youtube
Artem Dolobanko
Updated on September 14, 2022Comments
-
Artem Dolobanko over 1 year
Can somebody explain how mysqldump
--single-transaction
actually works for transactional tables, like InnoDB? I've read official documentation but still haven't glue. Does it perform per table lock instead of global lock as--lock-tables
do? Also, mysqldump runs--opt
as a default option, which includes--lock-tables
, but they are mutually exclusive with--single-transaction
as described in documentation link above. Should I use--skip-opt
when using--single-transaction
in single mysqldump command?I need to dump InnoDB table with size of ~700 Gb and I'm looking the right command to achive this. Currently I use below one:
mysqldump -B my_db --quick --single-transaction --max_allowed_packet=512M --compress --order-by-primary
Thanks in advance.
-
jschultz410 about 3 yearsSchema changes on other connections during a mysqldump --single-transaction can cause the dump to fail or return incorrect data according to MySQL docs. If you want your dump to be successful + correct, then you need to somehow ensure that no one tries to change the schema during such dumps.