MySQL备份与恢复 #

数据丢失原因：
├── 硬件故障
├── 软件错误
├── 人为误操作
├── 恶意攻击
└── 自然灾害

备份策略示例：
├── 每周日：全量备份
├── 每天凌晨：增量备份
├── 每小时：二进制日志备份
└── 异地备份：定期同步到远程

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

# 备份多个数据库
mysqldump -u root -p --databases mydb test > databases_backup.sql

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

# 备份单个表
mysqldump -u root -p mydb users > users_backup.sql

# 备份多个表
mysqldump -u root -p mydb users orders > tables_backup.sql

# 只备份结构
mysqldump -u root -p --no-data mydb > structure.sql

# 只备份数据
mysqldump -u root -p --no-create-info mydb > data.sql

# 包含存储过程和函数
mysqldump -u root -p --routines --triggers mydb > full_backup.sql

# 压缩备份
mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz

# 远程备份
mysqldump -h 192.168.1.100 -u root -p mydb > remote_backup.sql

# 使用单一事务（InnoDB）
mysqldump -u root -p --single-transaction mydb > consistent_backup.sql

# 锁定所有表
mysqldump -u root -p --lock-all-tables mydb > locked_backup.sql

# 主从复制备份
mysqldump -u root -p --master-data=2 --single-transaction mydb > master_backup.sql

#!/bin/bash

# 配置
DB_USER="root"
DB_PASS="password"
DB_NAME="mydb"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_FILE="${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

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

# 执行备份
mysqldump -u $DB_USER -p$DB_PASS --single-transaction --routines --triggers $DB_NAME | gzip > $BACKUP_FILE

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

echo "Backup completed: $BACKUP_FILE"

# 停止MySQL服务
systemctl stop mysql

# 复制数据目录
cp -r /var/lib/mysql /backup/mysql_cold_backup

# 启动MySQL服务
systemctl start mysql

# 安装XtraBackup
# Ubuntu/Debian
apt-get install percona-xtrabackup-80

# CentOS/RHEL
yum install percona-xtrabackup-80

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

# 准备备份
xtrabackup --prepare --target-dir=/backup/full

# 恢复备份
xtrabackup --copy-back --target-dir=/backup/full

# 修改权限
chown -R mysql:mysql /var/lib/mysql

# 创建基础备份
xtrabackup --backup --target-dir=/backup/base --user=root --password=password

# 创建增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base --user=root --password=password

# 准备基础备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base

# 应用增量备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

-- 查看二进制日志状态
SHOW VARIABLES LIKE 'log_bin';

-- 查看二进制日志文件
SHOW BINARY LOGS;

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

[mysqld]
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7
max_binlog_size=100M

# 刷新日志
mysqladmin -u root -p flush-logs

# 复制二进制日志
cp /var/lib/mysql/mysql-bin.* /backup/binlog/

# 使用mysqlbinlog备份
mysqlbinlog --read-from-remote-server --host=localhost --raw mysql-bin.000001

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

# 恢复压缩备份
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

# 在MySQL命令行中恢复
mysql> USE mydb;
mysql> SOURCE /path/to/backup.sql;

# 创建临时数据库
mysql -u root -p -e "CREATE DATABASE temp_db;"

# 恢复到临时数据库
mysql -u root -p temp_db < table_backup.sql

# 复制表到目标数据库
mysql -u root -p -e "
USE temp_db;
CREATE TABLE mydb.users LIKE users;
INSERT INTO mydb.users SELECT * FROM users;
"

# 删除临时数据库
mysql -u root -p -e "DROP DATABASE temp_db;"

# 恢复全量备份
mysql -u root -p mydb < full_backup.sql

# 应用二进制日志到指定时间点
mysqlbinlog --stop-datetime="2024-01-15 10:00:00" /var/lib/mysql/mysql-bin.000001 | mysql -u root -p

# 应用多个二进制日志
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

# 1. 立即停止应用写入
# 2. 创建当前数据快照
mysqldump -u root -p --single-transaction mydb > current_snapshot.sql

# 3. 从备份恢复
mysql -u root -p mydb < backup.sql

# 4. 应用二进制日志（跳过误操作）
mysqlbinlog --start-datetime="2024-01-15 09:00:00" --stop-datetime="2024-01-15 09:30:00" mysql-bin.000001 | mysql -u root -p

#!/bin/bash

# 配置
BACKUP_DIR="/backup/mysql"
DB_USER="root"
DB_PASS="password"
RETENTION_DAYS=7

# 创建日期目录
DATE=$(date +%Y%m%d)
BACKUP_PATH="${BACKUP_DIR}/${DATE}"
mkdir -p $BACKUP_PATH

# 全量备份
mysqldump -u $DB_USER -p$DB_PASS --single-transaction --routines --triggers --all-databases | gzip > "${BACKUP_PATH}/full_backup.sql.gz"

# 备份二进制日志
mysqladmin -u $DB_USER -p$DB_PASS flush-logs
cp /var/lib/mysql/mysql-bin.* $BACKUP_PATH/

# 清理旧备份
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

# 记录日志
echo "$(date) - Backup completed" >> $BACKUP_DIR/backup.log

# 编辑crontab
crontab -e

# 每天凌晨2点执行全量备份
0 2 * * * /scripts/mysql_backup.sh

# 每小时备份二进制日志
0 * * * * /scripts/binlog_backup.sh

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

# 检查备份内容
head -n 50 backup.sql
tail -n 50 backup.sql

# 验证SQL文件
grep "CREATE TABLE" backup.sql
grep "INSERT INTO" backup.sql

# 创建测试数据库
mysql -u root -p -e "CREATE DATABASE test_restore;"

# 恢复到测试数据库
mysql -u root -p test_restore < backup.sql

# 验证数据
mysql -u root -p test_restore -e "SHOW TABLES; SELECT COUNT(*) FROM users;"

# 清理测试数据库
mysql -u root -p -e "DROP DATABASE test_restore;"