DB-유

Backing Up MySQL Databases with mysqldump

· mysql, backup, linux

Every backup tool I have tried has been more complicated than mysqldump, and that's why it is still my default. It gives you a plain SQL file you can restore on the same or a newer version, read in a text editor, and gzip down to almost nothing. Even on bigger systems I keep a nightly logical dump next to the physical backup, because it's the easiest way to get one table back.

You need MySQL 5.6/5.7 or MariaDB 10.x with the client tools, and enough disk. A dump is roughly the size of data plus indexes rewritten as INSERT statements, before compression.

A dedicated backup user

Running backups as root works, but I use a restricted account since the password ends up in a config file:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'B4ckup-P@ss';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, RELOAD, EVENT, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

RELOAD is needed for --master-data and --flush-logs, PROCESS for --single-transaction on some versions to read InnoDB metadata, and EVENT if you use scheduled events.

Then store the credentials:

sudo tee /root/.my.cnf.backup > /dev/null <<'EOF'
[client]
user=backup
password=B4ckup-P@ss
EOF
sudo chmod 600 /root/.my.cnf.backup

Dumping one database consistently

For InnoDB tables the key option is --single-transaction. It opens a REPEATABLE READ transaction and dumps everything from that snapshot without locking the tables:

mysqldump --defaults-extra-file=/root/.my.cnf.backup \
  --single-transaction --quick --routines --triggers --events \
  --hex-blob shop | gzip > /backup/mysql/shop-$(date +%F).sql.gz
  • --quick streams rows instead of buffering the whole table in memory.
  • --routines --triggers --events include stored procedures, triggers and scheduled events, which are not part of the default output.
  • --hex-blob writes binary columns as hex literals, which avoids character set damage on restore.

If the database contains MyISAM tables, --single-transaction does not protect them. Use --lock-tables (the default) or --lock-all-tables instead and accept a short read lock.

Everything at once

mysqldump --defaults-extra-file=/root/.my.cnf.backup \
  --all-databases --single-transaction --quick --routines --triggers --events \
  --master-data=2 | gzip > /backup/mysql/all-$(date +%F).sql.gz

--master-data=2 writes the binary log file and position as a comment at the top of the dump, which is exactly what you need to set up a replica or to do point-in-time recovery later. It requires log_bin to be enabled on the server; otherwise the dump fails with mysqldump: Error: Binlogging on server not active.

Schedule and rotate

This small script keeps the last 14 days. I forgot the rotation once and found the backup disk full a month later, so the find line stays:

#!/bin/bash
set -e
DEST=/backup/mysql
mkdir -p "$DEST"
for db in $(mysql --defaults-extra-file=/root/.my.cnf.backup -N -e \
  "SHOW DATABASES" | grep -Ev '^(information_schema|performance_schema|sys)$'); do
  mysqldump --defaults-extra-file=/root/.my.cnf.backup --single-transaction --quick \
    --routines --triggers --events --hex-blob "$db" | gzip > "$DEST/$db-$(date +%F).sql.gz"
done
find "$DEST" -name '*.sql.gz' -mtime +14 -delete

Save it as /usr/local/bin/mysql-backup.sh, make it executable, and add a cron entry:

sudo chmod 700 /usr/local/bin/mysql-backup.sh
echo '30 2 * * * root /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1' | sudo tee /etc/cron.d/mysql-backup

I dump per database rather than with --all-databases. Single-database restores are trivial, and a corrupt table in one schema doesn't kill the whole job.

Test the restore

A backup you have never restored is a hope, not a backup. Restore into a scratch database:

mysql -e "CREATE DATABASE shop_restore"
zcat /backup/mysql/shop-$(date +%F).sql.gz | mysql shop_restore
mysql -e "SELECT COUNT(*) FROM shop_restore.orders"

Also check the file is not truncated: the last line of a complete dump reads -- Dump completed on ....

zcat /backup/mysql/shop-$(date +%F).sql.gz | tail -n 1

When it goes wrong

  • mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' to database 'shop' when using LOCK TABLES: the user lacks LOCK TABLES, or you are dumping with default locking. Grant the privilege or add --single-transaction.
  • mysqldump: Couldn't execute 'SHOW FUNCTION STATUS WHERE Db = 'shop'': Cannot load from mysql.proc. The table is probably corrupted (1548): the server was upgraded without running mysql_upgrade. Run it and retry.
  • Error 2013: Lost connection to MySQL server during query when dumping table: a large table exceeded net_read_timeout/net_write_timeout, or max_allowed_packet is too small for a big BLOB row. Add --max_allowed_packet=512M to the dump command and raise net_write_timeout on the server for the session.
  • Restoring a dump made with --all-databases onto a 5.7 server from a 5.6 source can fail on the mysql schema. Exclude system tables or restore user grants separately.

The catch is restore time: every row is re-inserted and every index rebuilt, so on big datasets add Percona XtraBackup. Below that, a nightly gzipped dump that you actually test now and then covers most of what goes wrong.