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

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25099483/viewspace-2110022/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25099483/viewspace-2110022/