使用replicate-rewrite-db 实现复制映射 + Replicate_Wild_Do_Table实现复制过滤
- 2019 年 10 月 5 日
- 筆記
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 statusG
*************************** 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 statusG
*************************** 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上测试下吧。
