MySQL - 备份恢复与日志

MySQL 备份恢复与日志

备份恢复策略
冷备份
逻辑备份（mysqldump）
单个表的备份
时间点恢复
位置恢复
MyISAM 表修复
日志管理

备份恢复策略

数据库的备份是数据库管理中非常重要的工作。备份策略要考虑：

备份频率：根据数据变化频率和重要性确定，如每日全备 + 增量备份
备份方式：冷备份、逻辑备份、物理备份
恢复时间目标（RTO）：需要多快恢复
恢复点目标（RPO）：能容忍丢失多少数据
备份存储：异地备份、多副本
定期演练：定期测试备份的可恢复性

冷备份

冷备份是在数据库关闭状态下复制数据文件。这种方式简单可靠，但需要停机。

# 停止 MySQL
mysqladmin shutdown

# 复制数据文件（MyISAM 的 .frm/.MYD/.MYI，InnoDB 的 ibdata/ib_logfile）
cp -r /var/lib/mysql /backup/mysql_cold_$(date +%Y%m%d)

# 启动 MySQL
mysqld_safe &

逻辑备份

逻辑备份使用 mysqldump 工具导出 SQL 语句，是最常用的备份方式。

备份单个数据库：

mysqldump -u root -p crashcourse > crashcourse_backup.sql

备份多个数据库：

mysqldump -u root -p --databases db1 db2 db3 > databases_backup.sql

备份所有数据库：

mysqldump -u root -p --all-databases > all_backup.sql

只备份表结构：

mysqldump -u root -p --no-data crashcourse > structure.sql

只备份数据：

mysqldump -u root -p --no-create-info crashcourse > data.sql

恢复逻辑备份：

# 恢复整个数据库
mysql -u root -p crashcourse < crashcourse_backup.sql

# 或在 mysql 命令行中
mysql> USE crashcourse;
mysql> SOURCE /path/to/crashcourse_backup.sql;

mysqldump 的常用选项：

选项	说明
`--single-transaction`	InnoDB 一致性备份，不锁表
`--lock-tables`	锁定所有表（MyISAM）
`--lock-all-tables`	全局锁定所有数据库的所有表
`--master-data[=value]`	记录备份时的 binlog 位置
`--routines`	备份存储过程和函数
`--triggers`	备份触发器
`--events`	备份事件调度器
`--flush-logs`	备份后刷新日志
`--quick`	不缓存查询结果，逐行输出（大表必备）

单个表的备份

# 备份单个表
mysqldump -u root -p crashcourse customers orders > tables_backup.sql

# 导出为文本文件
SELECT * FROM customers INTO OUTFILE '/tmp/customers.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

# 导入文本文件
LOAD DATA INFILE '/tmp/customers.txt'
INTO TABLE customers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

对于大数据量的导入导出，SELECT INTO OUTFILE + LOAD DATA INFILE 比 mysqldump 更快，且不会对记录进行锁定。

binlog（二进制日志）

binlog 记录了所有更改数据库数据的 SQL 语句（除了查询语句），用于复制和恢复。

查看 binlog 是否开启：

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';

在 my.cnf 中开启 binlog：

[mysqld]
log-bin=mysql-bin
binlog_format=MIXED
expire_logs_days=7

查看 binlog 列表：

SHOW BINARY LOGS;
SHOW MASTER STATUS;

查看 binlog 内容：

# 命令行查看
mysqlbinlog mysql-bin.000001

# 查看特定位置范围的日志
mysqlbinlog --start-position=4 --stop-position=500 mysql-bin.000001

# 查看特定时间范围的日志
mysqlbinlog --start-datetime="2026-07-19 00:00:00" --stop-datetime="2026-07-19 12:00:00" mysql-bin.000001

时间点恢复

利用 binlog 可以将数据库恢复到任意时间点。

步骤：

先恢复最近的全量备份
再用 binlog 重放从备份点到目标时间的所有操作

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

# 2. 用 binlog 恢复到指定时间点
mysqlbinlog --start-datetime="2026-07-19 02:00:00" --stop-datetime="2026-07-19 10:00:00" mysql-bin.000003 | mysql -u root -p

位置恢复

利用 binlog 的位置信息进行更精确的恢复，跳过某条出错的语句。

# 查看 binlog 找到出错语句前后的位置
mysqlbinlog mysql-bin.000003

# 恢复到出错前（位置 500 之前）
mysqlbinlog --stop-position=500 mysql-bin.000003 | mysql -u root -p

# 从出错后继续恢复（从位置 800 开始）
mysqlbinlog --start-position=800 mysql-bin.000003 | mysql -u root -p

MyISAM 表修复

MyISAM 表损坏时可以使用修复工具：

# 检查表
myisamchk /var/lib/mysql/crashcourse/products.MYI

# 修复表
myisamchk -r /var/lib/mysql/crashcourse/products.MYI

# 安全恢复模式
myisamchk -o /var/lib/mysql/crashcourse/products.MYI

在 SQL 中也可以使用：

-- 检查表
CHECK TABLE products;

-- 修复表
REPAIR TABLE products;

日志管理

MySQL 有多种日志，各有用途：

错误日志：记录 mysqld 启动和停止以及运行中发生的严重错误。

-- 查看错误日志位置
SHOW VARIABLES LIKE 'log_error';

-- 在 my.cnf 中配置
-- [mysqld]
-- log-error=/var/log/mysql/error.log

binlog（二进制日志）：记录所有更改数据的语句，用于复制和恢复（见上文）。

查询日志（通用查询日志）：记录所有 SQL 语句，包括查询。默认关闭，因为日志量很大。

SHOW VARIABLES LIKE 'general_log%';

-- 开启查询日志
SET GLOBAL general_log = 'ON';

慢查询日志：记录执行时间超过 long_query_time 秒的查询，是优化 SQL 的重要工具。

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 在 my.cnf 中配置
-- [mysqld]
-- slow_query_log = 1
-- slow_query_log_file = /var/log/mysql/slow.log
-- long_query_time = 2
-- log_queries_not_using_indexes = 1

分析慢查询日志可以使用 mysqldumpslow 工具：

# 按耗时排序显示前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

日志轮转：日志文件会越来越大，需要定期轮转。对于 binlog 可以用 expire_logs_days 自动清理，或者手动刷新：

-- 刷新所有日志（新建 binlog 文件）
FLUSH LOGS;

-- 重置 binlog（清空所有 binlog，谨慎使用）
RESET MASTER;

MySQL - 备份恢复与日志