MySQL Database Backup and Recovery

MySQL Database Backup and Recovery

Even with proper management, unexpected events such as power outages or human errors can cause data loss, so regular backups are essential to minimize damage.

Why Backups Are Needed

Backups prevent loss of original data, allow recovery after accidental deletions, software bugs, hardware failures, or security breaches, and support various scenarios like point‑in‑time recovery, test environment setup, and data migration.

Backup Types

MySQL supports several backup categories based on whether the server must be offline:

Hot Backup (Online) : Performed while the server is running. Includes full, incremental, differential, logical, and raw file backups.

Cold Backup (Offline) : Requires stopping MySQL; typically a simple copy of physical files.

Warm Backup : Runs online but only allows read operations during the backup.

Storage‑engine support varies: MyISAM does not support hot backup, while InnoDB supports hot, warm, and cold backups.

Common Backup Tools

mysqldump – logical backup, works with all engines, supports hot backup for InnoDB.

cp / tar – physical file copy, suitable for cold backup.

lvm2 snapshot – filesystem‑level snapshot.

mysqlhotcopy – only for MyISAM.

xtrabackup (Percona) – powerful InnoDB hot backup with incremental support.

Hot Backup and Recovery

Hot backup can be logical (mysqldump, SELECT … INTO OUTFILE) or raw file based (xtrabackup).

Logical Backup with mysqldump

mysqldump connects to MySQL, extracts data, and writes INSERT statements. To restore, run:

mysql -uroot -p <backup.sql or, if already connected, source /path/backup.sql .

SELECT … INTO OUTFILE

Exports table data to a text file with custom delimiters. Example:

SELECT * FROM test.person INTO OUTFILE 'C:/person.txt' FIELDS TERMINATED BY '\、' OPTIONALLY ENCLOSED BY '"' LINES STARTING BY '\-' TERMINATED BY '\r\n';

This creates a file where each record starts with “-”, fields are separated by the Chinese comma “、”, and string values are quoted.

Raw File Backup with xtrabackup

xtrabackup performs fast, non‑blocking physical backups of InnoDB/XtraDB data files.

Cold Backup and Recovery

Cold backup (offline) simply copies the data directory after stopping MySQL (e.g., mysqladmin -uroot -proot shutdown ). Restoration involves replacing the directory and restarting the server.

Migration

Database migration combines backup and restore. Three scenarios are covered:

Same‑version MySQL migration – usually a mysqldump pipe: mysqldump -h host1 -u root -p1 --all-databases | mysql -h host2 -u root -p2

Different‑version MySQL migration – use mysqldump for both MyISAM and InnoDB tables.

Cross‑database migration – export with mysqldump or SELECT … INTO OUTFILE, then adapt SQL for the target system (e.g., Oracle, SQL Server).

Backup of Multiple Databases

Backup a single database:

mysqldump -u username -p dbname > filename.sql

Backup several databases:

mysqldump -u username -P --databases db1 db2 > filename.sql

Backup all databases:

mysqldump -u username -P --all-databases > filename.sql

Restoring a Database

Use the mysql client to execute the backup file:

mysql -u username -P [dbname] < filename.sql

If the dump contains CREATE DATABASE statements, the database name need not be specified.

Exporting Table Data

The SELECT … INTO OUTFILE syntax allows custom field and line delimiters. The secure_file_priv variable may restrict the export path; adjust it in my.ini or query it with show variables like '%secure%';

Example export command (shown above) demonstrates using custom delimiters and line prefixes.

Importing Exported Data

Data can be re‑imported with LOAD DATA INFILE after creating a matching table.

Example:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/file.txt' INTO TABLE test_db.tb_students_copy FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

The article concludes with a reminder that the content is reproduced from the original blog post.