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

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.

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.