MySQL-binlog日志（日志管理&案例：使用binlog日志进行数据恢复）

置顶 已于 2023-04-11 16:56:50 修改
阅读量9.7k 收藏 22
点赞数 10
分类专栏： MySQL 文章标签： mysql
于 2020-05-30 21:10:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Allenzyg/article/details/106446992
版权

Binlog概念

Binlog其实是（binary log）MySQL的二进制日志，以二进制的形式记录了对于数据库的变更操作，不包括select和show操作。

  1. 用来查看MySQL变更
  2. MySQL的备份恢复
  3. MySQL的主从复制

文件位置

Binlog默认放置在数据目录下

Binlog的命名方式

mysql-bin.000001

binlog文件的生成方式

  1. MySQL启动的时候会产生binlog
  2. MySQL服务器在执行flush logs；可以产生新的binlog文件

关于binlog的参数

总体的关于binlog的参数如下：

sync_binlog            = 1
log_bin                = mysql-bin
binlog_format          = row
expire_logs_days        = 10
binlog_cache_size       = 4M
max_ binlog_cache_size  = 8M
max_binlog_size         = 1024M

相关参数讲解

Log_bin：决定了MySQL的binlog的名字，生成的binlog名字为mysql-bin.000001

Binlog_format：规定binlog的格式，binlog有三种格式statement、row、mixad，默认使用statement，建议使用row格式

Expire_logs_days：过期时间

Binlog_do_db：此参数表示只记录指定数据库的二进制日志

Binlog_ignore_db：此参数表示不记录指定的数据库的二进制日志

Sync_binlog：在提交n次事务后，进行binlog的落盘，0为不进行强行的刷新操作，而是由文件系统控制刷新日志文件，如果是在线交易和账有关的数据建议设置成1，如果是其他数据可以保持为0即可

Max_binlog_size：binlog文件的最大值，默认和最大是1G，并不能严格限定二进制文件的大小

Max_binlog_cache_size：表示binlog能够使用的最大cache内存大小

当我们执行多语句事务的时候，所有session的使用的内存超过max_binlog_cache_size的值时就会报错：”Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes ofstorage”

MySQL binlog格式

Statement格式

特点：记录每一条数据的sql，将执行的每一条sql记录在binlog中，减少日志量，节省IO，提高行能。

缺点：某些sql中的函数无法使用，比如sysdate()，在同步过程中会出现无法同步到问题。

Mixed格式

特点：一般的语句使用sql语句记录，遇到特殊的语句使用row格式记录，保证数据的一致性和复制的准确性

Row格式

特点：binlog中仅仅记录哪一条记录被修改，不记录dml的sql语句，会详细记录每一row的更改细节，不会出现无法复制的问题。

缺点：因为要记录每一条修改记录的日志，所有大量占用磁盘IO和大量使用磁盘空间。

清理过期的binlog日志

在开启MySQL的主从后，会产生大量的binlog日志文件，可能产生大量的磁盘空间

手工删除binlog

Mysql>reset master; //删除master的binlog，慎用！！！
Mysql>reset slave; //删除slave的中继日志
Mysql>purge master logs before ‘2019-11-22 16:39:01’; //删除指定日期以前的日志索引中binlog日志文件
Mysql>purge master logs to ‘binlog.000002’; //删除指定日志文件的日志索引中binlog日志文件

或者直接使用操作系统命令直接删除

自动删除binlog

通过binlog参数（expire_logs_days）来实现MySQL自动删除binlog

Mysql>show binary logs;
Mysql>show variables like ‘expire_logs_days’;
Mysql>set global expire_logs_days=3;

手工清除binlog文件

1. 可以通过MySQL命令清除binlog

使用reset master重置binlog文件

Mysql>reset master; //删除master所有的binlog文件，并且重置为1个，慎用！！！
Mysql>show master logs;
Log_name        file_size
Mysql-bin.000001     151

####reset master后，会造成slave无法找到master的严重后果

2. 直接rm本地删除binlog

查看正在使用的binlog

Mysql>show master status;
Fiel              position     binlog_do_db   binlog_ignore_db   executd_gtid_set
Mysql-bin.000001    151 

 直接使用rm命令删除不是当前使用的binlog

rm –rf mysql-bin.000002

###切记不要删除正在使用的binlog

-----------------------------------------------------------------------------------------------------------------

登录mysql之后使用下面的命令查看是否开启binlog

show variables like 'log_%';

开启binlog

编辑配置文件

vi /etc/my.cnf

添加如下内容：

server_id=2
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 30

重启mysql服务

systemctl restart mysqld

1.错误日志(log_error)

1.1 作用

记录启动\关闭\日常运行过程中,状态信息,警告,错误

1.2 错误日志配置

默认就是开启的:  /数据路径下/hostname.err

手工设定:

mysql>select @@log_error;
vim /etc/my.cnf
log_error=/var/log/mysql.log
log_timestamps=system
重启生效
show variables like 'log_error';

1.3 日志内容查看

主要关注[ERROR],看上下文

2. binlog(binary logs):二进制日志 *****

2.1作用：

  1. 备份恢复必须依赖二进制日志
  2. 主从环境必须依赖二进制日志

2.2Binlog配置（5.7必须加server_id）：

注意：MySQL默认是没有开启二进制日志的。

基础参数查看:

mysql> select @@log_bin;    --日志的开关
mysql> select @@log_bin_basename; --日志存放路径及名字
mysql> select @@server_id; --服务id号
mysql> select @@binlog_format; --二进制日志模式
mysql> select @@sync_binlog;

Mysql配置参数sync_binlog说明

MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。

默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

如果sync_binlog>0，表示每sync_binlog次事务提交，MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了，表示每次事务提交，MySQL都会把binlog刷下去，是最安全但是性能损耗最大的设置。这样的话，在数据库所在的主机操作系统损坏或者突然掉电的情况下，系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO，但是设置sync_binlog=1，多个事务同时提交，同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解，但是刷新的频率过高对IO的影响也非常大。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1，而是100或者是0。这样牺牲一定的一致性，可以获得更高的并发和性能。

2.3binlog记录了什么？

Binlog是sql层的功能，记录的是变更sql语句，不记录查询语句。

2.3.1记录sql语句种类

DDL：原封不动的记录当前DDL（statement）。

DCL：原封不动的记录当前DCL（statement）。

DML：只记录已经提交的事务DML。

2.3.2DML三种记录方式：

Binlog_format（binlog的记录格式）参数影响

  1. statement（5.6默认）SBR（statement based replication）：语句模式原封不动的记录当前DML
  2. ROW（5.7默认）RBR（row based replication）：记录数据行的变化（用户看不懂，需要工具分析）。
  3. Mixed（混合模式）MBR（mixed based replication）：以上两种模式的混合，一般不用这种模式

2.3.3 SBR和RBR模式的对比：（面试点）

Statement：可读性较高，日志量少，但是不够严谨

ROW：可读性低，日志量大，足够严谨

例如：

Update t1 set xxx=xxx where id > 1000;--->假如更新了500w行

如果是statement模式的话，只是记录这条sql语句，而row模式记录了这500w行数据变化。所以row模式日志量较大。

再例如：

Insert into t1 values (1,’zyg’,now());假如这条语句是2020/05/30，17:04:30执行的。

如果是statement模式的话，你在2020/05/31日查看的话，你会误以为是今天插进去，而row模式会记录具体的时间2020/05/30，17:04:30，所以row模式更加严谨。

所以我们建议使用：row记录模式。

2.4 Event（事件）是什么？

2.4.1 简介：

二进制日志的最小记录单元

对于DDL、DCL，一个语句就是一个event；对于DML语句来讲：只记录已提交的事务。

例如以下例子，就被分为了4个event

Begin;     120  -   240

DML1     240  -   360

DML2     360  -   480

Commit;   480  -   600

2.4.2 Event的组成

三部分构成

  1. 事件的开始标识
  2. 事件内容
  3. 事件的结束标识

2.5 日志文件查看

2.5.1 查看日志的开启情况

log_bin参数设置的路径,可以找到二进制日志

mysql> show variables like '%log_bin%';

2.5.2查看binlog日志文件有哪些：

mysql>show binary logs;

2.5.3 查看mysql正在使用的日志文件

mysql>show master status;

file：当前MySQL正在使用的文件名

Position：最后一个事件的结束位置号

2.6 日志内容查看

2.6.1 event查看

mysql>show binlog events in ‘master.000028’;

注释：

Log_name：binlog文件名

Pos：开始的position *****

Event_type：事件类型

Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息

Server_id：MySQL服务号标识

End_log_pos：事件结束位置号 *****

Info：事件内容 *****

2.6.2更多参数

mysql> help show binlog events;

2.6.3定位到具体sql语句：

[root@node4 mysql]# mysql -uroot -p -e "show binlog events in 'master.000024'" | grep create

2.6.4Binlog文件内容详细查看：

[root@node4 mysql]# mysqlbinlog /var/lib/mysql/master.000024

例如：

Position（位置号）：
开始标识：at 200
结束标识：end_log_pos 250（下一个开始标识不是251，而是250，上一个结束就是下一个的开始）
位置号的作用：为了方便我们截取事件

2.6.5转换成能看的懂的binlog文件内容查看：

[root@node4 mysql]# mysqlbinlog --base64-output=decode-rows -vvvvvv /var/lib/mysql/master.000024

注释：@1：代表第一列；@2代表第二列。。。以此类推

2.6.6只看某一个数据库的操作：

[root@node4 mysql]# mysqlbinlog --base64-output=decode-rows -vvvvvv /var/lib/mysql/master.000024 -d kettle

2.6.7日志截取查看：

1.查看某段时间日志

[root@node4 mysql]# mysqlbinlog --base64-output=decode-rows -vvvvvv --start-datetime="2020-05-22 15:01:53" --stop-datetime="2020-05-22 15:02:00" /var/lib/mysql/master.000024 -d kettle

注意：”=”两边不要有空格，不然会报错

2.查看某段position日志：

[root@node4 mysql]# mysqlbinlog --start-position=889 --stop-position=1003 /var/lib/mysql/master.000024 -d kettle

mysqlbinlog更多参数：

[root@node4 mysql]# mysqlbinlog –help
或者
[root@node4 mysql]# man mysqlbinlog

查看更多的参数，这里就不一一介绍了。

3案例：使用binlog日志进行数据恢复

1.

mysql> create database binlog charset utf8;

Query OK, 1 row affected (0.00 sec)

2.

mysql> use binlog;

Database changed

mysql> create table test(id int);

Query OK, 0 rows affected (0.33 sec)

3.

mysql> insert into test values (1);

Query OK, 1 row affected (0.01 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (2);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values (3);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

4.

mysql> drop database binlog;

Query OK, 1 row affected (0.35 sec)

5.查看当前用的日志事件

mysql> show master status;

6.

mysql> show binlog events in 'master.000028';

[root@node4 mysql]# mysqlbinlog --start-position=1492 --stop-position=2615 /var/lib/mysql/master.000028 > /tmp/bin.sql

mysql> show databases;

mysql> select @@sql_log_bin;

mysql> set sql_log_bin=0;

插一脚：

sql_log_bin用处：

1.当还原数据库的时候，如果不关闭二进制日志，那么你还原的过程仍然会记录在二进制日志里面，不仅浪费资源，那么增加了磁盘的容量，还没有必要（特别是利用二进制还原数据库的时候）所以一般还原的时候会选择关闭二进制日志，可以通过修改配置文件，重启关闭二进制日志。

也可以动态命令关闭sql_log_bin,然后导入数据库。

2. 如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。设为0后，在Master数据库上执行的语句都不记录binlog。要慎重使用global修饰符（set global sql_log_bin=0)，这样会导致所有在Master数据库上执行的语句都不记录到binlog，这肯定不是你想要的结果。

mysql> select @@sql_log_bin;

mysql> source /tmp/bin.sql

第79讲： MySQL Binlog二进制日志恢复误删数据的实践指南
江晓龙的博客
12-18 1万+
根据需求我们得知要恢复误删除的这张表，那么在事件信息中找到创建该表的事件，这个事件的开始标识位就作为我们截取Binlog日志的开始标识位号，结束标识位更好确定了，在事件信息中找到删除表的事件，这个事件的开始标识位也就作为我们截取Binlog日志的结束标识位，开始标识位号是上一个事件的结束表示为号。注意：一定不要将删除表事件的结束标识位号作为截取Binlog日志的结束标识位号，如果截取Binlog日志的结束标识位号是删除表的结束标识位号，就会把删除表的操作在还原时又进行了一遍，数据就不能恢复了。
MySQL-binlog2sql：非主从关系实现数据的【数据同步+数据恢复+数据追踪】
作者的博客园已搬家到CSDN，访问博客园主页将默认跳转至CSDN
11-24 1268
两个单独的数据库进行数据实时同步，binlog2sql工具实现反编译增量sql数据，进行传递并导入执行sql语句，实现数据的实时同步！
Mysql 开启bin-log日志
Mark66890620
01-04 1843
登录mysql数据库 mysql -u root -p 然后，输入密码登录 检查日志功能是否开启 show variables like 'log_%'; log_bin为OFF，表明binlog日志未开启 查找mysql的配置文件my.cnf的路径，默认的路径：/etc/my.cnf 编辑 vim /etc/my.cnf，然后按i，在文件[mysqld]加入下面3句，保存退出。（:wq）在[mysqld]下面增加 server-id=1 log-bin=/usr/local/
通过 mysqlbinlog 和 grep 命令定位binlog文件中指定操作
最新发布
hongjian006的博客
10-03 847
指定路径/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000012 | grep -i -A 10 -B 10 drop。指定路径/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v 指定路径/mysql_log/mysql3306_bin.000012 | grep -i drop。
mysql配置bin log参数_Mysql配置参数sync_binlog说明
weixin_32476639的博客
01-19 687
MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。默认，sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。如果sync_binlog>0，表示每sync_binlog次事务提交，M...
使用 MYSQLBINLOG 来恢复数据
weixin_33922672的博客
04-05 158
       BINLOG就是一个记录SQL语句的过程，和普通的LOG一样。不过只是她是二进制存储，普通的是十进制存储罢了。1、配置文件里要写的东西：[mysqld]log-bin=mysql-bin(名字可以改成自己的，如果不改名字的话，默认是以主机名字命名)重新启动MSYQL服务。二进制文件里面的东西显示的就是执行所有语句的详细记录，当然一些语句不被记录在内，要了解详细的，见手册页。2、查看自...
[MySQL Delay]生产环节主从延迟的问题解决过程记录： sync_binlog=0
系统 运维
01-20 422
接到山姆大叔的电话，主从延迟半个小时了 Seconds_Behind_Master: 7600 1， 检查show full processlist; 没有任何slow的dml sql语句。 2， 检查innodb status，没有任何lock的块。 3， 检查cacti，里面cpu usage从4%上升到了15%，Percona InnoDB I/O GT 从90%降低到了50...
开启MySQL的binlog日志
热门推荐
happyfly的博客
07-08 12万+
binlog 就是binary log，二进制日志文件，这个文件记录了mysql所有的dml操作。通过binlog日志我们可以做数据恢复，做主住复制和主从复制等等。对于开发者可能对binlog并不怎么关注，但是对于运维或者架构人员来讲是非常重要的。 如何开启mysql的binlog日志呢？ 在my.inf主配置文件中直接添加三行 log_bin=ON log_bin_basenam
第86讲：MySQLDump与Binlog日志实现企业级数据备份恢复案例
江晓龙的博客
01-08 1万+
这个参数，此参数会在备份文件中帮我们记录：从备份开始的时间算，Binlog中最近的一个GTID号、当前使用的Binlog日志名称、Binlog中事件的最近一个Position标识位号，有了这三个信息之后，找起点不再是难事，我们可以非常快速的指定我们要截取那些Binlog日志。（本次模拟过程中，没有将数据库所有文件删除，是因为Binlog也在这个路径，如果模拟数据库文件全部损坏，那么修复的时候就需要重新初始化数据库的，Binlog也会被覆盖，因此这里只模拟平台库数据库文件损坏，全部丢失。
go-mysql-canal:数据库binlog监听，elasticsearch同步
03-28
首先，MySQL 的 binlog 是一种记录所有更改数据库数据的事件的日志，这对于数据备份、恢复和复制至关重要。binlog 采用事件的形式记录，每个事件都包含了执行的 SQL 语句或者对数据进行操作的信息。go-mysql-canal ...
mysql 刷新二进制日志_使用binlog日志恢复MySQL数据库删除数据的方法
weixin_39674414的博客
02-01 1257
binlog日志简介:binlog 就是binarylog，二进制日志文件，这个文件记录了MySQL所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间。binlog日志包括两类文件：1)二进制日志索引文件(文件名后缀为.index)：用于记录所有的二进制文件；2)二进制日志文件(文件名后缀为.00000*)：记录数据库所有的DDL和DML(除了数据查询语句s...
Mysql之sync-binlog参数
weixin_34376986的博客
12-11 223
Mysql开启bin-log日志使用bin-log时，默认情况下，并不是每次执行写入就与硬盘同步，这样在服务器崩溃是，就可能导致bin-log最后的语句丢失。 可以通过这个参数来调节，sync_binlog=N，使执行N次写入后，与硬盘同步。1是最安全的，但是也是最慢的。 参考Mysql官网：http://dev.mysql.com/doc/refman/5.5/en/replication-...
MySQL面试必备二之binlog日志
weixin_43354181的博客
05-11 1139
关于 binlog，常被问到几个面试问题如下： 1. binlog 是什么 2. binlog 都记录什么数据 3. binlog 都有哪些类型，都有什么特点 4. 如何使用 binlog 恢复数据 5. binlog 都有哪些作用 6. binlog 属于逻辑日志还是物理日志
sync_binlog=1
weixin_33810302的博客
07-24 899
  MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。 sync_binlog=0，表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。 sync_binlog>0，表示每sync_binlog次事务提交...
Mysql开启binlog日志
hbhe0316的专栏
11-27 196
1.查看是否开启binlog日志 https://www.cndba.cn/hbhe0316/article/22610 https://www.cndba.cn/hbhe0316/article/22610 mysql> show variables like 'log_bin%'; +---------------------------------+---...
mysql日志
weixin_33923148的博客
07-11 246
MySQL日志： 1.查询日志 2.慢查询日志：查询执行时长超过指定时长的查询，即为慢查询 3.错误日志 4.二进制日志：复制功能依赖于此日志 5.中继日志： 6.事务日志： 随机I/O转换为顺序I/O ACID：持久性 日志文件组：至少应该有两个日志文件； 注意：尽可能使用小事务以提升事务引擎的性能； 查询日志： log={O...
sync_binlog
weixin_33969116的博客
06-10 229
http://blog.sina.com.cn/s/blog_4d8a2c970100f531.html sync_binlog=1 or N This makes MySQL synchronize the binary log’s contents to disk each time it commits a transaction 默认情...
mysql delaybin_[MySQL Delay]生产环节主从延迟的问题解决过程记录： sync_binlog=0
weixin_35761085的博客
02-03 99
接到山姆大叔的电话，主从延迟半个小时了Seconds_Behind_Master: 76001， 检查show full processlist; 没有任何slow的dml sql语句。2， 检查innodb status，没有任何lock的块。3， 检查cacti，里面cpu usage从4%上升到了15%，Percona InnoDB I/O GT 从90%降低到了50%%。4， 检查当前co...
MySQL binlog 恢复数据案例
06-03
下面是一个MySQL binlog恢复数据的案例，假设我们有一个名为`testdb`的数据库，里面有一张名为`testtable`的表，我们需要恢复这张表在某个时间点之前的数据。 1. 首先，我们需要确认我们的MySQL实例已经开启了...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

AllenGd

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值