二进制日志(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的值来查看是否启用。

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

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)创建日志

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