菜单

Log二进制日志文件的基本操作命令小结,基本认识

2019年11月4日 - 数据网络
Log二进制日志文件的基本操作命令小结,基本认识

前言

  在前几天的办事中,由于本身粗(zuo卡塔 尔(阿拉伯语:قطر‎心(si卡塔 尔(英语:State of Qatar)误update操作引致几百行的数码现身谬误,在迫不如待的还要(那时本身以致不理铲除了备份之后还应该有binlog日志复苏卡塔 尔(英语:State of Qatar)立马查资料学习binlog的恢复生机,随后立时张开了恢复生机。固然能够假装自个儿没出错(emmmmm……最终还是得认可的!卡塔尔,但下班今后心境不能长时间平复,立马打开计算机举办叁次试验记录技艺对得起协调犯的荒诞。

  注:此次尝试是在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除此之外卡塔 尔(阿拉伯语:قطر‎语句,利用它首要能够做两件事:

(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开启呢?

  图片 1

  图片 2

(4卡塔尔常用轻便属性表达:除了上述log_bin、binlog_format四个大约的安顿外,还是能有其它的天性配置

   
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

  这时你明确很慌,不过先不要慌(实际上慌也没用卡塔 尔(英语:State of Qatar),先看未有备份,若无再看是还是不是开启binlog(show
variables like
‘log_bin%’卡塔 尔(英语:State of Qatar),倘使双方都尚未(笔者相信我们都会准期备份+binlog卡塔 尔(英语:State of Qatar)从数据库那些层面是无力回天复苏的了,假设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:\ProgramData\MySQL\MySQL Server 5.7\Data执行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卡塔 尔(英语:State of Qatar)早先天真的以为开拓职员就没有必要太驾驭数据库相关的运转,可是后日经历过才晓得数据库的相干文化也是开荒职员必得询问的!

 

   
其朝气蓬勃: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*卡塔 尔(英语:State of Qatar)记录数据库全体的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_%’;

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

2.用mysql自带的工具mysqlbinlog,那是我们就供给知道bin-log存在硬盘的怎么职位,win7暗许存在C:\ProgramData\MySQL\MySQL
Server
5.1\data文件夹上面,若无此文件夹,那大家得以因而配备文件中的 
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:\ProgramData\MySQL\MySQL Server 5.1\data>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卡塔 尔(英语:State of Qatar).最长用的正是过来钦赐数据端的数量了,能够直接回复到数据库中: 

    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文件名(不内定正是率先个binlog文件)

  –binlog-do-db=db_name

              FROM pos       
钦命从哪个pos开首点开首查起(不点名正是从整个文件第四个pos点伊始算)

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

              LIMIT [offset,] 偏移量(不内定正是0)

  –binlog-ignore-db=db_name

              row_count      查询总条数(不点名正是全体行)

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

            截取部分查询结果:

系统变量

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

  log_bin

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图