广州技术支持 骏域网站建设,朋友圈广告推广平台,南沙区做网站,桂林市区景点MySQL binlog日志记录了MySQL数据库从启用日志以来所有对当前数据库的变更。binlog日志属于二进制文件#xff0c;我们可以从binlog提取出来生成可阅读的SQL语句来重建当前数据库以及根据需要实现时点恢复或不完全恢复。本文主要描述了如果提取binlog日志#xff0c;并给出相…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 /* xid382 */ || APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos4 |----------------------------------------------------------------------------------------------------------------- 在上面的结果中第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 /* xid394 */ |---------------------------------------------------------------------------------------------------------------c、查看当前正在写入的binlog文件(show master status\G)mysql show master status\G*************************** 1. row ***************************File: APP01bin.000002Position: 363Binlog_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 /* xid382 */ || APP01bin.000001 | 363 | Rotate | 11 | 409 | APP01bin.000002;pos4 |--------------------------------------------------------------------------------------------------------------3、演示mysqlbinlog方式提取binloga、提取指定的binlog日志# mysqlbinlog /opt/data/APP01bin.000001# mysqlbinlog /opt/data/APP01bin.000001|grep insert/*!40019 SET session.max_insert_delayed_threads0*/;insert into tb values(2,jack)b、提取指定position位置的binlog日志# mysqlbinlog --start-position120 --stop-position332 /opt/data/APP01bin.000001c、提取指定position位置的binlog日志并输出到压缩文件# mysqlbinlog --start-position120 --stop-position332 /opt/data/APP01bin.000001 |gzip extra_01.sql.gzd、提取指定position位置的binlog日志导入数据库# mysqlbinlog --start-position120 --stop-position332 /opt/data/APP01bin.000001 | mysql -uroot -pe、提取指定开始时间的binlog并输出到日志文件# mysqlbinlog --start-datetime2014-12-15 20:15:23 /opt/data/APP01bin.000002 --result-fileextra02.sqlf、提取指定位置的多个binlog日志文件# mysqlbinlog --start-position120 --stop-position332 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002|moreg、提取指定数据库binlog并转换字符集到UTF8# mysqlbinlog --databasetest --set-charsetutf8 /opt/data/APP01bin.000001 /opt/data/APP01bin.000002 test.sqlh、远程提取日志指定结束时间# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime2014-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.sql4、获取mysqlbinlog的帮助信息(仅列出常用选项)-d, --databasename-f, --force-read-h, --hostname-l, --local-loadname-p, --password[name]-P, --port#--protocolname-R, --read-from-remote-server|--read-from-remote-mastername-r, --result-filename-s, --short-form-S, --socketname--start-datetimename--stop-datetimename-j, --start-position#--server-id#--set-charsetname-D, --disable-log-bin-u, --username-v, --verbose-V, --version
