Master MySQL/MariaDB Backup & Recovery: Methods, Tools, and Step‑by‑Step Guide

Introduction

Databases store critical enterprise data; backup is essential to prevent loss. This article explains several backup methods for MySQL/MariaDB.

Backup Types

Full backup (entire database), partial backup (specific tables), incremental backup (changes since last backup), differential backup (changes since last full backup), hot/warm/cold backup, and physical vs. logical backup.

Backup Objects

Data files, stored procedures, functions, triggers, OS configuration files (e.g., crontab scripts), replication settings, and binary log files.

Backup Tools

Logical: mysqldump (supports all engines, hot backup for InnoDB). Physical: file‑system tools like cp, tar (cold backup). LVM snapshots (near‑hot physical backup). mysqlhotcopy (cold, MyISAM only). Percona xtrabackup (hot, InnoDB/XtraDB).

Backup Schemes

1. mysqldump + binlog : Full logical dump plus binary log for incremental changes. 2. LVM snapshot + binlog : Near‑hot physical backup using LVM snapshot and binary log. 3. XtraBackup : Hot backup supporting full and incremental for InnoDB.

mysqldump + binlog Example

Command syntax: mysqldump [OPTIONS] database [tables] – backup a single database or specific tables. mysqldump [OPTIONS] --databases DB1 [DB2 ...] – backup multiple databases. mysqldump [OPTIONS] --all-databases – backup all databases. Important options: -x, --lock-all-tables (lock all tables) -l, --lock-tables (lock selected tables) --single-transaction (start a large transaction for hot backup) -R, --routines (include stored procedures/functions) --triggers (include triggers) --master-data=1 (record CHANGE MASTER TO statement) During restoration, disable binary logging and other connections:

set session sql_log_bin=0;

LVM Snapshot + Binlog Procedure

1. Flush tables with read lock: flush tables with read lock; 2. Flush logs: flush logs; 3. Record binlog position: show master status; 4. Create snapshot: lvcreate -s -L 100M -n mydata-snap /dev/myvg/mydata -p r 5. Unlock tables: unlock tables; 6. Mount snapshot and copy data:

mkdir /snap; mount /dev/myvg/mydata-snap /snap; cp -a /snap /backup/

7. Backup binlog for incremental restore:

mysqlbinlog --start-position=245 --stop-position=534 /mydata/data/mysql-bin.000016 > /backup/binlog/binlog-$(date +%F_%T).sql

8. Restore: stop MySQL, replace data directory with snapshot copy, start MySQL, apply saved binlog.

Percona XtraBackup

Features: fast, hot backup for InnoDB/XtraDB, no service interruption, compression, automatic verification, quick restore. Installation:

# yum install percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm -y

Create minimal‑privilege backup user. Full backup command: # innobackupex --user=backupuser --password=****** /backup/ Output shows backup directory and binlog position. Incremental backup command (after first full backup):

# innobackupex --incremental /backup/ --incremental-basedir=/backup/2015-05-21_21-55-08/

Apply logs for recovery: # innobackupex --apply-log --redo-only BASE-DIR Apply each incremental in order:

# innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1

Final restore: # innobackupex --copy-back /backup/2015-05-21_21-55-08/ Set ownership and start MySQL.

Recovery Notes

After backup, data may be inconsistent; use --apply-log --redo-only on base and each incremental backup to roll forward committed transactions and roll back uncommitted ones, then copy back the data files, adjust ownership, and start the server.

Conclusion

Select the method that fits your environment; keep data files and binary logs on separate storage. The three methods each have strengths, and the guide provides the commands needed for reliable backup and restoration.