MySQL5.7 配置多从一主
使用场景
公司有这样一个需求，把之前的一个系统拆分为多个微服务，数据库也随之拆分，当我们用到数据统计的时候，所要用到的数据已已被分布到不同的服务器的不同的库中。这时，为了方便统计，我们需要把不同服务器上的数据同步到一个数据库中，方便统计分析。
数据库：MySQL 5.7.25
操作系统：CentOS 7.6
主库100：IP=172.16.10.56; PORT=3306; USER=root; PASSWORD=root; server-id=100; database=master_goods
主库200：IP=172.16.10.57; PORT=3306; USER=root; PASSWORD=root; server-id=200; database=master_user
主库300：IP=172.16.10.58; PORT=3306; USER=root; PASSWORD=root; server-id=300; database=master_order
主库400：IP=172.16.10.59; PORT=3306; USER=root; PASSWORD=root; server-id=400; database=master_storage
从库10：IP=192.168.10.30; PORT=3306; server-id=10; database=master_goods,master_user,master_order,master_storage
主库100
配置my.cnf 在mysqld下面加入
##########
# log bin
##########
server-id=100
log_bin=mysql-bin
binlog_format=MIXED
sync_binlog=1
expire_logs_days=7
binlog-do-db=master_goods
binlog-ignore-db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db = sys
log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set
查看master状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: master_goods
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
主库200
配置my.cnf 在mysqld下面加入
##########
# log bin
##########
server-id=200
log_bin=mysql-bin
binlog_format=MIXED
sync_binlog=1
expire_logs_days=7
binlog-do-db=master_user
binlog-ignore-db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db = sys
log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set
查看master状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: master_user
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
主库300
配置my.cnf 在mysqld下面加入
##########
# log bin
##########
server-id=300
log_bin=mysql-bin
binlog_format=MIXED
sync_binlog=1
expire_logs_days=7
binlog-do-db=master_order
binlog-ignore-db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db = sys
log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set
查看master状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: master_order
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
主库400
配置my.cnf 在mysqld下面加入
##########
# log bin
##########
server-id=400
log_bin=mysql-bin
binlog_format=MIXED
sync_binlog=1
expire_logs_days=7
binlog-do-db=master_storage
binlog-ignore-db=mysql
binlog_ignore_db=information_schema
binlog_ignore_db=performation_schema
binlog_ignore_db = sys
log_bin是否开启
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set
查看master状态
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 154
Binlog_Do_DB: master_storage
Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys
Executed_Gtid_Set:
1 row in set (0.00 sec)
主库my.cnf配置属性相关描述
server-id => 服务ID，必须唯一
log_bin => 开启及设置二进制日志文件名称
expire_logs_days => 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
binlog-do-db => 要同步的数据库
binlog-ignore-db => 不需要同步的数据库
从库10
配置my.cnf 在mysqld下面加入
###########
# log bin
###########
server-id=10
master_info_repository=table
relay_log_info_repository=table
设置【主库】信息
mysql> stop slave;
Query OK, 0 rows affected
mysql> CHANGE MASTER TO
MASTER_HOST='172.16.10.56',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154
for channel '100';
Query OK, 0 rows affected
mysql> CHANGE MASTER TO
MASTER_HOST='172.16.10.57',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154
for channel '200';
Query OK, 0 rows affected
mysql> CHANGE MASTER TO
MASTER_HOST='172.16.10.58',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154
for channel '300';
Query OK, 0 rows affected
mysql> CHANGE MASTER TO
MASTER_HOST='172.16.10.59',
MASTER_PORT=3306,
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154
for channel '400';
Query OK, 0 rows affected
mysql> start slave;
Query OK, 0 rows affected
配置从库的一些描述
stop slave; //停止同步
start slave; //开始同步
MASTER_HOST => 主库IP
MASTER_PORT => 主库端口
MASTER_USER => 访问主库且有同步复制权限的用户
MASTER_PASSWORD => 登录密码
MASTER_LOG_FILE => 【重要】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_POS => 【重要】从文件中指定位置开始读取，主库show master status返回结果
for channel => 定义通道名称
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.56
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 5741
Relay_Log_File: WIN-2CAUT7VCO7M-relay-bin-100.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5741
Relay_Log_Space: 6294
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: 100
Master_UUID: 71dcc6f7-5c2e-11e9-b67d-00155d556f06
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 100
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.57
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: WIN-2CAUT7VCO7M-relay-bin-200.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 541
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: 200
Master_UUID: 71dcc6f7-5c2e-11e9-b67d-00155d556f06
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 200
Master_TLS_Version:
2 rows in set (0.00 sec)
*************************** 3. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.58
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 5741
Relay_Log_File: WIN-2CAUT7VCO7M-relay-bin-300.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 5741
Relay_Log_Space: 6294
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: 300
Master_UUID: 71dcc6f7-5c2e-11e9-b67d-00155d556f89
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 300
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.10.59
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: WIN-2CAUT7VCO7M-relay-bin-400.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 541
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: 400
Master_UUID: 71dcc6f7-5c2e-11e9-b67d-00155d556374
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:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: 400
Master_TLS_Version:
2 rows in set (0.00 sec)
若需要单独启动或停止某个同步通道，可使用如下命令：
start slave for channel '100'; //启动名称为300的同步通道
stop slave for channel '100'; //停止名称为300的同步通道
