mysql replication-rewrite-db_使用replicate-rewrite-db 实现复制映射 + Replicate_Wild_Do_Table实现复制过滤...

node1和node2为两台不同业务的MySQL服务器。

业务方有个需求，需要将node1上的employees库的departments 、dept_manager 这2张表同步到 node2 的 hellodb 库下面。

node1的employee 里面有如下6张表：

employees > show tables;

+---------------------+

| Tables_in_employees |

+---------------------+

| departments|

| dept_manager|

| dept_emp            |

| employees           |

| salaries            |

| titles              |

+---------------------+

node2只要复制它的 departments 、dept_manager 表到hellodb库里面。

实验了下，整理好的详细操作步骤如下：

1、在node1导出数据并传送到node2去：

mysqldump -uroot -pAbcd@1234 -q --single-transaction employees departments dept_manager --master-data=2 > employees.sql   # 注意导出的时候不要加-B

scp employees.sql node2:/root/

2、然后到node2，导入刚才的数据

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)

可以看到2个表导入进来了。

head -35 /root/employees.sql , 记下change master to 的位置 ，例如

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql.000021', MASTER_LOG_POS=234757251;

3、停node2，改配置文件

/etc/int.d/mysql stop

修改node2 配置文件，加上下面3行：

replicate-rewrite-db = employees -> hellodb

replicate-wild-do-table=hellodb.departments

replicate-wild-do-table=hellodb.dept_manager

/etc/int.d/mysql start

4、配置主从关系

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、测试

在node1上测试下吧。