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在任何的binlog_format格式下都是有效果的 
当有跨库操作时，只能binlog_format=row才可以

转载请注明源出处 
QQ 273002188 欢迎一起学习 
QQ 群 236941212 
oracle,mysql,PG 相互交流