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)
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。 主库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的同步通道
更多精彩
