binlog 基本认识

作者:计算机专家

前言

  在近期的办事中,由于投机粗(zuo)心(si)误update操作形成几百行的多少出现谬误,在发急的同时(那时本人照旧不精通除了备份之后还应该有binlog日志复苏)立马查资料学习binlog的上涨,随后立即展开了还原。固然能够伪装自个儿没出错(emmmmm......最终照旧得分明的!),但下班之后心绪不能够悠久平复,立马打开Computer举办贰回实验记录手艺对得起和睦犯的一无是处。

  注:此番实验是在Wnidows下实行的(网络Linux挺多,不过Windows的什么少,加上本身笔者的微型Computer也是Win7就归纳做贰次尝试吧!

 


 

binlog 基本认知

MySQL Binary Log也等于常说的bin-log, ,是mysql实践改造发生的二进制日志文件,其关键作用有多个:
* 数据苏醒
* 主从数据库。用于slave端实行增加和删除改,保持与master同步。

1、初识binlog

(1)MySQL的binlog正是我们常常所说的Binary Log,即bin-log,是MySQL存款和储蓄对数据库退换的二进制文件,也等于记录了全部DDL与DML(select除此而外)语句,利用它最首要能够做两件事:

  • 数据恢复生机:通过mysqlbinog工具举办复原;
  • 数量复制:MySQL Replication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达成master-slave数据一致的目标。

(2)怎么查看MySQL的日记意况(是或不是张开等),当MySQL还没展开时候,通过命令:show variables like ‘log_bin%'查看

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
+---------------------------------+-------+
5 rows in set

(3)如何在Wnidows在修改log_bin状态为ON开启呢?

  • 找到C:ProgramDataMySQLMySQL Server 5.7my.ini文件(只顾:是C盘下隐蔽文件夹ProgramData,并不是Program Files下)  

  图片 1

  • 追加/修改常用的布署属性

  图片 2

(4)常用轻易属性表达:除了以上log_bin、binlog_format五个简易的安排外,还足以有其余的天性配置

  • log-bin = /xxx/xxx/mysql_bin #binlog日志文件,以mysql_bin开头,六个数字结尾的文件:mysql_bin.000001,并且会将文件存储在相应的xxx/xxx路径下,如果只配置mysql_bin的话默认在C:ProgramDataMySQLMySQL Server 5.7Data下;``
  • binlog_format = ROW #binlog日志格式,默认为STATEMENT:每一条SQL语句都会被记录;ROW:仅记录哪条数据被修改并且修改成什么样子,是binlog开启并且能恢复数据的关键;
  • expire_logs_days= 7 #binlog过期清理时间;
  • ``max_binlog_size = 100m #binlog每个日志文件大小;
  • binlog_cache_size = 4m #binlog缓存大小;
  • max_binlog_cache_size = 512m #最大binlog缓存大小。

    MySQL的二进制日志能够说是MySQL最珍视的日志了,它记录了装有的DDL和DML(除了数量查询语句)语句,以事件格局记录,还含有语句所施行的消耗的时辰,MySQL的二进制日志是业务安全型的。

1.开启binary log功能

2、恢复生机数据测验

(1)准备表user

mysql> select * from user;
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
+----+----------+----------------------------------+
3 rows in set

 (2)误update复苏,举例小编在update user set name = 'Lijian' where id = 1;的时候忘写where id = 1首要条件,结果导致整个数目被更新

mysql> update user set name ='Lijian';
Query OK, 3 rows affected
Rows matched: 3  Changed: 3  Warnings: 0
mysql> select*from user;
+----+--------+----------------------------------+
| id | name   | password                         |
+----+--------+----------------------------------+
|  1 | Lijian | 2d7284808e5111e8af74201a060059ce |
|  2 | Lijian | 2d73641c8e5111e8af74201a060059ce |
|  3 | Lijian | 2d73670c8e5111e8af74201a060059ce |
+----+--------+----------------------------------+
3 rows in set

  这一年你确定很慌,然则先不要慌(实际上慌也没用),先看未有备份,若无再看是或不是开启binlog(show variables like ‘log_bin%'),假设双方都尚未(小编信赖我们都会定时备份+binlog)从数据库那一个层面是无力回天苏醒的了,假诺binlog开启的话,一切都好说。就起初实践上边几步复苏吧!

  先是步:找到当前mysql记录的binlog文件,试行show master status;

  第二步:查看binlog,定位误操作的pos恐怕时间段。实行show binlog events in 'mysql_bin.000001';

mysql> show binlog events in 'mysql_bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.12-log, Binlog ver: 4 |
| mysql_bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000001 | 219 | Query          |         1 |         291 | BEGIN                                 |
| mysql_bin.000001 | 291 | Table_map      |         1 |         344 | table_id: 108 (test.user)             |
| mysql_bin.000001 | 344 | Update_rows    |         1 |         650 | table_id: 108 flags: STMT_END_F       |
| mysql_bin.000001 | 650 | Xid            |         1 |         681 | COMMIT /* xid=22 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set

  第三步:进入C:ProgramDataMySQLMySQL Server 5.7Data执行mysqlbinlog --start-position=219 --stop-position=681 mysql-bin.000001 > e:\update.sql将update部分单独备份出来到E盘下为update.sql  

 图片 3 

  第四步:登录mysql(mysql -uroot -p123)

  第五步:实行source e:update.sql复苏数据,部分截图如下:

  图片 4

  **第六步:查看结果**

mysql> select * from user;
+----+----------+----------------------------------+
| id | name     | password                         |
+----+----------+----------------------------------+
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
+----+----------+----------------------------------+
3 rows in set

    日常的话开启二进制日志差不离会有1%的性质损耗(参见MySQL官方中文手册 5.1.24版)。二进制有四个最主要的使用景况:

     必要修改mysql的配备文件,本篇的试验景况是win7,配置文件为mysql安装目录MySQL Server 5.1下的my.ini,增加一句log_bin = mysql_bin即可 

3、总结

  (1)选拔binlog只可以针对针对数据量不是得步提升的动静,真正的生育情况每一个多少个G的日记文件,不仅是光靠binlog复苏的,还大概有更加的多的法门,在这里只是做一个轻易易行的学习记录!

  (2)判定时间binlog日志的岁月阶段与pos地方很关键,不过急需领悟有关binlog的不菲参数!

  (3)之明日真的认为开采职员就不要求太明白数据库相关的运行,然而前日经历过才领悟数据库的连带文化也是开荒职员必得询问的!

 

    其一:MySQLReplication在Master端开启binlog,Mster把它的二进制日志传递给slaves来达到master-slave数据一致的指标。

  eg: 
  [mysqld] 
    ...... 
    log_bin = mysql_bin 
    ...... 
  log_bin是生成的bin-log的文件名,后缀则是6位数字的编码,从000001开始,按照上面的配置,生成的文件则为: 
    mysql_bin.000001 
    mysql_bin.000002 
    ...... 

    其二:自然正是数据恢复生机了,通过使用mysqlbinlog工具来使复苏数据。

  
    配置保存以往重启mysql的服务器,用show variables like  '%bin%'查看bin-log是不是开启,如图: 

    二进制日志富含两类公事:二进制日志索引文件(文件名后缀为.index)用于记录全部的二进制文件,二进制日志文件(文件名后缀为.00000*)记录数据库全数的DDL和DML(除了数量查询语句)语句事件。

图片 5

一、开启binlog日志:

2.查看发生的binary log

    vi编辑展开mysql配置文件

   bin-log因为是二进制文件,不可能经过记事本等编辑器直接张开查看,mysql提供二种艺术查看格局,在介绍以前,大家先对数据库实行一下增加和删除改的操作,不然log里边数据有一些空。 

    # vi /usr/local/mysql/etc/my.cnf    在[mysqld] 区块

 create table bin( id int(10) primary key auto_increment,name varchar(255));

    设置/增多log-bin=mysql-bin  确认是开采状态(值mysql-bin 是日记的基本名或前缀名);

(测验前本人早已建表) 

    重启mysqld服务使配置生效

 insert into bin(name) values ('orange'); 

    # pkill mysqld# /usr/local/mysql/bin/mysqld_safe --user=mysql &二、也可记名mysql服务器,通过mysql的变量配置表,查看二进制日志是还是不是已开启 单词:variable[ˈvɛriəbəl] 变量

     1.在顾客端中动用  show binlog events in 'mysql_bin.000001'  语句进行查看,为了排序赏心悦目,能够在结尾加G使结果横变纵,此时最后不必要加;语句甘休符。
      eg:

    登陆服务器

mysql> show binlog events in 'mysql_bin.000001'G 
...............省略............... 
*************************** 3. row *************************** 
 Log_name: mysql_bin.000001 
  Pos: 174 
Event_type: Intvar 
 Server_id: 1 
End_log_pos: 202 
  Info: INSERT_ID=2 
*************************** 4. row *************************** 
 Log_name: mysql_bin.000001 
  Pos: 202 
Event_type: Query 
 Server_id: 1 
End_log_pos: 304 
  Info: use `test`; insert into bin(name) values ('orange') 
*************************** 5. row *************************** 
...............省略............... 

    # /usr/local/mysql/bin/mysql -uroot -p123456mysql> show variables like 'log_%';

  • Log_name:此条log存在十三分文件中,从上边能够见见那2条log皆存在与mysql_bin.000001文件中。
  • Pos:log在bin-log中的起先地方
  • Event_type:log的类型消息
  • Server_id:能够查阅配置中的server_id,表示log是特别服务器发生
  • End_log_pos:log在bin-log中的结束地方
  • Info:log的有个别备注新闻,能够直观的见到实行了怎么样操作

    +----------------------------------------+---------------------------------------+    | Variable_name                          | Value                                |    +----------------------------------------+---------------------------------------+    | log_bin                                | ON                                    | ------> ON表示已经开启binlog日志

2.用mysql自带的工具mysqlbinlog,那是大家就须要知道bin-log存在硬盘的哪些岗位,win7默许存在C:ProgramDataMySQLMySQL Server 5.1data文件夹下边,若无此文件夹,那我们得以因而布置文件中的  datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data/" 定位,要是还并未有,那笔者就能够说“各类系统的搜求功效都做的没有错!”。这种查看格局就没非常赏心悦目了,如下

    | log_bin_basename                      | /usr/local/mysql/data/mysql-bin      |    | log_bin_index                          | /usr/local/mysql/data/mysql-bin.index |    | log_bin_trust_function_creators        | OFF                                  |    | log_bin_use_v1_row_events              | OFF                                  |    | log_error                              | /usr/local/mysql/data/martin.err      |    | log_output                            |FILE|    | log_queries_not_using_indexes          | OFF                                  |    | log_slave_updates                      | OFF                                  |    | log_slow_admin_statements              | OFF                                  |    | log_slow_slave_statements              | OFF                                  |    | log_throttle_queries_not_using_indexes | 0                                    |    | log_warnings                          | 1                                    |    +----------------------------------------+---------------------------------------+三、常用binlog日志操作命令

C:ProgramDataMySQLMySQL Server 5.1data>mysqlbinlog mysql_bin.000001 
/*!40019 SET @@session.max_insert_delayed_threads=0*/; 
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; 
DELIMITER /*!*/; 
# at 4 
#121015 16:35:56 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.51-community-log created 121015 16:35:56 at startup 
ROLLBACK/*!*/; 
BINLOG ' 
7Mp7UA8BAAAAZgAAAGoAAAAAAAQANS4xLjUxLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA 
AAAAAAAAAAAAAAAAAADsyntQEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC 
'/*!*/; 
# at 106 
#121015 16:36:51 server id 1 end_log_pos 174 Query thread_id=2  exec_time=0  error_code=0 
SET TIMESTAMP=1350290211/*!*/; 
SET @@session.pseudo_thread_id=2/*!*/; 
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/; 
SET @@session.sql_mode=1344274432/*!*/; 
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/; 
SET @@session.collation_database=DEFAULT/*!*/; 
BEGIN 
/*!*/; 
# at 174 
#121015 16:36:51 server id 1 end_log_pos 202 Intvar 
SET INSERT_ID=3/*!*/; 
# at 202 
#121015 16:36:51 server id 1 end_log_pos 309 Query thread_id=2  exec_time=0  error_code=0 
use test/*!*/; 
SET TIMESTAMP=1350290211/*!*/; 
insert into bin(name) values('xishizhaohua') 
/*!*/; 
# at 309 
#121015 16:36:51 server id 1 end_log_pos 336 Xid = 28 
COMMIT/*!*/; 
# at 336 
#121015 16:37:25 server id 1 end_log_pos 379 Rotate to mysql_bin.000002 pos: 4 
DELIMITER ; 
# End of log file 
ROLLBACK /* added by mysqlbinlog */; 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

    1.翻看全数binlog日志列表

固然如此排版有一些乱,但从图中我们能够赢得越来越多消息,如时间戳,自增的挥舞,是或不是自动提交业务等信息。如下图为从当中提取的一局地。

      mysql> show master logs;

图片 6

    2.翻看master状态,即最终(最新)贰个binlog日志的号码名称,及其最终贰个操作事件pos结束点(Position)值

3.利用bin_log复苏数据

      mysql> show master status;

    (1).最长用的正是过来钦定数据端的数量了,能够平昔回复到数据库中: 

    3.刷新log日志,自此刻始发发出一个新编号的binlog日志文件

 mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 |mysql -uroot -p123456 

      mysql>flush logs;

      亦可导出为sql文件,再导入至数据库中: 

      注:每当mysqld服务重启时,会活动推行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

  mysqlbinlog --start-date="2012-10-15 16:30:00" --stop-date="2012-10-15 17:00:00" mysql_bin.000001 >d:1.sql 
  source d:1.sql 

    4.重置(清空)所有binlog日志

     (2).钦定起始得了地方,从上边的查阅爆发的binary log大家能够明白有个别log的启幕到甘休的岗位,我们能够在回复的历程中钦定回复从A地方到B位置的log.必要用下边八个参数来钦定: 

      mysql>reset master;

 --start-positon="50" //指定从50位置开始 
 --stop-postion="100"//指定到100位置结束 

四、查看有个别binlog日志内容,常用有两种方法:

   最终介绍多少个bin_log的操作:
   (1).产看最后一个bin日志文件是特别,现在位置

    1.利用mysqlbinlog自带查看命令法:

图片 7

      注: binlog是二进制文件,普通文书查看器cat more vi等都不能展开,必得采纳自带的 mysqlbinlog 命令查看

    (2).启用新的日记文件,常常备份完数据库后推行

          binlog日志与数据库文件在同目录中(作者的条件安顿安装是接纳在/usr/local/mysql/data中)

图片 8

      在MySQL5.5之下版本采纳mysqlbinlog命令时一旦报错,就拉长“--no-defaults”选项

    (3).清空现成的所用bin-log

      # /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000013        上边截取八个有些深入分析:

图片 9

        ...............................................................................# at 552#131128 17:50:46 server id 1  end_log_pos 665  Query  thread_id=11    exec_time=0    error_code=0 ---->施行时间:17:50:46;pos点:665SET TIMESTAMP=1385632246/*!*/;

4.binary log连锁变量和参数

        update zyyshop.stu set name='李四' where id=4              ---->执行的SQL

命令行参数

        /*!*/;

  --log-bin [=file_name]

        # at 665#131128 17:50:46 server id 1  end_log_pos 692  Xid = 1454 ---->实施时间:17:50:46;pos点:692 ...............................................................................        注: server id 1    数据库主机的服务号;

  设置此参数表示启用binlog成效,并拟订路线名称。

            end_log_pos 665 pos点

  --log-bin-index[=file]

            thread_id=11    线程号

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

    2.地点这种办法读收取binlog日志的全文内容很多,不轻巧辨别查看pos点音讯,这里介绍一种特别有助于的询问命令:

  --max_binlog_size

      mysql> show binlog events [IN 'log_name'] [FROMpos] [LIMIT [offset,] row_count];

 Binlog最大值,最大和私下认可值是1GB,该装置并无法严控Binlog的尺寸,特别是Binlog相比接近最大值而又碰着三个非常大事务时,

            选项深入分析:

为了保证专门的职业的完整性,不或者做切换日志的动作,只可以将该事务的全体SQL都记录进当前几天记,直到专门的学问甘休。

              IN 'log_name'  内定要询问的binlog文件名(不点名就是第1个binlog文件)

  --binlog-do-db=db_name

              FROM pos        钦定从哪个pos伊始点最早查起(不钦赐正是从整个文件第4个pos点起始算)

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

              LIMIT [offset,] 偏移量(不点名正是0)

  --binlog-ignore-db=db_name

              row_count      查询总条数(不内定正是全体行)

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

            截取部分查询结果:

系统变量

            *************************** 20. row ***************************                Log_name:mysql-bin.000021  ----------------------------------------------> 查询的binlog日志文件名

  log_bin

本文由杏彩发布,转载请注明来源

关键词: