Why is Data Backup and Recovery Important?¶

In MySQL, data is the core. Imagine if your website suddenly can’t be accessed, customer data is accidentally lost, or the server hard drive is damaged—these situations can lead to huge losses. Data backup is like buying “insurance” for your data, which can help “recover” your data when an accident occurs. Recovery is about restoring the backed-up data back into MySQL to get the business running normally again. Therefore, backup and recovery are essential basic skills for every MySQL user.

1. MySQL Backup Tool: mysqldump (Most Commonly Used)¶

MySQL comes with a simple and easy-to-use backup tool called mysqldump, which can export entire databases, individual tables, or even partial data into SQL files. It’s very intuitive and suitable for beginners.

1.1 Backup an Entire Database¶

Suppose you have a database named test_db that needs to be backed up:

Steps:
1. Open the command-line tool (Command Prompt/PowerShell on Windows, Terminal on Linux/macOS).
2. Execute the backup command:

   mysqldump -u [username] -p [database_name] > [backup_file_name].sql

• -u: Specifies the MySQL username (e.g., root).
• -p: Indicates that you need to enter a password (you will be prompted for the password after executing the command).
• [database_name]: The name of the database to back up (e.g., test_db).
• >: Redirects the output to an SQL file (e.g., test_db_backup.sql).

Example:
Backup the test_db database to the test_backup.sql file:

mysqldump -u root -p test_db > test_backup.sql

After execution, you will be prompted to enter the password (e.g., 123456). Once the correct password is entered, MySQL will automatically generate the test_backup.sql file, containing all table structures and data of test_db.

1.2 Backup a Single Table¶

If you only want to back up a specific table (e.g., the users table) from a database, add the table name after the command:

mysqldump -u root -p test_db users > users_backup.sql

This will only export the data of the users table in test_db.

1.3 Backup Data with Specific Conditions¶

If you need to back up data based on conditions (e.g., only users over 18 years old in the users table), use the WHERE clause:

mysqldump -u root -p test_db users --where="age > 18" > filtered_users.sql

1.4 Other Simple Backup Methods (Supplementary)¶

• Manual Data File Copy: If binary logging is not enabled in MySQL, stop the MySQL service first, then copy the database folder (e.g., /var/lib/mysql/test_db on Linux) to a safe location. However, this method requires stopping the service and is not recommended for beginners.
• Using xtrabackup (Advanced): This is a tool from Percona, supporting hot backups (no need to stop the service), but it is slightly more complex for beginners and can be learned later.

2. Data Recovery: Restoring Backup Files to MySQL¶

The ultimate goal of backup is recovery. The recovery operation is also simple and can be completed through the mysql command-line tool.

2.1 Restore to an Existing Database¶

Suppose you want to restore test_backup.sql to the test_db database:

Steps:
1. Ensure the target database exists (if not, create it first):

   mysql -u [username] -p -e "CREATE DATABASE test_db;"  # Create a new database if it doesn't exist

2. Execute the recovery command:

   mysql -u [username] -p [database_name] < [backup_file_name].sql

Example:
Restore test_backup.sql to test_db:

mysql -u root -p test_db < test_backup.sql

After entering the password, MySQL will automatically execute the SQL statements in test_backup.sql to import the data into test_db.

2.2 Restore to a New Database Instance¶

If you need to restore to a completely new MySQL environment (e.g., changing servers), simply create a database with the same name on the new server first, then use the above command to restore.

3. Automated Backup: Scheduled “Automatic Insurance”¶

Manual backups are prone to being forgotten. It is recommended to set up scheduled backups. Taking Linux/macOS as an example, use crontab to achieve daily automatic backups at 2 AM:

3.1 Write a Backup Script¶

Create a backup.sh file with the following content (replace username, password, and database name):

#!/bin/bash
# Backup path
BACKUP_DIR="/path/to/backups"
# Database information
DB_USER="root"
DB_PASS="123456"
DB_NAME="test_db"
# Backup filename (with timestamp to avoid overwriting)
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="$BACKUP_DIR/$DB_NAME_$TIMESTAMP.sql"

# Execute backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME > $BACKUP_FILE

# Compress the backup file (optional)
gzip $BACKUP_FILE

# Delete backups older than 30 days (optional)
find $BACKUP_DIR -name "$DB_NAME_*.sql.gz" -mtime +30 -delete

• After saving, add execution permissions to the script: chmod +x backup.sh.
• Test if the script works: ./backup.sh.

3.2 Add Scheduled Tasks (crontab)¶

Execute crontab -e and add a line:

0 2 * * * /path/to/backup.sh  # Execute the script every day at 2 AM

• 0 2 * * *: Indicates 2:00 AM every day.
• /path/to/backup.sh: Replace with the actual path to your script.

4. Key Checks Before Recovery¶

Before restoring data, be sure to prepare the following to avoid “recovery errors”:
1. Check Backup File Integrity: Open test_backup.sql and verify that it starts with SQL statements like CREATE TABLE to ensure it is not corrupted.
2. Confirm Target Database is Empty: If restoring to an existing database, it is recommended to delete old data first (or rename it) to avoid duplication.
3. Close Unnecessary Services: When restoring large files, it is recommended to disable MySQL’s index checks, foreign key constraints, etc., and re-enable them after recovery (you can add the --skip-foreign-key-checks parameter in the script).

5. Common Problems and Solutions¶

1. Backup Error: Access denied
   - Reason: Incorrect username/password or insufficient permissions.
   - Solution: Check the content after -u and -p, ensure the password is correct, or add BACKUP permissions to the user.

2. Recovery Error: Table doesn’t exist
   - Reason: The target database does not exist or the database name in the backup file does not match the target.
   - Solution: Create the target database first, or modify the CREATE DATABASE statement in the backup file.

3. Backup File is Too Large
   - Reason: The database table has too much data.
   - Solution: Back up tables separately, or use the --single-transaction parameter (suitable for InnoDB engines) to reduce table locking time.

6. Summary¶

Data backup and recovery are the “lifelines” of MySQL operations. Remember the following key points:
- Choose mysqldump as the backup tool: Simple and intuitive, suitable for beginners.
- Regular Backups: Perform daily/weekly backups and make it a habit.
- Recovery Testing: Restore the backup file once a month to verify data integrity.

By mastering the backup and recovery commands of mysqldump and using the skills of scheduled backups, you can effectively avoid the risk of data loss. Data security starts with a correct backup!