Two databases synced backup

Question

I got a system that need two databases to work. I need a script to backup both databases at given point of time. I need databases to be in sync, so I need to be sure that backup does not allow changes in one of databases when the other is being backed up.

mysqldump db1 db2 > db1_db2.sql

Is the above code appropriate for this. Will it lock both databases for the time of backup, so I will get synced backup? What is the proper way to achieve this ? How would I do this, but with separated file for each database ?

EDIT:

Most of tables use InnoDB engine, but there are few less important tables that use archive engine.

Answer 1

Your best option is the following

mysqldump --single-transaction --databases db1 db2 > db1_db2.sql

The option --single-transaction creates a point-in-time window for all the data in the dump. The only restriction is that you do not execute any DDL

• ALTER TABLE
• CREATE TABLE
• DROP TABLE
• RENAME TABLE
• TRUNCATE TABLE
• Please see my DBA StackExchange post row locking within ACID transaction innodb to see all the statement that can intermittently harm transactions.

As long as you do not execute any of these for the duration of the mysqldump, db1 and db2 will be a perfect snapshot from the moment the mysqldump starts.

Your non-InnoDB tables should be just fine, provided you do nothing to them: no DDL, no INSERTs, no UPDATEs, no DELETEs.

UPDATE 2014-06-26 19:43 EDT

You asked

Are single-transaction and lock-all-tables mutually exclusive? Is one of them implied\default?

Right from the MySQL Documentation on --single-transaction

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

Yes, they are mutually exclusive.

Right from the MySQL Documentation on --lock-all-tables

Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.

Thus, lock-all-tables will disable --single-transaction

CAVEAT : Keep in mind that FLUSH TABLES WITH READ LOCK; and all other heavy-handed (all-or-nothing) locking mechanisms will not completely stop InnoDB from doing writes to the system tablespace. I wrote about before in the DBA StackExchange.

Here is a map of InnoDB for you to visual the other moving parts of InnoDB:

Please read How FLUSH TABLES WITH READ LOCK works with Innodb Tables (MySQL Performance Blog) about other ill effects of locking all tables against InnoDB.

Answer 2

Have a look at this URL for an overview of MySQL backups.

http://www.iheavy.com/2012/08/09/5-things-you-overlooked-with-mysql-dumps/

You need to add the following argument to lock both db's.

--lock-all-tables, -x

"Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump".

Your command should be as shown below.

mysqldump -x --databases db1 db2

You could also consider replicating your MySQL databases to a slave server and to backup from the slave.