使用mysqlbinlog提取二进制日志_MySQL
| MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。binlog日志属于二进制文件，我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复或不完全恢复。本文主要描述了如果提取binlog日志，并给出相关示例。 有关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 changedmysql> 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/leshamib、查看指定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.000001c、提取指定position位置的binlog日志并输出到压缩文件# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 |gzip >extra_01.sql.gzd、提取指定position位置的binlog日志导入数据库# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 | mysql -uroot -pe、提取指定开始时间的binlog并输出到日志文件# mysqlbinlog --start-datetime="2014-12-15 20:15:23" /opt/data/APP01bin.000002 --result-file=extra02.sqlf、提取指定位置的多个binlog日志文件# mysqlbinlog --start-position="120" --stop-position="332" /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|moreg、提取指定数据库binlog并转换字符集到UTF8# mysqlbinlog --database=test --set-charset=utf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 >test.sqlh、远程提取日志，指定结束时间 # mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime="2014-12-15 20:30:23" --read-from-remote-server mysql-bin.000033 |morei、远程提取使用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 |