mysql中的binlog日志介绍及恢复数据

本文中所使用的MySQL版本:5.6.44

本文中所使用的测试数据表都是基于:INNODB引擎

mysql中的binlog日志是什么?

MySQL的binlog就是大家经常所说的Binary Log,也就是我们常说的binlog,即 二进制日志,二进制日志记录了MySQL所有修改数据库的操作,然后以二进制的形式记录日志在日志文件中,其中还包括每条语句所执行的时间和消耗的资源,以及相关的事务信息。


binlog记录了对MySQL数据库执行更改的所有操作,但是不包括SELECT和SHOW这类操作,因为这类操作对数据本身并没有修改。然后,若操作本身并没有导致数据库发生变化,那么该操作也会写入二进制日志。默认情况下二进制日志功能是没有开启的,开启二进制日志功能可以在mysql中的配置文件里配置 log-bin[=file_name] 来开启二进制日志。


binlog日志的作用:

用于MySQL主从复制

增量数据备份(不是所有数据备份,而是最近的写操作) 及 恢复误操作的数据


binlog日志在INNODB和MYISAM中的区别:

对于INNODB存储引擎的表:commit后才会写binlog,如果语句被cancel,作为事务性引擎,会回滚,不会出现更新一半,也不会记binlog。


对于MYISAM存储引擎的表:只要语法和语义没有错误,MYSQL不等执行语句执行完,就会写binlog。如果binlog_format=statement对于复制而言就很危险,因为主库可能会将它执行到一半的时候cancel(取消),然后这条语句却会在从库上完整的执行,造成主从数据不一致。


可见使用innodb表相比myisam无事务的表来说,从很多方面来看都安全很多。

binlog日志的启动和关闭

查看是否开启binlog日志:SHOW VARIABLES LIKE '%log_bin%';

示例截图:

m1.png

如果log_bin的值是OFF 表示binlog日志是关闭状态


如何开启binlog日志?

找到mysql的配置文件,windows上一般是my.ini,而在linux上一般是my.cnf。无所谓,只要你自己知道你的mysql配置文件使用的是那个就好。


在mysql配置文件中加入如下2个配置选项:

server-id=1

log-bin=mysql-bin #开启binlog日志


然后重启mysql服务后,再次使用SHOW VARIABLES LIKE '%log_bin%'; 命令查看binlog日志是否开启成功。

示例截图:

m2.png

m3.png

log_bin的值是 ON 表示binlog日志成功开启

log_bin_basename:表示二进制的文件保存路径


注意:一般网上的大部分开启binlog日志的答案都是mysql配置文件中加入:log-bin=mysql-bin 这一行配置项就行了,但是只使用这个可能会导致一些错误或不稳定性,所以推荐加入 server-id=1 这一行配置项。关于 server-id=1 代表什么意思,可以从本博客中的搜索框里搜索:mysql主从 然后点进去自行查看,或者自己上网查。


关于binlog日志的默认开启和关闭:额 一些集成环境貌似都是将binlog日志开启了,比如windows上的phpstudy、Visual NMP等。linux上的lnmp、宝塔等集成环境貌似也都是默认开启的~~~


查看当前二进制日志文件状态:  show master status;

show master status; 可以查看master数据库当前正在使用的二进制日志及当前执行二进制日志位置。

示例截图:

m1.png

对于binlog来说上图中的mysql-bin.xxx就是binlog的节点日志文件,而mysql-bin.index这个文件记录的是所有日志文件的地址


我这里使用的是lnmp环境,默认二进制日志文件路径为:/usr/local/mysql/var

示例截图如下:

m2.png

注意:上图中的mysql-bin.xxxxxx等文件可以删除,但是mysql-bin.index文件不能删除!


注意:如果重新启动了mysql服务,那么使用 show master status; 查看日志文件会发现mysql会重新生成了一份新的binlog日志文件(可以自己试一下,这里就不进行截图演示了),也就是说 一个binlog日志文件记录的是 从启动(或重启)之后的时间段的日志信息,直到mysql服务停止或者重新启动为止。


清空所有的binlog日志文件:reset master;

功能说明:删除所有的binglog日志文件,并将日志索引文件(mysql-bin.index 文件)中的内容清空,重新开始所有新的日志文件。


示例截图:

m1.png


再次进入/usr/local/mysql/var 目录 查看mysql的二进制日志文件

m2.png

上图中可以看到之前显示的mysql-bin.000014和mysql-bin.000015两个binlog日志文件已经被清空,binlog日志文件重新开始从mysql-bin.000001开始,然后查看 mysql-bin.index 文件,会发现里面记录的日志文件地址又是从 mysql-bin.000001 这个文件开始记录了


谨慎使用该命令。。不要随便使用这个命令。。


使用binlog日志进行数据恢复操作

前期准备工作:

先将mysql中的bin目录添加到环境变量中,为了方便我们下面操作不用写全路径。。

我这里安装的是lnmp的环境,lnmp环境中的mysql的bin目录路径为:/usr/local/mysql/bin

vi /etc/profile

#在文件中加入下面2行代码,mysql中的bin目录换成你自己的
PATH=$PATH:/usr/local/mysql/bin
export PATH

#然后保存退出

最后:执行命令 source /etc/profile 或 执行点命令 ./profile使其修改生效,执行完可通过echo $PATH 命令查看是否添加成功。


开始数据恢复操作演示:

添加一下测试的数据操作

CREATE DATABASE bin DEFAULT CHARACTER SET utf8;

use bin;

CREATE TABLE `t`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

INSERT INTO t VALUES(null, 'php');
INSERT INTO t VALUES(null, 'mysql');


这个时候我们可以使用以下2中方式来查看我们的mysql中的二进制文件信息

由于我们上面执行了一遍reset master; 所以我们这里的binlog日志文件变成了 mysql-bin.000001 这个文件。


①、在终端中执行:mysqlbinlog ./mysql-bin.000001

注意:后面的 mysql-bin.000001 根据自己的实际情况来,别啥都不管一通复制。。最后发现结果不是你想要的或报错。。


示例截图:

m1.png

噗。。由于文件内容实在太多。。笔记本屏幕展示不全所有信息。。所以显示的完整日志文件内容如下:

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190928 20:38:22 server id 1  end_log_pos 120 CRC32 0xd63e04c0 	Start: binlog v 4, server v 5.6.44-log created 190928 20:38:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
PlSPXQ8BAAAAdAAAAHgAAAABAAQANS42LjQ0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA+VI9dEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcAE
PtY=
'/*!*/;
# at 120
#190928 20:38:32 server id 1  end_log_pos 238 CRC32 0xf0d50a9f 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1569674312/*!*/;
SET @@session.pseudo_thread_id=7/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE DATABASE bin DEFAULT CHARACTER SET utf8
/*!*/;
# at 238
#190928 20:38:54 server id 1  end_log_pos 590 CRC32 0x749950bb 	Query	thread_id=7	exec_time=0	error_code=0
use `bin`/*!*/;
SET TIMESTAMP=1569674334/*!*/;
CREATE TABLE `t`  (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact
/*!*/;
# at 590
#190928 20:39:08 server id 1  end_log_pos 667 CRC32 0x21928d55 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1569674348/*!*/;
BEGIN
/*!*/;
# at 667
# at 699
#190928 20:39:08 server id 1  end_log_pos 699 CRC32 0x0944f341 	Intvar
SET INSERT_ID=1/*!*/;
#190928 20:39:08 server id 1  end_log_pos 804 CRC32 0xe2cc779d 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1569674348/*!*/;
INSERT INTO t VALUES(null, 'php')
/*!*/;
# at 804
#190928 20:39:08 server id 1  end_log_pos 835 CRC32 0x747f6612 	Xid = 557
COMMIT/*!*/;
# at 835
#190928 20:39:10 server id 1  end_log_pos 912 CRC32 0x181bc814 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1569674350/*!*/;
BEGIN
/*!*/;
# at 912
# at 944
#190928 20:39:10 server id 1  end_log_pos 944 CRC32 0xc75b4910 	Intvar
SET INSERT_ID=2/*!*/;
#190928 20:39:10 server id 1  end_log_pos 1051 CRC32 0xaa340797 	Query	thread_id=7	exec_time=0	error_code=0
SET TIMESTAMP=1569674350/*!*/;
INSERT INTO t VALUES(null, 'mysql')
/*!*/;
# at 1051
#190928 20:39:10 server id 1  end_log_pos 1082 CRC32 0x1ea21d2e 	Xid = 558
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

通过以上二进制日志文件中的内容可以看上面我创建的数据库(bin)、数据表(t)、插入的2条数据通通都有记录。不过这种方式不推荐使用。。因为看起来实在是不容易。。推荐下面这种第二种方式。。


②、使用show binlog events; 命令查看某个binlog二进制日志文件中的内容

show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

各个选项解析说明:

IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)

FROM pos:指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)

LIMIT [offset,]:偏移量(不指定就是0)

row_count:查询总条数(不指定就是所有行)


这里的查看命令为:show binlog events in 'mysql-bin.000001';


示例截图如下:

m1.png

Log_name:顾名思义就是 查询的binlog日志的文件名

Pos:Pos的起始点

Event_type:事件类型

Server_id:标识是由哪台服务器执行的 

End_log_pos:Pos的结束点(即:下次的Pos的起始点)

Info:执行的sql语句信息


③、接下来做一个操作就是将t表中的所有数据进行删除(这是模拟某些不小心的同学做的操作)

mysql> delete FROM t;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)


然后可以在mysql使用 show binlog events; 查看二进制日志文件信息。

注意:show binlog events; 这里没有指定'log_name',则显示的是 第一个binlog的二进制日志文件中的内容信息,即mysql-bin.000001


或者直接指定要显示哪一个的binlog日志文件信息:show binlog events in 'mysql-bin.000001';


示例截图如下:

m1.png


现在来做恢复;根据数据情况找到数据的节点位置, 在我们这里 发现节点是从590开始到1082结束


为了防止报错,还是进入mysql的binlog日志文件中执行吧,并且指定mysqlbinlog这个程序的全路径吧。

#进入保存binlog日志文件中的目录
/usr/local/mysql/var

#开始将起始节点为 590 到 结束节点为 1082 这段节点之间的数据恢复到mysql中
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 --start-position 590 --stop-position 1082  | mysql -u root -p

然后按回车 然后输入密码即可


#/*********分割线************/

#除了以上这种直接恢复到数据库外,还可以将恢复的数据写入单个文件,然后将该文件导入到mysql中,这里只写一下相关命令,就不做截图演示了
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql  #将数据写入到一个文件 
shell> mysql -u root -p -e "source /tmp/statements.sql"  #将文件中的数据导入到mysql中

示例截图如下:

m2.png


m3.png

最后查看select * from t; 发现数据已经恢复了。


注意:我这里使用的是根据起始节点和结束节点的方式来恢复数据  除了这种方式外,也可以根据日期来恢复数据的。


删除跑路? 不存在的。  不慌 问题不大!不是只有DBA才能帮你找回数据   自己也可以!


简单总结:

①、使用binlog只能针对针对数据量不是很多的情况,真正的生产环境每个几个G的日志文件,不止是光靠binlog恢复的,还有更多其它的办法。(比如 每天都要将数据备份N遍等。。)

②、判断binlog日志的时间阶段与pos节点位置很重要,但是需要知道关于binlog的很多参数



不要输了就跑  不要疼了就抱怨  -->【华尔街】



声明:禁止任何非法用途使用,凡因违规使用而引起的任何法律纠纷,本站概不负责。

扫码支持
扫码打赏,你说多少就多少

打开支付宝扫一扫,即可进行扫码打赏哦

精彩评论

全部回复12人评论7,777人参与