mysql日志文件和数据恢复

前言

删库跑路易操作，恢复数据如登天。
前人跑路坦荡荡，后人接盘长戚戚。

多少删库跑路的故事在坊间流传，每闻壮举心中荡气回肠，让人且为惊喜，你心中久久义愤的壮举终于成为他人随手一掷而巧妙幻化的烟花般璀璨夺目。但你不是过客你是归人，你是烟花浪漫之后的清洁工，你是巴山夜雨加班赶路的路人甲，没有踩局南山下的潇洒，只有午夜灯明后的落寞和无可奈何​。​

binlog 基本认识

MySQL的二进制日志可以说是MySQL最重要的日志了，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
一般来说开启二进制日志大概会有1%的性能损耗(参见MySQL官方中文手册 5.1.24版)。二进制有两个最重要的使用场景:
其一：MySQL Replication在Master端开启binlog，Mster把它的二进制日志传递给slaves来达到master-slave数据一致的目的。
其二：自然就是数据恢复了，通过使用mysqlbinlog工具来使恢复数据。

二进制日志包括两类文件：
二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，
二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

预备工作:开启binlog日志

vi编辑打开mysql配置文件

    # vi /usr/local/mysql/etc/my.cnf
    在[mysqld] 区块 设置/添加
     log-bin=mysql-bin  #默认目录下
	server-id=1 # 5.7以上版本需要配置该值，单机和集群中唯一即可

确认是打开状态(值 mysql-bin 是日志的基本名或前缀名)；因为mysql默认的是关闭日志，然后binary-log为产生日志文件的文件名，此文件在C:\ProgramData\MySQL\MySQL Server 5.7\Data下面可以看到 binary-log.000001文件这就是产生的日志二进制文件

重启mysqld服务使配置生效

# pkill mysqld
# /usr/local/mysql/bin/mysqld_safe --user=mysql & 

查看二进制日志是否已开启

通过mysql的变量配置表

mysql> show variables like '%log_bin%';
    +----------------------------------------+---------------------------------------+
    | Variable_name                          | Value                                 |
    +----------------------------------------+---------------------------------------+
    | log_bin                                | ON                                    | ------> ON表示已经开启binlog日志
    | log_bin_basename                       | /usr/local/mysql/data/mysql-bin       |
    | log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |

如果log_bin是OFF那么你就不要想着能够回复数据了。因此正式工程项目必须开启log_bin日志,以及其他尽可能的定期备份和热备方案。

参数说明：

log_bin=ON  
log_bin_basename=/var/lib/mysql/mysql-bin  
log_bin_index=/var/lib/mysql/mysql-bin.index
第一个参数是打开binlog日志 
第二个参数是binlog日志的基本文件名，后面会追加标识来表示每一个文件 
第三个参数指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录

常用binlog日志操作命令

常用binlog日志操作命令
1.查看所有binlog日志列表

mysql> show master logs;

2.查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;

3.刷新log日志，自此刻开始产生一个新编号的binlog日志文件

mysql> flush logs;

注：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4.重置(清空)所有binlog日志,编号也会重新开始,重要数据中禁止使用或者提前把日志备份

mysql> reset master;

作用包括：
删除binlog索引文件中列出的所有binlog文件
清空binlog索引文件
创建一个新的binlog文件
清空系统变量gtid_purged和gtid_executed
在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 mysql.gtid_executed数据表。

在slave上执行

mysql > RESET SLAVE

作用包括：
清除slave 复制时的master binlog的位置
清空master info, relay log info
删除所有的relay log文件，并创建一个新的relay log文件。
重置复制延迟(CHANGE MASTER TO 的 MASTER_DELAY参数指定的)为0。
另外，
RESET SLAVE 不会改变gtid_executed or gtid_purged.
RESET SLAVE 不会改变复制连接使用的参数，例如master host, master port, master user, or master password

5.查看binlog日志内容（以表格形式）

mysql>  show binlog events in 'mysql-bin.000002';

mysqlbinlog命令使用

mysqlbinlog功能是将mysql的binlog日志转换成Mysql语句，默认情况下binlog日志是二进制文件，无法直接查看。我们直接在mysql目录的bin目录下启动该命令。
　　（在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “–no-defaults”选项）
　　
mysqlbinlog命令部分参数：

mysqlbinlog
-d  //指定库的binlog
-r  //相当于重定向到指定文件
--start-position--stop-position //按照指定位置精确解析binlog日志（精确），如不接--stop-positiion则一直到binlog日志结尾
--start-datetime--stop-datetime //按照指定时间解析binlog日志（模糊，不准确），如不接--stop-datetime则一直到binlog日志结尾

备注：myslqlbinlog分库导出binlog，如使用-d参数，更新数据时必须使用use database
例：解析yj-test数据库的binlog日志并写入my.sql文件

./mysqlbinlog -d yj-test /home/data/mysql-log/mysql-bin.000003 -r my.sql

使用位置精确解析binlog日志

//使用位置精确解析binlog日志
./mysqlbinlog mysql-bin.000003 --start-position=100  --stop-position=200 -r my.sql

binlog的三种工作模式

查看binlog日志格式：

show variables like "binlog_format";

一共有三种模式
Row，Statement （默认） ，Mixed
（1）Row level
日志中会记录成每一行数据被修改的形式（不包括查询），然后在slave端再对相同的数据进行修改，只记录要修改的数据，不会有sql多表关联的情况。
优点：在row模式下，bin-log中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程和function，以及trigger的调用和出发无法被正确复制问题。
缺点：在row模式下，所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

（2）Statement level（默认）
　　每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高性能
缺点：在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。版本和兼容性问题会造成部分函数无法执行从而影响复制效果：

由于mysql现在发展比较快，很多的新功能不断的加入，使mysql的复制遇到了不小的挑战，自然复制的时候涉及到越复杂的内容，bug也就越容易出现。在statement中，目前已经发现不少情况会造成Mysql的复制出现问题，主要是修改数据的时候使用了某些特定的函数或者功能的时候会出现，比如：sleep()函数在有些版本中就不能被正确复制，在存储过程中使用了last_insert_id()函数，可能会使slave和master上得到不一致的id等等。由于row是基于每一行来记录的变化，所以不会出现，类似的问题。

（3）Mixed（混合模式）
　　结合了Row level和Statement level的优点。
　　在默认情况下是statement,但是在某些情况下会切换到row状态，如当一个DML更新一个ndb引擎表，或者是与时间用户相关的函数等。在主从的情况下，在主机上如果是STATEMENT模式，那么binlog就是直接写now()，然而如果这样的话，那么从机进行操作的时间，也执行now()，但明显这两个时间不会是一样的，所以对于这种情况就必须把STATEMENT模式更改为ROW模式，因为ROW模式会直接写值而不是写语句（该案例是错误的，即使是STATEMENT模式也可以使用now()函数，具体原因以后再分析）。同样ROW模式还可以减少从机的相关计算，如在主机中存在统计写入等操作时，从机就可以免掉该计算把值直接写入从机。

一般企业binlog模式的选择：
互联网公司使用MySQL的功能较少（不用存储过程、触发器、函数），选择默认的Statement level；
用到MySQL的特殊功能（存储过程、触发器、函数）则选择Mixed模式；
用到MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化一直则选择Row模式；

MySql中查询日志相关：
show variables like ‘log_bin’;
show variables like ‘%general_log%’;
show variables like ‘%log_%’;

数据恢复操作

主要包括三种方式
0.使用备份数据恢复历史数据
1.直接使用日志文件节点回复
2.通过日志文件转成sql语句执行sql语句恢复

后两种方式都需要启用了日志记录，否则无法操作。同样，日志记录理论上只能回复日志记录中的数据，如果在此之前没有日志记录（没有启用日志或者已经删除了历史日志）那么也无法还原数据。

如同数据量非常之大，那么数据整理恢复的过程将异常的艰辛，因此尽量做到定期备份和日志记录同时开启为妙。这样恢复数据时先启用备份，而后从备份数据节点之后的开始执行到删库和删数据之前的节点即可。

通过日志方式恢复部分记录

1.查看数据库是否开启了binglog日志，如果为NO则继续往下看，否则就别想了，除非你只想恢复备份过的数据，如果连备份的数据都没有那就啥都不用做了

show variables like '%log_bin%';

2.查看日志的当前记录

 show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000024 |         0 |
| mysql-bin.000025 |       480 |
+------------------+-----------+

3.根据日志查找最近删除的数据记录pos

 show binlog events in 'mysql-bin.000025';
 ------------------------------+                                                                                                                                                                                                               
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                          |                                                                                                                                                                                                               
+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------
                                                                    |
| mysql-bin.000025 |  219 | Query          |         2 |         480 | use `miaosha`; CREATE TABLE ` test` (  `pos` bigint(20) NOT NULL COMMENT 'pos', `info` varchar(500) DEFAULT NULL COMMENT 'info',  PRIMARY KEY (`pos`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
| mysql-bin.000025 |  673 | Write_rows     |         2 |         722 | table_id: 310 flags: STMT_END_F
                              |
| mysql-bin.000025 |  722 | Xid            |         2 |         753 | COMMIT /* xid=78 */
                              |
| mysql-bin.000025 |  753 | Anonymous_Gtid |         2 |         818 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
                              |
| mysql-bin.000025 |  818 | Query          |         2 |         893 | BEGIN
                              |
| mysql-bin.000025 |  893 | Table_map      |         2 |         946 | table_id: 310 (miaosha.test)
                              |
| mysql-bin.000025 |  946 | Write_rows     |         2 |         995 | table_id: 310 flags: STMT_END_F
                              |
| mysql-bin.000025 |  995 | Xid            |         2 |        1026 | COMMIT /* xid=80 */
                              |
| mysql-bin.000025 | 1026 | Anonymous_Gtid |         2 |        1091 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
                              |
| mysql-bin.000025 | 1091 | Query          |         2 |        1166 | BEGIN
                              |
| mysql-bin.000025 | 1166 | Table_map      |         2 |        1219 | table_id: 310 (miaosha.test)
                              |
| mysql-bin.000025 | 1219 | Write_rows     |         2 |        1269 | table_id: 310 flags: STMT_END_F
                              |
| mysql-bin.000025 | 1269 | Xid            |         2 |        1300 | COMMIT /* xid=82 */
                              |
| mysql-bin.000025 | 1300 | Anonymous_Gtid |         2 |        1365 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
                              |
| mysql-bin.000025 | 1365 | Query          |         2 |        1440 | BEGIN
                              |
| mysql-bin.000025 | 1440 | Table_map      |         2 |        1493 | table_id: 310 (miaosha.test)
                              |
| mysql-bin.000025 | 1493 | Update_rows    |         2 |        1557 | table_id: 310 flags: STMT_END_F
                              |
| mysql-bin.000025 | 1557 | Xid            |         2 |        1588 | COMMIT /* xid=84 */
                              |
| mysql-bin.000025 | 1588 | Anonymous_Gtid |         2 |        1653 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
                              |
| mysql-bin.000025 | 1653 | Query          |         2 |        1728 | BEGIN
                              |
| mysql-bin.000025 | 1728 | Table_map      |         2 |        1781 | table_id: 310 (miaosha.test)
                              |
| mysql-bin.000025 | 1781 | Update_rows    |         2 |        1846 | table_id: 310 flags: STMT_END_F
                              |
| mysql-bin.000025 | 1846 | Xid            |         2 |        1877 | COMMIT /* xid=86 */
                              |
| mysql-bin.000025 | 1877 | Anonymous_Gtid |         2 |        1942 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
                              |
| mysql-bin.000025 | 1942 | Query          |         2 |        2017 | BEGIN
                              |
| mysql-bin.000025 | 2017 | Table_map      |         2 |        2070 | table_id: 310 (miaosha.test)
                              |
| mysql-bin.000025 | 2070 | Delete_rows    |         2 |        2120 | table_id: 310 flags: STMT_END_F
                              |
| mysql-bin.000025 | 2120 | Xid            |         2 |        2151 | COMMIT /* xid=101 */

Event_type：会记录每次插入，修改，和删除记录，删除表，删库的记录类型
Write_rows ：插入记录
Update_rows：修改记录
Delete_rows：删除记录
Query：包含了创建库，创建表和删除库删除表的记录（select是不包含的没有记录的必要）

Log_name: mysql-bin.000021  ----------------------------------------------> 查询的binlog日志文件名
                     Pos: 11197 ----------------------------------------------------------> pos起始点:
              Event_type: Query ----------------------------------------------------------> 事件类型：Query
               Server_id: 1 --------------------------------------------------------------> 标识是由哪台服务器执行的
             End_log_pos: 11308 ----------------------------------------------------------> pos结束点:11308(即：下行的pos起始点)

4.查找关键节点的pos记录（尽量包含始末节点位置）
以这里我们删除的记录Delete_rows 为例，要恢复它删除的数据需要执行到它的记录之前的pos,即1877

当前的记录没有3，我们把它给还原回来
mysql> SELECT * FROM miaosha.`test`;
+-----+------+
| pos | info |
+-----+------+
|   1 | 1111 |
|   2 | 222  |
+-----+------+
2 rows in set (0.00 sec)

5.回复记录
如果我们不设置起始pos则它默认从日志记录的最开始的pos开始记录，由于所有的表创建语句都在其中那么这个数据回复记录会出异常：
Table ‘test’ already exists；同理，如果回复的语句中存在已经插入过的数据记录则也会造成主键冲突Duplicate entry ‘1’ for key ‘PRIMARY’

# mysqlbinlog     --stop-position=1877 --database=miaosha D:\data\MySQL\data\mys
ql-bin.000025 | mysql -h127.0.0.1 -uroot -p123456 -v miaosha
ERROR 1050 (42S01) at line 30: Table 'test' already exists

 #mysqlbinlog   --start-position=545 --stop-position=1877 --database=miaosha D:\data\MySQL\data\mysq
l-bin.000025 | mysql -h127.0.0.1 -uroot -p123456 -v miaosha
ERROR 1062 (23000) at line 30: Duplicate entry '1' for key 'PRIMARY'

因此我们只执行第三条记录插入前的pos开始执行，到它提交成功为止，如下则数据插入成功，可以看到记录已经回来了

# mysqlbinlog   --start-position=1091 --stop-position=1877 --database=miaosha D:\data\MySQL\data\mys
ql-bin.000025 | mysql -h127.0.0.1 -uroot -p123456 -v miaosha

mysql> SELECT * FROM miaosha.`test`;
+-----+------+
| pos | info |
+-----+------+
|   1 | 1111 |
|   2 | 222  |
|   3 | 3333 |
+-----+------+
3 rows in set (0.00 sec)

通过日志导出为sql语句方式回复记录

另一种方式，把日志记录导出sql语句，整理清除一些建表，插入数据的记录，主要是避免这些语句会造成的建表冲突和数据插入冲突
先导出日志记录到mysql，sql语句整理（删除一些建表语句，数据插入语句）
然后通过mysql source操作进行数据恢复

# mysqlbinlog --start-position=4 --stop-position=98 ../data/mysql-bin.000001 > test.sql
mysql>source test.sql

MySQL Relay log介绍

在MySQL主从复制结构下，Slave服务器会产生三种日志文件，用来保存主库的二进制日志事件以及relay log已执行到的位置和状态。
1、relay log 文件：由IO thread线程从主库读取的二进制日志事件组成，该日志被Slave上的SQL thread线程执行，从而实现数据的复制。
2、master info log：该文件保存slave连接master的状态以及配置信息，如用户名，密码，日志执行的位置等。在5.6版本之前，都是使用master.info文件，从5.6开始，通过在my.cnf 中配置 --master-info-repository=TABLE。这些信息会被写入mysql.slave_master_info 表中，代替原来的master.info文件了。
3、relay log info log：该文件保存slave上relay log的执行位置。在5.6版本之前，都是使用relay-log.info文件，从5.6开始，通过在my.cnf中配置 --relay-log-info-repository=TABLE，使用mysql.slave_relay_log_info表代替原来的文件。每次当slave上执行start slave时，就会读取该表中的位置信息。

新版本使用表来代替原来的文件，主要为了crash-safe replication，从而大大提高从库的可靠性。为了保证意外情况下从库的可靠性，mysql.slave_master_info和mysql.slave_relay_log_info表必须为事务性的表，从5.6.6起，这些表默认使用InnoDB存储引擎。在5.6.5及之前的版本默认使用MyISAM引擎，可用下面语句进行转换：

ALTER TABLE mysql.slave_master_info ENGINE=InnoDB;
ALTER TABLE mysql.slave_relay_log_info ENGINE=InnoDB;
【注意】不要试图手工的更新、插入、删除以上两个表的内容，以免出现意料不到的问题。

参考资料

https://blog.csdn.net/a7442358/article/details/47355515

请关注二维码