Can somebody explain how mysqldump --single-transaction actually works for transactional tables, like InnoDB? I've read the official documentation but still have no clue. Does it perform per table lock instead of global lock as --lock-tables does? Also, mysqldump runs --opt as a default option, which includes --lock-tables, but they are mutually exclusive with --single-transaction as described in the documentation link above. Should I use --skip-opt when using --single-transaction in a single mysqldump command?
I need to dump InnoDB table with size of ~700 Gb and I'm looking for the right command to achieve this. Currently I use the below one:
mysqldump -B my_db --quick --single-transaction --max-allowed-packet=512M --compress --order-by-primary
Thanks in advance.
--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.