16

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.

Artem Dolobanko
  • 2,089
  • 2
  • 19
  • 21
  • Why don't you just look in the output file to see what it does? – Barmar Jan 16 '17 at 19:25
  • Will you be accessing the table(s) during the dump? For write? – Rick James Jan 16 '17 at 23:16
  • Instead, see Percona's Xtradbdump. – Rick James Jan 16 '17 at 23:16
  • 1
    @RickJames I've mysql running on AWS RDS. There isn't way of using Percona's Xtradbdump as RDS doesn't provide dirrect access to database files. I create dump from RDS read-replica, so replication still works during this process. – Artem Dolobanko Jan 17 '17 at 10:21
  • 1
    From [this](http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html) AWS RDS guide, they also suggest using `--single-transaction` option. I just want to understand how it exectly works in details. – Artem Dolobanko Jan 17 '17 at 10:24

2 Answers2

21

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

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 5
    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. – jschultz410 Jan 14 '21 at 16:57
  • But except for the noted ALTER TABLE etc commands, the running mysqldump won't affect the other workings of the server? I.e. all read/writes can continue as usual? – BjornW Apr 04 '23 at 12:51
  • 1
    @BjornW - Yes and no. The dump requires a large amount of I/O. (If practical, write the output on another server.) There will ba a variety of locks that have some impact. My point is that activity may be slowed down, but not stopped. – Rick James Apr 04 '23 at 14:17
  • @RickJames thanks. my case is trying to duplicate an AWS RDS mysql, so I can't copy the binary files, thus left to mysqldump and rather not take it down. My hope is that the dump can also point to a binlog point in time, so I can shut the server down after the dump is restored on the new server, and then get the new server to replicate the old from that point in the log. This is so messy with mysql... :/ – BjornW Apr 19 '23 at 12:27
  • "Point in time recovery"? Perhaps `gh-ost` will help. – Rick James Apr 22 '23 at 06:26
  • Yeah I managed to solve it by extracting the binlog name and position from the RDS MySQL, started a mysqldump, and then I could setup an external replica. But RDS makes it difficult for you - the user you get doesn't have SUPER privs etc. – BjornW May 08 '23 at 13:37
4

The --single-transaction flag will start a transaction before running. Rather than lock the entire database, this will let mysqldump read the database in the current state at the time of the transaction, making for a consistent data dump.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

Dev Rathi
  • 703
  • 6
  • 8