Mysql完整备份的步骤

1. 备份数据库（逻辑备份） # 使用mysqldump进行完整备份
mysqldump -u root -p --all-databases --triggers --routines --events --single-transaction --add-drop-database > /backup/mysql_full_$(date +%Y%m%d_%H%M%S).sql

# 压缩备份文件
gzip /backup/mysql_full_*.sql

# 检查备份文件大小
ls -lh /backup/mysql_full_*.gz2. 备份MySQL配置文件 # 备份所有配置文件
sudo tar czf /backup/mysql_config_$(date +%Y%m%d_%H%M%S).tar.gz /etc/mysql/

# 单独备份主配置文件
sudo cp /etc/mysql/my.cnf /backup/my.cnf.backup_$(date +%Y%m%d_%H%M%S)3. 备份数据目录结构（可选，不备份实际数据） # 只备份目录结构，不备份数据文件（因为数据已逻辑备份）
sudo ls -la /var/lib/mysql/ > /backup/mysql_dir_structure_$(date +%Y%m%d_%H%M%S).txt
sudo tree /var/lib/mysql/ -L 3 > /backup/mysql_tree_$(date +%Y%m%d_%H%M%S).txt 2>/dev/null || sudo find /var/lib/mysql/ -type d | sort > /backup/mysql_dir_tree_$(date +%Y%m%d_%H%M%S).txt4. 备份错误日志和状态信息 # 备份当前错误日志
sudo cp /var/log/mysql/error.log /backup/mysql_error_log_$(date +%Y%m%d_%H%M%S).log

# 备份数据库状态信息
mysql -u root -p -e "SHOW DATABASES;" > /backup/mysql_databases_$(date +%Y%m%d_%H%M%S).txt
mysql -u root -p -e "SHOW GLOBAL STATUS;" > /backup/mysql_status_$(date +%Y%m%d_%H%M%S).txt
mysql -u root -p -e "SHOW GLOBAL VARIABLES;" > /backup/mysql_variables_$(date +%Y%m%d_%H%M%S).txt5. 验证备份完整性 # 验证备份文件
echo "=== 备份验证 ==="
echo "1. 数据库备份文件:"
ls -lh /backup/mysql_full_*.gz

echo "2. 配置文件备份:"
ls -lh /backup/mysql_config_*.tar.gz

echo "3. 测试数据库连接:"
mysql -u root -p -e "SELECT '备份验证通过', NOW();" 2>/dev/null && echo "✅ 数据库连接正常" || echo "❌ 数据库连接失败"

echo "4. 检查关键数据库:"
mysql -u root -p -e "SHOW DATABASES;" 2>/dev/null | grep -E "(btchina|mysql|information_schema|performance_schema)"6. 创建备份验证脚本 # 创建验证脚本
cat > /backup/verify_backup.sh << 'EOF'
#!/bin/bash
echo "=== MySQL备份验证脚本 ==="
echo "备份时间: $(date)"
echo ""

# 检查备份文件
echo "1. 检查备份文件:"
BACKUP_FILE=$(ls -t /backup/mysql_full_*.gz | head -1)
if [ -f "$BACKUP_FILE" ]; then
echo "✅ 找到备份文件: $BACKUP_FILE"
echo " 文件大小: $(du -h "$BACKUP_FILE" | cut -f1)"
echo " 修改时间: $(stat -c %y "$BACKUP_FILE")"
else
echo "❌ 未找到备份文件"
fi

echo ""
echo "2. 检查MySQL状态:"
if systemctl is-active --quiet mysql; then
echo "✅ MySQL服务运行正常"
else
echo "❌ MySQL服务未运行"
fi

echo ""
echo "3. 数据库连接测试:"
mysql -u root -p$1 -e "SELECT VERSION() as mysql_version, NOW() as current_time;" 2>/dev/null | grep -v "mysql: \[Warning\]" && echo "✅ 数据库连接成功" || echo "❌ 数据库连接失败"

echo ""
echo "4. 数据库列表:"
mysql -u root -p$1 -e "SHOW DATABASES;" 2>/dev/null | tail -n +2

echo ""
echo "=== 备份验证完成 ==="
EOF

chmod +x /backup/verify_backup.sh📝 完整的备份清单创建备份清单文件： cat > /backup/backup_manifest_$(date +%Y%m%d_%H%M%S).txt << EOF
=== MySQL完整备份清单 ===
备份时间: $(date)
MySQL版本: 8.0.45
服务器: localhost

包含的备份内容:
1. 完整数据库导出 (mysqldump)
2. MySQL配置文件 (/etc/mysql/)
3. 错误日志副本
4. 数据库状态信息
5. 目录结构信息

备份文件位置:
- 数据库备份: /backup/mysql_full_*.gz
- 配置备份: /backup/mysql_config_*.tar.gz
- 状态备份: /backup/mysql_*.txt

恢复说明:
1. 停止MySQL: systemctl stop mysql
2. 清理数据目录: rm -rf /var/lib/mysql/*
3. 重新初始化: mysqld --initialize --user=mysql
4. 启动MySQL: systemctl start mysql
5. 导入数据: gunzip -c backup_file.gz | mysql -u root -p

重要提醒:
- 定期测试备份恢复
- 监控磁盘空间
- 保留多个备份版本

=== 备份完成 ===
EOF🔄 设置自动备份（可选但推荐） # 创建自动备份脚本
sudo nano /usr/local/bin/mysql_auto_backup.sh#!/bin/bash
# MySQL自动备份脚本
BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=7

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

# 备份数据库
mysqldump -u root -p你的密码 --all-databases --triggers --routines --events --single-transaction | gzip > $BACKUP_DIR/mysql_auto_$DATE.sql.gz

# 备份配置文件
tar czf $BACKUP_DIR/mysql_config_$DATE.tar.gz /etc/mysql/ 2>/dev/null

# 清理旧备份
find $BACKUP_DIR -name "mysql_auto_*.gz" -mtime +$RETENTION_DAYS -delete
find $BACKUP_DIR -name "mysql_config_*.tar.gz" -mtime +$RETENTION_DAYS -delete

# 记录备份日志
echo "$(date): 自动备份完成，文件: mysql_auto_$DATE.sql.gz" >> $BACKUP_DIR/backup.log# 设置脚本权限
sudo chmod +x /usr/local/bin/mysql_auto_backup.sh

# 添加到crontab（每天凌晨2点备份）
sudo crontab -l | { cat; echo "0 2 * * * /usr/local/bin/mysql_auto_backup.sh"; } | sudo crontab -✅ 最终验证 # 运行一次备份验证
mysql -u root -p -e "
SELECT '✅ 数据库状态验证' as title;
SELECT '1. 版本信息:' as item, VERSION() as value UNION
SELECT '2. 运行时间:', NOW() UNION
SELECT '3. 数据库数量:', COUNT(*) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('information_schema','performance_schema','mysql','sys') UNION
SELECT '4. InnoDB状态:', '正常' FROM dual WHERE @@global.innodb_force_recovery = 0;
"

# 检查无LSN错误
sudo grep -c "log sequence number is in the future" /var/log/mysql/error.log
[ $? -eq 1 ] && echo "✅ 无LSN错误" || echo "⚠️ 发现LSN错误"