How to use replicate-rewrite-db to implement replication mapping and Replicate_Wild_Do_Table to implement replication filtering
This article is about how to use replicate-rewrite-db to achieve replication mapping and Replicate_Wild_Do_Table to achieve replication filtering, the editor feels very practical, so share with you to learn, I hope you can get something after reading this article, say no more, follow the editor to have a look.
Node1 and node2 are two MySQL servers for different services.
There is a need for the business side to synchronize the departments and dept_manager tables of the employees library on node1 to the hellodb library of node2.
Node1's employee contains the following six tables:
Employees > show tables
+-+
| | Tables_in_employees |
+-+
| | departments |
| | dept_manager |
| | dept_emp |
| | employees |
| | salaries |
| | titles |
+-+
Node2 simply copies its departments and dept_manager tables into the hellodb library.
After the experiment, the detailed operation steps are as follows:
1. Export the data in node1 and transfer it to node2:
Mysqldump-uroot-pAbcd@1234-Q-- single-transaction employees departments dept_manager-- master-data=2 > employees.sql # Note do not add-B when exporting
Scp employees.sql node2:/root/
2. Then go to node2 and import the data just now
Mysql-uroot-pAbcd@1234 hellodb
Source / root/employees.sql
Show tables
+-+
| | Tables_in_hellodb |
+-+
| | departments |
| | dept_manager |
| | students |
| | teachers |
+-+
4 rows in set (0.00 sec)
You can see that two tables are imported.
Head-35 / root/employees.sql, note the location of the change master to, for example
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql.000021', MASTER_LOG_POS=234757251
3. Stop node2 and change the configuration file
/ etc/int.d/mysql stop
Modify the node2 configuration file by adding the following three lines:
Replicate-rewrite-db = employees-> hellodb
Replicate-wild-do-table=hellodb.departments
Replicate-wild-do-table=hellodb.dept_manager
/ etc/int.d/mysql start
4. Configure the master-slave relationship
CHANGE MASTER TO
MASTER_HOST='192.168.2.171'
MASTER_USER='rpl'
MASTER_PASSWORD='Abcd@1234'
MASTER_LOG_FILE='mysql.000021'
MASTER_LOG_POS=234757251
Show slave status\ G
[(none)] > show slave status\ G
* * 1. Row *
Slave_IO_State:
Master_Host: 192.168.2.171
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000021
Read_Master_Log_Pos: 234757251
Relay_Log_File: t72-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql.000021
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: hellodb.departments,hellodb.dept_manager
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 234757251
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: / data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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: (employees,hellodb)
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
Start slave
Show slave status\ G
[(none)] > show slave status\ G
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.171
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000021
Read_Master_Log_Pos: 234757251
Relay_Log_File: t72-relay-bin.000002
Relay_Log_Pos: 316
Relay_Master_Log_File: mysql.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: hellodb.departments,hellodb.dept_manager
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 234757251
Relay_Log_Space: 521
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: 71
Master_UUID: 02d27620-1d8c-11e7-b028-000c295b7c01
Master_Info_File: / data/mysql/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: (employees,hellodb)
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
5. Test
Test it on node1.
The above is how to use replicate-rewrite-db to achieve replication mapping and Replicate_Wild_Do_Table to achieve replication filtering. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.
