MySQL数据库表意外丢失怎么办?binlog是最后的救命稻草

如果哪天不小心把表给删了，数据都没了或者一不留神被删库跑路了，那怎么把数据恢复呢？这就需要今天的主角binlog登场了。

binlog 是什么

binlog又称二进制日志，是binary log的结合体。

还是看官方定义：

❝

The binary log contains “events” that describe database changes such as table creation operations or changes to table data.

❞

翻译：二进制日志包含描述数据库更改的“事件”，如表创建操作或表数据更改。

说白了就是二进制日志记录了对 MySQL 数据库执行更改的所有操作，若操作本身没有导致数据库发生变化，该操作可能也会写入二进制文件。 当然不包括 select和show这类不会对数据本身进行修改的操作。

binlog在语句或事务完成后立即执行，但在释放锁或执行提交之前执行，这可以确保按照提交顺序记录日志。对非事务性表的增删改操作在执行后立即存储在binlog中。

binlog 的作用

binlog主要有两个重要作用，恢复和主从复制，还有其他作用，审计。

恢复(recovery)：某些数据恢复操作需要使用二进制日志。恢复备份后，将重新执行在备份后记录的二进制日志中的事件。这些事件使数据库从备份点更新，数据将变化恢复到指定的时间点(Point-in-time)。

主从复制(replication)：主数据库向从数据库发送binlog到从数据库，从数据库执行和复制binlog从而将数据恢复到从数据库中。

审计(audit)：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

binlog的格式

MySQL有好3种格式记录binlog，但具体的格式取决于MySQL数据库的版本。3种格式如下：

STATEMENT：MySQL中的主从复制功能最初是基于SQL语句从源到副本的传播，这称为基于语句的日志记录(statement-based logging)。可以使用参数--binlog-format=STATEMENT进行配置。 优点：只需要记录执行语句的细节和上下文环境，不需要记录每一行的变化，减少了binlog日志量，节省了I/O。缺点：为了保证SQL语句能在slave上正确执行，必须记录上下文信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；另外，主从复制时，存在部分函数（如sleep）及存储过程在slave上会出现与master结果不一致的情况

ROW：默认值。基于行(row-based logging)修改的记录，仅保存修改的行记录。 优点：能清楚地记录每一行数据修改的细节，而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。缺点：记录的细节比较多，导致binlog文件会特别大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中。

MIXED：混合格式，上面两种格式的混用。默认情况下使用的是STATEMENT格式，在某些情况下会转换为ROW格式。一般的语句修改使用STATEMENT格式保存binlog，如一些函数，STATEMENT无法完成主从复制的操作，则采用ROW格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在STATEMENT和ROW之间选择一种.新版本的MySQL中对ROW格式也被做了优化，并不是所有的修改都会以ROW格式来记录，像遇到表结构变更的时候就会以STATEMENT模式来记录。至于update或者delete等修改数据的语句，还是会记录所有行的变更。

binlog的参数

log_bin：是否开启binlog，MySQL8.0之前默认是关闭的，之后默认是开启的。需要打开的话只要将配置文件中注释掉的log-bin参数打开，可通过语句SHOW VARIABLES LIKE '%log_bin%'查看binlog是否开启，ON是开启，OFF是关闭。log-bin可配置成log-bin=[name]的形式，其中name字段表示binlog文件的名字，可填可不填，如果不填的话name会自动变为主机名-bin，比如：host-bin.000001。其中000001代表序号，表示第一个binlog文件。

log_bin_index：binlog的索引文件的绝对路径，用来存储过往产生的binlog文件，通常情况下，不建议手动修改这个文件。 binlog的索引文件是一个文本文件，可通过记事本打开，里面存储的 默认情况下，log_bin_index的路径和名字跟log-bin的一样。比如：host-bin.index

log_bin_basename：binlog的路径和名字，可以通过log-bin配置。binlog默认在MySQL数据路径下。

max_binlog_size：单个binlog文件的最大值，默认值也是最大值是1GB，最小值为4096 byte。如果当前binlog文件超过最大值，MySQL会关闭当前binlog文件并重新打开一个并写入。

可以通过命令show binary logs查看都有哪些binlog文件

查看binlog内容

通过mysqlbinlog工具可以查看binlog存储的内容。首先，找到MySQL安装路径下的bin目录，然后进入命令行，我使用的是Windows系统，其他系统类似。最后，在命令行执行以下命令：

mysqlbinlog D:\ProgramData\mysql-bin.000001 --base64-output=decode-rows -v

binlog文件的目录要是绝对路径，并且目录中不要有空格。 --base64-output=decode-rows -v：表示binlog中的SQL语句以注释的形式展现出来。

有一张表如下：

orders@db_test

修改一下表中数据，通过上面命令查看binlog并截取其中一段

binlog部分内容

从binlog中可以看到记录更新前后的数据，这样就可以以可读的方式查看binlog了。

总结

开启binlog会使MySQL性能下降，官网中测试表名性能会下降1%左右。但是binlog的开启利大于弊，在数据需要恢复和主从复制的情况下，binlog的利显得更大。谁也不能保证一定不会出错，根据墨菲定律，只要有可能就一定会发生，万一数据库真的是不小心被格式化了，binlog就发挥很大作用了，binlog可以不用，但一定要有。