MySQL多主一从（多源复制）同步配置

心已赠人 2022-02-01 10:45 226阅读 0赞

> **多主一从，也称为多源复制，数据流向**

### 形式 ###

*  主库1 -> 从库s
 *  主库2 -> 从库s
 *  主库n -> 从库s

### **应用场景** ###

*  数据汇总，可将多个主数据库同步汇总到一个从数据库中，方便数据统计分析。
 *  读写分离，从库只用于查询，提高数据库整体性能。

### **部署环境** ###

注：使用docker部署mysql实例，方便快速搭建演示环境。但本文重点是讲解主从配置，因此简略描述docker环境构建mysql容器实例。（亦或者可以使用一键安装环境的插件，在这里不做详说）若不熟悉，可使用传统方式安装mysql，效果相同。

*  数据库：MySQL 5.7.x （相比5.5，5.6而言，5.7同步性能更好，支持多源复制，可实现多主一从，主从库版本应保证一致）
 *  操作系统：CentOS 7.x
 *  容器：Docker 17.09.0-ce
 *  镜像：mysql:5.7
 *  主库300：IP=192.168.1.218; PORT=3306; server-id=2; database=test3; table=user
 *  主库200：IP=192.168.1.225; PORT=3306; server-id=1; database=test4; table=user
 *  从库：IP=192.168.1.128; PORT=3306; server-id=3; database=test3,test4; table=user

### **配置约束** ###

*  主从库必须保证网络畅通可访问
 *  主库必须开启binlog日志
 *  主从库的server-id必须不同

### **【主库300】操作及配置** ###

*  **配置my.cnf**

[client]
    #password   = your_password
    port        = 3306
    socket      = /tmp/mysql.sock
    
    [mysqld]
    port        = 3306  #默认端口
    socket      = /tmp/mysql.sock
    datadir = /usr/local/mysql/var
    skip-external-locking
    key_buffer_size = 32M
    max_allowed_packet = 1M
    table_open_cache = 128
    sort_buffer_size = 768K
    net_buffer_length = 8K
    read_buffer_size = 768K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    thread_cache_size = 16
    query_cache_size = 16M
    tmp_table_size = 32M
    performance_schema_max_table_instances = 1000
    
    skip-grant-tables
    
    explicit_defaults_for_timestamp = true
    #skip-networking
    max_connections = 500
    max_connect_errors = 100
    open_files_limit = 65535
    
    log-bin=mysql-bin    #开启二进制日志记录
    binlog_format=mixed
    server-id   = 3    #配置唯一id
    expire_logs_days = 10    #日志过期时间（天）
    early-plugin-load = ""
    
    #不给从机同步的库
    binlog-ignore-db=sys
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    
    default_storage_engine = InnoDB
    innodb_file_per_table = 1
    innodb_data_home_dir = /usr/local/mysql/var
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/var
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50

*  **创建授权用户**

mysql重启，连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限；

mysql> CREATE USER '用户名'@'%' IDENTIFIED BY '密码';#创建用户
    mysql> GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';#分配权限
    mysql>flush privileges;   #刷新权限

*  **查看log\_bin是否开启 **

*  **查看master状态，记录二进制文件名(mysql-bin.000006)和位置(916)**

mysql> show master status;
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    | mysql-bin.000006 |      916 |              | sys,mysql,information_schema,performance_schema |                   |
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    1 row in set (0.00 sec)

### **【主库400】配置及操作 ** ###

*  **配置my.cnf**

[client]
    #password   = your_password
    port        = 3306
    socket      = /tmp/mysql.sock
    
    [mysqld]
    port        = 3306
    socket      = /tmp/mysql.sock
    datadir = /usr/local/mysql/var
    skip-external-locking
    key_buffer_size = 32M
    max_allowed_packet = 1M
    table_open_cache = 128
    sort_buffer_size = 768K
    net_buffer_length = 8K
    read_buffer_size = 768K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    thread_cache_size = 16
    query_cache_size = 16M
    tmp_table_size = 32M
    performance_schema_max_table_instances = 1000
    
    explicit_defaults_for_timestamp = true
    #skip-networking
    max_connections = 500
    max_connect_errors = 100
    open_files_limit = 65535
    
    log-bin=mysql-bin    #开启二进制日志
    binlog_format=mixed
    server-id   = 1    #唯一id
    expire_logs_days = 10    #过期日期（天）
    early-plugin-load = ""
    
    #不给从机同步的库
    binlog-ignore-db=sys
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    
    default_storage_engine = InnoDB
    innodb_file_per_table = 1
    innodb_data_home_dir = /usr/local/mysql/var
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/var
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    
    [mysqldump]
    quick
    "/etc/my.cnf" 69L, 1430C

*   **创建授权用户**

mysql重启，连接mysql主数据库，键入命令mysql -u root -p，输入密码后登录数据库。创建用户用于从库同步复制，授予复制、同步访问的权限；

mysql> CREATE USER '用户名'@'%' IDENTIFIED BY '密码';#创建用户
    mysql> GRANT REPLICATION SLAVE ON *.* TO '用户名'@'%';#分配权限
    mysql>flush privileges;   #刷新权限

注：各个主机创建的用户可以相同的。

*  **查看log\_bin是否开启**

*  **查看master状态，记录二进制文件名(mysql-bin.000008)和位置(892)**

mysql> show master status;
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set |
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    | mysql-bin.000008 |      892 |              | sys,mysql,information_schema,performance_schema |                   |
    +------------------+----------+--------------+-------------------------------------------------+-------------------+
    1 row in set (0.00 sec)

### **【从库】配置及操作** ###

*  **配置my.cnf**

[client]
    #password   = your_password
    port        = 3306
    socket      = /tmp/mysql.sock
    
    [mysqld]
    port        = 3306
    socket      = /tmp/mysql.sock
    datadir = /usr/local/mysql/var
    skip-external-locking
    key_buffer_size = 32M
    max_allowed_packet = 1M
    table_open_cache = 128
    sort_buffer_size = 768K
    net_buffer_length = 8K
    read_buffer_size = 768K
    read_rnd_buffer_size = 512K
    myisam_sort_buffer_size = 8M
    thread_cache_size = 16
    query_cache_size = 16M
    tmp_table_size = 32M
    performance_schema_max_table_instances = 1000
    
    explicit_defaults_for_timestamp = true
    #skip-networking
    max_connections = 500
    max_connect_errors = 100
    open_files_limit = 65535
    
    #log-bin=mysql-bin    #关闭二进制日志
    binlog_format=mixed
    server-id   = 2    #配置唯一id
    expire_logs_days = 10    #过期时间（天）
    early-plugin-load = ""
    
    #加上以下参数避免更新不及时，slave 重启后导致的主从复制出错
    read_only = 1
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    relay_log_recovery=1 #从机禁止写操作
    
    default_storage_engine = InnoDB
    innodb_file_per_table = 1
    innodb_data_home_dir = /usr/local/mysql/var
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_log_group_home_dir = /usr/local/mysql/var
    innodb_buffer_pool_size = 128M
    innodb_log_file_size = 32M
    innodb_log_buffer_size = 8M
    innodb_flush_log_at_trx_commit = 1
    innodb_lock_wait_timeout = 50
    
    [mysqldump]
    quick
    "/etc/my.cnf" 69L, 1494C

*   **重启mysql，打开mysql会话，执行同步SQL语句**(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)：

mysql> CHANGE MASTER TO
        ->     MASTER_HOST='192.168.1.218',
        ->     MASTER_USER='用户名',
        ->     MASTER_PASSWORD='密码',
        ->     MASTER_LOG_FILE='mysql-bin.000006',
        ->     MASTER_LOG_POS=916
        ->     for channel '300';
    
    mysql> CHANGE MASTER TO
        ->     MASTER_HOST='192.168.1.225',
        ->     MASTER_USER='用户名',
        ->     MASTER_PASSWORD='密码',
        ->     MASTER_LOG_FILE='mysql-bin.000008',
        ->     MASTER_LOG_POS=892
        ->     for channel '200';

关键点解说

1.  stop slave; //停止同步
2.  start slave; //开始同步
3.   //必须和【主库】的信息匹配。
4.  CHANGE MASTER TO MASTER\_HOST='192.168.10.212', //主库IP
5.  MASTER\_PORT=4300, //主库端口 
6.  MASTER\_USER='slave', //访问主库且有同步复制权限的用户
7.   MASTER\_PASSWORD='123456', //登录密码//【关键处】从主库的该log\_bin文件开始读取同步信息，主库show master status返回结果
8.  MASTER\_LOG\_FILE='mysql-bin.000003',//【关键处】从文件中指定位置开始读取，主库show master status返回结果 
9.  MASTER\_LOG\_POS=438 
10. for channel '300'; //定义通道名称

*  **启动slave同步进程**

mysql>start slave;

*  **查看同步状态**

mysql> show slave status \G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.225
                      Master_User: root2
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000008
              Read_Master_Log_Pos: 892
                   Relay_Log_File: localhost-relay-bin-200.000061
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000008
                 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: 892
                  Relay_Log_Space: 701
                  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: 6c83a613-5cfe-11e9-92a0-000c29804965
                 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. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.1.218
                      Master_User: root3
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000006
              Read_Master_Log_Pos: 916
                   Relay_Log_File: localhost-relay-bin-300.000065
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000006
                 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: 916
                  Relay_Log_Space: 701
                  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: 3
                      Master_UUID: 164794cb-7557-11e9-bb7c-000c29b01621
                 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 rows in set (0.00 sec)
    
    ERROR: 
    No query specified

可以看见设置两个的主从同步通道的所有状态信息。只有【Slave\_IO\_Running】和【Slave\_SQL\_Running】都是Yes，则同步是正常的。 如果是No或者Connecting都不行，一般出错都是【MASTER\_LOG\_FILE】和【MASTER\_LOG\_POS】不一致问题，需要细细核查才行。

配置完成，则【从库】开始自动同步。若需要单独启动或停止某个同步通道，可使用如下命令：start slave for channel '300'; //启动名称为300的同步通道 stop slave for channel '300'; //停止名称为300的同步通道

同步功能可自行验证（亲测有效）

### **参考** ###

[MySQL5.7多主一从（多源复制）同步配置][MySQL5.7]

[MySQL主从复制(Master-Slave)实践][MySQL_Master-Slave]

[MySQL5.7]: https://baijiahao.baidu.com/s?id=1617841562444776324&wfr=spider&for=pc
[MySQL_Master-Slave]: https://www.cnblogs.com/gl-developer/p/6170423.html