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

In this issue, Xiaobian will bring you about replicate-rewrite-db and Replicate_Wild_Do_Table how to achieve table-level mapping replication. The article is rich in content and analyzed and described from a professional perspective. After reading this article, I hope you can gain something.

Synchronize the two tables of sakila under instance 3306 to ming database under instance 5725.

initialization data

mysqldump -uroot -poracle -q --single-transaction -n -B sakila --tables actor --master-data=2 > sakila.sql

Note that adding-n does not generate a statement to create a database.

mysql> create database ming;

Query OK, 1 row affected (0.00 sec)

mysql> use ming

Database changed

mysql> source /root/sakila.sql

Modify slave library parameter file and restart slave library

Add:

replicate-rewrite-db = sakila -> ming

CHANGE MASTER TO

MASTER_HOST='192.168.61.2',

MASTER_USER='repl',

MASTER_PASSWORD='oracle',

MASTER_LOG_FILE='mysql-bin.000013',

MASTER_LOG_POS=154;

If you add only the replicate-rewrite-db parameter, you are only implementing replication at the database level, not at the table level. This makes it easy to copy other operations to the slave library, causing sql thread to report errors.

For example, the main library execution

mysql> use sakila

mysql> create table actor_bak like actor;

Query OK, 0 rows affected (0.31 sec)

mysql> insert into actor_bak select * from actor;

Query OK, 200 rows affected (0.17 sec)

Records: 200 Duplicates: 0 Warnings: 0

mysql> update tt.test01 set c2=10 where c1=9;

Query OK, 1 row affected (0.12 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Actions about actor_bak are passed to the slave library. But there is no tt database from the database, so the sql thread process will report an error.

Last_SQL_Error: Error 'Unknown database 'tt'' on query. Default database: 'ming'. Query: 'create table tt.t1 like actor_bak'

So the complete wording should be;

replicate-rewrite-db = sakila -> ming

replicate-wild-do-table=ming.actor

master library execution

mysql> use sakila

mysql> insert into tt.t1 select * from actor_bak;

Query OK, 200 rows affected (0.00 sec)

Records: 200 Duplicates: 0 Warnings: 0

The slave library will no longer report errors.

After adding the above parameters, you can see it in show slave status.

mysql [localhost] {msandbox} ((none)) > show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.61.2

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000013

Read_Master_Log_Pos: 9815

Relay_Log_File: relay-bin.000004

Relay_Log_Pos: 4683

Relay_Master_Log_File: mysql-bin.000013

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table: ming.actor

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 9815

Relay_Log_Space: 4884

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1051295

Master_UUID: 4c312339-ab38-11e9-86a8-000c29050245

Master_Info_File: /root/sandboxes/msb_5_7_25/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB: (sakila,ming)

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Before modifying parameters online, you need to pause the SQL thread process:

mysql [localhost] {msandbox} ((none)) > stop slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} ((none)) > change replication filter replicate_rewrite_db=((sakila,ming));

Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} ((none)) > start slave sql_thread;

Query OK, 0 rows affected (0.00 sec)

Remove Parameters

CHANGEREPLICATIONFILTER REPLICATE_DO_DB=(),REPLICATE_IGNORE_DB=();

Other writing:

CHANGE REPLICATION FILTER

REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');

If the same filtering rule occurs multiple times, only the last rule will take effect:

CHANGE REPLICATION FILTER

REPLICATE_DO_DB = (db1, db2), REPLICATE_DO_DB = (db3, db4);

CHANGE REPLICATION FILTER

REPLICATE_DO_DB = (db3,db4);

CHANGE REPLICATION FILTER

REPLICATE_DO_DB = (d1), REPLICATE_IGNORE_DB = (d2);

The above is how replicate-rewrite-db and Replicate_Wild_Do_Table achieve table-level mapping replication. If you happen to have similar doubts, you may wish to refer to the above analysis for understanding. If you want to know more about it, please pay attention to the industry information channel.

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.