Mysqldump --single-transaction option

18,372

--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.

Share:
18,372

Related videos on Youtube

Artem Dolobanko
Author by

Artem Dolobanko

Updated on September 14, 2022

Comments

  • Artem Dolobanko
    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
    jschultz410 about 3 years
    Schema 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.