Mysqldump --single-transaction option

Question

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.

Lippe

Why don't you just look in the output file to see what it does? – Populate

Will you be accessing the table(s) during the dump? For write? – Margherita

Instead, see Percona's Xtradbdump. – Margherita

@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. – Lippe

From this AWS RDS guide, they also suggest using --single-transaction option. I just want to understand how it exectly works in details. – Lippe

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.

Margherita

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

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? – Misrepresent

@Misrepresent - 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. – Margherita

@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... :/ – Misrepresent

"Point in time recovery"? Perhaps gh-ost will help. – Margherita

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

This flag will NOT slow down tables or halt writes – Cepeda

@Cepeda - The "history lists" would grow to maintain a consistent state for the backup. That may slow down activity in general. Those lists will later need to be cleaned up. – Margherita

Answer 2

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.

Parentage

Answer 3

The --single-transaction option allows mysqldump to backup a database using a single transaction. This means that the entire backup process will be executed within a single transaction, ensuring that the database remains in a consistent state throughout the backup.

Using the --single-transaction option is recommended for databases that are frequently updated or have large amounts of data. This is because it ensures that the database remains in a consistent state throughout the backup, which is important if you need to restore the database later.

Using the --single-transaction and --skip-lock-tables options together can provide several benefits:

• consistent backup: the --single-transaction option ensures a consistent backup of your database by wrapping the entire mysqldump operation in a single transaction. This guarantees that the backup reflects a consistent state of your database at the time the transaction started
• avoids locking tables: the --skip-lock-tables option avoids locking tables during the backup process, which can prevent downtime and disruption to active operations

https://mysqldump.guru/mysqldump-single-transaction-flag.html#mysqldump-single-transaction-what-is-and-when-to-use-it

Cepeda