MySQL Forums
Forum List  »  Backup

Does --single-transaction apply to all db'es being dumped with mysqldump simultaneously?
Posted by: Adam Flinton
Date: June 20, 2017 08:52AM

I have 3 databases which need to be kept in sync. They are on the same db server/mysql instance

At the moment I do a mysqldump with :

--databases db1 db2 db3 --quick --single-transaction

Someone has said that the single-transaction will only apply to one of the databases at a time (& the tables within) ergo the 3 databases might get out of sync during the dump. The only vague references I can find to this are:

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_databases
"--all-databases, -A
Dump all tables in all databases. This is the same as using the --databases option and naming all the databases on the command line."

&

"--single-transaction
This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications."

so in situation like this is the single transaction applicable to all 3 databases in the list or just the tables within a single database and thus given the time taken to dump the 3 databases might be out of sync?

If the latter then is there any known solution to dumping multiple db'es within a single transaction?

TIA

Options: ReplyQuote


Subject
Views
Written By
Posted
Does --single-transaction apply to all db'es being dumped with mysqldump simultaneously?
1205
June 20, 2017 08:52AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.