Our RSS Feeds
How to backup and import a MySQL InnoDB database
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.sqlYou 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.sqlor 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.
Interesting article. Can i translate it into russian and publish in my
digital magazine?
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.
Arjen is this a recent feature of InnoDB? In all the books around this
subject, I haven't seen any reference to this feature.
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.
I have taken a dump file from version 4 of mysql and tried to restore the
smae in version 3 mysql.
Abraham, looking at the documentation i read:
Thanks, Thanks, Thanks
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??