一个数据库必须拥有完善的日志系统,MySQL的日志系统组成如下:
- 错误日志(error log):记录启动、运行或停止mysqld时出现的问题。
- 通用日志(general log):记录建立的客户端连接和执行的语句。
- 二进制日志(binary log):记录所有对数据库的更改,还用于复制。
- 慢查询日志(slow query log):记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。
- Innodb事务日志:事务日志或者叫redo log/undo log
缺省情况下,MySQL只打开错误日志,并且默认所有日志创建于mysqld数据目录中。
可以通过刷新日志,来强制mysqld来关闭和重新打开日志文件(或者在某些情况下切换到一个新的日志)。
当你执行一个FLUSH
LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时,则日志被刷新。
本文只是对binary log做一个详细解释。
二进制日志基础
1. binlog包含的内容和作用:
对数据库的所有DML以及一些DDL更改操作都会记录在二进制日志中
主要目的是尽可能的将数据库恢复到数据库故障点,因为二进制日志包含数据库的所有更新binlog日志属于二进制文件,我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复(Point-In-Time)或不完全恢复。
用于在主从复制(mysql
replication),
启用该选项数据库性能降低1%,但保障数据库完整性,对于重要数据库值得以性能换完整。有些类似于oracle开启归档模式。
2. binlog相关变量和参数
- 命令行参数
--log-bin [=file_name]
设置此参数表示启用binlog功能,并制定路径名称。
--log-bin-index[=file]
设置此参数是指定二进制索引文件的路径与名称。
--max_binlog_size
binlog最大值,最大和默认值是1GB,该设置并不能严格控制Binlog的大小,尤其是Binlog比较靠近最大值而又遇到一个比较大事务时,为了保证事务的完整性,不可能做切换日志的动作,只能将该事务的所有SQL都记录进当前日志,直到事务结束。
--binlog-do-db=db_name
此参数表示只记录指定数据库的二进制日志
--binlog-ignore-db=db_name
此参数表示不记录指定的数据库的二进制日志
- 系统变量
log_bin
binlog_cache_size
此参数表示binlog使用的内存大小,可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。
max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸
binlog_cache_use
使用二进制日志缓存的事务数量
binlog_cache_disk_use
使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量。
binlog_do_db
binlog_ignore_db
sync_binlog
这个参数直接影响mysql的性能和完整性。
sync_binlog=0:
mysql中默认的设置是sync_binlog=0,即不做任何强制性的磁盘刷新指令,这时性能是最好的,但风险也是最大的。当事务提交后,mysql仅仅是将binlog_cache中的数据写入binlog文件,但不执行fsync之类的磁盘同步,而让Filesystem自行决定什么时候来做同步,这个是性能最好的。但是一旦系统Crash,在文件系统缓存中的所有Binlog信息都会丢失。
3. 日志格式
- 基于SQL语句的复制(statement-based replication,SBR),
- 基于行的复制(row-based replication,RBR),
- 混合模式复制(mixed-based replication,MBR)。
之前的MySQL一直都只有基于statement的复制模式,直到5.1.5版本的MySQL才开始支持row level的复制。
从5.0开始,MySQL的复制已经解决了大量老版本中出现的无法正确复制的问题。
但是由于存储过程的出现,给MySQL Replication复制又带来了更大的新挑战。
另外,看到官方文档说,从5.1.8版本开始,MySQL提供了除Statement Level和Row
Level之外的第三种复制模式:Mixed,实际上就前两种模式的结合。
在Mixed模式下,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式,也就是在Statement和Row之间选择一种。
新版本中的Statement Level还是和以前一样,仅仅记录执行的语句。
而新版本的MySQL中对row level模式也被做了优化,并不是所有的修改都会以row
level来记录,像遇到表结构变更的时候就会以statement模式来记录,如果sql语句确实就是update或者delete等修改数据的语句,
那么还是会记录所有行的变更。
静态设置binlog格式:
vi my.cnf log-bin = mysql-bin #binlog_format = "STATEMENT" #binlog_format = "ROW" binlog_format = "MIXED"
动态修改binlog格式:
mysql> SET SESSION binlog_format = 'STATEMENT'; mysql> SET SESSION binlog_format = 'ROW'; mysql> SET SESSION binlog_format = 'MIXED'; mysql> SET GLOBAL binlog_format = 'STATEMENT'; mysql> SET GLOBAL binlog_format = 'ROW'; mysql> SET GLOBAL binlog_format = 'MIXED';
4. 日志切换策略
使用索引来循环记录文件,在以下条件将循环至下一个索引
a.服务器重启
b.服务器被更新
c.日志达到了最大日志长度max_binlog_size
d.日志被手动刷新mysql> flush logs;
5. 清除binlog
PURGE {MASTER|BINARY} LOGS TO 'log_name' ; //log_name不会被清除
PURGE {MASTER|BINARY} LOGS BEFORE 'date' ; //date不会被清除
RESET MASTER; //删除之前所有的binlog,并重新生成新的binlog,后缀从000001开始。
二进制日志操作演示
a、启用二进制日志 --当前环境 root@localhost[(none)]> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | innodb_version | 5.5.39 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.39 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+------------------------------+ root@localhost[(none)]> show variables like '%log_bin%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | log_bin | OFF | --该参数用于设定是否启用二进制日志 | log_bin_trust_function_creators | OFF | | sql_log_bin | ON | +---------------------------------+-------+ --以下为binary log相关参数 root@localhost[(none)]> show variables like '%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)]> show variables like '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ --停止mysql服务器 suse11b:~ # service mysql stop Shutting down MySQL.... done --编辑my.cnf来设定binary log日志位置(注,配置二进制日志路径及文件名后,系统变量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 -R mysql:mysql /var/lib/mysql/binarylog suse11b:~ # /etc/init.d/mysql start Starting MySQL.. done suse11b:~ # ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 39 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.index #索引文件 -rw-rw---- 1 mysql mysql 107 Oct 3 13:41 /var/lib/mysql/binarylog/binlog.000001 #日志文件 b、切换日志 suse11b:~ # mysql -uroot -pxxx root@localhost[(none)]> flush logs; Query OK, 0 rows affected (0.04 sec) root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 78 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.index -rw-rw---- 1 mysql mysql 107 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000002 #切换后产生了000002 -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001 root@localhost[(none)]> system mysqladmin flush-logs #使用mysqladmin命令行工具flush-logs方式切换日志 root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001 -rw-rw---- 1 mysql mysql 117 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.index -rw-rw---- 1 mysql mysql 107 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000003 #切换后产生了000003 -rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002 root@localhost[(none)]> system mysqladmin refresh #使用mysqladmin命令行工具refresh方式切换日志 root@localhost[(none)]> system ls -hltr /var/lib/mysql/binarylog/* -rw-rw---- 1 mysql mysql 147 Oct 3 13:43 /var/lib/mysql/binarylog/binlog.000001 -rw-rw---- 1 mysql mysql 147 Oct 3 13:45 /var/lib/mysql/binarylog/binlog.000002 -rw-rw---- 1 mysql mysql 156 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.index -rw-rw---- 1 mysql mysql 107 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000004 #切换后产生了000004 -rw-rw---- 1 mysql mysql 147 Oct 3 13:46 /var/lib/mysql/binarylog/binlog.000003 c、模拟产生二进制日志及查看内容 root@localhost[(none)]> create database tempdb; Query OK, 1 row affected (0.00 sec) root@localhost[(none)]> use tempdb Database changed root@localhost[tempdb]> create table tb1(id smallint,val varchar(10)); Query OK, 0 rows affected (0.00 sec) root@localhost[tempdb]> insert into tb1 values(1,'jack'); Query OK, 1 row affected (0.01 sec) root@localhost[tempdb]> system strings /var/lib/mysql/binarylog/binlog.000004 bin?8.T 5.5.39-log z=.T tempdb create database tempdb tempdb create table tb1(id smallint,val varchar(10)) tempdb BEGIN tempdb 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/*!*/; create database tempdb /*!*/; # 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/*!*/; create table tb1(id smallint,val varchar(10)) /*!*/; # 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/*!*/; BEGIN /*!*/; # at 374 #141003 14:09:56 server id 1 end_log_pos 471 Query thread_id=1 exec_time=0 error_code=0 SET TIMESTAMP=1412316596/*!*/; insert into tb1 values(1,'jack') /*!*/; # 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' Description: Syntax: 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; +---------------+-----+------------+-----------+-------------+------------------------------------------------+ | 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') | +---------------+-----+------------+-----------+-------------+------------------------------------------------+ d、删除历史日志 --使用purge手动删除指定日志 --使用expire-log-days删除失效日志,设置变量expire_logs_days,删除超出这个变量保留期之前的所有日志被删除 --自动日志删除通常发生在服务器启动以及日志flush --reset master方式 root@localhost[(none)]> help purge; Name: 'PURGE BINARY LOGS' Description: Syntax: 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]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000003 | 147 | | binlog.000004 | 498 | +---------------+-----------+ 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) root@localhost[tempdb]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | binlog.000004 | 498 | +---------------+-----------+ --重置所有日志 --reset master将删除在索引文件中列出所有的日志文件并重置索引文件,最后生成一个新的binlog文件。 --该操作之前先备份binlog至其它位置以备以后需要。 root@localhost[tempdb]> help reset master; Name: 'RESET MASTER' Description: Syntax: 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) root@localhost[tempdb]> show binary logs; +---------------+-----------+ | Log_name | File_size | +---------------+-----------+ | 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 | +--------------------+
mysqlbinlog解析提取binlog
1、提取mysqlbinlog的几种方式
a、使用show binlog events方式可以获取当前以及指定binlog的日志,不适宜提取大量日志。
b、使用mysqlbinlog命令行提取(适宜批量提取日志)。
2、演示show binlog events方式
mysql> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.6.12-log | +---------------+------------+ mysql> show binary logs; +-----------------+-----------+ | Log_name | File_size | +-----------------+-----------+ | APP01bin.000001 | 120 | +-----------------+-----------+ a、只查看第一个binlog文件的内容(show binlog events) mysql> use replication; Database changed mysql> select * from tb; +------+-------+ | id | val | +------+-------+ | 1 | robin | +------+-------+ mysql> insert into tb values(2,'jack'); Query OK, 1 row affected (0.02 sec) mysql> flush logs; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb values(3,'fred'); Query OK, 1 row affected (0.00 sec) mysql> show binary logs; +-----------------+-----------+ | Log_name | File_size | +-----------------+-----------+ | APP01bin.000001 | 409 | | APP01bin.000002 | 363 | +-----------------+-----------+ mysql> show binlog events; +-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | APP01bin.000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 | | APP01bin.000001 | 120 | Query | 11 | 213 | BEGIN | | APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') | | APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ | | APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 | +-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+ -- 在上面的结果中第3行可以看到我们执行的SQL语句,第4行为自动提交 b、查看指定binlog文件的内容(show binlog events in 'binname.xxxxx') mysql> show binlog events in 'APP01bin.000002'; +-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+ | APP01bin.000002 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 | | APP01bin.000002 | 120 | Query | 11 | 213 | BEGIN | | APP01bin.000002 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(3,'fred') | | APP01bin.000002 | 332 | Xid | 11 | 363 | COMMIT /* xid=394 */ | +-----------------+-----+-------------+-----------+-------------+----------------------------------------------------+ c、查看当前正在写入的binlog文件(show master status\G) mysql> show master status\G *************************** 1. row *************************** File: APP01bin.000002 Position: 363 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) d、获取指定位置binlog的内容(show binlog events from) mysql> show binlog events from 213; +-----------------+-----+------------+-----------+-------------+----------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-----------------+-----+------------+-----------+-------------+----------------------------------------------------+ | APP01bin.000001 | 213 | Query | 11 | 332 | use `replication`; insert into tb values(2,'jack') | | APP01bin.000001 | 332 | Xid | 11 | 363 | COMMIT /* xid=382 */ | | APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos=4 | +-----------------+-----+------------+-----------+-------------+----------------------------------------------------+
3、演示mysqlbinlog方式提取binlog
a、提取指定的binlog日志 # mysqlbinlog /opt/data/APP01bin.000001 # mysqlbinlog /opt/data/APP01bin.000001|grep insert /*!40019 SET @@session.max_insert_delayed_threads=0*/; insert into tb values(2,'jack') b、提取指定position位置的binlog日志 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 c、提取指定position位置的binlog日志并输出到压缩文件 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gz d、提取指定position位置的binlog日志导入数据库 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -p e、提取指定开始时间的binlog并输出到日志文件 # mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sql f、提取指定位置的多个binlog日志文件 # mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|more g、提取指定数据库binlog并转换字符集到UTF8 # mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sql h、远程提取日志,指定结束时间 # mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |more i、远程提取使用row格式的binlog日志并输出到本地文件 # mysqlbinlog -urobin -p -P3606 -h192.168.1.177 --read-from-remote-server -vv inst3606bin.000005 >row.sql
4、获取mysqlbinlog的帮助信息(仅列出常用选项)
-?, --help
显示帮助消息并退出。-d, --database=name
只列出该数据库的条目(只适用本地日志)。-f, --force-read
使用该选项,如果mysqlbinlog读它不能识别的二进制日志事件,它会打印警告,忽略该事件并继续。没有该选项,如果mysqlbinlog读到此类事件则停止。-h, --host=name
获取给定主机上的MySQL服务器的二进制日志。-l, --local-load=name
为指定目录中的LOAD DATA INFILE预处理本地临时文件。
-o,
--offset=#
跳过前N个条目。-p, --password[=name]
当连接服务器时使用的密码。如果使用短选项形式(-p),选项和密码之间不能有空格。
如果在命令行中–password或-p选项后面没有密码值,则提示输入一个密码。-P, --port=#
用于连接远程服务器的TCP/IP端口号。--protocol=name
使用的连接协议。-R, --read-from-remote-server|--read-from-remote-master=name
从MySQL服务器读二进制日志。如果未给出该选项,任何连接参数选项将被忽略,即连接到本地。
这些选项是–host、–password、–port、–protocol、–socket和–user。-r, --result-file=name
将输出指向给定的文件。-s, --short-form
只显示日志中包含的语句,不显示其它信息,该方式可以缩小生成sql文件的尺寸。-S, --socket=name
用于连接的套接字文件。--start-datetime=name
从二进制日志中读取等于或晚于datetime参量的事件,datetime值相对于运行mysqlbinlog的机器上的本地时区。
该值格式应符合DATETIME或TIMESTAMP数据类型。例如:2004-12-25 11:25:56 ,建议使用引号标识。--stop-datetime=name
从二进制日志中读取小于或等于datetime的所有日志事件。关于datetime值的描述参见--start-datetime选项。-j, --start-position=#
从二进制日志中第1个位置等于N参量时的事件开始读。
--stop-position=#
从二进制日志中第1个位置等于和大于N参量时的事件起停止读。--server-id=#
仅仅提取指定server_id的binlog日志--set-charset=name
添加SET NAMES character_set到输出
-t,
--to-last-log
在MySQL服务器中请求的二进制日志的结尾处不停止,而是继续打印直到最后一个二进制日志的结尾。
如果将输出发送给同一台MySQL服务器,会导致无限循环。该选项要求–read-from-remote-server。-D, --disable-log-bin
禁用二进制日志。如果使用–to-last-logs选项将输出发送给同一台MySQL服务器,可以避免无限循环。
该选项在崩溃恢复时也很有用,可以避免复制已经记录的语句。注释:该选项要求有SUPER权限。-u, --user=name
连接远程服务器时使用的MySQL用户名。-v, --verbose
用于输出基于row模式的binlog日志,-vv为列数据类型添加注释-V, --version
显示版本信息并退出。
5、小结
a、可以通过show binlog events以及mysqlbinlog方式来提取binlog日志。
b、show binlog events 参数有限不适宜批量提取,mysqlbinlog可用于批量提取来建立恢复数据库的SQL。
c、mysqlbinlog可以基于时间点,position等方式实现不完全恢复或时点恢复。
d、mysqlbinlog可以从支持本地或远程方式提取binlog日志。
e、mysqlbinlog可以基于server_id,以及基于数据库级别提取日志,不支持表级别。