MySQL/MariaDB Backup and Restore¶

See Overview for how this section fits together, and Replication: Exporting Data for a New Replica for the replication-specific variants of mysqldump shown here.

Logical Backups (mysqldump)¶

Back up and compress every database¶

mysqldump -v -A | bzip2 -v -c > mysql-<server_name>-$(date +%Y.%m.%d.%H%M).sql.bz2

Back up every InnoDB database, another way¶

Useful when you want to exclude the mysql system database explicitly:

mysql -u root -e "SELECT DISTINCT table_schema FROM information_schema.tables WHERE engine='innodb' AND table_schema != 'mysql';" -s -N \
  | xargs mysqldump -u root --single-transaction --databases > all_databases.sql

Back up and compress specific databases¶

mysqldump -v --databases db_one db_two | bzip2 -v -c > mysql-backup-$(date +%Y.%m.%d.%H%M).sql.bz2

Non-default socket/port¶

mysqldump -S /var/lib/mysql/mysql.sock -P 3307 -h 127.0.0.1 --databases your_db > /tmp/your_db.sql

Back up a single table¶

mysqldump db_name table_name > table_name.sql

Back up a single record¶

mysqldump --where="some_field='some_value'" db_name table_name > single_record.sql

Back up SQL grants (for later restore)¶

mysql --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user" \
  | while read line; do mysql --skip-column-names -A -e "$line" | sed 's/$/;/g'; done > grants.sql

Restore them:

mysql < grants.sql

Export/import just the schema, no data¶

mysqldump --no-data --databases db_name > db_name-schema.sql
mysql --databases db_name < db_name-schema.sql

Useful mysqldump flags¶

--single-transaction — takes a consistent InnoDB snapshot without locking the whole database (does not work for MyISAM, which has no transactions to snapshot)
--opt — bundles a sensible default set (--quick, --add-drop-table, --add-locks, --extended-insert, --lock-tables); this is the default for mysqldump in most modern versions
--compact — the inverse: strips comments, drop-table statements, and other extras, useful for diffing schema dumps rather than restoring them
--lock-tables=false — needed if you hit Got error: 1016: Can't open file ... when using LOCK TABLES, typically from a storage engine or table that doesn't support the lock mysqldump wants to take

Encrypted backups¶

Not covered in depth here — pipe the dump through gpg or a similar tool before it hits disk if encryption at rest is a requirement, rather than storing a plaintext .sql file.

Backup Over SSH to a Remote Host¶

Useful when local disk space is tight:

mysqldump your_db | gzip -c | ssh user@remote_host 'cat > /path/to/backup.sql.gz'

A Dedicated Backup-Only User¶

Rather than running backups as an admin account, grant just what's needed:

GRANT LOCK TABLES, SELECT ON your_database.* TO 'backup'@'hostname' IDENTIFIED BY 'a_strong_password';

For more complete backup tooling (e.g. innobackupex/mariabackup-style physical backups) that also touch internal bookkeeping tables:

GRANT RELOAD ON *.* TO 'backup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.ibbackup_binlog_marker TO 'backup'@'localhost';
GRANT CREATE, INSERT, DROP ON mysql.backup_progress TO 'backup'@'localhost';
GRANT CREATE, INSERT, SELECT, DROP ON mysql.backup_history TO 'backup'@'localhost';
GRANT SUPER ON *.* TO 'backup'@'localhost';
GRANT CREATE TEMPORARY TABLES ON mysql.* TO 'backup'@'localhost';
GRANT REPLICATION CLIENT ON *.* TO 'backup'@'localhost';
-- Then, per database to be backed up:
GRANT LOCK TABLES, SELECT ON `your_database`.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Logical Restore¶

# From a bzip2-compressed dump
bzcat backup.sql.bz2 | mysql db_name

# From a gzip-compressed dump
zcat backup.sql.gz | mysql db_name

# From a plain SQL file
mysql db_name < backup.sql
# or
mysql < backup.sql   # if the dump includes CREATE DATABASE / USE statements

Restore into a different database name¶

# Take the dump WITHOUT --databases so it doesn't embed the original name
mysqldump old_db_name > old_db_name.sql
mysql new_db_name < old_db_name.sql

Restore a single table from within psql/mysql shell¶

SOURCE /full/path/to/table_name.sql;

Physical Backups (LVM Snapshot)¶

Useful for a near-instant, consistent backup of the whole data directory without a long table lock.

# Confirm where the data directory lives
mysqladmin variables | grep datadir
# datadir | /var/lib/mysql

# Confirm which logical volume backs it
df /var/lib/mysql
# e.g. /dev/mapper/vg0-mariadb

# Confirm the volume group has free space for a snapshot
vgdisplay vg0 | grep Free

-- Keep this session open; disconnecting releases the lock
FLUSH TABLES WITH READ LOCK;

# In a separate shell, while the lock is held:
lvcreate -L20G -s -n mariadb-backup /dev/vg0/mariadb

-- Back in the locked session:
UNLOCK TABLES;

# Mount the snapshot to verify or back it up
mkdir /mnt/snapshot
mount /dev/vg0/mariadb-backup /mnt/snapshot

# ... copy the mounted snapshot to your actual backup destination here ...

# Then release it
umount /mnt/snapshot
lvremove /dev/vg0/mariadb-backup

Replication — the --master-data and --dump-slave flags for seeding a new replica from a dump
Troubleshooting — the LOCK TABLES error and other dump-time failures