Master MySQL Backups: mysqldump, mysqlbackup, mysqlhotcopy, and XtraBackup Explained

1. mysqldump

mysqldump creates logical backups by exporting database schemas and data as SQL statements. Common usages: mysqldump --all-databases > dump.sql (excludes INFORMATION_SCHEMA, performance_schema, sys)

mysqldump --databases db1 db2 db3 > dump.sql

mysqldump test > dump.sql

(single database without CREATE DATABASE statements)

mysqldump --user USER --password=PASS db_name table_name > table_name.sql

(single table)

Key options:

--master-data : records binlog position for replication setup.

--dump-slave : dumps data from a slave (MySQL 5.7+).

-d / --no-data : dumps only table definitions.

When using --master-data, mysqldump locks tables, which can block reads. To avoid locking, add --single-transaction (InnoDB only).

2. mysqlbackup

MySQL Enterprise Backup (commercial) provides physical backups with encryption, timestamps, and incremental options.

Example command:

mysqlbackup --user=root --password=PASS --databases='test' --encrypt-password=1 --with-timestamp --backup-dir=/u01/backup/ backup

Important flags:

--databases : list of databases to back up.

--with-timestamp : creates a timestamped backup directory.

--backup-dir : target directory.

--compress : compression level 1‑9.

Backup operations include backup, backup-and-apply-log, backup-to-image; restore operations include copy-back, copy-back-and-apply-log. Incremental backups use --incremental together with --incremental-base and --incremental-backup-dir.

3. mysqlhotcopy

mysqlhotcopy performs fast physical backups of MyISAM and ARCHIVE tables by locking tables, flushing them, and copying the files. Usage: mysqlhotcopy db_name [/path/to/new_directory] To back up a single table: mysqlhotcopy db_name table_name /path/to/new_directory It requires the perl script and works only on the server where the data directory resides.

4. Percona XtraBackup / innobackupex

XtraBackup is an open‑source hot‑backup tool for InnoDB/XtraDB (MySQL 5.1‑5.7). It cannot back up MyISAM tables directly; innobackupex wraps XtraBackup and can handle MyISAM with a read lock.

Full backup example: xtrabackup --backup --target-dir=/data/backup/base Incremental backup (based on previous full backup):

xtrabackup --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base

After a backup, run --prepare to apply logs:

xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base

Then apply the incremental backup:

xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base \ 
    --incremental-dir=/data/backup/inc1

Restoration is performed with innobackupex --copy-back /data/backup/base/ after ensuring the data directory is empty.

5. Direct directory copy

The simplest method is to stop MySQL and copy the entire data directory. This works quickly for MyISAM tables but is unsafe for InnoDB without a shutdown window.

Q&A Highlights

Q: Restoring an innobackupex backup from MySQL 5.5 on a 5.6 server fails with missing ibdata1. A: Run mysql_upgrade before starting the server to adjust metadata.

Q: Are these methods suitable for clustered environments? A: Yes; the underlying principle of copying data works for master‑slave, PXC, MHA, etc.

Q: What backup solution for >1 TB production data? A: Use mysqlbackup for speed, and consider sharding to distribute load.

In practice, most production environments combine Percona XtraBackup (or MySQL Enterprise Backup) with regular validation to ensure backup integrity.