MySQL: MySQL 备份与恢复完全指南

mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

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

# 备份指定表
mysqldump -u root -p --single-transaction mydb users orders > tables_backup.sql

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

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

# 模式1：全量备份（所有库）
mysqldump -u root -p --all-databases \
  --single-transaction --routines --triggers \
  > full_backup_$(date +%Y%m%d).sql

# 模式2：单库备份
mysqldump -u root -p --single-transaction mydb > mydb.sql

# 模式3：单表备份
mysqldump -u root -p --single-transaction mydb users > users.sql

# 模式4：仅结构
mysqldump -u root -p --no-data mydb > schema.sql

# 模式5：仅数据
mysqldump -u root -p --no-create-info mydb > data.sql

mysql -u root -p mydb < mydb_backup.sql

gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

USE mydb;
SOURCE /var/backups/mysql/mydb_backup.sql;

# 步骤1：创建目标数据库
mysql -u root -p -e "CREATE DATABASE mydb_restore;"

# 步骤2：恢复数据
mysql -u root -p mydb_restore < mydb_backup.sql

# 步骤3：验证行数
mysql -u root -p -e "SELECT COUNT(*) FROM mydb_restore.users;"

# 恢复 gzip 压缩备份
gunzip < /backup/mydb_20260703.sql.gz | mysql -u root -p mydb

# 恢复并显示进度
pv /backup/mydb_20260703.sql.gz | gunzip | mysql -u root -p mydb

SELECT id, name, email
FROM users
WHERE status = 'active'
INTO OUTFILE '/tmp/active_users.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/active_users.csv'
INTO TABLE users_copy
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- 导出订单数据为 CSV
SELECT order_id, customer_id, total_amount, order_date
FROM orders
WHERE order_date >= '2026-01-01'
INTO OUTFILE '/tmp/orders_2026.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 导入到另一张表
LOAD DATA INFILE '/tmp/orders_2026.csv'
INTO TABLE orders_archive
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA LOCAL INFILE '/home/alice/data/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- 查看 binlog 是否开启
SHOW VARIABLES LIKE 'log_bin%';

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

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

-- 查看 binlog 事件内容
SHOW BINLOG EVENTS IN 'binlog.000003';

# 步骤1：恢复全量备份
mysql -u root -p mydb < full_backup_20260703.sql

# 步骤2：找到误操作之前的时间点
mysqlbinlog --base64-output=DECODE-ROWS -v binlog.000003 | grep -A5 "DROP TABLE"

# 步骤3：重放 binlog 到目标时间点
mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:30:00" \
  binlog.000003 binlog.000004 | mysql -u root -p

场景：14:25 误执行了 DROP TABLE orders，需要恢复到 14:24:59 的状态

时间线：
  02:00  全量备份完成
  ...
  14:24  最后一次正常操作
  14:25  DROP TABLE orders（误操作）
  14:30  发现问题，开始恢复

恢复步骤：
  1. 恢复 02:00 全量备份
  2. 用 mysqlbinlog 重放 02:00 至 14:24:59 的 binlog
  3. 验证 orders 表数据完整性

# 实际执行命令
mysql -u root -p mydb < /backup/full_20260703.sql

mysqlbinlog --start-datetime="2026-07-03 02:00:00" \
  --stop-datetime="2026-07-03 14:24:59" \
  /var/lib/mysql/binlog.000003 \
  /var/lib/mysql/binlog.000004 \
  | mysql -u root -p mydb

# 全量备份
xtrabackup --backup --target-dir=/backup/full -u root -p

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

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

# 增量备份（基于全量）
xtrabackup --backup --target-dir=/backup/inc1 \
  --incremental-basedir=/backup/full -u root -p

# 准备增量备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/full
xtrabackup --prepare --target-dir=/backup/full \
  --incremental-dir=/backup/inc1

# 全量备份
xtrabackup --backup \
  --target-dir=/data/backup/full_20260703 \
  --user=root --password=Secret123

# 准备阶段
xtrabackup --prepare \
  --target-dir=/data/backup/full_20260703

# 恢复前停止 MySQL
systemctl stop mysqld

# 清空数据目录并恢复
rm -rf /var/lib/mysql/*
xtrabackup --copy-back \
  --target-dir=/data/backup/full_20260703

# 修复权限并启动
chown -R mysql:mysql /var/lib/mysql
systemctl start mysqld

# 使用 rsync 传输到远程
rsync -avz /backup/mysql/ backup-server:/data/mysql_backup/

# 使用 scp 传输
scp /backup/mysql/full_20260703.sql.gz backup-server:/data/backup/

#!/bin/bash
# mysql_backup.sh - MySQL 自动化备份脚本
BACKUP_DIR="/data/backup/mysql"
RETAIN_DAYS=7
DB_USER="root"
DB_PASS="Secret123"
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/mysql_backup.log"

mkdir -p $BACKUP_DIR
echo "[$DATE] 开始备份..." >> $LOG_FILE

mysqldump -u$DB_USER -p$DB_PASS \
  --all-databases --single-transaction \
  --routines --triggers --set-gtid-purged=OFF \
  | gzip > $BACKUP_DIR/full_${DATE}.sql.gz

if [ $? -eq 0 ]; then
  SIZE=$(du -sh $BACKUP_DIR/full_${DATE}.sql.gz | cut -f1)
  echo "[$DATE] 备份成功，大小: $SIZE" >> $LOG_FILE
else
  echo "[$DATE] 备份失败！" >> $LOG_FILE
  exit 1
fi

find $BACKUP_DIR -name "full_*.sql.gz" -mtime +$RETAIN_DAYS -delete
echo "[$DATE] 清理 ${RETAIN_DAYS} 天前的备份" >> $LOG_FILE

#!/bin/bash
# full_backup_restore.sh - 完整备份与恢复验证脚本
BACKUP_DIR="/data/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="Secret123"
REMOTE_HOST="backup-server"
REMOTE_DIR="/data/mysql_backup"
DATE=$(date +%Y%m%d)
LOG="/var/log/backup_restore.log"

echo "=== $DATE 备份恢复流程 ===" >> $LOG

# 1. 全量备份
mysqldump -u$MYSQL_USER -p$MYSQL_PASS \
  --all-databases --single-transaction \
  --routines --triggers \
  | gzip > $BACKUP_DIR/full_${DATE}.sql.gz
echo "[$DATE] 全量备份完成" >> $LOG

# 2. 刷新并记录 binlog 位置
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "FLUSH BINARY LOGS;"
BINLOG_FILE=$(mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -N -e "SHOW MASTER STATUS;" | awk '{print $1}')
echo "[$DATE] 当前 binlog: $BINLOG_FILE" >> $LOG

# 3. 备份 binlog 文件
cp /var/lib/mysql/$BINLOG_FILE $BACKUP_DIR/
echo "[$DATE] binlog 已备份" >> $LOG

# 4. 异地传输
rsync -avz $BACKUP_DIR/full_${DATE}.sql.gz \
  $REMOTE_HOST:$REMOTE_DIR/ >> $LOG 2>&1
echo "[$DATE] 异地传输完成" >> $LOG

# 5. 恢复验证（在临时库）
mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -e "CREATE DATABASE IF NOT EXISTS verify_db;"
gunzip < $BACKUP_DIR/full_${DATE}.sql.gz | \
  mysql -u$MYSQL_USER -p$MYSQL_PASS verify_db
ROW_COUNT=$(mysql -u$MYSQL_USER -p$MYSQL_PASS \
  -N -e "SELECT COUNT(*) FROM verify_db.users;")
echo "[$DATE] 验证 users 行数: $ROW_COUNT" >> $LOG

# 6. 清理验证库与过期备份
mysql -u$MYSQL_USER -p$MYSQL_PASS -e "DROP DATABASE verify_db;"
find $BACKUP_DIR -name "full_*.sql.gz" -mtime +7 -delete
echo "=== $DATE 备份恢复流程结束 ===" >> $LOG

0 2 * * * /usr/local/bin/full_backup_restore.sh