mysql binlog恢复数据记录

基本介绍

binlog是Mysql sever层维护的一种二进制日志，与innodb引擎中的redo/undo log是完全不同的日志；其主要是用来记录对mysql数据更新或潜在发生更新的SQL语句，并以"事务"的形式保存在磁盘中；

作用主要有：

1. 复制：MySQL Replication在Master端开启binlog，Master把它的二进制日志传递给slaves并回放来达到master-slave数据一致的目的
2. 数据恢复：通过mysqlbinlog工具恢复数据
3. 增量备份

基本命令行：

• show variables like 'log_%'; 查看log_bin参数是否为ON
• show binlog events; 查看第一个binlog文件的内容
• show binlog events in 'mysql-bin.000001'; 查看指定binlog文件的内容
• show binary logs; 获取binlog文件列表
• show variables like '%binlog_format%'; 查看binlog的格式
• set global binlog_format='STATEMENT'; 设置binlog模式 设置之后需要退出mysql重新连接就能看到已修改（三种格式 statement | row | mixed）
• mysqlbinlog -d test /www/server/data/mysql-bin.000015 --start-position=6643; 解析mysqlbinlog，使用MySQLbinlog命令进行解析

Binglog参数：

-d, --database=name      仅显示指定数据库的转储内容。
-o, --offset=#           跳过前N行的日志条目。
-r, --result-file=name   将输入的文本格式的文件转储到指定的文件。
-s, --short-form         使用简单格式。
--set-charset=name       在转储文件的开头增加'SET NAMES character_set'语句。
--start-datetime=name    转储日志的起始时间。
--stop-datetime=name     转储日志的截止时间。
-j, --start-position=#   转储日志的起始位置。
--stop-position=#        转储日志的截止位置。

Binlog恢复数据示例：

1：查看当前binlog使用的位置和日志名称 show master status;

2：添加两行测试数据

3：在次查看位置 show master status;

4：删除一行数据 delete from t where id=4;并且查看数据只有3行（注意：之前表里有 1和2数据，添加3和4 删除的4）

5：binlog恢复数据生成sql文件 /www/server/mysql/bin/mysqlbinlog --start-position=9106 --stop-position=9648 /www/server/data/mysql-bin.000015 > test.sql （mysqlbinlog是自己的mysql/bin 下面执行，我这个是宝塔）
6：执行sql source test.sql

7:查看数据，已经恢复到删除之前

额外示例：

1：清空所有数据 truncate table t;

2：可利用管道符直接在mysql 执行

/www/server/mysql/bin/mysqlbinlog --start-position=9106  --stop-position=9648  /www/server/data/mysql-bin.000015  | mysql -uroot -p

注意：mysql -uroot -p 不要在-p 后面直接输入密码这样很不安全

3：数据已经恢复

恢复可能会遇到的错误

• 1062 错误是插入数据时唯一键冲突；
• 1032 错误是删除数据时找不到行。

    slave_skip_errors选项有四个可用值，分别为：off，all，ErorCode，ddl_exist_errors。

     默认情况下该参数值是off，我们可以列出具体的error code，也可以选择all，mysql5.6及MySQL Cluster NDB 7.3以及后续版本增加了参数ddl_exist_errors，该参数包含一系列error code（1007,1008,1050,1051,1054,1060,1061,1068,1094,1146）

    一些error code代表的错误如下：

    1007：数据库已存在，创建数据库失败

    1008：数据库不存在，删除数据库失败

    1050：数据表已存在，创建数据表失败

    1051：数据表不存在，删除数据表失败

    1054：字段不存在，或程序文件跟数据库有冲突

    1060：字段重复，导致无法插入

    1061：重复键名

    1068：定义了多个主键

    1094：位置线程ID

    1146：数据表缺失，请恢复数据库

    1053：复制过程中主服务器宕机

    1062：主键冲突 Duplicate entry '%s' for key %d



 my.cnf中的写法：

slave_skip_errors=1062,1053  

slave_skip_errors=all  

slave_skip_errors=ddl_exist_errors  

可以通过设置 slave_skip_errors 参数，slave_skip_errors 设置为 “1032,1062”，这样中间碰到这两个错误时就直接跳过。从上面字面意思也可以看出这两个问题是不会影响数据一致性问题的。

查看：show variables like 'slave_skip%';

根据时间恢复示例：

步骤和上面的根据位置恢复示例差不对，但是根据时间恢复在实际应用中比较有用；

命令： /www/server/mysql/bin/mysqlbinlog --start-datetime="2021-08-29 05:13:30" --stop-datetime="2021-08-29 05:14:04" /www/server/data/mysql-bin.000019 | mysql -uroot -p