1.使用show binlog events
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
a.获取binlog文件列表
mysql> show binary logs;
+—————————+—————–+
| Log_name | File_size |
+—————————+—————–+
| mysql–bin.000005 | 1288 |
| mysql–bin.000006 | 120 |
+—————————+—————–+
mysql>
b.查看当前正在写入的binlog文件
mysql> show master status\G;
*************************** 1. row ***************************
File: mysql–bin.000006
Position: 120
c.查看指定binlog文件的内容语法:
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
1.演示查看一个二进制文件中全部事务
注:binlog文件实际上是由一连串的event组成的一个组，即事务组。
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000005' \G
*************************** 1. row *************************** 一个row代表一个事务组
Log_name: mysql–bin.000005
Pos: 4
Event_type: Format_desc
Server_id: 1
End_log_pos: 120
Info: Server ver: 5.6.29–log, Binlog ver: 4
*************************** 2. row ***************************
Log_name: mysql–bin.000005
Pos: 120
Event_type: Query
Server_id: 1
End_log_pos: 194
Info: BEGIN
2.演示对一个二进制文件的事务做过滤查询
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000005' FROM 194 LIMIT 2 \G;
*************************** 1. row ***************************
Log_name: mysql–bin.000005
Pos: 194
Event_type: Query
Server_id: 1
End_log_pos: 319
Info: use
*************************** 2. row ***************************
Log_name: mysql–bin.000005
Pos: 319
Event_type: Query
Server_id: 1
End_log_pos: 458
Info: use
rows in set (0.00 sec)
mysql>
|
2.使用mysqlbinlog查看binlog(转:http://blog.csdn.net/leshami/article/details/41962243)
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
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
|
3.mysqlbinlog 查看row模式的binlog
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
当bin–log的模式设置为row时(binlog_format=row)，查看执行的sql时也稍微麻烦一点，默认解析出来的结果是这样的
–bash–4.1$ /opt/mysql/bin/mysqlbinlog /opt/mysql/var/mysql–bin.000004|more
/*!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
#160614 23:18:20 server id 1 end_log_pos 120 CRC32 0xbb07feb7 Start: binlog v 4, server v 5.6.14-log created 160614 23:18:20 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
PCBgVw8BAAAAdAAAAHgAAAABAAQANS42LjE0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA8IGBXEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAbf+
B7s=
'/*!*/;
|
完全看不懂，这时需要添加参数( base64-output=decode-rows -v)对输出结果解码。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
|
–bash–4.1$ /opt/mysql/bin/mysqlbinlog —base64–output=decode–rows –v /opt/mysql/var/mysql–bin.000004|more
/*!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
#160614 23:18:20 server id 1 end_log_pos 120 CRC32 0xbb07feb7 Start: binlog v 4, server v 5.6.14-log created 160614 23:18:20 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120
#160615 10:46:11 server id 1 end_log_pos 195 CRC32 0x9579c284 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1465958771/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1073741824/*!*/;
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=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 195
#160615 10:46:11 server id 1 end_log_pos 300 CRC32 0xfadf2614 Table_map:
dentist.ds_case_no_type mapped to number 93# at 300
#160615 10:46:11 server id 1 end_log_pos 690 CRC32 0x210669fe Update_rows: table id 93 flags: STMT_END_F
### UPDATE
dentist.ds_case_no_type### WHERE
### @1=’736666419b3a40d9b384a03311baf2be'
### @2=’5′
### @3=NULL
### @4=NULL
### @5=’88'
### @6=’66'
### @7=’1500000000′
### @8=’1500000006′
### @9=NULL
### @10=’0′
### @11=NULL
### @12=’c9c8d05d4d3b44379598dde5d02f1552′
### @13=’2016-06-07 17:03:57′
### @14=’c9c8d05d4d3b44379598dde5d02f1552′
### @15=’2016-06-12 13:17:33′
### @16=’67e05761a61243b68e4b259386dc0dc8′
### SET
### @1=’736666419b3a40d9b384a03311baf2be'
### @2=’5′
### @3=NULL
### @4=NULL
### @5=’88'
### @6=’66'
### @7=’1500000000′
### @8=’1500000007′
### @9=NULL
### @10=’0′
### @11=NULL
### @12=’c9c8d05d4d3b44379598dde5d02f1552′
### @13=’2016-06-07 17:03:57′
### @14=’c9c8d05d4d3b44379598dde5d02f1552′
### @15=’2016-06-12 13:17:33′
### @16=’67e05761a61243b68e4b259386dc0dc8′
# at 690
#160615 10:46:11 server id 1 end_log_pos 721 CRC32 0x70d0f695 Xid = 699
COMMIT/*!*/;
# at 721
|
from:http://www.cnblogs.com/polestar/p/5651870.html