MySQL 二进制日志(Binary Log)

同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。

MySQL有几种不同的日志文件。通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志能够帮助我们定位mysqld内部发生的事件。数据库性能故障，记录数据的变更历史。用户恢复数据库等等。

二进制日志，也叫binary log。是MySQL Server中最为重要的日志之中的一个，本文主要描写叙述二进制日志。

1、MySQL日志文件系统的组成

   c、更新日志：记录更改数据的语句。

该日志在MySQL 5.1中已不再使用。

还用于复制。

   f、Innodb日志：innodb redo log

   缺省情况下，全部日志创建于mysqld数据文件夹中。

   能够通过刷新日志。来强制mysqld来关闭和又一次打开日志文件（或者在某些情况下切换到一个新的日志）。

   当你运行一个FLUSH LOGS语句或运行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。

   对于存在MySQL复制的情形下。从复制server将维护很多其它日志文件，被称为接替日志。

2、二进制日志(Binary log)

  a、它包括的内容及作用例如以下：

    包括了全部更新了数据或者已经潜在更新了数据(比方没有匹配不论什么行的一个DELETE)

    包括关于每一个更新数据库(DML)的语句的运行时间信息

    不包括没有改动不论什么数据的语句，假设须要启用该选项，须要开启通用日志功能

    主要目的是尽可能的将数据库恢复到数据库故障点，由于二进制日志包括备份后进行的全部更新

    用于在主复制server上记录全部将发送给从server的语句

    启用该选项数据库性能减少1%。但保障数据库完整性，对于关键数据库值得以性能换完整。有些相似于oracle开启归档模式。

  b、开启二进制日志的方法及属性

    使用--log-bin[=file_name]选项或在配置文件里指定log-bin启动时，mysqld写入包括全部更新数据的SQL命令的日志文件。

    对于未给出file_name值， 默认名为-bin后面所跟的主机名。

    在未指定绝对路径的情形下，缺省位置保存在数据文件夹下。

    每一个二进制日志名会加入一个数字扩展名用于日志老化，因此不支持自己定义的扩展名，会被mysql数字扩展名动态替换。

    若当前的日志大小达到max_binlog_size，则自己主动创建新的二进制日志。

    对于大的事务，二进制日志会超过max_binlog_size设定的值。也即是事务只写入一个二进制日志。

    由是可知。二进制日志文件大小接近。其size不是全然相等，这点不同于oracle。

    二进制日志文件会有一个相应二进制日志索引文件，该文件包括全部的二进制日志，其文件名称与二进制日志同样。扩展名为.index

    二进制索引文件通过--log-bin-index[=file_name]选项来指定

    RESET MASTER语句将删除全部二进制日志文件，这将影响到从库。

也能够用PURGE MASTER LOGS只删除部分二进制文件。

3、二进制日志相关演示

8.T

5.5.39-log

z=.T

tempdb

create database tempdb

create table tb1(id smallint,val varchar(10))

BEGIN

insert into tb1 values(1,'jack')

root@localhost[tempdb]> system more /var/lib/mysql/binarylog/binlog.index

/var/lib/mysql/binarylog/binlog.000001

/var/lib/mysql/binarylog/binlog.000002

/var/lib/mysql/binarylog/binlog.000003

/var/lib/mysql/binarylog/binlog.000004

--使用命令行工具mysqlbinlog直接提取二进制日志的内容

root@localhost[tempdb]> system mysqlbinlog /var/lib/mysql/binarylog/binlog.000004

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

#141003 13:46:39 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.39-log created 141003 13:46:39

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

BINLOG '

PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

# at 107

#141003 14:08:58 server id 1 end_log_pos 194 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1412316538/*!*/;

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

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

/*!*/;

# at 194

#141003 14:09:36 server id 1 end_log_pos 304 Query thread_id=1 exec_time=0 error_code=0

use `tempdb`/*!*/;

SET TIMESTAMP=1412316576/*!*/;

# at 304

#141003 14:09:56 server id 1 end_log_pos 374 Query thread_id=1 exec_time=0 error_code=0

SET TIMESTAMP=1412316596/*!*/;

# at 374

#141003 14:09:56 server id 1 end_log_pos 471 Query thread_id=1 exec_time=0 error_code=0

# at 471

#141003 14:09:56 server id 1 end_log_pos 498 Xid = 25

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

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

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

--从以上的内容能够看出二进制日志记录了全部操作的DML语句及其开销，以及一些系统环境变量的信息。

d、管理二进制日志

--对于二进制日志，应尽可能保存在安全的位置，与数据分开存储

--使用show binary logs获取二进制日志相关信息

root@localhost[(none)]> help show binary logs;

Name: 'SHOW BINARY LOGS'

Description:

Syntax:

SHOW BINARY LOGS

SHOW MASTER LOGS

Lists the binary log files on the server. This statement is used as

part of the procedure described in [HELP PURGE BINARY LOGS], that shows

how to determine which logs can be purged.

root@localhost[tempdb]> show binary logs;

+---------------+-----------+

| Log_name | File_size |

| binlog.000001 | 147 |

| binlog.000002 | 147 |

| binlog.000003 | 147 |

| binlog.000004 | 498 |

show binlog events用于在二进制日志中显示事件。假设未指定'log_name'，则显示第一个二进制日志。

root@localhost[(none)]> help show binlog events; --获取帮助信息

Name: 'SHOW BINLOG EVENTS'

SHOW BINLOG EVENTS

[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Shows the events in the binary log. If you do not specify 'log_name',

the first binary log is displayed.

root@localhost[(none)]> show binlog events;

+---------------+-----+-------------+-----------+-------------+---------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

| binlog.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |

| binlog.000001 | 107 | Rotate | 1 | 147 | binlog.000002;pos=4 |

root@localhost[(none)]> show binlog events in 'binlog.000005'; --binlog.000005不存在，所以报错

ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Could not find target log

--以下的这个查询中。前面运行的DML在这里均能够看到

root@localhost[tempdb]> show binlog events in 'binlog.000004';

+---------------+-----+-------------+-----------+-------------+-------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

| binlog.000004 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.39-log, Binlog ver: 4 |

| binlog.000004 | 107 | Query | 1 | 194 | create database tempdb |

| binlog.000004 | 194 | Query | 1 | 304 | use `tempdb`; create table tb1(id smallint,val varchar(10)) |

| binlog.000004 | 304 | Query | 1 | 374 | BEGIN |

| binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |

| binlog.000004 | 471 | Xid | 1 | 498 | COMMIT /* xid=25 */ |

root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374;

+---------------+-----+------------+-----------+-------------+------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

| binlog.000004 | 374 | Query | 1 | 471 | use `tempdb`; insert into tb1 values(1,'jack') |

| binlog.000004 | 471 | Xid | 1 | 498 | COMMIT /* xid=25 */ |

root@localhost[tempdb]> show binlog events in 'binlog.000004' from 374 limit 1;

d、删除历史日志

--使用purge手动删除指定日志

--使用expire-log-days删除失效日志，设置变量expire_logs_days，删除超出这个变量保留期之前的全部日志被删除

--自己主动日志删除通常发生在server启动以及日志flush

--reset master方式

root@localhost[(none)]> help purge;

Name: 'PURGE BINARY LOGS'

PURGE { BINARY | MASTER } LOGS

{ TO 'log_name' | BEFORE datetime_expr }

Examples:

PURGE BINARY LOGS TO 'mysql-bin.010';

PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

root@localhost[tempdb]> purge binary logs to 'binlog.000003';

Query OK, 0 rows affected (0.12 sec)

root@localhost[tempdb]> system ls -hltr /var/lib/mysql/binarylog/*

-rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003

-rw-rw---- 1 mysql mysql 498 Oct 3 14:09 /var/lib/mysql/binarylog/binlog.000004

-rw-rw---- 1 mysql mysql 78 Oct 3 14:23 /var/lib/mysql/binarylog/binlog.index

--使用before子句purge日志，binlog.000003被删除

root@localhost[tempdb]> purge binary logs before '2014-10-03 14:09:56';

Query OK, 0 rows affected (0.02 sec)

--Author: Leshami

--Blog : http://blog.csdn.net/leshami

--重置全部日志

--reset master将删除在索引文件里列出全部的日志文件并重置索引文件。最后生成一个新的binlog文件。

--该操作之前先备份binlog至其他位置以备以后须要。

root@localhost[tempdb]> help reset master;

Name: 'RESET MASTER'

RESET MASTER

Deletes all binary log files listed in the index file, resets the

binary log index file to be empty, and creates a new binary log file.

This statement is intended to be used only when the master is started

for the first time.

root@localhost[tempdb]> reset master;

Query OK, 0 rows affected (0.13 sec)

| binlog.000001 | 107 | --reset之后，从000001開始生成全新空日志

--expire_log系统变量控制二进制日志自己主动删除的天数。

默认值为0,表示“没有自己主动删除”。

启动时和二进制日志循环时可能删除。

root@localhost[tempdb]> show variables like 'expire_log%';

+------------------+-------+

| Variable_name | Value |

| expire_logs_days | 0 |

root@localhost[tempdb]> set expire_logs_days=7; --提示次系统变量为全局变量

ERROR 1229 (HY000): Variable 'expire_logs_days' is a GLOBAL variable and should be set with SET GLOBAL

root@localhost[tempdb]> set global expire_logs_days=7; --设置

Query OK, 0 rows affected (0.01 sec)

root@localhost[tempdb]> select @@expire_logs_days;

+--------------------+

| @@expire_logs_days |

| 7 |

本文转自mfrbuaa博客园博客，原文链接：http://www.cnblogs.com/mfrbuaa/p/5264744.html，如需转载请自行联系原作者