How to use the mysqlbinlog rewrite-db option
This article introduces the knowledge of "how to use mysqlbinlog rewrite-db options". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
5.7. the rewrite-db option has been added to the new mysqlbinlog. When you use binlog_format=row, you can convert the name of the library:
In binlog_format=row, do not cross libraries
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.01sec)
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
Compare / 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
46c46
< VZhNVxMBAAAALAAAAOgBAAAAAG4AAAAAAAEABHRlc3QAAWMAAQMAAXj2Bxk= --- >VZhNVxMBAAAAKgAAAOgBAAAAAG4AAAAAAAEAAnR0AAFjAAEDAAF49gcZ
In binlog_format=statement, do not cross libraries
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` / *! * /
It seems possible, too.
But binlog_format=statement is not good for performing operations in one library in another.
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.01sec)
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
The result is the same, so the conversion at this time has no effect.
When binlog_format=row, across libraries
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
53c53
< iZ1NVxMBAAAALAAAAIwCAAAAAHIAAAAAAAEABHRlc3QAAWEAAQMAAT29zLk= --- >IZ1NVxMBAAAALAAAAIwCAAAAAHIAAAAAAAEABHNvbmcAAWEAAQMAAT29zLk=
That's all for "how to use mysqlbinlog rewrite-db options". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
