Comprehensive Guide to MySQL Backup Types, Tools, and Procedures

Backup Types

MySQL backups can be categorized by server state and by result object. By server state there are hot backups (read/write unaffected), warm backups (read‑only), and cold backups (offline, both read and write stopped). By result object there are physical backups (copying data files) and logical backups (exporting data to text files).

Backup Tools

MySQL Built‑in Tools

mysqldump – logical backup tool supporting all storage engines; MyISAM behaves as a warm backup, InnoDB as a hot backup; moderate backup speed, very slow restore but highly flexible.

mysqlhotcopy – physical backup tool for MyISAM only; essentially a cold backup with fast speed.

File‑System Backup Tools

cp – simple cold physical copy; fast backup and restore but low flexibility and requires global read lock or server stop.

LVM – snapshot‑based near‑hot backup; works with all engines, very fast backup and restore, minimal impact on the running server, but snapshot creation still briefly locks tables and cannot snapshot multiple logical volumes simultaneously.

Commercial Tools

ibbackup – commercial solution; MyISAM warm, InnoDB hot, fast backup/restore, priced at $5,000 per server.

MySQL Enterprise Backup (mysqlbackup) – Oracle‑provided enterprise‑grade backup software.

Open‑Source Tools

xtrabackup – open‑source alternative to ibbackup; MyISAM warm, InnoDB hot.

mydumper – multi‑threaded logical dump tool offering higher speed than mysqldump.

Backup Introduction

Based on LVM Snapshot Backup

LVM Principle

When source data changes, the copy‑on‑write (COW) layer records the new blocks. Reads from the snapshot retrieve original data if unchanged, otherwise they read from COW, ensuring the snapshot reflects the state at creation time.

Advantages

Almost hot backup; no need to stop MySQL, only a brief lock during snapshot creation.

Fast backup speed.

No additional software required.

Disadvantages

Lock duration depends on the longest running query before the lock.

Complexity increases if data spans multiple physical volumes.

Operation Steps

mysql> FLUSH TABLES WITH READ LOCK;
2. Flush binary logs for point‑in‑time recovery:
   mysql> FLUSH LOGS;
3. Create snapshot volume:
   lvcreate –L 1G –s –n data-snap –p –r /opt/mysql/3306/data
4. Release read lock:
   UNLOCK TABLES;
5. Mount snapshot and back up:
   mount –r /dev/myvg/data-snap /mnt/snap
   (then archive /mnt/snap)

mysqldump

mysqldump is a client utility that dumps databases into SQL statements for recreation. It supports standard SQL output and delimiter‑separated files, and can copy data between MySQL instances.

Advantages

Built‑in, easy to use, suitable for small datasets.

Disadvantages

Slow restore for large data sets because it replays SQL statements; not recommended for massive databases where XtraBackup is preferred.

Principle Analysis

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like '%general%';
+------------------+------------------------------------+
| Variable_name    | Value                              |
+------------------+------------------------------------+
| general_log      | ON                                 |
| general_log_file | /opt/mysqldata/general.log         |
+------------------+------------------------------------+
2 rows in set (0.01 sec)

mysql> \q
Bye

Typical dump command:

mysqldump -S /opt/mysqldata/mysql.sock -p --single-transaction --master-data=2 db_test > db_test.sql

General Log Examination

The general log shows the sequence of commands executed during the dump, such as FLUSH TABLES WITH READ LOCK , transaction isolation settings, and the use of START TRANSACTION WITH CONSISTENT SNAPSHOT to obtain a consistent view of InnoDB tables.

Xtrabackup

Advantages

Fast physical backup with reliable consistency.

No table locking; transactions continue uninterrupted.

Compression and streaming reduce storage and bandwidth usage.

Automatic backup verification and rapid restore.

Low impact on server performance.

Backup Procedure Overview

Xtrabackup backup process:
1. Start a background thread to monitor MySQL redo logs.
2. Copy InnoDB data files and system tablespace (ibdata*).
3. Execute FLUSH TABLES WITH READ LOCK, then copy .frm, .MYI, .MYD files.
4. Unlock tables and stop the redo‑log monitoring thread.
5. Write backup metadata and finish.

Incremental Backup

Incremental backups compare the LSN of the last full backup with the current LSN, copying only changes since that point.

A redo‑log monitoring thread records any LSN growth during the incremental run.

Recruitment Notice

The Zero technology team in Hangzhou is hiring; the team works on cloud‑native, blockchain, AI, low‑code platforms, middleware, big data, and more. Interested candidates can contact zcy‑tc@cai‑inc.com.