實驗環境：CentOS7.2 + MySQL5.7
node1（192.168.2.171）和node2(192.168.2.172) 為兩臺不同業務的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的mysql，改配置文件
/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上多測試下吧，看看node2上有沒有正常復制過來。
原創文章，作者：lirulei，如若轉載，請注明出處：http://www.www58058.com/74853
