How to backup and import a MySQL InnoDB database

How to backup and import a MySQL InnoDB database
« H E » email
posted Tuesday, 16 August 2005
Due to the way Innodb tables are structured, you cannot perform a hotback up of this database without using third party tools. Therefore you must take your database offline for a small period of time while you take the backup.
Backing up MySQL
MySQL backups are performed using the common mysqldump tool. This is a command line utility that ships with MySQL and you use at as follows:
% mysqldump --user=user --password=pass --opt DBNAME > dumpfile.sql

You may also need to specify the --host= parameter to force the hostname you are connecting to. This depends largely on how you've setup your user security. This will produce a text file with a series of INSERT/DROP/CREATE SQL statements that will recreate the database.
The --opt flag is very important. This is shorthand to pass in many flags at once; --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. This ensures that your database is in a good state while the backup is performed, including restricting all write access while the backup is in operation. Any locks placed will be automatically removed when this utility finishes.


Restoring a backup
Restoring a backup, or importing from an existing dump file can take a long time depending on the number of indexes and primary keys you have on each table. You can speed this process up dramatically by modifying your original dump file by surrounding it with the following:
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

.. your dump file ..

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
This turns off all the checks and auto-commits. This is a safe operation to do if you are fully restoring a database since the previous dump has already been validated for legal keys. If however you are importing, or adding to an existing database, then this step is not advisable.
You can then easily import the SQL file into MySQL using:

% mysql --user=user --password=pass DBNAME < dumpfile.sql

or using
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name
mysql> SET FOREIGN_KEY_CHECKS = 1;
mysql> COMMIT;
Backing up without taking MySQL offline
You cannot perform this with Innodb with the tools supplied with MySQL. However you can get around this limitation by running two MySQL database instances. You setup another MySQL database running on another machine, that is a replication server. Replication features come part of the standard MySQL installation. This replication server can be taken offline for as long as you wish while the backup is performed from the replicated server. Once online, it will resync itself up with any operations that were done while it was offline.

tags: innodb mysql backup


links: digg this del.icio.us technorati



comments (8)



1. Andriy left...
Friday, 19 August 2005 6:10 am
Interesting article. Can i translate it into russian and publish in my digital magazine?



2. Arjen Lentz left...
Monday, 22 August 2005 12:38 am
You don't want to lock InnoDB tables for backup... it's not necessary, either. InnoDB has MVCC (multi-versioned concurrency control), you can get a consistent snapshots without any locking. Just specify --single-transaction to mysqldump, and that's it. It simply starts a transaction, and the default isolation level of an InnoDB transaction is consistent read. Does exactly what you want.



3. Alan Williamson left...
Wednesday, 24 August 2005 6:56 pm :: http://alan.blog-city.com/
Arjen is this a recent feature of InnoDB? In all the books around this subject, I haven't seen any reference to this feature.

That said though, i think its more to do with the time it takes to do the backup. For large datasets you can lock up the table for a considerable amount of minutes, which in computer terms is forever! So still using the slave as the backup mechanism insures no slow down of the main application.



4. Arjen Lentz left...
Wednesday, 24 August 2005 11:40 pm
Nah InnoDB has always been multiversioning and able to do repeatable reads. This *implies* the ability to do a non-locking consistent backup, it follows logically.

The --single-transaction option to mysqldump is relatively new yes, I don't remember the exact version when it came in but it's no magic and doesn't rely on anything new in the MySQL server.

I'm all for using replication for backup and also as a source for sql dumps and binary backups. But I do feel it's important to realize that locking tables is not necessary and a backup from InnoDB can be done completely lock-free. Regardless of the size of the dataset. That's pretty cool, right?



5. abraham left...
Tuesday, 6 September 2005 5:54 am
I have taken a dump file from version 4 of mysql and tried to restore the smae in version 3 mysql.

Now the databaese is giving an error. Is it because of the version.

Is there any way i can take the dump file from verson 4 and put it in version 3 of mysql.

please reply thanks abraham jacob



6. Alan Williamson left...
Friday, 9 September 2005 10:38 am :: http://alan.blog-city.com/
Abraham, looking at the documentation i read:

For mysqldump utility

--compatible=name

Produce output that is compatible with other database systems or with older MySQL servers. The value of name can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, or no_field_options. To use several values, separate them by commas.

This looks to be what you are looking for.



7. Annerose left...
Sunday, 28 January 2007 2:06 pm
Thanks, Thanks, Thanks

http://www.heimarbeit-job-geld-verdienen-nebenjob.de


8. TC left...
Tuesday, 22 May 2007 11:46 pm
I'm using MySQL Backup Script ver 2.5 - http://sourceforge.net/projects/automysqlbackup and it seems to work fine with innodb -- anything I may be missing in terms of this??