docker-compose 搭建mysql主从实战

docker-compose 搭建mysql主从实践

安装docker就不赘述了

从安装docker-compose开始,推荐国内镜像

下载路径是【/usr/local/bin/】下载完之后可以看下【/usr/local/bin】这个目录有没有【docker-compose】文件

curl -L https://get.daocloud.io/docker/compose/releases/download/1.29.1/docker-compose-`uname -s`-`uname -m` > /usr/local/bin/docker-compose

给docker compose 目录授权

sudo chmod +x /usr/local/bin/docker-compose

主从配置

主容器ip 192.168.10.1 执行目录：/usr/local/mysql-master1
编写主容器docker-compose.yml脚本如下：

#使用说明 V3.2.0
#docker-compose up
version: '3.1'
services:
  mysql8-master1:
    hostname: mysql8-master1
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_HOST: '%'
      MYSQL_ROOT_PASSWORD: 3.141592653
      MYSQL_USER: master1
      MYSQL_PASSWORD: 3.141592653
    command:
      --default-authentication-plugin=mysql_native_password
      --max_connections=1000
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --default-time-zone='+8:00'
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --expire-logs-days=7
    image: mysql:8.0.31
    privileged: true
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
    restart: always
    container_name: mysql8-master1
    ports:
      - 3306:3306
    volumes:
      - ./data:/var/lib/mysql
      - ./logs:/var/log/mysql
      - ./conf/my.cnf:/etc/mysql/my.cnf

创建主容器 my.cnf，置于/usr/local/mysql-master1目录下

[mysqld]
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
datadir= /var/lib/mysql
secure-file-priv= NULL
 
#gtid:
server_id= 1                   #服务器id
gtid_mode= on                  #开启gtid模式
enforce_gtid_consistency= on   #强制gtid一致性，开启后对于特定create table不被支持
 
#binlog
log_bin= mysql-binlog
log_slave_updates= on
binlog_format= row             #强烈建议，其他格式可能造成数据不一致
 
#relay log
skip_slave_start= 1
 
# Custom config should go here
!includedir /etc/mysql/conf.d/

从容器ip 192.168.10.2 执行目录：/usr/local/mysql-slave1
编写从容器docker-compose.yml脚本

# 使用说明 V3.2.0
# docker-compose up
version: '3.1'
services:
  mysql8-slave1:
    hostname: mysql8-slave1
    environment:
      TZ: Asia/Shanghai
      MYSQL_ROOT_HOST: '%'
      MYSQL_ROOT_PASSWORD: 3.141592653
      MYSQL_USER: slave1
      MYSQL_PASSWORD: 3.141592653
    command:
      --default-authentication-plugin=mysql_native_password
      --max_connections=1000
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --default-time-zone='+8:00'
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
      --expire-logs-days=7
    image: mysql:8.0.31
    privileged: true
    logging:
      driver: "json-file"
      options:
        max-size: "100m"
    restart: always
    container_name: mysql8-slave1
    ports:
      - 3306:3306
    volumes:
      - ./data:/var/lib/mysql
      - ./logs:/var/log/mysql
      - ./conf/my.cnf:/etc/mysql/my.cnf

创建从容器 my.cnf，置于/usr/local/mysql-slave1目录下

[mysqld]
pid-file= /var/run/mysqld/mysqld.pid
socket= /var/run/mysqld/mysqld.sock
datadir= /var/lib/mysql
secure-file-priv= NULL
 
#gtid:
server_id= 2                   #服务器id
gtid_mode= on                  #开启gtid模式
enforce_gtid_consistency= on   #强制gtid一致性，开启后对于特定create table不被支持
 
#binlog
log_bin= mysql-binlog
log_slave_updates= on
binlog_format= row             #强烈建议，其他格式可能造成数据不一致
 
#relay log
skip_slave_start= 1
 
read_only= on                   #设置只读
 
# Custom config should go here
!includedir /etc/mysql/conf.d/

启动主容器

cd /usr/local/mysql-master1
docker-compose up -d

启动从容器

cd /usr/local/mysql-slave1
docker-compose up -d

主容器账户赋权

docker exec -it mysql8-master1 bash;
mysql -uroot -p3.141592653;
GRANT ALL ON *.* TO master1; 
flush privileges;

从容器账户赋权

docker exec -it mysql8-slave1 bash;
mysql -uroot -p3.141592653;
GRANT REPLICATION CLIENT ON *.* TO slave1;
GRANT REPLICATION SLAVE ON *.* TO slave1;
GRANT SUPER ON *.* TO slave1;
GRANT RELOAD ON *.* TO slave1;
GRANT SELECT ON *.* TO slave1;
flush privileges;

进入从节点的mysql，使用slave账户连接主节点，开启备份

docker exec -it mysql8-slave1 bash;
mysql -uslave -p3.141592653;
CHANGE MASTER TO master_host='192.168.10.1', master_port=3306, master_user='master1', master_password='3.141592653', master_auto_position=1;
START SLAVE;

SHOW SLAVE STATUS

两项显示YES，则ok了

遇到的问题

主从同步异常导致同步任务暂停

解决方法一：

场景为：主从同步创建function时，从库log_bin_trust_function_creators为OFF，创建函数失败，Slave_SQL_Running变为NO，同步停止；

这时我采用的是在从库手动设置log_bin_trust_function_creators为ON

set global log_bin_trust_function_creators=TRUE;

然后重新启动SLAVE

Stop slave;
Start slave;

这时发现数据重新同步过来，完美！！！

解决方法二：适用于数据差异不大，错误直接跳过，可手动补偿的场景

从节点START SLAVE后，SHOW SLAVE STATUS\G,发现Slave_SQL_Running=NO

在LAST_ERROR中有日志如下：

Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '4f65871a-b326-11ed-8ca3-0242ac130002:1' at master log mysql-binlog.000001, end_log_pos 369. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

这是主从同步异常导致，主从数据恢复一致后需要在slave上跳过报错的事务

Stop slave;
Set @@SESSION.GTID_NEXT='4f65871a-b326-11ed-8ca3-0242ac130002:1';
Begin;
Commit;
Set @@SESSION.GTID_NEXT = AUTOMATIC;
Start slave;

解决方法三：备份主库数据，重新做主从

先进入主库，进行锁表，防止数据写入

flush tables with read lock;

进行主库数据备份

mysqldump -uroot -p3.141592653 --databases echo >/tmp/echo_bak.sql

将备份sql拷贝至从服务器

停止slave状态

stop slave;

执行备份脚本

source /tmp/echo_bak.sql;

开启slave状态

start slave;

进入主库，解锁

unlock tables;

从容器再执行 SHOW SLAVE STATUS\G，发现Slave_SQL_Running=YES,是为正常，主从可正常同步啦！！！