Mysql环境搭建-主从

创建目录和配置文件

mkdir -p mysql_master_slave/{mysql_3306/config,mysql_3307/config} \
&& touch mysql_master_slave/mysql_3306/config/my.cnf \
&& touch mysql_master_slave/mysql_3307/config/my.cnf \
&& touch mysql_master_slave/docker-compose.yml

编辑配置文件

mysql_3306/config/my.cnf

[mysqld]
# 解决docker启动连接慢的问题
skip-name-resolve
server-id=1
port=3306
default-storage-engine=INNODB
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
max_connections=200
max_connect_errors=10
# 开启二进制日志
log-bin=mysql-bin
log_bin-index=mysql-bin.index
log_bin_trust_function_creators=ON
binlog-format=ROW
# 设置自增起始偏移量
auto_increment_offset=1
# 设置自增步长
auto_increment_increment=1
# 开启慢查询日志
slow_query_log=ON
# 设置慢查询时间阈值
long_query_time=1
# 设置慢查询日志文件位置
slow_query_log_file=/var/lib/mysql/slow_query.log
# 要同步的数据库
# binlog-do-db=db_name_01
# binlog-do-db=db_name_02
# 忽略同步的数据库
# binlog-ignore-db=db_name_03
# binlog-ignore-db=db_name_04

mysql_3307/config/my.cnf

[mysqld]
# 解决docker启动连接慢的问题
skip-name-resolve
server-id=2
port=3307
default-storage-engine=INNODB
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
max_connections=200
max_connect_errors=10
# 开启二进制日志
log-bin=mysql-bin
log_bin-index=mysql-bin.index
log_bin_trust_function_creators=ON
binlog-format=ROW
# 设置自增起始偏移量
auto_increment_offset=1
# 设置自增步长
auto_increment_increment=1
# 开启慢查询日志
slow_query_log=ON
# 设置慢查询时间阈值
long_query_time=1
# 设置慢查询日志文件位置
slow_query_log_file=/var/lib/mysql/slow_query.log
# 使更新的数据写进二进制日志中 
log-slave-updates=1
# 开启中继日志
relay-log=mysql-relay
relay-log-index=mysql-relay.index
# 如果salve库名称与master库名相同，使用本配置
# replicate-do-db=db_name
# 如果master库名[mastdemo]与salve库名[mastdemo01]不同, 使用以下配置[需要做映射]
# replicate-rewrite-db=db_name -> db_name_rewrite
# 如果不是要全部同步[默认全部同步], 则指定需要同步的表
# replicate-wild-do-table=db_name.table_name_01
# replicate-wild-do-table=db_name.table_name_02

mysql_master_slave/docker-compose.yml

version: '3'
services:
  mysql_3306:
    image: mysql:8.0.21
    container_name: mysql_3306
    ports:
      - 3306:3306
    privileged: true
    volumes:
      - ./mysql_3306/config:/etc/mysql/conf.d
      - ./mysql_3306/data:/var/lib/mysql
      - ./mysql_3306/logs:/var/log/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_USER=admin
      - MYSQL_PASSWORD=admin
      - TZ=Asia/Shanghai
  mysql_3307:
    image: mysql:8.0.21
    container_name: mysql_3307
    ports:
      - 3307:3307
    privileged: true
    volumes:
      - ./mysql_3307/config:/etc/mysql/conf.d
      - ./mysql_3307/data:/var/lib/mysql
      - ./mysql_3307/logs:/var/log/mysql
    environment:
      - MYSQL_ROOT_PASSWORD=root
      - MYSQL_USER=admin
      - MYSQL_PASSWORD=admin
      - TZ=Asia/Shanghai
networks:
  default:
    external:
      name: service_network

启动服务

docker-compose up -d

主从配置

主节点操作

-- 创建用来同步的用户, 并授权
CREATE USER 'repl_user' @'%' IDENTIFIED WITH mysql_native_password BY '123456';
GRANT replication slave, replication client, file ON *.* TO 'repl_user' @'%';
-- 刷新权限
FLUSH PRIVILEGES;
-- 查看主节点同步状态
SHOW MASTER STATUS;

从节点操作

-- 设置主节点信息
CHANGE MASTER TO master_host = '192.168.56.11',
master_port = 3306,
master_user = 'repl_user',
master_password = '123456',
master_log_file = 'mysql-bin.000003',
master_log_pos = 870;
-- 开启slave
START SLAVE;
-- 查看主从同步状态
SHOW SLAVE STATUS;

开启半同步

mysql主从架构默认采用[异步]的方式进行数据同步, 为了提高数据一致性, 可以设置为[半同步]

安装插件

-- master安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so';
-- slave安装插件
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
-- 查看插件
show plugins;

启动半同步

-- 查看半同步开启状态
show global variables like 'rpl_semi%';
-- 先启动从库 1-启用, 0-禁止
set global rpl_semi_sync_slave_enabled = 1;
-- 再启动主库
set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_master_timeout = 10000; -- 设置半同步超时时间, 单位为毫秒