Master MySQL Backup & Recovery: Mysqldump, LVM Snapshots, and Xtrabackup Explained

Backup and Recovery Overview

To ensure data safety, regular backups are required. This article introduces three common MySQL backup and recovery methods: using mysqldump with binary logs, using LVM snapshots with binary logs, and using Xtrabackup.

1. Experimental Environment

System: CentOS 6.4 x64 MySQL version: 5.5.33

2. Mysqldump + Binary Log

2.1 Concept

Mysqldump creates a logical backup by exporting table structures and data into a text file containing CREATE and INSERT statements. It can back up the whole server or specific databases, tables, rows, stored procedures, functions, and triggers, and records the binary log position.

2.2 Backup Strategy

Full backup with mysqldump plus incremental backup with binary logs.

2.3 Procedure

(1) Full backup

# mysqldump -uroot -pmypass --lock-all-tables --master-data=2 --events --routines --all-databases > /zhao/database_`date +%F`.sql

--lock-all-tables applies a read lock to all tables; --master-data=2 records the current binary log file and position in the dump; --events backs up scheduled events; --routines backs up stored procedures and functions; --all-databases backs up all databases.

(2) Binary full backup

# mysqlbinlog mysql-bin.000001 > /zhao/binlog_`date +%F`.sql

(3) Binary incremental backup

# mysqlbinlog --start-position=14203 --stop-position=14527 mysql-bin.000001 > /zhao/binlog_`date +%F_%H`.sql

--start-position=14203 is the position after the last full backup; --stop-position=14527 is the position of the most recent binary event.

Simulate database corruption by dropping the database and then restore using the full dump and binary logs:

# mysql -uroot -pmypass < /zhao/database_2013-08-13.sql

# mysql -uroot -pmypass < /zhao/binlog_2013-08-13_19.sql

3. LVM Snapshot + Binary Log

3.1 Concept

LVM snapshots require MySQL data to reside on a logical volume. A global read lock is taken, a snapshot is created, then the lock is released.

3.2 Backup Strategy

Full backup via LVM snapshot plus incremental binary‑log backup.

3.3 Prerequisites

Create a logical volume, mount it, and configure MySQL to use the new data directory (e.g., /mydata/data).

3.4 Procedure

(1) Verify that data files and binary logs are on the same volume.

# ls /mydata/data/

(2) Apply a global read lock and flush logs.

# mysql -e "FLUSH TABLES WITH READ LOCK;"

# mysql -e "FLUSH LOGS;"

(3) Create the snapshot.

# lvcreate -L 100M -s -p r -n mydata-lvm /dev/vg1/mydata

(4) Release the lock.

# mysql -e "UNLOCK TABLES;"

(5) Copy the snapshot data to a backup directory.

# cp -a * /zhao/lvmback-2013-08-14/

(6) Perform binary incremental backup as described in section 2 and restore using the same steps.

4. Xtrabackup

4.1 Advantages

Hot backup that supports full and incremental backups, point‑in‑time recovery, no impact on running transactions, network transfer, compression, automatic verification, and fast restore. Works best with InnoDB; MyISAM is limited to warm backups.

4.2 Installation

# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.x86_64.rpm

If installation fails, install the dependency:

# yum -y install perl-DBD-mysql

4.3 Full Backup

# innobackupex --user=root --password=mypass /innobackup/

The command creates a timestamped backup directory (e.g., /innobackup/2013-08-14_07-04-49/) and records the binary‑log file and position.

4.4 Prepare Backup

# innobackupex --apply-log /innobackup/2013-08-14_07-04-49/

This makes the backup consistent by rolling forward committed transactions and rolling back uncommitted ones.

4.5 Simulate Crash and Restore

Stop MySQL and delete the data directory:

# service mysqld stop

# rm -rf /mydata/data/*

Copy the backup files back:

# innobackupex --copy-back /innobackup/2013-08-14_07-04-49/

Set correct ownership and start MySQL:

# chown -R mysql:mysql /mydata/data/

# service mysqld start

4.6 Incremental Backup

# innobackupex --incremental /innobackup --incremental-basedir=/innobackup/2013-08-14_08-14-12/ --user=root --password=mypass

Repeat the command for subsequent increments, changing --incremental-basedir to point to the previous incremental backup.

4.7 Apply Incremental Backups

First apply the full backup with redo‑only, then merge each incremental backup:

# innobackupex --apply-log --redo-only /innobackup/2013-08-14_08-14-12/ --incremental-dir=/innobackup/2013-08-14_08-29-05/

# innobackupex --apply-log --redo-only /innobackup/2013-08-14_08-14-12/ --incremental-dir=/innobackup/2013-08-14_09-08-39/

After all increments are merged, copy back and start MySQL as in section 4.5.

4.8 Recover Using Binary Logs

Export the binary log from the last incremental backup:

# mysqlbinlog --start-position=780 mysql-bin.000001 > all.sql

Import the statements with binary logging disabled, then re‑enable it:

# mysql -uroot -pmypass

SET SQL_LOG_BIN=0;

source /innobackup/all.sql;

SET SQL_LOG_BIN=1;

Conclusion

All three backup and recovery approaches rely on MySQL binary logs, underscoring their critical role in data protection and point‑in‑time restoration.