docker-compose部署MySQL主从服务

悠悠 2023-01-20 12:55 86阅读 0赞

**设置前注意下面几点：**  
1）要保证同步服务期间之间的网络联通。即能相互`ping`通，能使用对方授权信息连接到对方数据库（防火墙开放3306端口）。  
2）关闭selinux。  
3）同步前，双方数据库中需要同步的数据要保持一致。这样，同步环境实现后，再次更新的数据就会如期同步了。如果主库是新库，忽略此步。

**1、创建目录**

Master主机
    [root@sgpexchangemysql-192-168-8-61 ~]# cd /data/docker-compose/
    [root@sgpexchangemysql-192-168-8-61 docker-compose]# ls
    
    [root@sgpexchangemysql-192-168-8-61 docker-compose]# mkdir mysql-master
    
    Slave主机
    [root@sgpexchangemysql-192-168-8-62 ~]# cd /data/docker-compose/
    [root@sgpexchangemysql-192-168-8-62 docker-compose]# ls
    
    [root@sgpexchangemysql-192-168-8-62 docker-compose]# mkdir mysql-slave

**2、编写docker-compose.yml**

**Master docker-compose文件**

version: '3.8'
    services:
      mysql-master:
        container_name: mysql-master
        image: mysql:5.7.31
        restart: always
        ports:
          - 3306:3306
        privileged: true
        volumes:
          - $PWD/log:/var/log/mysql
          - $PWD/conf/my.cnf:/etc/mysql/my.cnf
          - $PWD//data:/var/lib/mysql
        environment:
          MYSQL_ROOT_PASSWORD: "123456"
        command: [
            '--character-set-server=utf8mb4',
            '--collation-server=utf8mb4_general_ci',
            '--max_connections=3000'
        ]
        networks:
          - myweb
    
    networks:
    
      myweb:
        driver: bridge

Slave docker-compose文件

version: '3.8'
    services:
      mysql-slave:
        container_name: mysql-slave
        image: mysql:5.7.31
        restart: always
        ports:
          - 3306:3306
        privileged: true
        volumes:
          - $PWD/log:/var/log/mysql
          - $PWD/conf/my.cnf:/etc/mysql/my.cnf
          - $PWD/data:/var/lib/mysql
        environment:
          MYSQL_ROOT_PASSWORD: "123456"
        command: [
            '--character-set-server=utf8mb4',
            '--collation-server=utf8mb4_general_ci',
            '--max_connections=3000'
        ]
        networks:
          - myweb
    
    networks:
    
      myweb:
        driver: bridge

3、**创建配置文件夹**

A、master主机配置文件

[root@sgpexchangemysql-192-168-8-61 mysql-master]# vim conf/my.cnf 
    
    [mysqld]
    # [必须]服务器唯一ID，默认是1，一般取IP最后一段
    server-id=1
    
    # [必须]启用二进制日志
    log-bin=mysql-bin
    
    # 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
    binlog-ignore-db=mysql
    
    # 设置需要同步的数据库 binlog_do_db = 数据库名； 
    # 如果是多个同步库，就以此格式另写几行即可。
    # 如果不指明对某个具体库同步，表示同步所有库。除了binlog-ignore-db设置的忽略的库
    # binlog_do_db = test #需要同步test数据库。
    
    # 确保binlog日志写入后与硬盘同步
    sync_binlog = 1
    
    # 跳过所有的错误，继续执行复制操作
    slave-skip-errors = all

B、Slave主机配置文件

[root@sgpexchangeintermediate-192-168-8-62 mysql-slave]# vim conf/my.cnf
    
    [mysqld]
    # [必须]服务器唯一ID，默认是1，一般取IP最后一段  
    server-id=2
    
    # 如果想实现 主-从（主）-从 这样的链条式结构，需要设置：
    # log-slave-updates      只有加上它，从前一台机器上同步过来的数据才能同步到下一台机器。
    
    # 设置需要同步的数据库，主服务器上不限定数据库，在从服务器上限定replicate-do-db = 数据库名；
    # 如果不指明同步哪些库，就去掉这行，表示所有库的同步（除了ignore忽略的库）。
    # replicate-do-db = test；
    
    # 不同步test数据库 可以写多个例如 binlog-ignore-db = mysql,information_schema 
    replicate-ignore-db=mysql
    
    # 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
     log-bin=mysql-bin
    log-bin-index=mysql-bin.index
    
    # relay_log配置中继日志
    #relay_log=edu-mysql-relay-bin  
    
    # 还可以设置一个log保存周期：
    #expire_logs_days=14
    
    # 跳过所有的错误，继续执行复制操作
    slave-skip-errors = all

**4、启动服务**

A、启动Master服务

[root@sgpexchangemysql-192-168-8-61 mysql-master]# docker-compose up -d
    Creating network "mysql-master_myweb" with driver "bridge"
    Creating mysql-master ... done

B、启动Slave服务

[root@sgpexchangeintermediate-192-168-8-62 mysql-slave]# docker-compose up -d
    Creating network "mysql-slave_myweb" with driver "bridge"
    Pulling mysql-slave (mysql:5.7.31)...
    5.7.31: Pulling from library/mysql
    Digest: sha256:b3dc8d10307ab7b9ca1a7981b1601a67e176408be618fc4216d137be37dae10b
    Status: Downloaded newer image for mysql:5.7.31
    Creating mysql-slave ... done

5、配置主从复制

**进入主mysql服务**

[root@sgpexchangemysql-192-168-8-61 mysql-master]# docker exec -it mysql-master bash
    root@848a9dc5e445:/# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    # 查看server_id是否生效
    mysql> show variables like '%server_id%';
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | server_id      | 1     |
    | server_id_bits | 32    |
    +----------------+-------+
    2 rows in set (0.00 sec)
    # 看master信息 File 和 Position 从服务上要用
    mysql> show master status;
    +------------------+----------+--------------+------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +------------------+----------+--------------+------------------+-------------------+
    | mysql-bin.000004 |      154 |              | mysql            |                   |
    +------------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
    # 开权限
    mysql> grant replication slave,replication client on *.* to 'slave'@'%' identified by "123456";
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> ^DBye

B、进入Slave从服务

[root@sgpexchangeintermediate-192-168-8-62 mysql-slave]# docker exec -it mysql-slave bash
    root@a2f225d71183:/# mysql -uroot -p123456
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.31-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    # 查看server_id是否生效
    mysql> show variables like '%server_id%';
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | server_id      | 2     |
    | server_id_bits | 32    |
    +----------------+-------+
    2 rows in set (0.00 sec)
    # 连接主mysql服务 master_log_file 和 master_log_pos的值要填写主master里查出来的值
    mysql> change master to master_host='192.168.8.61',master_user='slave',master_password='123456',master_port=3306,master_log_file='mysql-bin.000004', master_log_pos=154,master_connect_retry=30;
    Query OK, 0 rows affected, 2 warnings (0.00 sec)
    # 启动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.8.61
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 30
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 610
                   Relay_Log_File: a2f225d71183-relay-bin.000002
                    Relay_Log_Pos: 776
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: mysql
               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: 610
                  Relay_Log_Space: 990
                  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: 97350a1b-b388-11eb-876f-0242ac120002
                 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)
    
    ERROR: 
    No query specified

连接主mysql参数说明：

**master_port**：Master的端口号，指的是容器的端口号
    
    **master_user**：用于数据同步的用户
    
    **master_password**：用于同步的用户的密码
    
    **master_log_file**：指定 Slave 从哪个日志文件开始复制数据，即上文中提到的 File 字段的值
    
    **master_log_pos**：从哪个 Position 开始读，即上文中提到的 Position 字段的值
    
    **master_connect_retry**：如果连接失败，重试的时间间隔，单位是秒，默认是60秒

上面看到，有两个Yes，说明已经成功了

Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes

6、**设置从服务器slave为只读模式**

在从服务器slave上操作

mysql> SHOW VARIABLES LIKE '%read_only%';
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_read_only      | OFF   |
    | read_only             | OFF   |
    | super_read_only       | OFF   |
    | transaction_read_only | OFF   |
    | tx_read_only          | OFF   |
    +-----------------------+-------+
    5 rows in set (0.00 sec)
    
    mysql> SET GLOBAL super_read_only=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SET GLOBAL read_only=1;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> stop slave;
    Query OK, 0 rows affected (0.00 sec)
    
    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.8.61
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 30
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 610
                   Relay_Log_File: a2f225d71183-relay-bin.000003
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: mysql
               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: 610
                  Relay_Log_Space: 1156
                  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: 97350a1b-b388-11eb-876f-0242ac120002
                 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)
    
    ERROR: 
    No query specified
    
    mysql> ^DBye
    root@a2f225d71183:/# exit