数据库集群系列(十一):基于Docker容器实现MySQL 5.7版本数据库的Master-Slave主从复制

2024年 7月 17日 100.2k 0

一、基本情况

    在此前已经通过传统方式实现MySQL的主从复制,先通过容器方式实现。

    Master:192.168.1.137

    Slave: 192.168.1.138

    docker容器的安装:略。

二、安装及配置Master

1、创建挂载目录

    mkdir -p usr/local/mysql-master/log
    mkdir -p usr/local/mysql-master/data
    mkdir -p /usr/local/mysql-master/conf

    2、拉取镜像

      docker pull mysql:5.7.34

      3、修改mysql配置文件

              vim /usr/local/mysql-master/conf/my.cnf

        [mysqld]
        ## 设置server_id,同一局域网中需要唯一
        server_id=137
        ## 指定不需要同步的数据库名称
        binlog-ignore-db=mysql
        ## 开启二进制日志功能
        log-bin=mall-mysql-bin
        ## 设置二进制日志使用内存大小(事务)
        binlog_cache_size=1M
        ## 设置使用的二进制日志格式(mixed,statement,row)
        binlog_format=mixed
        ## 二进制日志过期清理时间。默认值为0,表示不自动清理。
        expire_logs_days=7
        ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
        ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
        slave_skip_errors=1062


        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

        4、运行容器

          docker run --privileged=true -p 3307:3306 --name mysql-master \
          -v usr/local/mysql-master/log:/var/log/mysql \
          -v usr/local/mysql-master/data:/var/lib/mysql \
          -v usr/local/mysql-master/conf:/etc/mysql \
          -e MYSQL_ROOT_PASSWORD=123456 \
          -d mysql:5.7.34

          5、添加主从复制账号

            # mysql -uroot -p123456 -P3307 -h192.168.1.137


            mysql> CREATE USER 'sync'@'%' IDENTIFIED BY '123456';
            Query OK, 0 rows affected (0.00 sec)


            mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'sync'@'%';
            Query OK, 0 rows affected (0.00 sec)


            mysql> FLUSH PRIVILEGES;
            Query OK, 0 rows affected (0.01 sec)

            三、安装及配置Slave

            1、创建挂载目录

              # mkdir -p usr/local/mysql-slave/log
              # mkdir -p usr/local/mysql-slave/data
              # mkdir -p usr/local/mysql-slave/conf

              2、拉取镜像

                docker pull mysql:5.7.34

                3、运行容器

                  docker run --privileged=true -p 3307:3306 --name mysql-slave \
                  > -v usr/local/mysql-slave/log:/var/log/mysql \
                  > -v usr/local/mysql-slave/data:/var/lib/mysql \
                  > -v usr/local/mysql-slave/conf:/etc/mysql \
                  > -e MYSQL_ROOT_PASSWORD=123456 \
                  > -d mysql:5.7.34

                  4、修改配置文件并重启docker容器

                      vim /usr/local/mysql-slave/conf/my.cnf

                    [mysqld]
                    ## 设置server_id,同一局域网中需要唯一
                    server_id=138
                    ## 指定不需要同步的数据库名称
                    binlog-ignore-db=mysql
                    ## 开启二进制日志功能,以备Slave作为其它数据库实例的Master时使用
                    log-bin=mall-mysql-slave1-bin
                    ## 设置二进制日志使用内存大小(事务)
                    binlog_cache_size=1M
                    ## 设置使用的二进制日志格式(mixed,statement,row)
                    binlog_format=mixed
                    ## 二进制日志过期清理时间。默认值为0,表示不自动清理。
                    expire_logs_days=7
                    ## 跳过主从复制中遇到的所有错误或指定类型的错误,避免slave端复制中断。
                    ## 如:1062错误是指一些主键重复,1032错误是因为主从数据库数据不一致
                    slave_skip_errors=1062
                    ## relay_log配置中继日志
                    relay_log=mall-mysql-relay-bin
                    ## log_slave_updates表示slave将复制事件写进自己的二进制日志
                    log_slave_updates=1
                    ## slave设置为只读(具有super权限的用户除外)
                    read_only=1




                    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

                    四、配置及开启主从复制

                    1、Master查看主从复制状态:show master status;

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

                      2、Slave上添加Master信息

                        change master to master_host='192.168.1.137',
                        master_user='sync',
                        master_password='123456',
                        master_port=3307,
                        master_log_file='mall-mysql-bin.000001',
                        master_log_pos=607,
                        master_connect_retry=30;


                        Query OK, 0 rows affected, 1 warning (0.01 sec)

                        3、Slave上开启主从复制:start slave;

                          mysql> start slave;
                          Query OK, 0 rows affected (0.00 sec)

                            mysql> show slave status\G;
                            *************************** 1. row ***************************
                            Slave_IO_State: Waiting for master to send event
                            Master_Host: 192.168.1.137
                            Master_User: sync
                            Master_Port: 3307
                            Connect_Retry: 30
                            Master_Log_File: mall-mysql-bin.000001
                            Read_Master_Log_Pos: 607
                            Relay_Log_File: mall-mysql-relay-bin.000002
                            Relay_Log_Pos: 325
                            Relay_Master_Log_File: mall-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: 607
                            Relay_Log_Space: 537
                            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: 137
                            Master_UUID: 1cff5c04-a93e-11ee-b566-0242ac110002
                            Master_Info_File: /var/lib/mysql/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:
                            Master_TLS_Version:
                            1 row in set (0.00 sec)



                            数据库集群系列(十一):基于Docker容器实现MySQL 5.7版本数据库的Master-Slave主从复制-1

                            五、主从复制验证与测试

                                在Master上新建数据库为solar的库,并导入sql;

                                同时观察到Slave上数据可以实时进行同步。

                                同理,在Master上执行删除,验证效果一样。

                            数据库集群系列(十一):基于Docker容器实现MySQL 5.7版本数据库的Master-Slave主从复制-2

                            相关文章

                            Oracle如何使用授予和撤销权限的语法和示例
                            Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
                            下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
                            社区版oceanbase安装
                            Oracle 导出CSV工具-sqluldr2
                            ETL数据集成丨快速将MySQL数据迁移至Doris数据库

                            发布评论