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 相互交流