我们经常听人说，只要你想，半个月甚至一个月之内，MySQL可以恢复到任何状态。网上也流传着很多删除数据库跑路的笑话。。。所以今天松哥想和大家聊一聊MySQL中的binlog，教大家如何使用binlog恢复MySQL中的数据。这样以后万一不小心把数据库删了，那就不用跑路了。MySQL中比较重要的日志有binlog（归档日志）、redolog（重做日志）和undolog，所以binlog主要和我们这篇文章相关，另外两个日志宋哥有空再详细介绍将来。1.binlogbinlog中文一般称为archivedlog。如果你之前看过松哥发的MySQL主从构建，应该对这篇日志有印象。我们在搭建MySQL主从的时候，离不开binlog。Binlog是MySQLServer层的日志，不是存储引擎自带的日志。它记录了所有的DDL和DML（不包括数据查询语句）语句，并以事件的形式记录下来，包括语句执行所消耗的时间等，需要注意的是：binlog是一种逻辑日志，它记录SQL语句的原始逻辑，比如对某个字段+1。注意这里不同于redolog的物理日志（在某个数据页上对它做了什么修改）。binlog文件写满后，会自动切换到下一个日志文件继续写入，不会覆盖之前的日志。这也不同于重做日志。redolog是循环写入的，即后面的写入可能会覆盖前面的。进入。一般来说，我们在配置binlog的时候，可以指定binlog文件的有效期，这样过期后，日志文件会自动删除，避免占用更多的存储空间。根据MySQL官方文档，开启binlog后，会有1%左右的性能损失，但这还是可以接受的。一般来说，binlog有两个重要的使用场景：MySQL主从复制：在宿主机上启用Binlog，master将binlog同步到slave，slave通过binlog同步数据，从而实现master与从属之间的数据同步奴隶。MySQL数据恢复，使用mysqlbinlog工具结合binlog文件，可以将数据恢复到过去的某个时间点。2.开启binlog为了演示方便，宋大哥在Docker中安装了MySQL，我们就以此为例开始今天的演示。不知道怎么用docker的小伙伴可以在公众号后台回复docker，里面有宋哥写的教程。首先我们在docker中安装MySQL，然后进入容器，使用如下命令查看binlog是否开启：这个OFF表示binlog处于关闭状态，没有开启。接下来，我们将启用binlog。开启binlog主要是修改MySQL的配置文件mysqld.cnf，该文件在容器的/etc/mysql/mysql.conf.d目录下。对于这个配置文件，我们做如下修改：#该参数表示开启binlog功能，指定binlog的存放目录log-bin=javaboy_logbin#设置一个binlog文件的最大字节#设置最大100MBmax_binlog_size=104857600#设置binlog文件的大小有效期（单位：天）expire_logs_days=7#binlog日志只记录指定库的更新（配置主从复制时使用）#binlog-do-db=javaboy_db#binlog日志不记录指定库的更新（配置master-slave复制时会用到）#binlog-ignore-db=javaboy_no_db#多少次写入缓存，刷新一次磁盘，默认0表示这个操作是由操作系统根据自己的负载决定多久写一次磁盘#1表示每次事务commit都会立即写入磁盘，n表示n个事务会被提交写入thedisksync_binlog=0#获取当前服务的唯一id（MySQL5.7之后需要配置）server-id=1各项配置的含义松哥已经在看中描述了。截图如下：配置后完成后，执行如下命令重启mysql容器（这里mysql1是我容器的名字）：dockerrestartmysql1重启后，再次执行showvariableslike'log_bin%'；可以看到binlog已经开启。除了log_bin这个变量，还有两个变量名值得我们注意：log_bin_basename：这个是以后生成的binlog日志文件的名字前缀，也就是说按照目前看到的配置，binlog以后生成的日志文件名为javaboy_logbin.xxx，这个文件会用来记录所有的DDL和DML语句事件。log_bin_index：这个是binlog的索引文件，里面保存了所有的binlog目录，因为可能有多个binlog。我们可以看一下当前的javaboy_logbin.index文件：可以看到，目前只有一个logbin文件。3.常用的binlog操作下面介绍几种常用的binlog操作命令。1、查看所有binlog日志我们可以通过以下方式查看binlog日志列表：showmasterlogs；可以看到，我这里目前只有一个日志文件，文件名为javaboy_logbin.000001，file_size表示这个文件占用的字节大小是154.2。查看主状态这个命令在MySQL主从搭建的时候经常用到，如下：这时候可以看到最新的binlog日志文件名和上次操作事件的Position值（这个值有什么用，我们后面会给大家详细介绍）。3、刷新binlog一般情况下，一个binlog写满后，会自动切换到下一个binlog开始写入，但是我们也可以执行一个flushlogs命令来手动刷新binlog。手动刷新binlog后，会生成一个新的binlog日志文件，之后所有的binlog日志都会记录在一个新的文件中。如下：从上图可以看出，我们刷新日志后，可以通过showmasterlogs查看日志，发现生成了新的日志文件，再通过showmaster查看最新的日志文件信息status，发现也变成了javaboy_logbin.000002。4.resetbinlogresetmaster重置binlog日志文件，让log重新从000001开始记录，但是如果当前master有一个或多个slave在运行，那么这个命令就不起作用了（因为slave是通过binlog实现的数据库同步，host清除binlog，slave会报找不到binlog的错误）。5、查看binlog由于binlog是二进制日志文件，如果直接打开肯定是看不到的：看不到任何有用的信息。为了查看binlog，MySQL官方为我们提供了两个工具。让我们一一看看。首先是mysqlbinlog命令，如下：虽然看起来乱七八糟，但仔细看其实还是有迹可循的。因为这里是新安装的数据库，所以我只是新建了一个名为javaboy的库，然后创建了一个名为user的表并添加了两条数据，其他什么都没做，所以我们实际创建库的脚本可以在各种文档中找到。生成的日志文件中有一个end_log_pos，就是日志文件的pos点，对以后的数据恢复会有用。然而，这种观看方式对用户来说并不友好。我们说binlog是按照事件来记录日志的，那么如果能按照事件来查看日志就更好了。我们来看看下面的命令：showbinlogevents[IN'log_name'][FROMpos][LIMIT[offset,]row_count];意思是以事件的形式查看binlog，涉及到几个参数：log_name：可以指定要查看的binlog日志文件的名称，如果不指定，表示查看最早的binlog文件。pos：从哪个pos点开始查看。所有记录在binlog中的操作都有一个pos点。这实际上相当于我们可以指定从哪个操作开始查看日志。如果不指定，就是从binlog的开头开始查看。offset：这个是偏移量，如果不指定，默认为0。row_count：查看多少行记录，如果不指定，表示查看全部。我们来看一个简单的例子：showbinlogeventsin'javaboy_logbin.000001'；这样就清楚多了，我们可以看到之前的所有操作，例如：在Pos219-322之间创建了一个library。在Pos387-537之间创建了一个表。在Pos677-780之间添加一条记录。……四、数据恢复实战准备就绪。有了前面的基础知识准备，松哥就给大家展示一个手工删除/恢复数据库的场景。先说一下我的数据库目前的情况。这是一个新安装的数据库，我在其中新建了一个名为javaboy的数据库，并在javaboy库中新建了一张名为user的表。user中有两条记录，如下：现在假设我们定期（每周三凌晨三点）对数据库进行备份。现在是凌晨三点，开始了数据库的自动备份。我们使用如下命令将数据库备份成SQL脚本，如下：下面几个参数给大家解释一下：-u，-p不用多说。--flush-logs：意思是导出前刷新binlog。刷新binlog后，会生成一个新的binlog文件，后续的操作都会存储在新的binlog中。--lock-tables：这意味着在开始导出之前锁定所有表。需要注意的是，在导出多个数据库时，--lock-tables会分别为每个数据库锁定表，所以该选项不能保证数据库间导出文件中表的逻辑一致性，不同数据库表的导出状态可以完全不同。-B：表示要导出的数据库名称。如果使用--all-databases或-A而不是-B，则导出所有数据库。执行完上面的命令后，会在/root目录下生成一个javaboy.bak.sql文件，这个文件就是备份的sql文件。这是周三凌晨三点发生的事情。然后在星期四早上，我来上班了。简单的操作之后，又在数据库中添加了两个操作，如下：接下来，小X今天和领导吵架了，很不爽，于是决定删除运行：领导发现了一个大惊喜，立即请求立即恢复数据。该是你行动的时候了。首先，我们有周三早上的备份文件，先用该文件进行数据恢复：恢复后，我们现在有截至周三凌晨3:00的数据。从周三凌晨3:00到周四的数据现在没有了。这个时候我们就用binlog来恢复。大家还记得我们周三凌晨3点做备份的时候，用了一个参数叫--flush-logs，意思是从备份的那一刻起，新的binlog会在一个新的日志文件中生成。对于我们这里来说，新的binlog文件当然是javaboy_logbin.000002，我们查看文件：我这里生成的文件比较长，截取了一部分：可以看到发生在Pos764-865中删除数据库跑路事件，我们只需要回放文件，就可以将数据恢复到764位置。由于javaboy_logbin.000002文件是周三凌晨3点备份后生成的新文件，所以这个文件从头到764的Pos的操作是周三凌晨3点到删除数据库之前的操作。然后再看通过binlog恢复数据的命令：那么这里涉及到两个参数：--stop-position=764表示恢复到764的Pos，不指定则恢复整个文件.如果按当前文件恢复的话，由于binlog文件中有删除数据库的语句，执行binlog后javaboy库会被删除。--database=javaboy表示恢复javaboy库。另外，还有一个我们这里不用的参数--start-position，表示初始Pos。如果不指定，则表示数据恢复从头开始。好了，完成后，我们再查看一下数据库：数据恢复了~所有操作前，记得备份备份（以防出错回不去），松哥省略了上面的一些备份操作，以节省麻烦。5、小结好了，今天的文章主要是和小伙伴们分享MySQL的binlog日志，并用一个小案例来演示如何通过binlog实现数据库的删除和恢复。
