13518219792
本篇文章給大家分享的是有關如何使用replicate-rewrite-db實現復制映射以及Replicate_Wild_Do_Table實現復制過濾，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。
成都創新互聯2013年至今，先為魚峰等服務建站，魚峰等地企業，進行企業商務咨詢服務。為魚峰企業網站制作PC+手機+微官網三網同步一站式服務解決您的所有建站問題。
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上測試下吧。
以上就是如何使用replicate-rewrite-db實現復制映射以及Replicate_Wild_Do_Table實現復制過濾，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注創新互聯行業資訊頻道。
