mysqlbinlog -v与-vv --base64-output 与不加的区别

最新推荐文章于 2021-01-21 17:57:20 发布

原创最新推荐文章于 2021-01-21 17:57:20 发布 · 9.6k 阅读

4 ·

CC 4.0 BY-SA版权

MYSQL 专栏收录该内容

108 篇文章

订阅专栏

本文介绍了MySQL Binlog的基本使用方法，包括如何通过不同参数设置来解析Binlog文件，从而获取SQL语句及其详细信息，这对于数据库操作的审计和误操作的恢复具有重要意义。

-v, --verbose Reconstruct pseudo-SQL statements out of row events. -v

-v adds comments on column data types.

-v参数是可能从binlog中重建sql语句，这对于误操作的恢复很我帮助。-vv增加了备注部分

[root@qht131 mysql]# mysqlbinlog --no-defaults mysql_bin.000039 > nov.sql
[root@qht131 mysql]# mysqlbinlog --no-defaults -v mysql_bin.000039 > onev.sql
[root@qht131 mysql]# mysqlbinlog --no-defaults -v -v mysql_bin.000039 > twov.sql

加了-v参数比不加-v的多了sql部分：

[root@qht131 mysql]# diff nov.sql onev.sql
50a51,53
> ### INSERT INTO `db1`.`t1`
> ### SET
> ###   @1=1
71a75,77
> ### INSERT INTO `db1`.`t1`
> ### SET
> ###   @1=2
92a99,101
> ### INSERT INTO `db1`.`t1`
> ### SET
> ###   @1=3
113a123,125
> ### INSERT INTO `db1`.`t1`
> ### SET
> ###   @1=4

-v和-vv的区别在于-vv增加了备注部分

[root@qht131 mysql]# diff onev.sql twov.sql
53c53
< ###   @1=1
---
> ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
77c77
< ###   @1=2
---
> ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
101c101
< ###   @1=3
---
> ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
125c125
< ###   @1=4
---
> ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */

--base64-output参数用来控制binlog部分是否显示出来的，指定为decode-rows表示不显示binglog部分

[root@qht131 mysql]# mysqlbinlog --no-defaults -v mysql_bin.000039 > nobase64.sql
[root@qht131 mysql]# mysqlbinlog --no-defaults -v --base64-output=decode-rows mysql_bin.000039 > base64.sql

[root@qht131 mysql]# diff nobase64.sql base64.sql
8,12d7
< BINLOG '
< JV7gWg8QJwAAdwAAAHsAAAABAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
< AAAAAAAAAAAAAAAAAAAlXuBaEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
< AZMlD6o=
< '/*!*/;
46,50d40
<
< BINLOG '
< XGHgWhMQJwAALAAAAO8BAAAAAGwAAAAAAAEAA2RiMQACdDEAAQMAAeAiC0o=
< XGHgWh4QJwAAKAAAABcCAAAAAGwAAAAAAAEAAgAB//4BAAAAcayaSA==
< '/*!*/;
70,74d59
<
< BINLOG '
< X2HgWhMQJwAALAAAAOoCAAAAAGwAAAAAAAEAA2RiMQACdDEAAQMAAZQPLZ0=
< X2HgWh4QJwAAKAAAABIDAAAAAGwAAAAAAAEAAgAB//4CAAAA5lfIsg==
< '/*!*/;
94,98d78
<
< BINLOG '
< 4WHgWhMQJwAALAAAAOUDAAAAAGwAAAAAAAEAA2RiMQACdDEAAQMAAVfdyUk=
< 4WHgWh4QJwAAKAAAAA0EAAAAAGwAAAAAAAEAAgAB//4DAAAAlL7CPg==
< '/*!*/;
118,122d97
<
< BINLOG '
< YWLgWhMQJwAALAAAAOAEAAAAAGwAAAAAAAEAA2RiMQACdDEAAQMAAXrMoIc=
< YWLgWh4QJwAAKAAAAAgFAAAAAGwAAAAAAAEAAgAB//4EAAAAV5wOww==
< '/*!*/;