Mysql学习之--Mysql二进制日志管理

简介:

简介:
     MySQL的二进制日志可以说或是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是失误安全型的.
      MySQL的二进制日志的作用是显而易见的,可以方便的备份这些日志以便做数据恢复,也可以作为主从复制的同步文件,然而二进制日志的大小可能会根据不同的需求而存在麻烦,所以让日志回滚是必须的,当然MySQL已经为我们提供了二进制回滚的功能,那就是max_binlog_size参数。

       默认MySQL的二进制日志达到1G后就会自动回滚,如果我们想要更小的二进制日志,可以使用max_binlog_size参数来设置。测试使用max_binlog_size=200M,具体应用就是在配置文件里添加这个参数max_binlog_size=200M,官方文档是这样解释的:

        mysqld在每个二进制日志名后面添加一个数字扩展名。每次你启动服务器或刷新日志时该数字则增加。如果当前的日志大小达到 max_binlog_size,还会自动创建新的二进制日志。如果你正使用大的事务,二进制日志还会超过 max_binlog_size:事务全写入一个二进制日志中,绝对不要写入不同的二进制日志中。也就是说,在达到max_binlog_size的时候,如果正在处理一个大的事务,那么二进制日志会在处理完这个事务后才会回滚,所以该二进制日志可能会大于所设定的max_binlog_size。

         在主从复制的应用中,可能我们不希望二进制日志过大,因为日志过大可能会影响日志的执行效率,适当调整max_binlog_size的值还是非常有意义的,当然还要记得定期清理长时间不用的日志哦,要不然你的硬盘空间很快就会被用完的。

开启mysql二进制日志:

编辑my.cnf,添加

1
2
3
4
5
6
[root@ogg mysql]# cat /etc/my.cnf
# binary logging is required  for  replication
log-bin = / var /lib/mysql/mysql-bin
# binary logging format - mixed recommended
#binlog_format=mixed
binlog_format=row

     开启日志后需要myssqladmin flush logs才能生效。需要注意的是log-bin指定扩展名是无效的,当mysql创建二进制日志文件时,首先创建一个以“mysql_log_bin”为名称,以“.index”为后缀的文件;再创建一个以“mysql_log_bin”为名称,以 “.000001”为后缀的文件。当mysql服务重新启动一次以“.000001”为后缀的文件会增加一个,并且后缀名加1递增;如果日志长度超过了 max_binlog_size的上限(默认是1G)也会创建一个新的日志文件;使用flush logs(mysql命令符)或者执行mysqladmin –u –p flush-logs(windows命令提示符)也会创建一个新的日志文件。

案 例分析:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
事务做DML操作:
mysql>  delete  from  test2  where  id= 60 ;
Query OK,  1  row affected ( 0.01  sec)
mysql> commit;
Query OK,  0  rows affected ( 0.00  sec)
 
mysql>   insert  into  test2 values ( 60 , 'jack' );
Query OK,  1  row affected ( 0.00  sec)
mysql> commit;
Query OK,  0  rows affected ( 0.00  sec)
 
mysql> update test2 set name= 'hellen'  where  id= 50 ;
Query OK,  1  row affected ( 0.01  sec)
Rows matched:  1   Changed:  1   Warnings:  0
mysql> commit;
Query OK,  0  rows affected ( 0.00  sec)
 
查看日志:
[root@ogg mysql]# mysqlbinlog mysql-bin .000008  -d test
 
/*!40019 SET @@session.max_insert_delayed_threads=0*/ ;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/ ;
DELIMITER  /*!*/ ;
#  at  4
# 140928  13 : 44 : 37  server id  1   end_log_pos  114    Start: binlog v  4 , server v  5.6. 4 -m7-log created  140928  13 : 44 : 37  at  startup
# Warning:  this  binlog is either  in  use  or  was  not  closed properly.
ROLLBACK /*!*/ ;
BINLOG '
RaAnVA8BAAAAbgAAAHIAAAABAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABFoCdUEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAADM7vj8=
' /*!*/ ;
#  at  114
# 140928  13 : 45 : 40  server id  1   end_log_pos  182    Query   thread_id= 3      exec_time= 0      error_code= 0
SET TIMESTAMP= 1411883140 /*!*/ ;
SET @@session.pseudo_thread_id= 3 /*!*/ ;
SET @@session.foreign_key_checks= 1 , @@session.sql_auto_is_null= 0 , @@session.unique_checks= 1 , @@session.autocommit= 1 /*!*/ ;
SET @@session.sql_mode= 0 /*!*/ ;
SET @@session.auto_increment_increment= 1 , @@session.auto_increment_offset= 1 /*!*/ ;
/*!\C latin1 */ /*!*/ ;
SET @@session.character_set_client= 8 ,@@session.collation_connection= 8 ,@@session.collation_server= 8 /*!*/ ;
SET @@session.lc_time_names= 0 /*!*/ ;
SET @@session.collation_database=DEFAULT /*!*/ ;
BEGIN
/*!*/ ;
#  at  182
#  at  229
# 140928  13 : 45 : 40  server id  1   end_log_pos  229    Table_map: `test`.`test2` mapped to number  66
# 140928  13 : 45 : 40  server id  1   end_log_pos  268    Delete_rows: table id  66  flags: STMT_END_F
BINLOG '
hKAnVBMBAAAALwAAAOUAAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
hKAnVBkBAAAAJwAAAAwBAAAAAEIAAAAAAAEAAv/8PAAAAARqYWNr
' /*!*/ ;
#  at  268
# 140928  13 : 45 : 40  server id  1   end_log_pos  295    Xid =  11
COMMIT /*!*/ ;
#  at  295
# 140928  13 : 46 : 39  server id  1   end_log_pos  363    Query   thread_id= 3      exec_time= 0      error_code= 0
SET TIMESTAMP= 1411883199 /*!*/ ;
BEGIN
/*!*/ ;
#  at  363
#  at  410
# 140928  13 : 46 : 39  server id  1   end_log_pos  410    Table_map: `test`.`test2` mapped to number  66
# 140928  13 : 46 : 39  server id  1   end_log_pos  449    Write_rows: table id  66  flags: STMT_END_F
BINLOG '
v6AnVBMBAAAALwAAAJoBAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
v6AnVBcBAAAAJwAAAMEBAAAAAEIAAAAAAAEAAv/8PAAAAARqYWNr
' /*!*/ ;
#  at  449
# 140928  13 : 46 : 39  server id  1   end_log_pos  476    Xid =  14
COMMIT /*!*/ ;
#  at  476
# 140928  13 : 49 : 22  server id  1   end_log_pos  544    Query   thread_id= 3      exec_time= 0      error_code= 0
SET TIMESTAMP= 1411883362 /*!*/ ;
BEGIN
/*!*/ ;
#  at  544
#  at  591
# 140928  13 : 49 : 22  server id  1   end_log_pos  591    Table_map: `test`.`test2` mapped to number  66
# 140928  13 : 49 : 22  server id  1   end_log_pos  643    Update_rows: table id  66  flags: STMT_END_F
BINLOG '
YqEnVBMBAAAALwAAAE8CAAAAAEIAAAAAAAEABHRlc3QABXRlc3QyAAID/gL+CgM=
YqEnVBgBAAAANAAAAIMCAAAAAEIAAAAAAAEAAv ///DIAAAAEam9obvwyAAAABmhlbGxlbg==
' /*!*/ ;
#  at  643
# 140928  13 : 49 : 22  server id  1   end_log_pos  670    Xid =  16
COMMIT /*!*/ ;
DELIMITER ;
# End of log file
ROLLBACK  /* added by mysqlbinlog */ ;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/ ;

查看日志:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@ogg mysql]# pwd
/ var /lib/mysql
[root@ogg mysql]# ls -l
total  28820
-rw-rw----  1  mysql mysql        56  Sep  17  14 : 03  auto.cnf
-rw-rw----  1  mysql mysql  18874368  Sep  28  15 : 23  ibdata1
-rw-rw----  1  mysql mysql   5242880  Sep  28  15 : 23  ib_logfile0
-rw-rw----  1  mysql mysql   5242880  Sep  17  13 : 51  ib_logfile1
-rw-rw----  1  mysql mysql        32  Sep  26  17 : 35  master-log-bin.index
drwx------  2  mysql root       4096  Sep  17  13 : 51  mysql
-rw-rw----  1  mysql mysql       133  Sep  28  13 : 14  mysql-bin .000001
-rw-rw----  1  mysql mysql       574  Sep  28  13 : 22  mysql-bin .000002
-rw-rw----  1  mysql mysql       417  Sep  28  13 : 27  mysql-bin .000003
-rw-rw----  1  mysql mysql       315  Sep  28  13 : 29  mysql-bin .000004
-rw-rw----  1  mysql mysql       314  Sep  28  13 : 31  mysql-bin .000005
-rw-rw----  1  mysql mysql       133  Sep  28  13 : 33  mysql-bin .000006
-rw-rw----  1  mysql mysql       314  Sep  28  13 : 44  mysql-bin .000007
-rw-rw----  1  mysql mysql      9539  Sep  28  15 : 23  mysql-bin .000008
-rw-rw----  1  mysql mysql       256  Sep  28  13 : 44  mysql-bin.index
-rw-rw----  1  mysql root      77132  Sep  28  15 : 13  ogg.err
-rw-rw----  1  mysql mysql         5  Sep  28  13 : 44  ogg.pid
drwx------  2  mysql mysql      4096  Sep  17  13 : 51  performance_schema
drwx------  2  mysql root       4096  Sep  26  15 : 33  test

由于日志是以二进制方式存储的,不能直接读取,需要使用mysql自带的mysqlbinlog工具来进行查看

mysqlbinlog mysql-bin.000002 -d test

mysqlbinlog有一些选项可以使用,简单说明常用选项:

-d,--database=name :指定数据库名称,只列出指定数据库的操作.
-D, --disable-log-bin :执行恢复的时候,禁止二进制日志.可以防止同一台MySQL加上-t时进入死循环
-o,--offset=n :忽略掉日志前n行命令
-r,--result-file=name :将输出日志到指定文件
-R, --read-from-remote-server :从一个MySQL服务器上读取二进制
-s,--short-form :显示简单格式,省略一些信息
-S, --socket=name  :socket文件连接path.
-t, --to-last-log  :和-R一起使用,在二进制日志结束的时候并不会停止,而是在MySQL服务器最后生成的binlog结束,如果输出和输入都在一台MySQL上可能会导致死循环.
--set-charset=char-name :在输出文本格式的时候,在第一行加上set names char-name.
--start-datetime=# --stop-datetime=# :指定输出起始日期的日志.
--start-position=# --stop-position=# :指定起始日志的位置.

清理:
删除全部二进制日志:

reset master

删除部分日志:

PURGE MASTER LOGS TO & PURGE MASTER LOGS BEFORE
PURGE MASTER LOGS TO 'mysql-bin.******'命令,是将'******'编号之前的所有日志进行删除
PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:mm:ss'命令,是将在'yyyy-mm-dd hh:mm:ss'时间之前的所有日志进行删除

设置日志过期时间:
修改my.cnf

expire_log_day=5

这里设置保存5天的日志,超过5天的日志会被自动删除

恢复:
完全恢复:

mysqlbinlog mysql-bin.00001|mysql -uroot -p

基于时间点的恢复:
如果误删了一张表,使用完全恢复是没有用的,因为日志里同样也保留着删除的sql语句,所以我们需要恢复到误操作前的状态,然后跳过误操作的语句。
假如我在20:00误删了一张表,可以使用以下语句恢复:

mysqlbinlog --stop-date='2012-06-05 19:59:59' /var/log/mysql-bin.000001 | mysql -uroot -p

跳过误删除的时间点,再执行:

mysqlbinlog --start-date='2012-06-05 20:01:00' /var/log/mysql-bin.000001 | mysql -uroot -p

基于位置点的恢复:
基于位置点的恢复可以得到更为精确的数据。
binlog
如上图,drop table test这条语句的起始位置是889107,终止位置是889189,那么我们可以使用于以下语句进行恢复:

mysqlbinlog --stop-position='889107' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p
mysqlbinlog --start-position='889189' /var/lib/mysql/mysql-bin.000001|mysql -uroot -p

有时有可能因为系统版本的问题,以上方法行不通,可以将二进制导出到一个sql文件中,再直接根据sql语句进行恢复

mysqlbinlog  mysqlbinlog.000001 >log.sql

完全恢复案例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> select *  from  test2;
+------+-------+
| id   | name  |
+------+-------+
|    10  | tom   |
|    20  | jerry |
 
恢复以前的数据:
[root@ogg mysql]# mysqlbinlog  / var /lib/mysql/mysql-bin .000003 |mysql -uroot -poracle
 
恢复后查询: 
mysql> select *  from  test2;
+------+-------+
| id   | name  |
+------+-------+
|    10  | tom   |
|    20  | jerry |
|    60  | jack  |
|    60  | jack  |
|    60  | jack  |
|    60  | jack  |
|    60  | jack  |
|    50  | john  |
|    40  | ellen |
|    30  | rose  |
+------+-------+
10  rows  in  set ( 0.00  sec)

Binlog_format参数也特别重要。从mysql5.1版本开始引入这个参数,该参数可以设置的值有STATEMENT、ROW、MIXED;

(1)STATEMENT格式和之前的mysql版本一样,二进制日志文件记录的是日志的逻辑失SQL语句。

(2)在ROW格式下,二进制日志记录的不再是简单的SQL语句了,而是记录表的行更改情况,此时可以将InnoDB的事务隔离基本设为READ COMMITTED,以获得更好的并发性。

(3)MIXED格式下,mysql默认采用的STATEMENT格式进行二进制日志文件的记录,但是在一些情况下会使用ROW格式,可能的情况包括:

1)表的存储引擎为NDB,这时对于表的DML操作都会以ROW格式记录

2)使用了UUID()、USER()、CURRENT_USER()、FOUND_ROWS()、ROW_COUNT()等不确定函数

3)使用了INSERT DELAY语句

4)使用了用户定义函数

5)使用了临时表

binary log相关变量和参数

命令行参数

  --log-bin [=file_name]

  设置此参数表示启用binlog功能,并制定路径名称。

  --log-bin-index[=file]

  设置此参数是指定二进制索引文件的路径与名称。

  --max_binlog_size

 Binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,

为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。

  --binlog-do-db=db_name

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

  --binlog-ignore-db=db_name

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

系统变量

  log_bin

  binlog_cache_size

  此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

  max_binlog_cache_size

  此参数表示binlog使用的内存最大的尺寸

  binlog_cache_use

  使用二进制日志缓存的事务数量

  binlog_cache_disk_use

  使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。

  binlog_do_db

  binlog_ignore_db

  sync_binlog

  这个参数直接影响mysql的性能和完整性。

  sync_binlog=0:

  当事务提交后,Mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘,同步指令通知文件系统将缓存刷新到磁盘,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。

  sync_binlog=0,在进行n次事务提交以后,Mysql将执行一次fsync之类的磁盘同步指令,通知文件系统将Binlog文件缓存刷新到磁盘。

  Mysql中默认的设置是sync_binlog=0,即不做任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失。










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1559144,如需转载请自行联系原作者
相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
目录
相关文章
|
Ubuntu 关系型数据库 MySQL
MySQL二进制包安装
本文详细介绍了在多种Linux系统上通过二进制包安装MySQL 8.0和8.4版本的完整过程,涵盖用户创建、glibc版本匹配、程序解压、环境变量配置、初始化数据库及服务启动等步骤,并提供支持多发行版的一键安装脚本,助力高效部署MySQL环境。
2212 4
MySQL二进制包安装
|
NoSQL 算法 Redis
【Docker】(3)学习Docker中 镜像与容器数据卷、映射关系!手把手带你安装 MySql主从同步 和 Redis三主三从集群!并且进行主从切换与扩容操作,还有分析 哈希分区 等知识点!
Union文件系统(UnionFS)是一种**分层、轻量级并且高性能的文件系统**,它支持对文件系统的修改作为一次提交来一层层的叠加,同时可以将不同目录挂载到同一个虚拟文件系统下(unite several directories into a single virtual filesystem) Union 文件系统是 Docker 镜像的基础。 镜像可以通过分层来进行继承,基于基础镜像(没有父镜像),可以制作各种具体的应用镜像。
1223 6
|
SQL 运维 关系型数据库
深入探讨MySQL的二进制日志(binlog)选项
总结而言,对MySQL binlogs深度理解并妥善配置对数据库运维管理至关重要;它不仅关系到系统性能优化也是实现高可靠性架构设计必须考虑因素之一。通过精心规划与周密部署可以使得该机能充分发挥作用而避免潜在风险带来影响。
454 6
|
SQL 关系型数据库 MySQL
Mysql基础学习day01
本课程为MySQL基础学习第一天内容,涵盖MySQL概述、安装、SQL简介及其分类(DDL、DML、DQL、DCL)、数据库操作(查询、创建、使用、删除)及表操作(创建、约束、数据类型)。适合初学者入门学习数据库基本概念和操作方法。
387 6
|
关系型数据库 MySQL 数据管理
Mysql基础学习day03-作业
本内容包含数据库建表语句及多表查询示例,涵盖内连接、外连接、子查询及聚合统计,适用于员工与部门数据管理场景。
256 1
|
SQL 关系型数据库 MySQL
Mysql基础学习day02-作业
本教程介绍了数据库表的创建与管理操作,包括创建员工表、插入测试数据、删除记录、更新数据以及多种查询操作,涵盖了SQL语句的基本使用方法,适合初学者学习数据库操作基础。
295 0
|
SQL 关系型数据库 MySQL
Mysql基础学习day03
本课程为MySQL基础学习第三天内容,主要讲解多表关系与多表查询。内容涵盖物理外键与逻辑外键的区别、一对多、一对一及多对多关系的实现方式,以及内连接、外连接、子查询等多表查询方法,并通过具体案例演示SQL语句的编写与应用。
348 0
|
SQL 关系型数据库 MySQL
Mysql基础学习day01-作业
本教程包含三个数据库表的创建练习:学生表(student)要求具备主键、自增长、非空、默认值及唯一约束;课程表(course)定义主键、非空唯一字段及数值精度限制;员工表(employee)包含自增主键、非空字段、默认值、唯一电话号及日期时间类型字段。每个表的结构设计均附有详细SQL代码示例。
284 0
|
SQL 关系型数据库 MySQL
Mysql基础学习day02
本课程为MySQL基础学习第二天内容,涵盖数据定义语言(DDL)的表查询、修改与删除操作,以及数据操作语言(DML)的增删改查功能。通过具体SQL语句与实例演示,帮助学习者掌握MySQL表结构操作及数据管理技巧。
340 0
|
SQL 监控 关系型数据库
MySQL日志分析:binlog、redolog、undolog三大日志的深度探讨。
数据库管理其实和写小说一样,需要规划,需要修订,也需要有能力回滚。理解这些日志的作用与优化,就像把握写作工具的使用与运用,为我们的数据库保驾护航。
1153 23

推荐镜像

更多