娃哈哈好喝Mysql 多主一从+过滤复制+复制映射
1、测试环境及测试目标
系统：centos7.6
Mysql版本：5.7.28
| Mysql | 库名 | 表名 |
|---|---|---|
| 172.20.20.231:3306(主1) | db1 | user1 |
| 172.20.20.232:3306(主2) | db2 | user2 |
| 172.20.20.233:3306(从) | db3 | user1，user2 |
本次测试目的是将两台主db1和db2中的两个表user1、user2同步到从库db3中的user1和user2，对应关系：
172.20.20.231:3306.db1.user1->172.20.20.233:3306.db3.user1
172.20.20.232:3306.db2.user2->172.20.20.233:3306.db3.user2
2、主库配置
修改my.cnf
#id要唯一
server-id = 231
log_bin = binlog
binlog_format = ROW
2台主库的配置只有server-id不一样，其他都一样
3、从库配置
修改my.cnf
#id要唯一
server-id = 233
log_bin = binlog
binlog_format = ROW
#映射库，将db1库映射本地db3
replicate-rewrite-db = db1 -> db3
#过滤表，直复制映射库之后的表名，将db1.user1映射到db3.user1，如果有多个表可以写多行
replicate-wild-do-table=db3.user1
#映射库，将db2库映射本地db3
replicate-rewrite-db = db2 -> db3
#过滤表，直复制映射库之后的表名，将db2.user1映射到db3.user1
replicate-wild-do-table=db3.user2
修改完配置文件需要重启服务，接下来配置slave指向master，因为本环境中有2台master，需要设置2次
#还是要停止slave
#首先设置指向master231，最后的for channel不能写一样了，否则就覆盖了
CHANGE MASTER TO MASTER_HOST='172.20.20.231',MASTER_PORT=3306,MASTER_USER='slaveuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000008',MASTER_LOG_POS=544 for channel '231';
#再设置指向master232
CHANGE MASTER TO MASTER_HOST='172.20.20.232',MASTER_PORT=3306,MASTER_USER='slaveuser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000010',MASTER_LOG_POS=234 for channel '232';
#启动slave
start slave;
具体的MASTER_LOG_FILE和MASTER_LOG_POS可以在master上通过show master status\G来查看：
[[email protected]][(none)]>show master status\G;
*************************** 1. row ***************************
File: binlog.000009
Position: 894
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: eca2ddf0-6097-11eb-8bee-0aed26cb74ee:1-23
1 row in set (0.00 sec)
ERROR:
No query specified
[[email protected]][(none)]>
配置好之后产看slave的状态
[[email protected]][db3]>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.20.231
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000009
Read_Master_Log_Pos: 894
Relay_Log_File: relay-231.000003
Relay_Log_Pos: 1061
Relay_Master_Log_File: binlog.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db3.user1,db3.user2
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 894
Relay_Log_Space: 1425
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: 1
Master_UUID: eca2ddf0-6097-11eb-8bee-0aed26cb74ee
Master_Info_File: mysql.slave_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: eca2ddf0-6097-11eb-8bee-0aed26cb74ee:22-23
Executed_Gtid_Set: eca2ddf0-6097-11eb-8bee-0aed26cb74e2:3-10,
eca2ddf0-6097-11eb-8bee-0aed26cb74e3:1-3,
eca2ddf0-6097-11eb-8bee-0aed26cb74ee:1-20:22-23
Auto_Position: 0
Replicate_Rewrite_DB: (db1,db3),(db2,db3)
Channel_Name: 231
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.20.20.232
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000010
Read_Master_Log_Pos: 934
Relay_Log_File: relay-232.000002
Relay_Log_Pos: 1017
Relay_Master_Log_File: binlog.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: db3.user1,db3.user2
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 934
Relay_Log_Space: 1218
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: 2
Master_UUID: eca2ddf0-6097-11eb-8bee-0aed26cb74e2
Master_Info_File: mysql.slave_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: eca2ddf0-6097-11eb-8bee-0aed26cb74e2:9-10
Executed_Gtid_Set: eca2ddf0-6097-11eb-8bee-0aed26cb74e2:3-10,
eca2ddf0-6097-11eb-8bee-0aed26cb74e3:1-3,
eca2ddf0-6097-11eb-8bee-0aed26cb74ee:1-20:22-23
Auto_Position: 0
Replicate_Rewrite_DB: (db1,db3),(db2,db3)
Channel_Name: 232
Master_TLS_Version:
3 rows in set (0.00 sec)
ERROR:
No query specified
[[email protected]][db3]>
将会查看到这样的字样：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Wild_Do_Table: db3.user1,db3.user2
Replicate_Rewrite_DB: (db1,db3),(db2,db3)