MySQL 二进制日志(Binary Log)

同大多数关系型数据库一样，日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件，通常包括错误日志文件，二进制日志，通用日志，慢查询日志，等等。这些日志可以帮助我们定位mysqld内部发生的事件，数据库性能故障，记录数据的变更历史，用户恢复数据库等等。二进制日志，也叫binary log，是MySQL Server中最为重要的日志之一，本文主要描述二进制日志。

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

a、错误日志：记录启动、运行或停止mysqld时出现的问题。

b、通用日志：记录建立的客户端连接和执行的语句。

c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。

d、二进制日志：记录所有更改数据的语句。还用于复制。

e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

f、Innodb日志：innodb redo log

缺省情况下，所有日志创建于mysqld数据目录中。

可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。

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

对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。

2、二进制日志(Binary log)

a、它包含的内容及作用如下：

包含了所有更新了数据或者已经潜在更新了数据(比如没有匹配任何行的一个DELETE)

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

不包含没有修改任何数据的语句，如果需要启用该选项，需要开启通用日志功能

主要目的是尽可能的将数据库恢复到数据库故障点，因为二进制日志包含备份后进行的所有更新

用于在主复制服务器上记录所有将发送给从服务器的语句

启用该选项数据库性能降低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、二进制日志相关演示

a、启用二进制日志

--当前环境

root@localhost[(none)]>showvariableslike'%version%';

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

|Variable_name|Value|

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

|innodb_version|5.5.39|

|protocol_version|10|

|slave_type_conversions||

|version|5.5.39|

|version_comment|MySQLCommunityServer(GPL)|

|version_compile_machine|x86_64|

|version_compile_os|Linux|

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

root@localhost[(none)]>showvariableslike'%log_bin%';

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

|Variable_name|Value|

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

|log_bin|OFF|--该参数用于设定是否启用二进制日志

|log_bin_trust_function_creators|OFF|

|sql_log_bin|ON|

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

--以下为binarylog相关参数

root@localhost[(none)]>showvariableslike'%binlog%';

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

|Variable_name|Value|

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

|binlog_cache_size|32768|

|binlog_direct_non_transactional_updates|OFF|

|binlog_format|STATEMENT|

|binlog_stmt_cache_size|32768|

|innodb_locks_unsafe_for_binlog|OFF|

|max_binlog_cache_size|18446744073709547520|

|max_binlog_size|1073741824|

|max_binlog_stmt_cache_size|18446744073709547520|

|sync_binlog|0|

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

--当前mysql服务器数据文件的缺省位置

root@localhost[(none)]>showvariableslike'%datadir%';

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

|Variable_name|Value|

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

|datadir|/var/lib/mysql/|

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

--停止mysql服务器

suse11b:~#servicemysqlstop

ShuttingdownMySQL....done

--编辑my.cnf来设定binarylog日志位置(注，配置二进制日志路径及文件名后，系统变量log_bin被自动置为on)

suse11b:~#vi/etc/my.cnf

suse11b:~#grep-v^#/etc/my.cnf

[mysqld]

log-error=/tmp/suse11b.err

log_bin=/var/lib/mysql/binarylog/binlog

suse11b:~#mkdir-p/var/lib/mysql/binarylog

suse11b:~#chown-Rmysql:mysql/var/lib/mysql/binarylog

suse11b:~#/etc/init.d/mysqlstart

StartingMySQL..done

suse11b:~#ls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql39Oct313:41/var/lib/mysql/binarylog/binlog.index#索引文件

-rw-rw----1mysqlmysql107Oct313:41/var/lib/mysql/binarylog/binlog.000001#日志文件

b、切换日志

suse11b:~#mysql-uroot-pxxx

root@localhost[(none)]>flushlogs;

QueryOK,0rowsaffected(0.04sec)

root@localhost[(none)]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql78Oct313:43/var/lib/mysql/binarylog/binlog.index

-rw-rw----1mysqlmysql107Oct313:43/var/lib/mysql/binarylog/binlog.000002#切换后产生了000002

-rw-rw----1mysqlmysql147Oct313:43/var/lib/mysql/binarylog/binlog.000001

root@localhost[(none)]>systemmysqladminflush-logs#使用mysqladmin命令行工具flush-logs方式切换日志

root@localhost[(none)]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql147Oct313:43/var/lib/mysql/binarylog/binlog.000001

-rw-rw----1mysqlmysql117Oct313:45/var/lib/mysql/binarylog/binlog.index

-rw-rw----1mysqlmysql107Oct313:45/var/lib/mysql/binarylog/binlog.000003#切换后产生了000003

-rw-rw----1mysqlmysql147Oct313:45/var/lib/mysql/binarylog/binlog.000002

root@localhost[(none)]>systemmysqladminrefresh#使用mysqladmin命令行工具refresh方式切换日志

root@localhost[(none)]>systemls-hltr/var/lib/mysql/binarylog/*

-rw-rw----1mysqlmysql147Oct313:43/var/lib/mysql/binarylog/binlog.000001

-rw-rw----1mysqlmysql147Oct313:45/var/lib/mysql/binarylog/binlog.000002

-rw-rw----1mysqlmysql156Oct313:46/var/lib/mysql/binarylog/binlog.index

-rw-rw----1mysqlmysql107Oct313:46/var/lib/mysql/binarylog/binlog.000004#切换后产生了000004

-rw-rw----1mysqlmysql147Oct313:46/var/lib/mysql/binarylog/binlog.000003

c、模拟产生二进制日志及查看内容

root@localhost[(none)]>createdatabasetempdb;

QueryOK,1rowaffected(0.00sec)

root@localhost[(none)]>usetempdb

Databasechanged

root@localhost[tempdb]>createtabletb1(idsmallint,valvarchar(10));

QueryOK,0rowsaffected(0.00sec)

root@localhost[tempdb]>insertintotb1values(1,'jack');

QueryOK,1rowaffected(0.01sec)

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

bin?8.T

5.5.39-log

z=.T

tempdb

createdatabasetempdb

tempdb

createtabletb1(idsmallint,valvarchar(10))

tempdb

BEGIN

tempdb

insertintotb1values(1,'jack')

root@localhost[tempdb]>systemmore/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]>systemmysqlbinlog/var/lib/mysql/binarylog/binlog.000004

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

/*!40019SET@@session.max_insert_delayed_threads=0*/;

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

DELIMITER/*!*/;

#at4

#14100313:46:39serverid1end_log_pos107Start:binlogv4,serverv5.5.39-logcreated14100313:46:39

#Warning:thisbinlogiseitherinuseorwasnotclosedproperly.

BINLOG'

PzguVA8BAAAAZwAAAGsAAAABAAQANS41LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==

'/*!*/;

#at107

#14100314:08:58serverid1end_log_pos194Querythread_id=1exec_time=0error_code=0

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

/*!/Cutf8*//*!*/;

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

createdatabasetempdb

/*!*/;

#at194

#14100314:09:36serverid1end_log_pos304Querythread_id=1exec_time=0error_code=0

use`tempdb`/*!*/;

SETTIMESTAMP=1412316576/*!*/;

createtabletb1(idsmallint,valvarchar(10))

/*!*/;

#at304

#14100314:09:56serverid1end_log_pos374Querythread_id=1exec_time=0error_code=0

SETTIMESTAMP=1412316596/*!*/;

BEGIN

/*!*/;

#at374

#14100314:09:56serverid1end_log_pos471Querythread_id=1exec_time=0error_code=0

SETTIMESTAMP=1412316596/*!*/;

insertintotb1values(1,'jack')

/*!*/;

#at471

#14100314:09:56serverid1end_log_pos498Xid=25

COMMIT/*!*/;

DELIMITER;

#Endoflogfile

ROLLBACK/*addedbymysqlbinlog*/;

/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

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

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

d、管理二进制日志

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

--使用showbinarylogs获取二进制日志相关信息

root@localhost[(none)]>helpshowbinarylogs;

Name:'SHOWBINARYLOGS'

Description:

Syntax:

SHOWBINARYLOGS

SHOWMASTERLOGS

Liststhebinarylogfilesontheserver.Thisstatementisusedas

partoftheproceduredescribedin[HELPPURGEBINARYLOGS],thatshows

howtodeterminewhichlogscanbepurged.

root@localhost[tempdb]>showbinarylogs;

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

|Log_name|File_size|

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

|binlog.000001|147|

|binlog.000002|147|

|binlog.000003|147|

|binlog.000004|498|

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

showbinlogevents用于在二进制日志中显示事件。如果未指定'log_name'，则显示第一个二进制日志。

root@localhost[(none)]>helpshowbinlogevents;--获取帮助信息

Name:'SHOWBINLOGEVENTS'

Description:

Syntax:

SHOWBINLOGEVENTS

[IN'log_name'][FROMpos][LIMIT[offset,]row_count]

Showstheeventsinthebinarylog.Ifyoudonotspecify'log_name',

thefirstbinarylogisdisplayed.

root@localhost[(none)]>showbinlogevents;

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

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

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

|binlog.000001|4|Format_desc|1|107|Serverver:5.5.39-log,Binlogver:4|

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

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

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

ERROR1220(HY000):ErrorwhenexecutingcommandSHOWBINLOGEVENTS:Couldnotfindtargetlog

--下面的这个查询中，前面执行的DML在这里均可以看到

root@localhost[tempdb]>showbinlogeventsin'binlog.000004';

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

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

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

|binlog.000004|4|Format_desc|1|107|Serverver:5.5.39-log,Binlogver:4|

|binlog.000004|107|Query|1|194|createdatabasetempdb|

|binlog.000004|194|Query|1|304|use`tempdb`;createtabletb1(idsmallint,valvarchar(10))|

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

|binlog.000004|374|Query|1|471|use`tempdb`;insertintotb1values(1,'jack')|

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

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

root@localhost[tempdb]>showbinlogeventsin'binlog.000004'from374;

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

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

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

|binlog.000004|374|Query|1|471|use`tempdb`;insertintotb1values(1,'jack')|

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

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

root@localhost[tempdb]>showbinlogeventsin'binlog.000004'from374limit1;

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

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

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

|binlog.000004|374|Query|1|471|use`tempdb`;insertintotb1values(1,'jack')|

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

d、删除历史日志

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

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

--自动日志删除通常发生在服务器启动以及日志flush

--resetmaster方式

root@localhost[(none)]>helppurge;

Name:'PURGEBINARYLOGS'

Description:

Syntax:

PURGE{BINARY|MASTER}LOGS

{TO'log_name'|BEFOREdatetime_expr}

Examples:

PURGEBINARYLOGSTO'mysql-bin.010';

PURGEBINARYLOGSBEFORE'2008-04-0222:46:26';

root@localhost[tempdb]>purgebinarylogsto'binlog.000003';

QueryOK,0rowsaffected(0.12sec)

root@localhost[tempdb]>showbinarylogs;

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

|Log_name|File_size|

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

|binlog.000003|147|

|binlog.000004|498|

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

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

-rw-rw----1mysqlmysql147Oct313:46/var/lib/mysql/binarylog/binlog.000003

-rw-rw----1mysqlmysql498Oct314:09/var/lib/mysql/binarylog/binlog.000004

-rw-rw----1mysqlmysql78Oct314:23/var/lib/mysql/binarylog/binlog.index

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

root@localhost[tempdb]>purgebinarylogsbefore'2014-10-0314:09:56';

QueryOK,0rowsaffected(0.02sec)

root@localhost[tempdb]>showbinarylogs;

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

|Log_name|File_size|

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

|binlog.000004|498|

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

--Author:Leshami

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

--重置所有日志

--resetmaster将删除在索引文件中列出所有的日志文件并重置索引文件，最后生成一个新的binlog文件。

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

root@localhost[tempdb]>helpresetmaster;

Name:'RESETMASTER'

Description:

Syntax:

RESETMASTER

Deletesallbinarylogfileslistedintheindexfile,resetsthe

binarylogindexfiletobeempty,andcreatesanewbinarylogfile.

Thisstatementisintendedtobeusedonlywhenthemasterisstarted

forthefirsttime.

root@localhost[tempdb]>resetmaster;

QueryOK,0rowsaffected(0.13sec)

root@localhost[tempdb]>showbinarylogs;

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

|Log_name|File_size|

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

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

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

--expire_log系统变量控制二进制日志自动删除的天数。默认值为0,表示“没有自动删除”。启动时和二进制日志循环时可能删除。

root@localhost[tempdb]>showvariableslike'expire_log%';

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

|Variable_name|Value|

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

|expire_logs_days|0|

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

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

ERROR1229(HY000):Variable'expire_logs_days'isaGLOBALvariableandshouldbesetwithSETGLOBAL

root@localhost[tempdb]>setglobalexpire_logs_days=7;--设置

QueryOK,0rowsaffected(0.01sec)

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

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

|@@expire_logs_days|

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

|7|

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