-- 查看当前 binlog 格式
SHOW VARIABLES LIKE 'binlog_format';

-- 设置 binlog 格式
SET GLOBAL binlog_format = 'ROW';

-- my.cnf 配置
[mysqld]
binlog_format = ROW
server_id = 1               -- 必须设置（主从复制需要）
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7        -- 日志保留 7 天
max_binlog_size = 500M      -- 每个 binlog 文件最大 500MB

-- 查看 binlog 文件列表
SHOW BINARY LOGS;

-- 查看当前正在写入的 binlog
SHOW MASTER STATUS;

-- 查看 binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000001' LIMIT 10;

-- 通过 mysqlbinlog 工具查看 binlog 内容
-- 命令行：
mysqlbinlog --base64-output=decode-rows -vv /var/log/mysql/mysql-bin.000001

-- 刷新 binlog（生成新文件）
FLUSH LOGS;

-- 删除指定 binlog 之前的日志
PURGE BINARY LOGS TO 'mysql-bin.000010';

-- 删除所有 binlog 之前指定日期的
PURGE BINARY LOGS BEFORE '2024-06-01 00:00:00';

-- 设置 binlog 过期时间（推荐自动清理）
SET GLOBAL expire_logs_days = 7;

-- 场景：误删了某张表的数据，需要通过 binlog 恢复

-- 步骤1：找到误操作的时间点和 binlog 位置
-- 查看 binlog 事件
SHOW BINLOG EVENTS IN 'mysql-bin.000005';

-- 假设找到了误删的 binlog 位置：从 position 5000 开始

-- 步骤2：将 binlog 导出为 SQL（排除误删的语句）
-- 导出到误删之前的 SQL
mysqlbinlog --start-position=1 --stop-position=5000 \
    /var/log/mysql/mysql-bin.000005 > recovery_before.sql

-- 步骤3：执行恢复
mysql -u root -p < recovery_before.sql

-- 更精确的做法：使用 mysqlbinlog 的时间点
mysqlbinlog --stop-datetime="2024-06-15 14:29:59" \
    /var/log/mysql/mysql-bin.000005 | mysql -u root -p

-- 跳过误删的语句然后继续后面的操作
mysqlbinlog --start-position=6000 \
    /var/log/mysql/mysql-bin.000005 | mysql -u root -p

-- 备份整个数据库
mysqldump -u root -p --databases mydb > mydb_backup.sql

-- 备份单表
mysqldump -u root -p mydb orders > orders_backup.sql

-- 备份多表
mysqldump -u root -p mydb orders users > mydb_part.sql

-- 常用参数
-- --single-transaction：InnoDB 一致性备份（不锁表）
-- --routines：导出存储过程和函数
-- --triggers：导出触发器
-- --master-data=2：记录 binlog 位置（注释形式）
mysqldump -u root -p \
    --single-transaction \
    --routines \
    --triggers \
    --master-data=2 \
    mydb > full_backup.sql

-- 恢复
mysql -u root -p mydb < full_backup.sql

-- 压缩备份
mysqldump -u root -p --single-transaction mydb | gzip > mydb_backup.sql.gz
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

-- Percona XtraBackup 安装后使用

-- 全量备份
xtrabackup --backup \
    --target-dir=/backup/mysql/full_20240601 \
    --user=root \
    --password=your_password

-- 准备备份（应用 redo log，使数据一致）
xtrabackup --prepare \
    --target-dir=/backup/mysql/full_20240601

-- 恢复备份
xtrabackup --copy-back \
    --target-dir=/backup/mysql/full_20240601 \
    --datadir=/var/lib/mysql

-- 周一：全量备份
xtrabackup --backup --target-dir=/backup/mysql/full

-- 周二到周日：增量备份（基于前一天的增量）
xtrabackup --backup \
    --target-dir=/backup/mysql/incr_mon \
    --incremental-basedir=/backup/mysql/full

xtrabackup --backup \
    --target-dir=/backup/mysql/incr_tue \
    --incremental-basedir=/backup/mysql/incr_mon

-- 恢复时：准备全量 + 应用增量
xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full
xtrabackup --prepare --apply-log-only \
    --target-dir=/backup/mysql/full \
    --incremental-dir=/backup/mysql/incr_mon
xtrabackup --prepare \
    --target-dir=/backup/mysql/full \
    --incremental-dir=/backup/mysql/incr_tue

-- copy-back 恢复
xtrabackup --copy-back --target-dir=/backup/mysql/full

#!/bin/bash
# 全量备份脚本

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d)
USER="root"
PASSWORD="your_password"

# 创建备份目录
mkdir -p $BACKUP_DIR/full_$DATE

# 全量备份
mysqldump -u$USER -p$PASSWORD \
    --single-transaction \
    --all-databases \
    --master-data=2 \
    --routines \
    --triggers \
    | gzip > $BACKUP_DIR/full_$DATE/full_backup.sql.gz

# 删除 30 天前的备份
find $BACKUP_DIR -name "full_*" -mtime +30 -exec rm -rf {} \;

echo "Backup completed: $DATE"

# crontab 定时任务
# 每天凌晨 2 点执行全量备份
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

-- 场景：2024-06-15 14:30:00 误执行了 DROP TABLE orders

-- 前提：开启了 binlog

-- 步骤1：确认 binlog 文件和位置
SHOW MASTER STATUS;
-- 假设当前 binlog 文件：mysql-bin.000008

-- 步骤2：找到误操作前的 binlog 位置
SHOW BINLOG EVENTS IN 'mysql-bin.000008';
-- 找到含有 DROP TABLE 的事件，记录其 position

-- 步骤3：使用之前全量备份恢复
-- 先恢复最近一次全量备份（假设是 2024-06-15 凌晨 02:00）
mysql -u root -p < /backup/mysql/full_20240615/full_backup.sql

-- 步骤4：应用 binlog 增量（从备份完成到误操作前）
mysqlbinlog --start-datetime="2024-06-15 02:00:00" \
    --stop-datetime="2024-06-15 14:29:59" \
    mysql-bin.000008 mysql-bin.000007 | mysql -u root -p

-- 数据恢复到误删除前的状态！

-- 场景：误执行了 DELETE FROM orders WHERE status = 0（无 WHERE 条件）

-- 如果开启了 binlog 且格式为 ROW
-- 可以使用 mysqlbinlog 反解出原始数据

-- 步骤1：找到误操作对应的 binlog
mysqlbinlog --base64-output=decode-rows -vv \
    --start-datetime="2024-06-15 14:00:00" \
    mysql-bin.000008 > decoded.sql

-- 步骤2：从 ROW 格式的日志中找到 DELETE 事件的旧值
-- 文件中会有类似：
-- ### DELETE FROM `mydb`.`orders`
-- ### WHERE
-- ###   @1=1   -- id
-- ###   @2='20240101001'  -- order_no
-- ###   @3=100  -- user_id
-- ### ...

-- 步骤3：将 DELETE 转换为 INSERT
-- 可以写脚本将 DELETE 的旧值转为 INSERT 语句

-- 使用工具：mysqlbinlog 的 flashback 功能（第三方工具）
-- 或使用 binlog2sql 工具（Python）
pip install binlog2sql
binlog2sql --flashback \
    -h127.0.0.1 -P3306 -uroot -p'password' \
    -d mydb -t orders \
    --start-file='mysql-bin.000008' \
    --start-datetime='2024-06-15 14:00:00' \
    --stop-datetime='2024-06-15 14:30:00' > flashback.sql
mysql -u root -p < flashback.sql

-- 场景：DROP TABLE orders;

-- 方案1：从全量备份恢复 + 增量 binlog
-- 同上 3.1

-- 方案2：如果开启了延迟复制
-- 有一台延迟 1 小时的从库
-- 1. 停止从库同步
STOP SLAVE;

-- 2. 查看延迟了多少
SHOW SLAVE STATUS\G
-- Seconds_Behind_Master: 3600

-- 3. 手动应用 binlog 到误操作前的位置
START SLAVE UNTIL ...

-- 4. 导出误删的表
mysqldump -u root -p mydb orders > orders_recovery.sql

-- 5. 恢复到主库
mysql -u root -p mydb < orders_recovery.sql

-- 备份策略设计：

-- 1. 物理备份（xtrabackup）
--    周日：全量备份
--    周一~周六：增量备份

-- 2. 逻辑备份（mysqldump）
--    每天凌晨：导出关键业务表

-- 3. Binlog 实时保存
--    定期将 binlog 同步到备份服务器

-- 恢复能力：
-- - 全量+增量+binlog：恢复到任意时间点
-- - RTO（恢复时间目标）：2 小时内
-- - RPO（恢复点目标）：最多丢失 5 分钟数据

-- 验证备份有效性：
-- 定期（每月）做一次恢复演练：
-- 1. 在一台测试实例上恢复全量备份
-- 2. 应用增量备份
-- 3. 应用 binlog 到指定时间点
-- 4. 验证数据完整性