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

发布时间：2021-11-28作者：laosun阅读（1084）

准备环境

# 使用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机器下创建完成后，如下所示：

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 机器下创建完成后，如下所示：

启动MySQL服务

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

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

初始化配置 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状态

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

初始化配置 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)

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

# 执行指令，连接主节点，开启备份
# 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)

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

如果下边的标红加粗部分（Slave_IO_Running、Slave_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则只读