networked day to day technical issues

8Apr/120

Fast MySQL database restore / import from full dump files

With MySQL Community Edition in most of the cases you have two ways of creating a full database backup:

  • using the command line utility mysqldump which works with both Myisam and Innodb tables, while the database server is running
  • shutting down the MySQL server and performing a copy of the full data dir in case of Innodb databases or just the database folder in the data dir in case of Innodb based databases

The full list of methods to do backups is available on Mysql's site.

While a binary backup will be the fastest to "restore" it has limitations, mainly that if using Innodb storage engine then you have to restore the whole MySQL instance and not just the specific database; and that you can safely restore on the same Mysql version (though it may work on newer ones too).
On the other hand a db dump created using mysqldump will allow you to restore only the needed database (or all of them if you want to and you have a full dump of all databases), it will allow you to restore on different Mysql versions as long as the features required are supported (if restoring on an older MySQL version) and it is also the most disk space efficient way to restore (see how MySQL manages disk space for Innodb tables)

The problem lies in the details and when restoring a large dump created with mysqldump you can disover it can take even days if the dump file is large (i've seen it for a 30GB dump file which isn't that large). The problem lies in the fact that the dump file is a series of SQL statements and each INSERT will trigger and index update.

To speed as much as possible a dump file import do as much as possible from the following list:

    Filed under: Linux, mysql Continue reading
    25Feb/120

    mysql backups using mysqldump

    I keep encountering all sort of bad attempts or at least not optimal attempts at doing Mysql full db backups. While it looks like a trivial task using the mysqldump tool, there are several things one needs to take into account:

    • if you backup all databases into a file (--all-databases) then when you will need to restore only one database from the backup you will be in trouble as in order to restore it you need to restore all databases on a staging server and afterward dump just the needed one or remove all of the rest of databases but with the second approach you still have the "mysql" database changed; or use some tool which can extract from a full dbdump just the needed one (it's basically a text file so you could scrip around it). Update: you can use mysql (mysql -D db_name -o < dump_file.sql) to restore a particular db from a dump done with --all-databases , just take care to have db_name created before attempting the restore
    • if you backup separately each database to it's own dump file then you will quickly learn that you should have also backed up the usernames and passwords which are allowed to access/modify the database
    • if you run a cron script each night which creates the dump(s) and overwrites the previous night's backup file(s) then you may learn it the hard way that a 0 bytes dump or incomplete dump will leave you not only without today's backup but also possibly without yesterday's valid backup (in case it wasn't bad too). So in this case the advice is keep more then tonight's and/or yesterday's backup. I generally keep at least 7 of them if done nightly as generally by the time someone realizes they need a backup a day might have passed. Also it is recommended to have a real backup infrastructure in place (with the associated retention policy)
    Filed under: Linux, mysql Continue reading