AI 助理
备案控制台
开发者社区 数据库 文章 正文

Mysql的二进制日志---binlog

2017-11-08 984
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
日志服务 SLS，月写入数据量 50GB 1个月
简介：

  二进制日志（BINLOG）记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但是不包括数据查询语句。语句以“事件”的形式保存，它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。

1、日志的位置和格式

  当用--log-bin[=file_name]选项启动时，mysqld将包含所有更新数据的SQL命令写入日志文件。如果没有给出file_name值，默认名为主机名后面跟“-bin”。如果给出了文件名，但没有包含路径，则文件默认被写入参数DATADIR（数据目录）指定的目录。

  MySQL5.6中二进制的格式分为3种，STATEMENT、ROW、MIXED，可以在启动时通过参数--binlog_format进行设置。

  STATEMENT

  MySQL5.1之前的版本都 采用这种方式，顾名思义，日志中记录的都是语句(statement)，每一条对数据造成修改的SQL语句都会记录在日志中，通过mysqlbin工具可以清晰地看到每条语句的文本。主从复制的时候，从库(slave)会将日志解析为原文本，并在从库重新执行一次。这种格式的优点是日志记录清晰易读、日志量少，对I/O影响较小。缺点是在某些情况下slave的日志复制会出错

  ROW

  MySQL5.1.11之后，出现了这种新的日志格式。它将每一行的变更记录到日志中，而不是记录SQL语句。比如一个简单的更新SQL:update emp set name='abc'，由于是对全表进行更新，也就是每一行记录都会发生变更，如果是一个100万行的大表，则日志中会记录100万条记录的变化情况。这种格式的优点是会记录每一行数据的变化细节，不会出现某些情况下无法复制的情况。缺点是日志量大，对I/O影响较大。

  MIXED

  这种日志格式，即混合了STATEMENT和ROW两种日志。默认情况下采用STATEMENT，但在一些特殊情况下采用ROW来进行记录，比如采用NDB存储引擎，此时对表的DML语句全部采用ROW；客户端使用了临时表；客户端使用了不确定函数，比如current_user()等，因为这种不确定函数在主从中得到的值可能不同，导致主从数据产生不一致。MIXED格式能尽量利用两种模式的优点，而避开它们的缺点。

2、启用binlog

  默认情况下，binlog是不启用的。可以通过查看变量 log_bin的值来查看是否启用。

1
2
3
4
5
6
7
8
9
mysql> show variables  like  'log_bin' ;
+ ---------------+-------+
| Variable_name | Value |
+ ---------------+-------+
| log_bin       |  OFF    |
+ ---------------+-------+
1 row  in  set  (0.01 sec)
mysql> show  binary  logs;
ERROR 1381 (HY000): You are  not  using  binary  logging

 启用binlong

  vi /etc/my.cnf

  [mysqld]下边添加

  log-bin=mysqlbin   #"="后边的名字为自己定义的前缀，如果没有自定义则为hostname-bin

  在5.7.3及以后的版本启用binlog必须要连server-id一同指定，否则mysql无法启动。

  重启mysql服务

  service mysql restart

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> show variables  like  'log_bin' ;
+ ---------------+-------+
| Variable_name | Value |
+ ---------------+-------+
| log_bin       |  ON     |
+ ---------------+-------+
1 row  in  set  (0.03 sec)
 
mysql> show  binary  logs;
+ -----------------+-----------+
| Log_name        | File_size |
+ -----------------+-----------+
| mysqlbin.000001 |       120 |
+ -----------------+-----------+
1 row  in  set  (0.00 sec)
 
mysql> show binlog events;
+ -----------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name        | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+ -----------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysqlbin.000001 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.34-log, Binlog ver: 4 |
+ -----------------+-----+-------------+-----------+-------------+---------------------------------------+

3、读取binlog日志

  由于服务器生成的二进制日志文件以二进制格式保存，所以如果要想检查这些文件的文本格式，就会用到mysqlbinlog日志管理工具。

mysqlbinlog的具体用法如下：

shell> mysqlbinlog [options] log-files1 log-files2...

option有很多选项，常用的如下：

  -d, --database=name        指定数据库名称，只列出指定的数据库相关操作。

  -o, --offset=#   忽略掉日志中的前n行命令

  -r, --result-file=name       将输出的文本格式日志输出到指定文件

  -s, --short-form       显示简单格式，省略掉一些信息

  --set-charset=char-name：在输出为文本格式时，在文件第一行加上set names char-name，这个选项在某些情况下装载数据时，非常有用。

  --start-datetime=name –stop-datetime=name：指定日期间隔内的所有日志

  --start-position=#  --stop-position=#：指定位置间隔内的所有日志

option可放日志文件前也可放日志文件后

  （1）创建日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> use test
Reading  table  information  for  completion  of  table  and  column  names
You can turn  off  this feature  to  get a quicker startup  with  -A
 
Database  changed
 
mysql>  insert  into  emp (ename)  values ( 'bin_zx' );
Query OK, 1 row affected (0.05 sec)
 
mysql> use test1
Reading  table  information  for  completion  of  table  and  column  names
You can turn  off  this feature  to  get a quicker startup  with  -A
 
Database  changed
mysql>  insert  into  emp (ename)  values ( 'bin_zx1' );
Query OK, 1 row affected (0.05 sec)
 
mysql>  delete  from  emp  where  ename= 'bin_zx1' ;
Query OK, 1 row affected (0.00 sec)

（2）不加任何参数，显示所有日志(加粗为上一步执行的sql)

    [root@rhel6 mysql]# mysqlbinlog mysqlbin.000002 

    /*!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

    #161121 10:36:44 server id 1  end_log_pos 120 CRC32 0x7067a31f Start: binlog v 4, server v 5.6.34-log created 161121 10:36:44 at startup

    # Warning: this binlog is either in use or was not closed properly.

    ROLLBACK/*!*/;

    BINLOG '

    vF0yWA8BAAAAdAAAAHgAAAABAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

    AAAAAAAAAAAAAAAAAAC8XTJYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAR+j

    Z3A=

    '/*!*/;

    # at 120

    #161121 12:48:15 server id 1  end_log_pos 218 CRC32 0xb953ea67 Querythread_id=1exec_time=0error_code=0

    use `test`/*!*/;

    SET TIMESTAMP=1479703695/*!*/;

    SET @@session.pseudo_thread_id=1/*!*/;

    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=8/*!*/;

    SET @@session.lc_time_names=0/*!*/;

    SET @@session.collation_database=DEFAULT/*!*/;

    DELETE FROM `test`.`tab_memory`

    /*!*/;

    # at 218

    #161121 12:49:03 server id 1  end_log_pos 297 CRC32 0x6d922200 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    BEGIN

    /*!*/;

    # at 297

    #161121 12:49:03 server id 1  end_log_pos 411 CRC32 0xa78a9de5 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    insert into emp (ename) values('bin_zx')

    /*!*/;

    # at 411

    #161121 12:49:03 server id 1  end_log_pos 442 CRC32 0x0af0231f Xid = 20

    COMMIT/*!*/;

    # at 442

    #161121 12:49:08 server id 1  end_log_pos 542 CRC32 0x4c7a37af Querythread_id=1exec_time=0error_code=0

    use `test1`/*!*/;

    SET TIMESTAMP=1479703748/*!*/;

    DELETE FROM `test1`.`tab_memory`

    /*!*/;

    # at 542

    #161121 12:49:16 server id 1  end_log_pos 623 CRC32 0x1b2f79ad Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703756/*!*/;

    BEGIN

    /*!*/;

    # at 623

    #161121 12:49:16 server id 1  end_log_pos 740 CRC32 0x4f52a0ca Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703756/*!*/;

    insert into emp (ename) values('bin_zx1')

    /*!*/;

    # at 740

    #161121 12:49:16 server id 1  end_log_pos 771 CRC32 0x5c57e391 Xid = 34

    COMMIT/*!*/;

    # at 771

    #161121 12:49:29 server id 1  end_log_pos 852 CRC32 0x02a2e65c Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703769/*!*/;

    BEGIN

    /*!*/;

    # at 852

    #161121 12:49:29 server id 1  end_log_pos 965 CRC32 0xa0d7e1f2 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703769/*!*/;

    delete from emp where ename='bin_zx1'

    /*!*/;

    # at 965

    #161121 12:49:29 server id 1  end_log_pos 996 CRC32 0xab27f255 Xid = 35

    COMMIT/*!*/;

    DELIMITER ;

    # End of log file

    ROLLBACK /* added by mysqlbinlog */;

    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

  （3）加-d选项，将只显示对test数据库的操作日志。

    [root@rhel6 mysql]# mysqlbinlog -d test mysqlbin.000002 

    /*!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

    #161121 10:36:44 server id 1  end_log_pos 120 CRC32 0x7067a31f Start: binlog v 4, server v 5.6.34-log created 161121 10:36:44 at startup

    # Warning: this binlog is either in use or was not closed properly.

    ROLLBACK/*!*/;

    BINLOG '

    vF0yWA8BAAAAdAAAAHgAAAABAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

    AAAAAAAAAAAAAAAAAAC8XTJYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAR+j

    Z3A=

    '/*!*/;

    # at 120

    #161121 12:48:15 server id 1  end_log_pos 218 CRC32 0xb953ea67 Querythread_id=1exec_time=0error_code=0

    use `test`/*!*/;

    SET TIMESTAMP=1479703695/*!*/;

    SET @@session.pseudo_thread_id=1/*!*/;

    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=8/*!*/;

    SET @@session.lc_time_names=0/*!*/;

    SET @@session.collation_database=DEFAULT/*!*/;

    DELETE FROM `test`.`tab_memory`

    /*!*/;

    # at 218

    #161121 12:49:03 server id 1  end_log_pos 297 CRC32 0x6d922200 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    BEGIN

    /*!*/;

    # at 297

    #161121 12:49:03 server id 1  end_log_pos 411 CRC32 0xa78a9de5 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    insert into emp (ename) values('bin_zx')

    /*!*/;

    # at 411

    #161121 12:49:03 server id 1  end_log_pos 442 CRC32 0x0af0231f Xid = 20

    COMMIT/*!*/;

    # at 442

    # at 542

    #161121 12:49:16 server id 1  end_log_pos 623 CRC32 0x1b2f79ad Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703756/*!*/;

    BEGIN

    /*!*/;

    # at 623

    # at 740

    #161121 12:49:16 server id 1  end_log_pos 771 CRC32 0x5c57e391 Xid = 34

    COMMIT/*!*/;

    # at 771

    #161121 12:49:29 server id 1  end_log_pos 852 CRC32 0x02a2e65c Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703769/*!*/;

    BEGIN

    /*!*/;

    # at 852

    # at 965

    #161121 12:49:29 server id 1  end_log_pos 996 CRC32 0xab27f255 Xid = 35

    COMMIT/*!*/;

    DELIMITER ;

    # End of log file

    ROLLBACK /* added by mysqlbinlog */;

    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

  (4)加-r选项，将上面的结果输出到文件resultfile中

    [root@rhel6 mysql]# mysqlbinlog -r binlog mysqlbin.000002 

    [root@rhel6 mysql]# more binlog

    /*!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

    #161121 10:36:44 server id 1  end_log_pos 120 CRC32 0x7067a31f Start: binlog v 4, server v 5.6.34-log created 161121 10:36:44 at startup

    # Warning: this binlog is either in use or was not closed properly.

    ROLLBACK/*!*/;

    BINLOG '

    vF0yWA8BAAAAdAAAAHgAAAABAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

    AAAAAAAAAAAAAAAAAAC8XTJYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAR+j

    Z3A=

    '/*!*/;

    # at 120

    #161121 12:48:15 server id 1  end_log_pos 218 CRC32 0xb953ea67 Querythread_id=1exec_time=0error_code=0

    use `test`/*!*/;

    SET TIMESTAMP=1479703695/*!*/;

    SET @@session.pseudo_thread_id=1/*!*/;

    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=8/*!*/;

    SET @@session.lc_time_names=0/*!*/;

    SET @@session.collation_database=DEFAULT/*!*/;

    DELETE FROM `test`.`tab_memory`

    /*!*/;

    # at 218

    #161121 12:49:03 server id 1  end_log_pos 297 CRC32 0x6d922200 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    BEGIN

    /*!*/;

    # at 297

    #161121 12:49:03 server id 1  end_log_pos 411 CRC32 0xa78a9de5 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    insert into emp (ename) values('bin_zx')

    /*!*/;

    # at 411

    #161121 12:49:03 server id 1  end_log_pos 442 CRC32 0x0af0231f Xid = 20

    COMMIT/*!*/;

    # at 442

    #161121 12:49:08 server id 1  end_log_pos 542 CRC32 0x4c7a37af Querythread_id=1exec_time=0error_code=0

    use `test1`/*!*/;

    SET TIMESTAMP=1479703748/*!*/;

    DELETE FROM `test1`.`tab_memory`

    /*!*/;

    # at 542

    #161121 12:49:16 server id 1  end_log_pos 623 CRC32 0x1b2f79ad Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703756/*!*/;

    BEGIN

    /*!*/;

    # at 623

    #161121 12:49:16 server id 1  end_log_pos 740 CRC32 0x4f52a0ca Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703756/*!*/;

    insert into emp (ename) values('bin_zx1')

    /*!*/;

    # at 740

    #161121 12:49:16 server id 1  end_log_pos 771 CRC32 0x5c57e391 Xid = 34

    COMMIT/*!*/;

    # at 771

    #161121 12:49:29 server id 1  end_log_pos 852 CRC32 0x02a2e65c Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703769/*!*/;

    BEGIN

    /*!*/;

    # at 852

    #161121 12:49:29 server id 1  end_log_pos 965 CRC32 0xa0d7e1f2 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703769/*!*/;

    delete from emp where ename='bin_zx1'

    /*!*/;

    # at 965

    #161121 12:49:29 server id 1  end_log_pos 996 CRC32 0xab27f255 Xid = 35

    COMMIT/*!*/;

    DELIMITER ;

    # End of log file

    ROLLBACK /* added by mysqlbinlog */;

    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

  （5）结果显示内容较多，显得比较乱，加-s选项将上面的内容进行简单显示。

    [root@rhel6 mysql]# mysqlbinlog -s mysqlbin.000002 

    /*!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 /*!*/;

    ROLLBACK/*!*/;

    use `test`/*!*/;

    SET TIMESTAMP=1479703695/*!*/;

    SET @@session.pseudo_thread_id=999999999/*!*/;

    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=8/*!*/;

    SET @@session.lc_time_names=0/*!*/;

    SET @@session.collation_database=DEFAULT/*!*/;

    DELETE FROM `test`.`tab_memory`

    /*!*/;

    SET TIMESTAMP=1479703743/*!*/;

    BEGIN

    /*!*/;

    SET TIMESTAMP=1479703743/*!*/;

    insert into emp (ename) values('bin_zx')

    /*!*/;

    COMMIT/*!*/;

    use `test1`/*!*/;

    SET TIMESTAMP=1479703748/*!*/;

    DELETE FROM `test1`.`tab_memory`

    /*!*/;

    SET TIMESTAMP=1479703756/*!*/;

    BEGIN

    /*!*/;

    SET TIMESTAMP=1479703756/*!*/;

    insert into emp (ename) values('bin_zx1')

    /*!*/;

    COMMIT/*!*/;

    SET TIMESTAMP=1479703769/*!*/;

    BEGIN

    /*!*/;

    SET TIMESTAMP=1479703769/*!*/;

    delete from emp where ename='bin_zx1'

    /*!*/;

    COMMIT/*!*/;

    DELIMITER ;

    # End of log file

    ROLLBACK /* added by mysqlbinlog */;

    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 （6）加“--start-datetime --stop-datetime”显示指定时间内的日志

    [root@rhel6 mysql]# mysqlbinlog mysqlbin.000002  --start-datetime='2016/11/21 12:48:00' --stop-datetime='2016/11/21 12:49:00'

    /*!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

    #161121 10:36:44 server id 1  end_log_pos 120 CRC32 0x7067a31f Start: binlog v 4, server v 5.6.34-log created 161121 10:36:44 at startup

    # Warning: this binlog is either in use or was not closed properly.

    ROLLBACK/*!*/;

    BINLOG '

    vF0yWA8BAAAAdAAAAHgAAAABAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

    AAAAAAAAAAAAAAAAAAC8XTJYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAR+j

    Z3A=

    '/*!*/;

    # at 120

    #161121 12:48:15 server id 1  end_log_pos 218 CRC32 0xb953ea67 Querythread_id=1exec_time=0error_code=0

    use `test`/*!*/;

    SET TIMESTAMP=1479703695/*!*/;

    SET @@session.pseudo_thread_id=1/*!*/;

    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=8/*!*/;

    SET @@session.lc_time_names=0/*!*/;

    SET @@session.collation_database=DEFAULT/*!*/;

    DELETE FROM `test`.`tab_memory`

    /*!*/;

    DELIMITER ;

    # End of log file

    ROLLBACK /* added by mysqlbinlog */;

    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

    开始日期和结束日期可以只写一个。如果只写开始日期，表示范围是开始日期到日志结束；如果只写结束日期，表示日志开始到指定的结束日期。

（7）--start-position=#和—stop-position=#和日期范围类似，不过可以更精确地表示范围

    [root@rhel6 mysql]# mysqlbinlog  mysqlbin.000001 mysqlbin.000002  --start-position=120 --stop-position=297

    /*!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

    #161120 22:45:37 server id 1  end_log_pos 120 CRC32 0x1ce9680d Start: binlog v 4, server v 5.6.34-log created 161120 22:45:37 at startup

    ROLLBACK/*!*/;

    BINLOG '

    EbcxWA8BAAAAdAAAAHgAAAAAAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

    AAAAAAAAAAAAAAAAAAARtzFYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQ1o

    6Rw=

    '/*!*/;

    # at 120

    #161120 23:28:57 server id 1  end_log_pos 143 CRC32 0x69c1b9d0 Stop

    # at 4

    #161121 10:36:44 server id 1  end_log_pos 120 CRC32 0x7067a31f Start: binlog v 4, server v 5.6.34-log created 161121 10:36:44 at startup

    # Warning: this binlog is either in use or was not closed properly.

    ROLLBACK/*!*/;

    BINLOG '

    vF0yWA8BAAAAdAAAAHgAAAABAAQANS42LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

    AAAAAAAAAAAAAAAAAAC8XTJYEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAR+j

    Z3A=

    '/*!*/;

    # at 120

    #161121 12:48:15 server id 1  end_log_pos 218 CRC32 0xb953ea67 Querythread_id=1exec_time=0error_code=0

    use `test`/*!*/;

    SET TIMESTAMP=1479703695/*!*/;

    SET @@session.pseudo_thread_id=1/*!*/;

    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=8/*!*/;

    SET @@session.lc_time_names=0/*!*/;

    SET @@session.collation_database=DEFAULT/*!*/;

    DELETE FROM `test`.`tab_memory`

    /*!*/;

    # at 218

    #161121 12:49:03 server id 1  end_log_pos 297 CRC32 0x6d922200 Querythread_id=1exec_time=0error_code=0

    SET TIMESTAMP=1479703743/*!*/;

    BEGIN

    /*!*/;

    DELIMITER ;

    # End of log file

    ROLLBACK /* added by mysqlbinlog */;

    /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

    /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

4、删除binlog日志

  对于比较繁忙的OLTP（在线事务处理）系统，由于每天生成日志量大，这些日志如果长时间不清除，将会对磁盘空间带来很大的浪费。因此定期删除日志是DBA维护MySQL数据库的一个重要工作内容。下面介绍几种删除日志的常见方法。

  方法1：RESET MASTER;

    执行"RESET MASTER;"命令，该命令将删除所有BINLOG日志，新日志编号从"0000001"开始。

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql  143 Nov 20 23:28 mysqlbin.000001

    -rw-rw---- 1 mysql mysql 1019 Nov 21 15:12 mysqlbin.000002

    -rw-rw---- 1 mysql mysql  143 Nov 21 15:12 mysqlbin.000003

    -rw-rw---- 1 mysql mysql  143 Nov 21 15:46 mysqlbin.000004

    -rw-rw---- 1 mysql mysql  143 Nov 21 15:53 mysqlbin.000005

    -rw-rw---- 1 mysql mysql  143 Nov 21 16:08 mysqlbin.000006

    -rw-rw---- 1 mysql mysql  166 Nov 21 16:42 mysqlbin.000007

    -rw-rw---- 1 mysql mysql  368 Nov 21 18:11 mysqlbin.000008

    -rw-rw---- 1 mysql mysql  143 Nov 21 18:11 mysqlbin.000009

    -rw-rw---- 1 mysql mysql  120 Nov 21 21:11 mysqlbin.000010

    -rw-rw---- 1 mysql mysql  180 Nov 21 21:11 mysqlbin.index

    [root@rhel6 mysql]# mysql -uroot -p 

    Enter password: 

    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Your MySQL connection id is 1

    Server version: 5.6.34-log MySQL Community Server (GPL)

    

    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    

    Oracle is a registered trademark of Oracle Corporation and/or its

    affiliates. Other names may be trademarks of their respective

    owners.

    

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    

    mysql> reset master;

    Query OK, 0 rows affected (0.05 sec)

    

    mysql> exit

    Bye

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql 120 Nov 21 23:11 mysqlbin.000001

    -rw-rw---- 1 mysql mysql  18 Nov 21 23:11 mysqlbin.index

  方法二：PURGE MASTER LOGS TO 'mysql-bin.******'

    执行"PURGE MASTER LOGS TO 'mysql-bin.******'"命令，该命令将删除"******"编号之前的所的日志。

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:12 mysqlbin.000001

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:15 mysqlbin.000002

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:15 mysqlbin.000003

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:15 mysqlbin.000004

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:15 mysqlbin.000005

    -rw-rw---- 1 mysql mysql 120 Nov 21 23:15 mysqlbin.000006

    -rw-rw---- 1 mysql mysql 108 Nov 21 23:15 mysqlbin.index

    [root@rhel6 mysql]# mysql -uroot -p

    Enter password: 

    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Your MySQL connection id is 5

    Server version: 5.6.34-log MySQL Community Server (GPL)

    

    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    

    Oracle is a registered trademark of Oracle Corporation and/or its

    affiliates. Other names may be trademarks of their respective

    owners.

    

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    

    mysql> purge master logs to 'mysqlbin.000005';

    Query OK, 0 rows affected (0.03 sec)

    

    mysql> exit

    Bye

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:15 mysqlbin.000005

    -rw-rw---- 1 mysql mysql 120 Nov 21 23:15 mysqlbin.000006

    -rw-rw---- 1 mysql mysql  36 Nov 21 23:16 mysqlbin.index

  方法3：PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'

    执行"PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh24:mi:ss'"命令，该命令将删除日期为"yyyy-mm-dd hh24:mi:ss"之前产生的所有日志。

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:15 mysqlbin.000005

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:19 mysqlbin.000006

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:19 mysqlbin.000007

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:19 mysqlbin.000008

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:19 mysqlbin.000009

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:19 mysqlbin.000010

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:20 mysqlbin.000011

    -rw-rw---- 1 mysql mysql 120 Nov 21 23:20 mysqlbin.000012

    -rw-rw---- 1 mysql mysql 144 Nov 21 23:20 mysqlbin.index

    [root@rhel6 mysql]# mysql -uroot -p

    Enter password: 

    Welcome to the MySQL monitor.  Commands end with ; or \g.

    Your MySQL connection id is 8

    Server version: 5.6.34-log MySQL Community Server (GPL)

    

    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    

    Oracle is a registered trademark of Oracle Corporation and/or its

    affiliates. Other names may be trademarks of their respective

    owners.

    

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    

    mysql> purge master logs before '2016-11-21 23:20:00';

    Query OK, 0 rows affected (0.04 sec)

    

    mysql> exit

    Bye

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:20 mysqlbin.000011

    -rw-rw---- 1 mysql mysql 120 Nov 21 23:20 mysqlbin.000012

    -rw-rw---- 1 mysql mysql  36 Nov 21 23:20 mysqlbin.index

  方法4：设置expire_logs_days

    设置--expire_logs_days=#，此参数的含义是设置日志的过期天数，过了指定的天数后日志将会被自动删除，这样将有利于减少DBA管理日志的工作量。

    [root@rhel6 mysql]# ls -l mysqlbin*

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:20 mysqlbin.000011

    -rw-rw---- 1 mysql mysql 143 Nov 21 23:30 mysqlbin.000012

    -rw-rw---- 1 mysql mysql 120 Nov 21 23:31 mysqlbin.000013

    -rw-rw---- 1 mysql mysql  54 Nov 21 23:31 mysqlbin.index

    在/etc/my.cnf的[mysqld]中添加expire_logs_day=2

    [root@rhel6 mysql]# grep expire_logs_day /etc/my.cnf

    expire_logs_day=2

    重启mysql

    [root@rhel6 mysql]# service mysql restart

    Shutting down MySQL..                                      [  OK  ]

    Starting MySQL.                                            [  OK  ]

    [root@rhel6 mysql]# date

    Mon Nov 21 23:32:13 CST 2016

    修改日期为1天后

    [root@rhel6 mysql]# date -s '20161122 23:00:00'

    Tue Nov 22 23:00:00 CST 2016

    [root@rhel6 mysql]# date

    Tue Nov 22 23:00:01 CST 2016

    使用flush logs触发日志文件更新，由于还不到2天，日志不会被删除

    [root@rhel6 mysql]# mysqladmin flush-log

    mysqladmin: connect to server at 'localhost' failed

    error: 'Access denied for user 'root'@'localhost' (using password: NO)'

    [root@rhel6 mysql]# mysqladmin -uroot -p flush-log

    Enter password: 

    [root@rhel6 mysql]# ls -lt mysqlbin*

    -rw-rw---- 1 mysql mysql  90 Nov 22 23:00 mysqlbin.index

    -rw-rw---- 1 mysql mysql 120 Nov 22 23:00 mysqlbin.000015

    -rw-rw---- 1 mysql mysql 166 Nov 22 23:00 mysqlbin.000014

    -rw-rw---- 1 mysql mysql 143 Nov 21 23:31 mysqlbin.000013

    -rw-rw---- 1 mysql mysql 143 Nov 21 23:30 mysqlbin.000012

    -rw-rw---- 1 mysql mysql 166 Nov 21 23:20 mysqlbin.000011

    修改日期为3天后，并再次执行flush logs

    [root@rhel6 mysql]# date -s '20161124 23:00:00'

    Thu Nov 24 23:00:00 CST 2016

    [root@rhel6 mysql]# mysqladmin -uroot -p flush-log

    Enter password: 

    两天前的日志(11月21日)被删除了

    [root@rhel6 mysql]# ls -lt mysqlbin*

    -rw-rw---- 1 mysql mysql  72 Nov 24 23:00 mysqlbin.index

    -rw-rw---- 1 mysql mysql 120 Nov 24 23:00 mysqlbin.000017

    -rw-rw---- 1 mysql mysql 166 Nov 24 23:00 mysqlbin.000016

    -rw-rw---- 1 mysql mysql 166 Nov 23 23:00 mysqlbin.000015

    -rw-rw---- 1 mysql mysql 166 Nov 22 23:00 mysqlbin.000014


  参考：《深入浅出MySQL》

  参考文档：http://www.myexception.cn/mysql/1779525.html





     本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1874987，如需转载请自行联系原作者




文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
MySQL
监控
SQL
数据库
关键词：
云数据库 RDS MySQL 版binlog
mysql日志服务
mysql binlog
云数据库 RDS MySQL 版日志
日志服务binlog
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
技术小胖子
目录
相关文章
智物科技库
|
20天前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志和二进制日志是确保数据库稳定性和可靠性的关键组件。重做日志主要用于事务的持久性和原子性，通过记录数据页的物理修改信息来恢复未提交的事务；而二进制日志记录SQL语句的逻辑变化，支持数据复制、恢复和审计。两者在写入时机、存储方式及配置参数等方面存在显著差异。
智物科技库
41 6
栈江湖
|
4天前
|
SQL 关系型数据库 MySQL
数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
《数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog》介绍了如何利用MySQL的二进制日志（Binlog）恢复误删除的数据。主要内容包括： 1. **启用二进制日志**：在`my.cnf`中配置`log-bin`并重启MySQL服务。 2. **查看二进制日志文件**：使用`SHOW VARIABLES LIKE 'log_%';`和`SHOW MASTER STATUS;`命令获取当前日志文件及位置。 3. **创建数据备份**：确保在恢复前已有备份，以防意外。 4. **导出二进制日志为SQL语句**：使用`mysqlbinlog`
栈江湖
25 2
拉丁解牛说技术
|
20天前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理，包括它们如何确保数据的一致性和完整性，以及在事务提交过程中各自的角色。同时，文章还探讨了这些日志在故障恢复中的重要性，强调了合理配置相关参数对于提高系统稳定性的必要性。
拉丁解牛说技术
64 6
赵渝强老师
|
1月前
|
关系型数据库 MySQL 数据库
【赵渝强老师】MySQL的binlog日志文件
MySQL的binlog日志记录了所有对数据库的更改操作（不包括SELECT和SHOW），主要用于主从复制和数据恢复。binlog有三种模式，可通过设置binlog_format参数选择。示例展示了如何启用binlog、设置格式、查看日志文件及记录的信息。
赵渝强老师
124 6
大数据文摘
|
1月前
|
存储 SQL 关系型数据库
mysql 的ReLog和BinLog区别
MySQL中的重做日志（Redo Log）和二进制日志（Binary Log）是两种重要的日志系统。重做日志主要用于保证事务的持久性和原子性，通过记录数据页的物理修改信息来恢复未提交的事务更改。二进制日志则记录了数据库的所有逻辑变化操作，用于数据的复制、恢复和审计。两者在写入时机、存储方式、配置参数和使用范围上有所不同，共同确保了数据库的稳定性和可靠性。
大数据文摘
46 2
蓝染-惣右介
|
1月前
|
XML 安全 Java
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
本文介绍了Java日志框架的基本概念和使用方法，重点讨论了SLF4J、Log4j、Logback和Log4j2之间的关系及其性能对比。SLF4J作为一个日志抽象层，允许开发者使用统一的日志接口，而Log4j、Logback和Log4j2则是具体的日志实现框架。Log4j2在性能上优于Logback，推荐在新项目中使用。文章还详细说明了如何在Spring Boot项目中配置Log4j2和Logback，以及如何使用Lombok简化日志记录。最后，提供了一些日志配置的最佳实践，包括滚动日志、统一日志格式和提高日志性能的方法。
蓝染-惣右介
347 30
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
运维有小邓
|
14天前
|
监控 安全 Apache
什么是Apache日志？为什么Apache日志分析很重要？
Apache是全球广泛使用的Web服务器软件，支持超过30%的活跃网站。它通过接收和处理HTTP请求，与后端服务器通信，返回响应并记录日志，确保网页请求的快速准确处理。Apache日志分为访问日志和错误日志，对提升用户体验、保障安全及优化性能至关重要。EventLog Analyzer等工具可有效管理和分析这些日志，增强Web服务的安全性和可靠性。
运维有小邓
38 9
最好zzz
|
2月前
|
XML JSON Java
Logback 与 log4j2 性能对比：谁才是日志框架的性能王者？
【10月更文挑战第5天】在Java开发中，日志框架是不可或缺的工具，它们帮助我们记录系统运行时的信息、警告和错误，对于开发人员来说至关重要。在众多日志框架中，Logback和log4j2以其卓越的性能和丰富的功能脱颖而出，成为开发者们的首选。本文将深入探讨Logback与log4j2在性能方面的对比，通过详细的分析和实例，帮助大家理解两者之间的性能差异，以便在实际项目中做出更明智的选择。
最好zzz
333 3
运维有小邓
|
24天前
|
存储 监控 安全
什么是事件日志管理系统？事件日志管理系统有哪些用处？
事件日志管理系统是IT安全的重要工具，用于集中收集、分析和解释来自组织IT基础设施各组件的事件日志，如防火墙、路由器、交换机等，帮助提升网络安全、实现主动威胁检测和促进合规性。系统支持多种日志类型，包括Windows事件日志、Syslog日志和应用程序日志，通过实时监测、告警及可视化分析，为企业提供强大的安全保障。然而，实施过程中也面临数据量大、日志管理和分析复杂等挑战。EventLog Analyzer作为一款高效工具，不仅提供实时监测与告警、可视化分析和报告功能，还支持多种合规性报告，帮助企业克服挑战，提升网络安全水平。
运维有小邓
51 2
绿水长流*z
|
2月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现，而持久性则依赖于事务日志中的`Redo Log`。在MySQL中，`Redo Log`确保已提交事务的数据能持久保存，即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改，待事务提交时写入磁盘。此外，`Redo Log`采用简单的物理日志格式和高效的顺序IO，确保快速提交。通过不同的落盘策略，可在性能和安全性之间做出权衡。
绿水长流*z
1710 14

热门文章

最新文章

  • 1
    数据库灾难应对：MySQL误删除数据的救赎之道，技巧get起来！之binlog
  • 2
    MySQL数据库下载安装教程（Windows&Linux）
  • 3
    MySQL常用函数：IF、SUM等用法
  • 4
    Python处理数据库：MySQL与SQLite详解 | python小知识
  • 5
    从新手到高手：彻底掌握MySQL表死锁
  • 6
    MySQL事务隔离级别及默认隔离级别的设置
  • 7
    Liunx部署java项目Tomcat、Redis、Mysql教程
  • 8
    Mysql索引：深入理解InnoDb聚集索引与MyisAm非聚集索引
  • 9
    MySQL导入.sql文件后数据库乱码问题
  • 10
    MySQL的插件式认证到底是干什么的？
  • 1
    启用Linux防火墙日志记录和分析功能
    22
  • 2
    日志服务 SQL 引擎全新升级
    17
  • 3
    3D-Speaker：阿里通义开源的多模态说话人识别项目，支持说话人识别、语种识别、多模态识别、说话人重叠检测和日志记录
    71
  • 4
    MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！
    20
  • 5
    Mysql并发控制和日志
    40
  • 6
    云上数据安全保护：敏感日志扫描与脱敏实践详解
    67
  • 7
    什么是Apache日志？为什么Apache日志分析很重要？
    38
  • 8
    使用事件日志识别常见 Windows 错误
    36
  • 9
    南大通用GBase 8a配置gcware日志等级，减少日志输出，节省磁盘IO
    33
  • 10
    MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
    65

    • 相关课程

    更多
  • Java Spring Boot 2.6.0开发实战-1024程序员节创造营公益课
  • 大数据知识图谱系列—基于ELK+Flink日志全观测最佳实践
  • MySQL实操课程
  • MySQL高级应用 - 索引和锁
  • Linux MySQL服务器搭建与应用
  • 场景实践-基于阿里云Quick BI 对MOOC网站日志分析

    • 相关电子书

    更多
  • PostgresChina2018_赖思超_PostgreSQL10_hash索引的WAL日志修改版final
  • Kubernetes下日志实时采集、存储与计算实践
  • 日志数据采集与分析对接

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens管理SLS日志采集
  • 使用CloudLens采集PolarDB日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    手把手教你白嫖阿里云服务器(免费领服务器)