MySQL备份与恢复-数据安全的最后防线

# 备份单个数据库
mysqldump -uroot -p mydb > mydb.sql

# 备份多个数据库
mysqldump -uroot -p --databases db1 db2 > dbs.sql

# 备份所有数据库
mysqldump -uroot -p --all-databases > all.sql

# 备份指定表
mysqldump -uroot -p mydb users orders > tables.sql

# 常用参数
-uroot           # 用户名
-p                # 密码（不建议直接写密码）
--single-transaction  # 事务备份（InnoDB）
--lock-tables     # 锁表备份（MyISAM）
--routines        # 备份存储过程
--triggers        # 备份触发器
--events          # 备份事件
--master-data     # 记录binlog位置（用于主从）
--flush-logs      # 刷新日志
--quick           # 快速备份（大表）
--set-charset     # 设置字符集
--default-character-set=utf8mb4  # 指定字符集

# 完整备份（InnoDB推荐）
mysqldump -uroot -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  mydb > mydb_$(date +%Y%m%d).sql

# 压缩备份
mysqldump -uroot -p mydb | gzip > mydb_$(date +%Y%m%d).sql.gz

# 备份并记录binlog位置
mysqldump -uroot -p \
  --single-transaction \
  --master-data=2 \
  --flush-logs \
  mydb > mydb_backup.sql

# 备份结构（不包含数据）
mysqldump -uroot -p --no-data mydb > mydb_schema.sql

# 备份数据（不包含结构）
mysqldump -uroot -p --no-tablespaces mydb > mydb_data.sql

# 安装（如果未安装）
# apt install mysql-client

# 使用mysqlpump
mysqlpump -uroot -p mydb > mydb.sql

# 并行备份（加快速度）
mysqlpump -uroot -p \
  --default-parallelism=4 \
  --databases mydb > mydb.sql

# 优点：
# 1. 多线程备份，速度更快
# 2. 可以备份多个数据库并行
# 3. 支持压缩输出
# 4. 可以排除表

# 示例：排除指定表
mysqlpump -uroot -p \
  --exclude-databases=information_schema \
  --exclude-tables=test% \
  --databases mydb > mydb.sql

# Ubuntu/Debian
sudo apt install xtrabackup

# CentOS/RHEL
sudo yum install percona-xtrabackup

# 检查安装
xtrabackup --version

# 创建备份目录
sudo mkdir -p /backup/full
sudo chown -R mysql:mysql /backup

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

# 备份完成后检查
ls -la /backup/full

# 第一次增量备份（基于全量）
xtrabackup --backup \
  --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full \
  --user=root \
  --password=your_password

# 第二次增量备份（基于第一次）
xtrabackup --backup \
  --target-dir=/backup/inc2 \
  --incremental-basedir=/backup/inc1 \
  --user=root \
  --password=your_password

# 1. 准备恢复（应用日志）
xtrabackup --prepare --target-dir=/backup/full

# 如果有增量，需要依次prepare
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 2. 停止MySQL
sudo systemctl stop mysql

# 3. 清空数据目录
sudo rm -rf /var/lib/mysql/*

# 4. 恢复数据
xtrabackup --copy-back --target-dir=/backup/full \
  --user=root \
  --password=your_password

# 5. 设置权限
sudo chown -R mysql:mysql /var/lib/mysql

# 6. 启动MySQL
sudo systemctl start mysql

# Ubuntu/Debian
sudo apt install mydumper

# CentOS/RHEL
sudo yum install mydumper

# 验证
mydumper --version

# 备份单个数据库
mydumper -u root -p password \
  -o /backup/mydumper \
  -B mydb

# 备份所有数据库
mydumper -u root -p password \
  -o /backup/mydumper \
  -A

# 多线程备份
mydumper -u root -p password \
  -o /backup/mydumper \
  -B mydb \
  -t 4  # 4个线程

myloader -u root -p password \
  -# 恢复数据库d /backup/mydumper \
  -B mydb

# 恢复所有数据库
myloader -u root -p password \
  -d /backup/mydumper \
  -A

# 恢复数据库
mysql -uroot -p mydb < mydb.sql

# 恢复压缩的备份
gunzip < mydb.sql.gz | mysql -uroot -p mydb

# 恢复所有数据库
mysql -uroot -p < all.sql

# 从备份文件中提取指定表
sed -n '/CREATE TABLE.*users/,/UNLOCK TABLES/p' mydb.sql > users_table.sql

# 或者使用grep
grep -A 50 'CREATE TABLE.*users' mydb.sql > users_table.sql

# 恢复指定表
mysql -uroot -p mydb < users_table.sql

#!/bin/bash
# backup.sh

# 配置
DB_NAME="mydb"
DB_USER="root"
DB_PASS="password"
BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)

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

# 备份
echo "Starting backup at $(date)"
mysqldump -u$DB_USER -p$DB_PASS \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --master-data=2 \
  --flush-logs \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

echo "Backup completed at $(date)"

# 编辑crontab
crontab -e

# 添加定时任务（每天凌晨2点执行）
0 2 * * * /path/to/backup.sh >> /var/log/backup.log 2>&1

# 重启crontab
sudo systemctl restart cron

# 每天凌晨2点全量备份
0 2 * * * /path/to/backup.sh

# 每周日凌晨3点全量备份
0 3 * * 0 /path/to/backup.sh

# 每天每小时增量备份（需要配置binlog）
0 * * * * /path/to/inc_backup.sh

# 检查备份文件
ls -lh /backup/

# 检查备份文件完整性
gzip -t mydb_20260214.sql.gz

# 检查SQL文件
head -20 mydb_20260214.sql
tail -20 mydb_20260214.sql

# 测试恢复（恢复测试库）
mysql -uroot -p testdb < mydb_20260214.sql

# 检查数据
mysql -uroot -p -e "USE testdb; SELECT COUNT(*) FROM users;"

# 自动备份
-- 登录阿里云RDS控制台
-- 配置自动备份策略
-- 设置备份保留时间

# 手动备份
-- 手动创建备份
-- 下载备份文件

# 自动备份
-- 默认开启自动备份
-- 可配置保留天数（1-35天）
-- 创建手动快照

# 从快照恢复
-- 进入RDS控制台
-- 选择快照
-- 恢复实例

# 备份到OSS/MinIO/S3
# 安装ossutil
# 配置访问密钥
# 同步备份文件

# 示例：备份到阿里云OSS
ossutil cp mydb_$(date +%Y%m%d).sql.gz oss://mybucket/backup/

# 检查备份文件是否存在
[ -f /backup/mydb_$(date +%Y%m%d).sql.gz ] || echo "Backup failed!"

# 检查备份文件大小
SIZE=$(stat -c%s /backup/mydb_$(date +%Y%m%d).sql.gz)
if [ $SIZE -lt 1000 ]; then
    echo "Backup file too small!"
fi

# 检查备份时间
LAST_BACKUP=$(stat -c %Y /backup/mydb_latest.sql.gz)
NOW=$(date +%s)
DIFF=$((NOW - LAST_BACKUP))
if [ $DIFF -gt 86400 ]; then
    echo "Backup too old!"
fi

# 配置企业微信/钉钉/邮件告警
# 在备份脚本中添加告警逻辑
if [ $? -ne 0 ]; then
    curl -X POST "https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=XXX" \
         -H 'Content-Type: application/json' \
         -d '{"msgtype": "text", "text": {"content": "Backup failed!"}}'
fi