有关binlog的介绍与描述请参考：MySQL 二进制日志(Binary Log)
1、提取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行为自动提交 -- Author : Leshami -- Blog : http://blog.csdn.net/leshami 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的帮助信息(仅列出常用选项)
-d, --database=name
-f, --force-read
-h, --host=name
-l, --local-load=name
-p, --password[=name]
-P, --port=#
--protocol=name
-R, --read-from-remote-server|--read-from-remote-master=name
-r, --result-file=name
-s, --short-form
-S, --socket=name
--start-datetime=name
--stop-datetime=name
-j, --start-position=#
--server-id=#
--set-charset=name
-D, --disable-log-bin
-u, --user=name
-v, --verbose
-V, --version
下载本文