MySQL增量备份配置咨询：每日仅备份数据库变更数据

Got it, let's walk through exactly how to set up incremental backups for your MySQL server—since you only want to back up daily changes, we'll rely on MySQL's binary logs (binlog). These logs record every data-modifying operation (like inserts, updates, deletes), making them perfect for incremental backups. Here's a detailed, production-ready setup:

1. First: Enable Binary Logging (Mandatory) #

Incremental backups depend entirely on binlogs, so first confirm they're enabled:

• Log into your MySQL shell and run:

SHOW VARIABLES LIKE 'log_bin';

If the Value is ON, you're good to go. If it's OFF, edit your MySQL config file (path varies: /etc/my.cnf or /etc/mysql/my.cnf on Linux, my.ini on Windows) and add these lines under the [mysqld] section:

[mysqld]
# Enable binlog, specify storage path
log_bin = /var/lib/mysql/mysql-bin
# Use ROW format (most reliable for recovery—tracks row-level changes)
binlog_format = ROW
# Unique server ID (required even for single-server setups)
server-id = 1
# Auto-purge old logs after 7 days (adjust as needed)
expire_logs_days = 7

Restart MySQL to apply changes:

# Linux
systemctl restart mysqld
# Windows: Use Services manager to restart MySQL

Re-run SHOW VARIABLES LIKE 'log_bin'; to confirm it's now ON.

2. Take a Full Backup (Your Incremental Base) #

Incremental backups build on the last full backup, so you need a starting point first. Use mysqldump for a consistent full backup:

mysqldump -u root -p --all-databases --single-transaction --flush-logs --master-data=2 > full_backup_$(date +%Y%m%d).sql

Let's break down the key flags:

• --all-databases: Backs up every database (replace with --databases db1 db2 if you only need specific ones)
• --single-transaction: Ensures a consistent backup for InnoDB tables without locking
• --flush-logs: Rotates the binlog after backup, so all future changes go into a new log file
• --master-data=2: Adds a comment to the backup file with the current binlog position—critical for recovery

Enter your MySQL password when prompted, and you'll get a full backup file like full_backup_20240520.sql. Store this somewhere safe (off-server is best).

3. Daily Incremental Backup of Binlogs #

Now, we'll set up daily backups of the new binlog files created since your last full (or incremental) backup.

Option 1: Manual Backup (For Testing/Small Setups) #

Each day, check which new binlogs have been created:

SHOW BINARY LOGS;

Copy the new log files (e.g., mysql-bin.000002, mysql-bin.000003) to your backup directory:

cp /var/lib/mysql/mysql-bin.000002 /your/backup/dir/binlogs/
cp /var/lib/mysql/mysql-bin.000003 /your/backup/dir/binlogs/

Option 2: Automated Script (Production-Grade) #

Create a shell script mysql_inc_backup.sh to handle this automatically:

#!/bin/bash
# Configuration
BACKUP_DIR="/your/backup/dir/binlogs"
MYSQL_USER="root"
MYSQL_PASS="your_mysql_password"
LOG_FILE="$BACKUP_DIR/backup_log_$(date +%Y%m%d).log"

# Create backup dir if it doesn't exist
mkdir -p $BACKUP_DIR

# Get the latest binlog file before flushing
LAST_BINLOG=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "SHOW BINARY LOGS;" | tail -n 1 | awk '{print $1}')

# Rotate binlog so we can safely back up old ones
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH LOGS;"

# Backup all binlogs except the latest one (which is now empty)
for BINLOG in $(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "SHOW BINARY LOGS;" | awk '{print $1}'); do
    if [ "$BINLOG" != "$LAST_BINLOG" ]; then
        cp /var/lib/mysql/$BINLOG $BACKUP_DIR/
        echo "$(date +'%Y-%m-%d %H:%M:%S') Successfully backed up $BINLOG" >> $LOG_FILE
    fi
done

# Clean up backups older than 7 days
find $BACKUP_DIR -type f -mtime +7 -delete
echo "$(date +'%Y-%m-%d %H:%M:%S') Purged backups older than 7 days" >> $LOG_FILE

Make the script executable:

chmod +x mysql_inc_backup.sh

Then use crontab to run it daily (e.g., at 2 AM):

crontab -e

Add this line:

0 2 * * * /path/to/mysql_inc_backup.sh

Save and exit—now your incremental backups run automatically every day.

4. How to Restore from Incremental Backups #

If you ever need to recover data, follow these steps:

1. Restore the latest full backup first:

mysql -u root -p < full_backup_20240520.sql

2. Apply each incremental binlog in order using mysqlbinlog:

mysqlbinlog /your/backup/dir/binlogs/mysql-bin.000002 | mysql -u root -p
mysqlbinlog /your/backup/dir/binlogs/mysql-bin.000003 | mysql -u root -p

This replays all the changes since the full backup.

Key Notes for Production #

• Store backups off-server: Never keep backups on the same machine as your MySQL server—use a separate storage server, cloud storage, or external drive.
• Test recovery regularly: It's useless to have backups if you can't restore them. Do a test restore every few weeks to confirm everything works.
• Consider Percona XtraBackup: For large InnoDB databases, this tool offers faster incremental backups with less overhead than binlog-only backups.
• Secure backup files: Backup files contain sensitive data—restrict access with file permissions, and encrypt them if stored off-site.

内容的提问来源于stack exchange，提问作者Abhijith VA