Mysqldump --single-transaction option

Question

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.

Answer 1

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