docker-compose MySQL5.7 binlog主从复制方式

发布时间:2021-11-28作者:laosun阅读(488)

docker-compose

    准备环境

    # 使用docker pull mysql:5.7.36 拉取下来的mysql版本是:5.7.36
    
    # 准备2台机器
    192.168.0.121  (安装master)
    192.168.0.122  (安装slave)

    操作目录都是在 /usr/local/docker/mysql57

    先分别在两台机器的这个目录下分别创建2个文件夹。

    mkdir data logs

    192.168.0.121 master 机器

    增加my.cnf ,这里我命名为master.cnf

    master.cnf

    [mysqld]
    # 表示mysql服务器ID,该ID必须在该主从中是唯一的,默认是1,该ID可以自行自定义,但必须为数字。
    server-id=1
    log-bin=mysql-bin
    
    # 注意:如果binlog-do-db和binlog-ignore-db不加的话,那么默认是同步复制整个mysql数据库。
    # 表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    binlog-do-db=d_sun
    # 表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    binlog-ignore-db=mysql
    binlog-ignore-db=sys
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    
    skip-name-resolve
    
    log-slave-updates
    
    back_log=100
    
    expire_logs_days=3
    
    # 表示每个binlog文件最大大小,当此文件大小等于100M时,会自动生成一个新的日志文件。注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    max_binlog_size=100M
    
    character_set_server=utf8mb4
    
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

    增加 docker-compose.yaml

    version: '3.9'
    services:
      mysql-master:
        container_name: mysql57
        image: mysql:5.7.36
        #docker 容器重启后自动重启该服务
        restart: always
        command:
          --character-set-server=utf8mb4
          --collation-server=utf8mb4_general_ci
          --explicit_defaults_for_timestamp=true
          --lower_case_table_names=1
        environment:
          - TZ=Asia/Shanghai
          - MYSQL_ROOT_PASSWORD=root
        ports:
          - 3307:3306
        volumes:
          - ${PWD}/data:/var/lib/mysql
          - ${PWD}/logs:/var/log/mysql
          - ${PWD}/master.cnf:/etc/my.cnf

    master机器下创建完成后,如下所示:

    1.png

    192.168.0.122 slave 机器

    增加my.cnf ,这里我命名为slave.cnf

    slave.cnf

    [mysqld]
    # 表示mysql服务器ID,该ID必须在该主从中是唯一的,默认是1,该ID可以自行自定义,但必须为数字。
    server-id=2
    log-bin=mysql-bin
    # 以下内容应该可以不写,这个没做测试,就加着吧
    # 注意:如果binlog-do-db和binlog-ignore-db不加的话,那么默认是同步复制整个mysql数据库。
    # 表示需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    binlog-do-db=d_sun
    # 表示不需要同步的数据库名字,如果是多个数据库,就以此格式再写一行即可。
    binlog-ignore-db=mysql
    binlog-ignore-db=sys
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    
    skip-name-resolve
    
    log-slave-updates
    
    back_log=100
    
    expire_logs_days=3
    
    # 表示每个binlog文件最大大小,当此文件大小等于100M时,会自动生成一个新的日志文件。注意:一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
    max_binlog_size=100M
    
    character_set_server=utf8mb4
    
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    
    # 设置只读
    read_only=1
    # 禁止超级管理员写操作,不要禁止,否则不能登录了,具体如何设置请看文章的最后
    # super_read_only=1

    增加 docker-compose.yaml

    version: '3.9'
    services:
      mysql-master:
        container_name: mysql57
        image: mysql:5.7.36
        #docker 容器重启后自动重启该服务
        restart: always
        command:
          --character-set-server=utf8mb4
          --collation-server=utf8mb4_general_ci
          --explicit_defaults_for_timestamp=true
          --lower_case_table_names=1
        environment:
          - TZ=Asia/Shanghai
          - MYSQL_ROOT_PASSWORD=root
        ports:
          - 3307:3306
        volumes:
          - ${PWD}/data:/var/lib/mysql
          - ${PWD}/logs:/var/log/mysql
          - ${PWD}/slave.cnf:/etc/my.cnf

    slave 机器下创建完成后,如下所示:

    2.png

    启动MySQL服务

    分别使用以下指令启动两台mysql服务

    # 进入这个文件夹
    cd /usr/local/docker/mysql57
    # 启动mysql服务
    docker-compose up -d

    3.png

    初始化配置 master 服务(创建账户)

    # 登录192.168.0.121这台master服务
    
    # 进入docker 容器
    docker exec -it mysql57 bash
    
    # 登录mysql
    mysql -u root -proot
    
    # 查看server_id是否生效
    show variables like '%server_id%';
    
    # 如果打印以下内容表示生效
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | server_id      | 1     |
    | server_id_bits | 32    |
    +----------------+-------+
    2 rows in set (0.00 sec)

    可以使用 show master status; 查看master状态

    4.png

    # 创建一个专用的账户用户bin log的同步
    # 账号为:slave 密码为:000000
    CREATE USER 'slave'@'%' IDENTIFIED BY '000000'; 
    GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
    flush privileges;

    5.png

    初始化配置 slave 服务(配置master指向)

    # 启动mysql服务
    docker-compose up -d
    
    # 进入docker 容器
    docker exec -it mysql57 bash
    
    # 登录mysql
    mysql -u root -proot
    
    # 查看server_id是否生效
    show variables like '%server_id%';
    
    # 如果打印以下内容表示生效
    +----------------+-------+
    | Variable_name  | Value |
    +----------------+-------+
    | server_id      | 2     |
    | server_id_bits | 32    |
    +----------------+-------+
    2 rows in set (0.00 sec)
    
    
    # 查看MASTER状态
    show slave status\G;
    
    # 输出Empty set, 1 warning (0.01 sec)

    6.png

    从节点使用备份账户连接主节点,开启备份

    # 执行指令,连接主节点,开启备份
    # master_host:Master的地址
    # master_port:Master的端口号
    # master_user:用于数据同步的用户
    # maser_password:用于同步的用户的密码
    # master_log_file:指定 Slave 从哪个日志文件开始复制数据,即上文中提到的 File 字段的值,注意:这里需要登录master机器后,使用 show master status; 获得
    # master_log_pos:从哪个 Position 开始读, 注意:这里需要登录master机器后,使用 show master status; 获得
    # master_connect_retry当重新建立主从连接时,如果连接建立失败,间隔多久后重试。
    
    change master to master_host='192.168.0.121', master_port=3307,master_user='slave',master_password='000000',master_log_file='mysql-bin.000003',master_log_pos=749;
          
    # 启动同步
    start slave;
    
    # 如果出现异常:ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
    # 则可以执行重置,然后再 start slave;
    reset slave;
    # 设置slave读库禁止超级管理员写入
    mysql> show global variables like "%read_only%";
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_read_only      | OFF   |
    | read_only             | ON    |
    | 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;
    mysql> show global variables like "%read_only%";
    +-----------------------+-------+
    | Variable_name         | Value |
    +-----------------------+-------+
    | innodb_read_only      | OFF   |
    | read_only             | ON    |
    | super_read_only       | ON    |
    | transaction_read_only | OFF   |
    | tx_read_only          | OFF   |
    +-----------------------+-------+
    5 rows in set (0.00 sec)

    7.png

    可以使用 show slave status\G; 查看状态。

    如果下边的标红加粗部分(Slave_IO_RunningSlave_SQL_Running)都显示为Yes,表示主从复制一切正常。

    mysql> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.0.121
                      Master_User: slave
                      Master_Port: 3307
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 901
                   Relay_Log_File: 5c94f1ca2b2d-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 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: 901
                  Relay_Log_Space: 534
                  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: 5093c171-3cab-11ec-b8a2-0242c0a89002
                 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

    测试

    # 登录192.168.0.121 master 机器
    # 查看目前所有的数据库
    show databases;
    # 创建一个新的数据库
    CREATE DATABASE `d_sun` CHARACTER SET 'utf8mb4';
    
    # 登录192.168.0.122 slave机器
    # 查看目前所有的数据库,可以看到d_sun这个数据库已经同步过来了。
    show databases;
    
    
    # 剩下的表数据同步这里就不进行测试了。

    记得从机器使用超级管理员账户是可以进行修改的,不要进行改动,以免影响主从复制。

    其他知识:

    对于数据库读写状态,主要靠 “read_only”全局参数来设定;默认情况下,数据库是用于读写操作的,所以read_only参数也是0或faluse状态,这时候不论是本地用户还是远程访问数据库的用户,都可以进行读写操作;如需设置为只读状态,将该read_only参数设置为1或TRUE状态,但设置 read_only=1 状态有两个需要注意的地方:

    • read_only=1只读模式,不会影响slave同步复制的功能,所以在MySQL slave库中设定了read_only=1后,通过 show slave status\G 命令查看salve状态,可以看到salve仍然会读取master上的日志,并且在slave库中应用日志,保证主从数据库同步一致

    • read_only=1只读模式,可以限定普通用户进行数据修改的操作,但不会限定具有super权限的用户的数据修改操作;在MySQL中设置read_only=1后,普通的应用用户进行insert、update、delete等会产生数据变化的DML操作时,都会报出数据库处于只读模式不能发生数据变化的错误,但具有super权限的用户,例如在本地或远程通过root用户登录到数据库,还是可以进行数据变化的DML操作;

    • super_read_only是限定具有root权限的用户的权限,因此这个变量置为on的时候root权限的用户也只能查看而不能修改。(所以需要将两个变量同时置为on或者1),修改此变量也不会影响主从复制

    如果值为off或0则可以读写

    如果置为on或者1则只读


0 +1

版权声明

 数据库  mysql  docker  docker-compose

 请文明留言

0 条评论