MySQL — binlog 操作与恢复

124次阅读

共计 4657 个字符，预计需要花费 12 分钟才能阅读完成。

binlog 开启、查看：
> show variables like ‘log_bin’; #查看是否开启

> set sql_log_bin=1 || set sql_log_bin=0；#启用 || 停用
> show binary logs; // 获取 binlog 文件列表，对应 MySQL-bin.index；

> show master logs; // 查看主上的 binlog
> show master status；// 查看当前正在写入的 binlog
> show binlog events; // 查看第一个 binlog 内容

> show binlog events in ‘mysql-bin.000002’ 指定查看。
binlog 删除：
1. 关闭 mysql 主从，关闭 binlog，然后重启数据库

2、设置自动清理：

> mysql -e “show variables like ‘expire_log%’;”
> mysql -e ‘set global expire_logs_days=3;’ // 设置自动清理时间为 3 天
> mysql -e ‘flush logs’ // 让二进制日志重新生成新文件，并自动清理过期日志
（如果执行不生效，确认 mysql-bin.index 与外面文件一致，否则删除多余的，再 > flush logs；）
3、

> PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); // 删除 10 天前的 MySQL binlog 日志
> purge master logs before ‘2012-03-30 17:20:00’; // 删除指定日期以前的日志索引中 binlog 日志文件
> purge master logs to ‘mysql-bin.000002’; // 删除指定日志文件的日志索引中 binlog 日志文件
> reset master; or reset slave; // 置空 master 的 binlog；置空 slave 的中继日志

若误删了最新的，确保 mysql-bin.index 内记录的和外面现有文件一致，否则删除多余的，再 flush logs

4、直接 rm

进入数据库数据目录 && 查看一下当前使用的 binlog 日志是哪个，除了这个以外的，其它都可以使用 rm，删除后把 mysql-bin.index 内容也对应一下
如果误删了当前使用的 binlog，你会发现 mysql 不记录 binlog 日志了，你要先更改 mysql-bin.index 文件内容，对应一下，然后去 flush logs。
—————————————————————————————————-

binlog 简介：

mysql 复制的三种方式：
基于 SQL 语句的复制(statement-based replication, SBR)

基于行的复制(row-based replication, RBR)

混合模式复制(mixed-based replication, MBR)

binlog 的三种格式：

Statement

每一条会修改数据的 sql 都会记录在 binlog 中，不需记录每一行的变化，减少日志量，节约了 io；
Row

5.1.5 版本的 MySQL 才开始支持 row level 的复制, 它不记录 sql 语句上下文相关信息，仅保存哪条记录被修改；
Mixed

从 5.1.8 版本开始，MySQL 提供了 Mixed 格式，实际上就是 Statement 与 Row 的结合，一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存
文件配置：
binlog_format = MIXED //binlog 日志格式
log_bin = 目录 /mysql-bin.log //binlog 日志名
expire_logs_days = 7 //binlog 过期清理时间
max_binlog_size 100m //binlog 每个日志文件大小，默认 1G
binlog 恢复：
1、开启 binlog，最好是改配置文件，或者数据库内直接打开
2、模拟数据写入，依照如下附录 ……. 查询当前的数据，然后记录下当前内容，如下：
mysql> select * from number;
+——————————–+
| 1 | 2016-06-29 23:27:15 |
| 2 | 2016-06-29 23:27:15 |
| 3 | 2016-06-29 23:27:15 |
| 4 | 2016-06-29 23:27:15 |
| 5 | 2016-06-29 23:27:15 |
| 6 | 2016-06-29 23:27:15 |
| 7 | 2016-06-29 23:27:15 |
| 8 | 2016-06-29 23:27:15 |
| 9 | 2016-06-29 23:27:15 |
| 10 | 2016-06-29 23:27:15 |
+——-+————————+
3、进行全量备份，mysqldump -F –master-data=2 backup |gzip > backup_all.sql.gz //-F 刷新 binlog 为 0002（或者我们最好每天早上 4 / 5 点进行一次全量备份）
4、继续模拟数据写入，执行附录下的内容，查看：
+—-+—————————+
| 1 | 2016-06-29 23:27:15 |
| 2 | 2016-06-29 23:27:15 |
| 3 | 2016-06-29 23:27:15 |
| 4 | 2016-06-29 23:27:15 |
| 5 | 2016-06-29 23:27:15 |
| 6 | 2016-06-29 23:27:15 |
| 7 | 2016-06-29 23:27:15 |
| 8 | 2016-06-29 23:27:15 |
| 9 | 2016-06-29 23:27:15 |
| 10 | 2016-06-29 23:27:15 |
| 11 | 2016-06-29 23:31:03 |
| 12 | 2016-06-29 23:31:03 |
| 13 | 2016-06-29 23:31:03 |
| 14 | 2016-06-29 23:31:03 |
| 15 | 2016-06-29 23:31:03 |
| 16 | 2016-06-29 23:31:03 |
| 17 | 2016-06-29 23:31:03 |
| 18 | 2016-06-29 23:31:03 |
| 19 | 2016-06-29 23:31:03 |
| 20 | 2016-06-29 23:31:03 |
+——-+———————+
5、删除数据表：delete from number;
6、再次模拟数据写入，select * from bumber;
+——+————————+
| id | updatetime |
+——+————————+
| 21 | 2016-06-29 23:41:06 |
| 22 | 2016-06-29 23:41:06 |
| 23 | 2016-06-29 23:41:06 |
| 24 | 2016-06-29 23:41:06 |
| 25 | 2016-06-29 23:41:06 |
| 26 | 2016-06-29 23:41:06 |
| 27 | 2016-06-29 23:41:06 |
| 28 | 2016-06-29 23:41:06 |
| 29 | 2016-06-29 23:41:06 |
| 30 | 2016-06-29 23:41:06 |
+——+————————+
7、恢复数据：
先锁住表，不进行数据操作：lock table number read;
之后最好刷新一下日志，产生新的 binlog，单独操作出问题的 binlog（先备份问题 binlog）
进数据库看问题的 pos 点：show binlog events in ‘mysql-bin.000002’; // 然后删除出问题的 pos 点记录
导入早上或某时的全量备份：mysql backup < backup_all.sql
binlog 日志恢复法一：
mysqlbinlog mysql-bin.000002 | mysql - u 用户名 - p 密码数据库名
[mysqlbinlog –start-position=1038 –stop-position=1164 –database=zyyshop mysql-bin.00002 | mysql -v zyyshop]
常用选项：
–start-position=953 起始 pos 点
–stop-position=1437 结束 pos 点
—start-datetime=”2013-11-29 13:18:54″ 起始时间点
—stop-datetime=”2013-11-29 13:21:53″ 结束时间点
—database=zyyshop 指定只恢复 zyyshop 数据库(一台主机上往往有多个数据库，只限本地 log 日志)
binlog 日志恢复法二：
mysqlbinlog mysql-bin.000002 > tmp.sql
vim tmp.sql // 找到 delete 那步操作，将之删除
mysql backup < tmp.sql // 然后把操作打入数据库
注：因为锁表后续没有数据插入，如果有数据写入，也要把最新的 binlog 导入到数据库。
8、查看会发现数据都已经回来了。