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

骑猪看日落 2021-11-09 11:20 939阅读 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> 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';

关键点解说

stop slave; //停止同步
    start slave; //开始同步
     //必须和【主库】的信息匹配。
    CHANGE MASTER TO MASTER_HOST='192.168.10.212', //主库IP
    MASTER_PORT=4300, //主库端口 
    MASTER_USER='slave', //访问主库且有同步复制权限的用户
     MASTER_PASSWORD='123456', //登录密码//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
    MASTER_LOG_FILE='mysql-bin.000003',//【关键处】从文件中指定位置开始读取，主库show master status返回结果 
    MASTER_LOG_POS=438 
    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多主一从（多源复制）同步配置

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