数据库集群系列（十五）使用docker-compose构建Mysql 5.7数据库主从复制、解决UUID重复导致复制失败问题

数据运维 2024-07-23 大白菜程序猿 手机阅读

一、基本情况

    项目服务器，因经常频繁断电，出现过核心数据丢失的情况，现需要重构部署，考虑采用docker-compose容器集群方式实现。

    俩台主机：

    192.168.1.137【主数据库】、my.cnf开启binlog日志

    192.168.1.138【从数据库】、my.cnf未开启binlog日志

二、Master部署

1、创建挂载目录

    mkdir -p mysql/master/{mydir,datadir,conf,source}

2、配置yaml文件

    vim docker-compose.yaml

    version: '3'
    services:
    mysql:
    restart: always
    image: mysql:5.7.34
    container_name: mysql-5.7master
    volumes:
    - mysql/mydir:/mydir
    - mysql/master/datadir:/var/lib/mysql
    - mysql/master/conf/my.cnf:/etc/my.cnf
    environment:
    - "MYSQL_ROOT_PASSWORD=123456"
    - "TZ=Asia/Shanghai"
    ports:
    - 3308:3306

    3、配置my.cnf文件

        vim conf/my.cnf 

      [mysqld]
      character-set-server = utf8
      collation-server = utf8_general_ci
      sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
      lower_case_table_names = 1
      transaction-isolation = READ-COMMITTED
      default-time_zone = '+8:00'
      log_timestamps = SYSTEM




      log-bin=mysql-bin #开启日志
      server-id=137 #唯一编号
      expire_logs_days=15 #日志保存天数




      ###############################
      innodb_buffer_pool_size = 2G
      innodb_log_file_size=128M
      innodb_log_files_in_group=4
      innodb_log_buffer_size=16M
      innodb_write_io_threads = 8
      innodb_read_io_threads = 8
      innodb_max_dirty_pages_pct = 90
      innodb_lock_wait_timeout = 120
      innodb-file-per-table=1
      auto_increment_increment=1
      auto_increment_offset=1
      connect_timeout=10
      group_concat_max_len=1024
      innodb_thread_concurrency=0
      innodb_thread_sleep_delay=10000
      innodb_write_io_threads=12
      interactive_timeout=28800
      lock_wait_timeout=31536000
      long_query_time=10.000000
      low_priority_updates=OFF
      max_allowed_packet=500M
      max_connect_errors=999999999
      max_connections=1600
      max_length_for_sort_data=1024
      max_prepared_stmt_count=16382
      max_user_connections=0
      net_read_timeout=30
      net_retry_count=10
      net_write_timeout=60
      ngram_token_size=2
      open_files_limit=102400
      performance_schema=OFF
      query_alloc_block_size=8192
      query_cache_limit=1048576
      query_cache_size=0
      query_cache_type=OFF
      query_cache_wlock_invalidate=OFF
      query_prealloc_size=8192
      slow_launch_time=2
      table_definition_cache=768
      table_open_cache=512
      table_open_cache_instances=16
      thread_cache_size=512
      tmp_table_size=1073741824
      wait_timeout=2147483
      interactive_timeout=31536000
      explicit_defaults_for_timestamp = true
      log-bin-trust-function-creators = 1
      [client]
      default-character-set=utf8
      [mysql]
      default-character-set=utf8

      4、启动master服务

        docker-compose up -d
        docker-compose down

        5、连接并创建测试账号

            # mysql -uroot -p123456 -P3308 -h127.0.0.1

          -- 创建用户
          CREATE USER 'sync'@'%' IDENTIFIED BY '123456';


          -- 赋予复制数据权限
          grant replication slave on *.* to 'sync'@'%' IDENTIFIED BY '123456';


          -- 刷新授权，使之立即生效
          flush privileges;

          6、查看Master相关信息

              show master status;

            mysql> show master status;
            +------------------+----------+--------------+------------------+-------------------+
            | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
            +------------------+----------+--------------+------------------+-------------------+
            | mysql-bin.000004 | 154 | | | |
            +------------------+----------+--------------+------------------+-------------------+
            1 row in set (0.00 sec)

            三、Slave部署

            1、创建挂载目录

                mkdir -p mysql/slave/{mydir,datadir,conf,source}

            2、配置yaml文件

                vim docker-compose.yaml

              version: '3'
              services:
              mysql:
              restart: always
              image: mysql:5.7.34
              container_name: mysql-5.7slave
              volumes:
              - mysql/slave/datadir:/var/lib/mysql
              - mysql/slave/conf/my.cnf:/etc/my.cnf
              environment:
              - "MYSQL_ROOT_PASSWORD=123456"
              - "TZ=Asia/Shanghai"
              ports:
              - 3308:3306

              3、修改my.cnf配置文件

                [mysqld]
                server-id=138
                character-set-server = utf8
                collation-server = utf8_general_ci
                sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
                lower_case_table_names = 1
                transaction-isolation = READ-COMMITTED
                default-time_zone = '+8:00'
                log_timestamps = SYSTEM








                ###############################
                innodb_buffer_pool_size = 2G
                innodb_log_file_size=128M
                innodb_log_files_in_group=4
                innodb_log_buffer_size=16M
                innodb_write_io_threads = 8
                innodb_read_io_threads = 8
                innodb_max_dirty_pages_pct = 90
                innodb_lock_wait_timeout = 120
                innodb-file-per-table=1
                auto_increment_increment=1
                auto_increment_offset=1
                connect_timeout=10
                group_concat_max_len=1024
                innodb_thread_concurrency=0
                innodb_thread_sleep_delay=10000
                innodb_write_io_threads=12
                interactive_timeout=28800
                lock_wait_timeout=31536000
                long_query_time=10.000000
                low_priority_updates=OFF
                max_allowed_packet=500M
                max_connect_errors=999999999
                max_connections=1600
                max_length_for_sort_data=1024
                max_prepared_stmt_count=16382
                max_user_connections=0
                net_read_timeout=30
                net_retry_count=10
                net_write_timeout=60
                ngram_token_size=2
                open_files_limit=102400
                performance_schema=OFF
                query_alloc_block_size=8192
                query_cache_limit=1048576
                query_cache_size=0
                query_cache_type=OFF
                query_cache_wlock_invalidate=OFF
                query_prealloc_size=8192
                slow_launch_time=2
                table_definition_cache=768
                table_open_cache=512
                table_open_cache_instances=16
                thread_cache_size=512
                tmp_table_size=1073741824
                wait_timeout=2147483
                interactive_timeout=31536000
                explicit_defaults_for_timestamp = true
                log-bin-trust-function-creators = 1
                [client]
                default-character-set=utf8
                [mysql]
                default-character-set=utf8

                4、启动slave服务

                  docker-compose up -d
                  docker-compose down

                  5、连接并配置连接到Master

                      mysql -uroot -P3308 -p123456 -h127.0.0.1

                    stop slave;
                    reset master;
                    change master to
                    master_host='192.168.1.137',
                    master_port=3308,
                    master_user='sync',
                    master_password='123456',
                    master_log_file='mysql-bin.000002',
                    master_log_pos=4162,
                    MASTER_AUTO_POSITION=0;

                        start slave;

                    6、查看开启slave是否成功

                        show slave status;

                    数据库集群系列（十五）使用docker-compose构建Mysql 5.7数据库主从复制、解决UUID重复导致复制失败问题-每日运维网

                    7、报错处理The slave I/O thread stops

                        查看日志信息：docker logs -f mysql-5.7master

                      mysql-5.7slave | 2024-01-03T17:05:52.332922+08:00 8 [ERROR] Slave I/O for channel '':
                      Fatal error:
                      The slave I/O thread stops because master and slave have equal MySQL server UUIDs;
                      these UUIDs must be different for replication to work. Error_code: 1593
                      mysql-5.7slave | 2024-01-03T17:05:52.332951+08:00 8
                      [Note] Slave I/O thread exiting for channel '', read up to log 'mysql-bin.000002', position 154

                      解决：

                          先查看数据文件路径：SHOW VARIABLES LIKE 'datadir';

                         删除 auto.cnf文件

                          重新执行docker-compose up 启动即可

                      三、主从模式验证

                          在master数据库创建一个test库，并导入sql；可以发现slave库也同步导入。

                      证明主从模式配置无问题，一切使用正常。

                      数据库集群系列（十五）使用docker-compose构建Mysql 5.7数据库主从复制、解决UUID重复导致复制失败问题-每日运维网