怎么使用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选项”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！

亿速云「云数据库 MySQL」免部署即开即用，比自行安装部署数据库高出1倍以上的性能，双节点冗余防止单节点故障，数据自动定期备份随时恢复。点击查看>>