Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.