Mariadb（Mysql）通过二进制日志实现数据恢复

亦凉 2021-05-12 11:55 580阅读 0赞

mysql从删库到跑路，你真的删干净了吗。。。数据库的日志还在吗，同步备份数据库日志脚本在执行吗，这么说来你是不是至少还有两个东西没删，会不会有种想回去继续删完在跑路0.0

**binlog：**

**简介：** mariadb的二进制日志文件，以事件的形式记录了mariadb的库表结构以及表数据的所有变更信息。mysql（mariadb是mysql的一个分支而已）系列还提供了其他三种日志：错误日志（error log）、普通日志（general log）、慢日志（slow log）。binlog不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询普通日志（general log）来查看MySQL执行过的所有语句。

**作用：** 个人认为主要的用途有两个：1、数据恢复，起到一个备份的作用；2、用于主从复制（从库copy主库的二进制文件从而导致从库数据和主库保持一致）

**开启二进制日志：**

1、打开mariadb的配置文件：D:\\mariaDB\\mariadb-10.0.17-winx64\\my.ini

2、查看\[mysqld\]下面的log-bin是否配置（网上都说这个默认是关闭的，但是mariadb好像默认是开启的0.0）

3、设置log-bin名字：log-bin=XXX，其中XXX位日志的名字，如果没有指定文件名,默认使用 datadir/log-basename-bin, datadir/mysql-bin 或者 datadir/mariadb-bin(如果也没有 –log-basename 选项,根据server版本的不同,会使用后两个中的一个);

4、设置binlog\_format：即日志以哪种形式记录，默认值是mixed  
![这里写图片描述][70]  
5、重启mariadb服务，然后查看binlog是否开启：show variables like ‘log\_bin’; on表示开启了  
![这里写图片描述][70 1]

**binlog\_format：** 二进制日志记录模式，分为三种：row，statement，mixed

1、row：  
简介：日志中记录每一行被修改的记录。  
优点：记录每行数据修改的记录，不需要太注重sql执行的上下文（即sql执行的顺序，但是只是不太需要注意，很多时候还是需要注意的），日志清晰容易理解，不会出现因为存储过程、某些函数或者触发器从而导致数据无法恢复的问题。  
缺点：因为记录的是每行被修改的日志，这样容易使日志的内容大大增加，比如：一句批量修改的sql，如果有1行满足修改的条件那完全能接受，如果是100万条呢，如果在row模式下，就会产生100万条的记录，想想就可怕。如果执行的是修改表结构的sql产生的日志就更是一个惊人的数量。

2、statement：  
简介：记录的是每条执行的sql。  
优点：不需要记录每一行数据的变化，只记录每条执行的sql，减少了 bin-log 日志量，节省 I/O 以及存储资源，提高性能。  
缺点：必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，保证sql执行的顺序。

3、mixed：根据执行的每一条具体的 SQL 语句来区分对待记录的日志形式，也就是在 statement 和 row 之间选择一种。

**如何通过二进制日志恢复数据呢**

完成以上配置之后，我们来进行一个实例：

1、创建一个名字为ceshi的数据源

2、创建表和数据

CREATE TABLE `testtbale` ( `id` int NULL , `name` varchar(255) NULL );
    INSERT INTO `testtbale` (`id`, `name`) VALUES ('1', '小明');
    INSERT INTO `testtbale` (`id`, `name`) VALUES ('2', '小红')

3、然后把小红给删了：

delete FROM testtbale where id=2;

4、查看当前日志是那个文件：show master status;  
![这里写图片描述][70 2]  
5、查看所有二进制文件列表（这里是因为新建的缘故，所以日志文件才一个）：show binary logs;  
![这里写图片描述][70 3]  
6、将日志转成sql脚本：mysqlbinlog ../data/mysql-bin.000001  
![这里写图片描述][70 4]  
然后问题来了，它的意思应该是默认值设置的有问题。。。

解决方法：

① 设置mariadb配置文件，即my.ini，将default-character-set=utf8改为character\_set\_server=utf8  
![这里写图片描述][70 5]  
然后重启mariadb，转换为sql脚本之后在讲这里改回去，然后再重启mariadb，显然很繁琐。。。

② 加一个参数：no-defaults  
总的语句为：mysqlbinlog - -no-defaults ../data/mysql-bin.000001 >myhzt.sql  
成功执行完之后，myhzt.sql这个文件将会在bin这个目录下产生，打开你会发现有一些其他数据源的日志也在里面，这样就使得整个sql脚本很乱

解决方法：解决方法当然是配置数据源喽

mysqlbinlog - -no-defaults - -database=ceshi ../data/mysql-bin.000001 >myhzt.sql 这样一来导出的就只有ceshi 这个数据源了，就算这个用户下还有其他数据源，导出来的sql脚本也只是躲了一下无效语句而已，如图：这些是其他数据源的sql脚本，设置数据源之后就会变成这样。  
![这里写图片描述][70 6]

7、找到我们刚才执行的那条delete语句，删除他，然后再其他地方新建一个数据源，然后执行以下这个sql脚本即可恢复数据。我们可以看到小红回来了0.0  
![这里写图片描述][70 7]

[70]: /images/20210511/525d35e559bb4ebfb1c3ae0ee8c8ca92.png
[70 1]: /images/20210511/3c0d21d771de48138aa685563fd5e009.png
[70 2]: /images/20210511/3472b60381fb40b49281f9173240bf2d.png
[70 3]: /images/20210511/2221b530b56a46f2be2ee01ab574c4bd.png
[70 4]: /images/20210511/8bbe70a78eff4270b47247e7258fa02d.png
[70 5]: /images/20210511/7f04f45423da4f82b521c478deef48b1.png
[70 6]: /images/20210511/562aad3b376547889c24d5ff4ab31f37.png
[70 7]: /images/20210511/994c0807cee94833aa2069030b09ff79.png