MySQL: The Complete Guide to MySQL Backup and Recovery

Last updated: 2026-08-26

The hard drive on Alice’s company server suddenly failed, causing the database to crash completely. With no backups, three years’ worth of customer data, order records, and financial information were lost forever. This disaster served as a wake-up call for her, prompting her to establish a comprehensive backup system: daily full mysqldump backups, continuous incremental binlog backups, weekly off-site data transfers, and monthly recovery drills. Since then, even if she encounters another hardware failure, she can fully restore all data within 30 minutes.

In this lesson, you will learn:

• 5 Modes of mysqldump Logical Backups (Full/Single Database/Single Table/Structure Only/Data Only)
• How to Perform Recovery Using the MySQL Command and the SOURCE Statement
• SELECT INTO OUTFILE export and LOAD DATA INFILE import
• The Complete Process of Binary Log (binlog) and Point-in-Time Recovery (PITR)
• XtraBackup: Principles of Physical Backups and Backup Strategy Design

flowchart TD
    A[Daily Full Backup<br/>mysqldump --single-transaction] --> B[binlog Ongoing Record<br/>Real-Time Incremental]
    B --> C[Weekly Remote Data Transfer<br/>rsync/scp To Remote]
    C --> D[Monthly Recovery Drills<br/>Verify Backup Availability]
    D -->|Drill Passed| A
    D -->|Identifying Problems| E[Adjust the Backup Strategy]
    E --> A

flowchart TD
    A[Daily Full Backup<br/>mysqldump --single-transaction] --> B[binlog Ongoing Record<br/>Real-Time Incremental]
    B --> C[Weekly Remote Data Transfer<br/>rsync/scp To Remote]
    C --> D[Monthly Recovery Drills<br/>Verify Backup Availability]
    D -->|Drill Passed| A
    D -->|Identifying Problems| E[Adjust the Backup Strategy]
    E --> A

1. Story: The Price of Not Having a Backup

TechFlow, the company where Alice works, operates a MySQL database for its e-commerce platform that stores three years’ worth of customer orders and account information. The operations team had never established a formal backup process, relying solely on a RAID array as a “safety net.” One Friday night, a failure in the data center’s air conditioning system caused the servers to overheat, resulting in the simultaneous failure of two hard drives and the collapse of the RAID array. All data—customer information, transaction records, and inventory data—was lost. The company spent three months attempting data recovery but managed to retrieve less than 10% of the data. The direct financial loss exceeded 500,000 USD, and the company also lost a great deal of customer trust.

After this painful lesson, Alice took the lead in establishing a comprehensive backup system: automatic full backups every day at midnight, real-time incremental backups via binlog, weekly off-site synchronization, and monthly recovery drills. Six months later, the server failed again, but this time she completed a full recovery within 30 minutes, with virtually no business interruption.

2. Logical Backups and Physical Backups

(1) Overview of Logical Backups

A logical backup exports data from a database into an SQL text file containing statements such as CREATE TABLE and INSERT. To restore the data, simply re-execute these SQL statements. A representative tool is mysqldump.

Advantages: High readability, cross-version compatibility, and the ability to selectively back up individual databases and tables.

Disadvantages: Slow speed; both backup and restore require the involvement of the MySQL process; time-consuming for large databases.

(2) Overview of Physical Backups

A physical backup directly copies the database’s underlying data files (.ibd, .frm, etc.); to restore, simply copy the files back to the data directory. A representative tool is XtraBackup.

Advantages: Fast, no table locking (InnoDB), suitable for large databases.

Disadvantages: Not human-readable, limited cross-platform compatibility, and requires downtime or the use of specialized tools to maintain consistency.

3. A Detailed Explanation of mysqldump Logical Backups

(1) Full Backup

A full backup exports all data from every database; it is the most basic and safest backup method.

mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

The --single-transaction parameter uses consistency snapshots for reads on InnoDB tables and does not lock the table. MyISAM tables are still locked.

(2) Single-Database and Single-Table Backups

# Backing Up a Single Database
mysqldump -u root -p --single-transaction mydb > mydb_backup.sql

# Back up the specified table
mysqldump -u root -p --single-transaction mydb users orders > tables_backup.sql

(3) Separation of Structure and Data

# Back up only the table structure
mysqldump -u root -p --no-data mydb > mydb_schema.sql

# Back up only the data
mysqldump -u root -p --no-create-info mydb > mydb_data.sql

▶ Example: Five mysqldump Backup Modes

Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.

# Pattern1：Full Backup（All Libraries）
mysqldump -u root -p --all-databases \
  --single-transaction --routines --triggers \
  > full_backup_$(date +%Y%m%d).sql

# Pattern2：Single-Database Backup
mysqldump -u root -p --single-transaction mydb > mydb.sql

# Pattern3：Single-Table Backup
mysqldump -u root -p --single-transaction mydb users > users.sql

# Pattern4：Structure Only
mysqldump -u root -p --no-data mydb > schema.sql

# Pattern5：Data Only
mysqldump -u root -p --no-create-info mydb > data.sql

Output:

Output displayed

4. Restore the Database

(1) MySQL Command Recovery

Restore data from a backup SQL file to a specified database:

mysql -u root -p mydb < mydb_backup.sql

Restore a compressed backup file:

gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

(2) Restoring with the SOURCE Command

Restore using the SOURCE command within the MySQL client:

USE mydb;
SOURCE /var/backups/mysql/mydb_backup.sql;

▶ Example: Recovery Process

# Steps1：Create the target database
mysql -u root -p -e "CREATE DATABASE mydb_restore;"

# Steps2：Restore Data
mysql -u root -p mydb_restore < mydb_backup.sql

# Steps3：Number of rows to verify
mysql -u root -p -e "SELECT COUNT(*) FROM mydb_restore.users;"

Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Welcome to the MySQL monitor.  Commands end with ; or \g.

▶ Example: Restoring a Compressed Backup

# Restore gzip Compressed Backup
gunzip < /backup/mydb_20260703.sql.gz | mysql -u root -p mydb

# Restore and display progress
pv /backup/mydb_20260703.sql.gz | gunzip | mysql -u root -p mydb

Output:

Output displayed

5. Data Export and Import

(1) SELECT INTO OUTFILE

Export the query results to a server-side CSV file:

SELECT id, name, email
FROM users
WHERE status = 'active'
INTO OUTFILE '/tmp/active_users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note: The path for INTO OUTFILE is the path on the MySQL server, not the client path. The MySQL process must have write permissions for this path, and the secure_file_priv variable must allow access to this directory.

(2) LOAD DATA INFILE

Import data from a server-side CSV file into a table:

LOAD DATA INFILE '/tmp/active_users.csv'
INTO TABLE users_copy
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

▶ Example: The Complete Process for Exporting and Importing CSV Files

Output:

---------+-------------+--------------+--------------------
order_id | customer_id | total_amount | order_date         
---------+-------------+--------------+--------------------
1        | 1           | 25.00        | 2024-01-15 10:30:00
2        | 2           | 50.00        | 2024-01-15 10:30:00
---------+-------------+--------------+--------------------
2 rows in set

Query OK, 0 rows affected

-- Export order data as CSV
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= '2026-01-01'
INTO OUTFILE '/tmp/orders_2026.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Import into another table
LOAD DATA INFILE '/tmp/orders_2026.csv'
INTO TABLE orders_archive
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Output:

Query OK, 0 rows affected

▶ Example: Importing Client Files Using LOCAL

LOAD DATA LOCAL INFILE '/home/alice/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Output:

Output displayed

6. Binary Logs and Point-in-Time Recovery

(1) Binlog Basics

The binary log records all SQL statements that modify data and is primarily used for master-slave replication and point-in-time recovery (PITR).

-- View binlog Enabled or Disabled
SHOW VARIABLES LIKE 'log_bin%';

-- View Current binlog List of Files
SHOW BINARY LOGS;

-- View the data currently being written binlog
SHOW MASTER STATUS;

-- View binlog Details of the Incident
SHOW BINLOG EVENTS IN 'binlog.000003';

Three binlog formats:

(2) Point-in-Time Recovery Process

The core approach to point-in-time recovery: First restore the most recent full backup, then replay the binlog to the target point in time.

# Steps1：Restore a Full Backup
mysql -u root -p mydb < full_backup_20260703.sql

# Steps2：Find the point in time before the error occurred
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000003 | grep -A5 "DROP TABLE"

# Steps3：Replay binlog By the target date
mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:30:00" \
  binlog.000003 binlog.000004 | mysql -u root -p

▶ Example: Point-in-Time Recovery After Accidental Table Deletion

Scene: At 14:25, accidentally executed DROP TABLE orders, need to revert to 14:24:59 state

Timeline:
  02:00  Full backup completed
  ...
  14:24  Last normal operation
  14:25  DROP TABLE orders (operational error)
  14:30  Problem identified, start recovery

Recovery steps:
  1. Restore 02:00 full backup
  2. Use mysqlbinlog to replay binlog from 02:00 to 14:24:59
  3. Verify orders table data integrity

# Execute the command
mysql -u root -p mydb < /backup/full_20260703.sql

mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:24:59" \
  /var/lib/mysql/binlog.000003 \
  /var/lib/mysql/binlog.000004 \
  | mysql -u root -p mydb

7. Physical Backup with XtraBackup

(1) How XtraBackup Works

XtraBackup is an open-source physical backup tool developed by Percona. Its core principle is:

1. Background thread Continuously reads InnoDB data pages while monitoring changes in the redo log
2. New redo logs generated during the backup are also continuously recorded.
3. After the backup is complete, use the redo log to roll forward the data pages to a consistent state.
4. No table locking throughout the process; business read and write operations are not affected.

(2) Basic Operations

# Full Backup
xtrabackup --backup --target-dir=/backup/full -u root -p

# Preparing to Back Up（Applications redo log，Ensure data consistency）
xtrabackup --prepare --target-dir=/backup/full

# Restore Backup
xtrabackup --copy-back --target-dir=/backup/full

# Incremental Backup（Based on the full dataset）
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full -u root -p

# Preparing an Incremental Backup
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

8. Designing a Backup Strategy

(1) Full and Incremental Combination

• Daily Full Backup + Real-time Incremental Binlog Backup: Suitable for small and medium-sized databases; simple to restore
• Weekly Full Backup + Daily Incremental Backup (XtraBackup): Suitable for large databases; saves space

(2) Off-site Backup

Backup files must be transferred to off-site storage to prevent data center-level disasters:

# Usage rsync Transmit to a remote location
rsync -avz /backup/mysql/ backup-server:/data/mysql_backup/

# Usage scp Transmission
scp /backup/mysql/full_20260703.sql.gz backup-server:/data/backup/

(3) Regular Disaster Recovery Drills

A backup that isn’t verified is no backup at all. Conduct a full recovery drill at least once a month to verify the following:

• Backup files can be extracted and restored normally
• Data integrity check passed after recovery
• Recovery time is within an acceptable RTO range

9. Automated Backups and Comprehensive Examples

(1) Key Points for Automated Backups

• Use crontab to run backup scripts on a schedule
• Backup files are named by date, making them easy to find
• Automatic compression saves space
• Regularly delete expired backups (retain for N days)
• Send a notification once the backup is complete

❓ FAQ

📖 Summary

• mysqldump is the most commonly used logical backup tool, supporting five modes: full, single database, single table, structure, and data.
• The --single-transaction parameter ensures that InnoDB backups do not lock tables; it is essential for production environments.
• Two methods for restoring logical backups: mysql < file.sql and SOURCE
• SELECT INTO OUTFILE / LOAD DATA INFILE is suitable for importing and exporting data in CSV format
• binlog enables point-in-time recovery (PITR), allowing you to roll back accidental operations with sub-second precision.
• XtraBackup performs fast physical backups without locking tables, making it suitable for large databases
• Backup strategies should be selected based on the volume of data: full backups daily for small databases, and full backups weekly plus daily incremental backups for large databases.
• Off-site backups and regular recovery drills are key to ensuring the effectiveness of backups

📝 Exercises

1. Basic Question (Difficulty: ⭐): Use mysqldump to back up a database, then restore it to a new database. Compare the number of tables and rows in the original database with those in the restored database.

2. Basic Exercise (Difficulty: ⭐): Export the data from a table to a CSV file using SELECT INTO OUTFILE, then import it into another table using LOAD DATA INFILE, and verify that the data matches.

3. Advanced Exercise (Difficulty: ⭐⭐): Simulate a scenario where a table is accidentally deleted: First, perform a full backup; then execute some INSERT operations; next, run a DROP TABLE command; finally, use a binlog point-in-time restore to revert to the state prior to the DROP.

4. Advanced Exercise (Difficulty: ⭐⭐): Write a shell script to automatically perform a full daily backup, compress it with gzip, retain the backup for 7 days, and send an email notification of the backup results.

5. Challenge Question (Difficulty: ⭐⭐⭐): Design a comprehensive backup strategy covering four dimensions: full, incremental, off-site, and testing. For a 200 GB production database, specify the tools to be used, the execution frequency, the recovery steps, and the verification methods.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us