怎么运用mysqlbinlog rewrite-db选项

本篇内容介绍了“怎么使用mysqlbinlog rewrite-db选项”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

5.7中新mysqlbinlog新增加了rewrite-db选项，在binlog_format=row时，可以转换库的名称： 

在binlog_format=row时，不跨库 

flush logs; 

use tt; 

mysql> create table c(id int); 

Query OK, 0 rows affected (0.02 sec) 

mysql> insert into c values (1); 

Query OK, 1 row affected (0.01 sec) 

mysql> commit; 

Query OK, 0 rows affected (0.00 sec) 

mysqlbinlog --rewrite-db='tt->test' 1.000002 > /tmp/a.sql 

mysqlbinlog 1.000002 > /tmp/b.sql 

把/tmp/a.sql /tmp/b.sql进行对比 

diff /tmp/a.sql /tmp/b.sql 

20c20 

< use `test`/*!*/; 

--- 

> use `tt`/*!*/; 

41c41 

< #160531 21:57:41 server id 1 end_log_pos 488 CRC32 0x1907f678 Table_map: `test`.`c` mapped to number 110 

--- 

> #160531 21:57:41 server id 1 end_log_pos 488 CRC32 0x1907f678 Table_map: `tt`.`c` mapped to number 110 

46c46 

< VZhNVxMBAAAALAAAAOgBAAAAAG4AAAAAAAEABHRlc3QAAWMAAQMAAXj2Bxk= 

--- 

> VZhNVxMBAAAAKgAAAOgBAAAAAG4AAAAAAAEAAnR0AAFjAAEDAAF49gcZ 

在binlog_format=statement时，不跨库 

mysql> show variables like '%binlog_f%'; 

+---------------+-----------+ 

| Variable_name | Value | 

+---------------+-----------+ 

| binlog_format | STATEMENT | 

+---------------+-----------+ 

mysql> use tt; 

Reading table information for completion of table and column names 

You can turn off this feature to get a quicker startup with -A 

Database changed 

mysql> drop table c; 

Query OK, 0 rows affected (0.00 sec) 

mysql> flush logs; 

Query OK, 0 rows affected (0.01 sec) 

mysql> create table c(id int); 

Query OK, 0 rows affected (0.02 sec) 

mysql> insert into c values (1); 

Query OK, 1 row affected (0.00 sec) 

diff /tmp/a.sql /tmp/b.sql 

20c20 

< use `test`/*!*/; 

--- 

> use `tt`/*!*/; 

看起来也是可以的。 

但是对于在一个库中执行另外一个库中的操作 binlog_format=statement就不行了 

mysql> show variables like '%binlog_f%'; 

+---------------+-----------+ 

| Variable_name | Value | 

+---------------+-----------+ 

| binlog_format | STATEMENT | 

+---------------+-----------+ 

mysql> flush logs; 

Query OK, 0 rows affected (0.00 sec) 

mysql> create database song; 

Query OK, 1 row affected (0.01 sec) 

mysql> create table song.a(id int); 

Query OK, 0 rows affected (0.02 sec) 

mysql> insert into song.a values (1); 

Query OK, 1 row affected (0.00 sec) 

[root@10-13-38-7 data]# mysqlbinlog --rewrite-db='song->test' 1.000004 > /tmp/song.sql 

[root@10-13-38-7 data]# mysqlbinlog 1.000004 > /tmp/song_no.sql 

[root@10-13-38-7 data]# diff /tmp/song.sql /tmp/song_no.sql 

结果是一样的，所以这时的转换是没有效果的 

当binlog_format=row时，跨库 

mysql> drop database song; 

Query OK, 1 row affected (0.00 sec) 

mysql> flush logs; 

Query OK, 0 rows affected (0.00 sec) 

mysql> show variables like '%binlog_f%'; 

+---------------+-------+ 

| Variable_name | Value | 

+---------------+-------+ 

| binlog_format | ROW | 

+---------------+-------+ 

1 row in set (0.00 sec) 

mysql> create database song; 

Query OK, 1 row affected (0.00 sec) 

mysql> create table song.a(id int); 

Query OK, 0 rows affected (0.02 sec) 

mysql> insert into song.a values (1); 

Query OK, 1 row affected (0.00 sec) 

[root@10-13-38-7 data]# mysqlbinlog --rewrite-db='song->test' 1.000005 > /tmp/song.sql 

[root@10-13-38-7 data]# mysqlbinlog 1.000005 > /tmp/song_no.sql 

[root@10-13-38-7 data]# diff /tmp/song.sql /tmp/song_no.sql 

48c48 

< #160531 22:19:53 server id 1 end_log_pos 652 CRC32 0xb9ccbd3d Table_map: `test`.`a` mapped to number 114 

--- 

> #160531 22:19:53 server id 1 end_log_pos 652 CRC32 0xb9ccbd3d Table_map: `song`.`a` mapped to number 114 

53c53 

< iZ1NVxMBAAAALAAAAIwCAAAAAHIAAAAAAAEABHRlc3QAAWEAAQMAAT29zLk= 

--- 

> iZ1NVxMBAAAALAAAAIwCAAAAAHIAAAAAAAEABHNvbmcAAWEAAQMAAT29zLk= 

“怎么使用mysqlbinlog rewrite-db选项”的内容就介绍到这里了，感谢大家的阅读。

（编辑：银川站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!