数据库备份与策略【全量备份、增量备份、日志恢复】

数据库备份策略与SQL语句实现

一、基础备份SQL语句

1. 全量备份（逻辑备份）

-- 备份单个数据库
mysqldump -u [username] -p[password] --single-transaction --routines --triggers 
--events --master-data=2 [database_name] > backup.sql

-- 备份所有数据库
mysqldump -u [username] -p[password] --all-databases --single-transaction 
--routines --triggers --events --master-data=2 > full_backup.sql

-- 压缩备份
mysqldump -u [username] -p[password] [database_name] | gzip > backup_$(date +%Y%m%d).sql.gz

2. 增量备份（基于二进制日志）

-- 查看当前二进制日志状态
SHOW MASTER STATUS;

-- 刷新日志并开始新日志文件（用于定时增量备份）
FLUSH BINARY LOGS;

-- 备份二进制日志
mysqlbinlog --start-datetime="2023-07-01 00:00:00" --stop-datetime="2023-07-02 00:00:00" 
/mysql/data/binlog.000123 > binlog_backup_20230701.sql

二、自动化备份策略实现

1. 创建备份存储过程

DELIMITER //
CREATE PROCEDURE sp_daily_backup()
BEGIN
    DECLARE backup_path VARCHAR(255) DEFAULT '/backup/mysql/';
    DECLARE backup_file VARCHAR(255);
    
    SET backup_file = CONCAT(backup_path, 'full_', DATE_FORMAT(NOW(), '%Y%m%d'), '.sql');
    
    -- 执行全量备份（通过系统调用）
    SET @cmd = CONCAT('mysqldump -u backup_user -pbackup_pass --all-databases --single-transaction --routines --triggers --events > ', backup_file);
    SET @output = sys_exec(@cmd);
    
    -- 记录备份日志
    INSERT INTO backup_logs (backup_type, backup_file, start_time, end_time, status)
    VALUES ('FULL', backup_file, NOW(), NOW(), IF(@output=0, 'SUCCESS', 'FAILED'));
    
    -- 刷新二进制日志
    FLUSH BINARY LOGS;
END //
DELIMITER ;

2. 创建备份日志表

CREATE TABLE backup_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_type ENUM('FULL', 'INCREMENTAL') NOT NULL,
    backup_file VARCHAR(255) NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    status ENUM('SUCCESS', 'FAILED', 'IN_PROGRESS') NOT NULL,
    file_size BIGINT,
    checksum VARCHAR(64),
    notes TEXT
);

三、备份策略SQL实现

1. 全量备份策略（每周日）

CREATE EVENT ev_weekly_full_backup
ON SCHEDULE EVERY 1 WEEK STARTS '2023-07-02 02:00:00'
DO
BEGIN
    CALL sp_daily_backup();
    
    -- 清理30天前的旧备份
    SET @old_backups = CONCAT('find /backup/mysql/ -name "full_*.sql" -mtime +30 -exec rm {} \\;');
    SET @output = sys_exec(@old_backups);
END;

2. 增量备份策略（每日）

CREATE EVENT ev_daily_incremental
ON SCHEDULE EVERY 1 DAY STARTS '2023-07-01 01:00:00'
DO
BEGIN
    DECLARE last_log_file VARCHAR(255);
    DECLARE last_log_pos INT;
    DECLARE backup_file VARCHAR(255);
    
    -- 获取上次备份位置
    SELECT MAX(backup_file) INTO @last_backup FROM backup_logs 
    WHERE backup_type = 'FULL' OR backup_type = 'INCREMENTAL' 
    ORDER BY id DESC LIMIT 1;
    
    -- 备份自上次以来的二进制日志
    SET backup_file = CONCAT('/backup/mysql/incr_', DATE_FORMAT(NOW(), '%Y%m%d'), '.sql');
    
    SET @cmd = CONCAT('mysqlbinlog --read-from-remote-server --host=localhost --user=backup_user --password=backup_pass ',
                     '--raw --stop-never --result-file=', backup_file, ' binlog.000123');
    SET @output = sys_exec(@cmd);
    
    -- 记录备份日志
    INSERT INTO backup_logs (backup_type, backup_file, start_time, end_time, status)
    VALUES ('INCREMENTAL', backup_file, NOW(), NOW(), IF(@output=0, 'SUCCESS', 'FAILED'));
END;

四、物理备份策略（XtraBackup）

# 全量备份
innobackupex --user=backup_user --password=backup_pass /backup/mysql/full_$(date +%Y%m%d)

# 增量备份
innobackupex --user=backup_user --password=backup_pass 
--incremental /backup/mysql/incr_$(date +%Y%m%d) 
--incremental-basedir=/backup/mysql/full_20230701

五、备份验证与恢复

1. 验证备份完整性

-- 创建校验表
CREATE TABLE backup_verification (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_file VARCHAR(255) NOT NULL,
    verification_time DATETIME NOT NULL,
    record_count INT,
    checksum VARCHAR(64),
    status ENUM('VALID', 'INVALID', 'PENDING')
);

-- 定期验证备份（示例：验证最近的全量备份）
INSERT INTO backup_verification (backup_file, verification_time, status)
SELECT backup_file, NOW(), 'PENDING' FROM backup_logs 
WHERE backup_type = 'FULL' 
ORDER BY id DESC LIMIT 1;

2. 恢复测试SQL

-- 准备恢复脚本
CREATE TABLE restore_procedures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_file VARCHAR(255) NOT NULL,
    restore_command TEXT NOT NULL,
    last_test_time DATETIME,
    test_result ENUM('SUCCESS', 'FAILED', 'NOT_TESTED')
);

-- 记录恢复步骤
INSERT INTO restore_procedures (backup_file, restore_command)
VALUES 
('full_20230701.sql', 'mysql -u root -p[password] < /backup/mysql/full_20230701.sql'),
('incr_20230702.sql', 'mysqlbinlog /backup/mysql/incr_20230702.sql | mysql -u root -p[password]');

六、备份策略最佳实践

1. 3-2-1备份原则：

   • 至少保留3份备份
   • 存储在2种不同介质上
   • 1份异地备份

2. 备份保留策略：

   -- 自动清理旧备份
   CREATE EVENT ev_clean_old_backups
   ON SCHEDULE EVERY 1 DAY
   DO
   BEGIN
       -- 删除超过30天的全量备份
       SET @cmd = 'find /backup/mysql/ -name "full_*.sql" -mtime +30 -delete';
       SET @output = sys_exec(@cmd);
       
       -- 删除超过7天的增量备份
       SET @cmd = 'find /backup/mysql/ -name "incr_*.sql" -mtime +7 -delete';
       SET @output = sys_exec(@cmd);
   END;sq
   

3. 监控备份状态：

   -- 检查最近备份状态
   SELECT * FROM backup_logs 
   ORDER BY end_time DESC 
   LIMIT 5;
   
   -- 检查备份失败情况
   SELECT * FROM backup_logs 
   WHERE status = 'FAILED' 
   AND start_time > DATE_SUB(NOW(), INTERVAL 7 DAY);