DB-유

Restoring a MySQL Database from a mysqldump File

· mysql, backup, restore

Everybody sets up mysqldump backups. Far fewer people have restored one while someone is standing behind them asking how long it will take. A dump is just a long SQL script, but the target database name, DEFINER clauses, max_allowed_packet and binary logging on the destination can turn a ten-minute job into an evening. I've lost a few evenings that way.

The examples assume a dump made with mysqldump --single-transaction --routines --triggers --events dbname | gzip > dbname.sql.gz on MySQL 5.6 or 5.7. Have free disk (the restored InnoDB files are often 3-5 times the size of the compressed dump) and an account with CREATE, DROP, INSERT, ALTER, CREATE ROUTINE, TRIGGER, plus SUPER if the dump has DEFINER clauses.

Look at the dump first

Never load a file blind. Check whether it was taken with --databases / --all-databases (it then has CREATE DATABASE and USE) or with a bare name (it doesn't). Look at the header:

zcat dbname.sql.gz | head -40
zcat dbname.sql.gz | grep -m5 -E '^(CREATE DATABASE|USE |-- Server version)'

The -- Server version line tells you which version produced the dump. If you see SET @@GLOBAL.GTID_PURGED= near the top, the source had GTIDs enabled; that matters when you load it.

Create the target database

If the dump has no CREATE DATABASE, create it yourself with the right character set:

CREATE DATABASE shop_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Restoring into a differently named database is a good way to test a backup without touching the live schema.

Load it

The basic form:

zcat dbname.sql.gz | mysql -u root -p shop_restore

I always add a few options: they speed things up, and they keep the restore from replicating to slaves when I don't want that. Forgetting the second part once is enough to remember it:

zcat dbname.sql.gz | mysql -u root -p \
  --init-command="SET SESSION sql_log_bin=0; SET SESSION foreign_key_checks=0; SET SESSION unique_checks=0;" \
  shop_restore

sql_log_bin=0 requires SUPER. If the dump contains a GTID_PURGED line and the target server already has its own GTID history, either remove the line or add --set-gtid-purged=OFF when taking the dump next time:

zcat dbname.sql.gz | sed '/^SET @@GLOBAL.GTID_PURGED/d;/^SET @@SESSION.SQL_LOG_BIN/d' | mysql -u root -p shop_restore

For a dump created with --all-databases where you only want one schema, --one-database works as long as the file contains USE statements:

zcat all.sql.gz | mysql -u root -p --one-database shop

Watching progress

A restore of many gigabytes is silent. pv helps:

pv dbname.sql.gz | zcat | mysql -u root -p shop_restore

Or from another session:

SHOW PROCESSLIST;
SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema='shop_restore';

Pulling out a single table

There's no built-in option for this. Extract the section for the table with sed; dumps place a -- Table structure for table \name`` comment before every table:

zcat dbname.sql.gz | sed -n '/^-- Table structure for table `orders`/,/^-- Table structure for table/p' > orders.sql
mysql -u root -p shop_restore < orders.sql

The extract still ends with the header comment of the next table, which is harmless because it is a comment.

Did it work?

Compare row counts and checksums against the source when it is still available:

SELECT COUNT(*) FROM shop_restore.orders;
CHECKSUM TABLE shop_restore.orders;
SHOW TRIGGERS FROM shop_restore;
SHOW PROCEDURE STATUS WHERE Db='shop_restore';

If --routines and --triggers were used, the procedure and trigger lists must match the source. Finally connect the application to the restored copy in a staging environment and run its own health checks.

Errors you will probably meet

  • ERROR 2006 (HY000) at line 3412: MySQL server has gone away – Usually a single INSERT larger than max_allowed_packet on the server. Raise it temporarily with SET GLOBAL max_allowed_packet=1073741824; and rerun; also check that the client side value is not smaller (mysql --max_allowed_packet=1G).
  • ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation – The dump contains DEFINER=\someuser`@`localhost`on views or routines and you are restoring as a non-SUPER account, or the definer account does not exist. Either restore as root or strip definers:sed -E 's/DEFINER=[^]+@[^]+//g'`.
  • ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes – The source used utf8mb4 with innodb_large_prefix and the target is 5.6 with default settings. Enable innodb_large_prefix=1 and innodb_file_format=Barracuda, or restore to 5.7 where this is the default.
  • ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty – Strip the GTID_PURGED line, as shown above.
  • ERROR 1050 (42S01): Table 'orders' already exists – The dump was taken with --skip-add-drop-table. Drop the target tables first or regenerate the dump.

A restore you have never tested is not a backup. Every few months I load the latest dump into a _restore schema on a staging box and time it.