2

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.

1
  • 1
    What are the underlying tables? You need to handle myisam, and innodb differently. You probably need --single-transaction, or --lock-all-tables though. I hope you are using innodb if you really care about changes being atomic. – Zoredache Jun 25 '14 at 17:20
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

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:

InnoDB Architecture

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.

1
  • Are single-transaction and lock-all-tables mutually exclusive? Is one of them implied\default? – rumburak Jun 26 '14 at 22:48
1

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.

2
  • tnx. Is it also possible to do the same but split backup into separate files for each db ? – rumburak Jun 25 '14 at 17:30
  • It is possible with scripting. Either by putting the db's in read only mode and then backing them up individually and then putting them back to read-write or by extracting the backup for a database from the mysqldump output. – John Auld Jun 26 '14 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.