docker mysql 主从

mysql配置:

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
skip-name-resolve

## 添加master主从复制部分配置
server_id=1
log-bin=mysql-bin
read-only=0
binlog-do-db=要同步的库名

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

master节点：

$CONTAINER_HOME=/share/data/container;
mkdir -p $CONTAINER_HOME/mysql-master/data;
mkdir -p $CONTAINER_HOME/mysql-master/conf;


docker run --name mysql-master \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=China@1949 \
-v $CONTAINER_HOME/mysql-master/conf/my.cnf:/etc/mysql/my.cnf \
-v $CONTAINER_HOME/mysql-master/conf/conf.d:/etc/mysql/conf.d \
-v $CONTAINER_HOME/mysql-master/data:/var/lib/mysql \
-p 3307:3306 \
-p 33061:33060 \
-d \
mysql:8.4.5

slave配置：

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4
 
[mysqld]
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8
collation-server=utf8_general_ci
skip-character-set-client-handshake
skip-name-resolve

## 添加master主从复制部分配置
server_id=2
log-bin=mysql-bin
read-only=0
#binlog-do-db=要同步的库名

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

slave节点：

mkdir -p $CONTAINER_HOME/mysql-slave/data;
mkdir -p $CONTAINER_HOME/mysql-slave/conf;

docker run --name mysql-slave \
-e TZ=Asia/Shanghai \
-e MYSQL_ROOT_PASSWORD=China@1949 \
-v $CONTAINER_HOME/mysql-slave/conf/my.cnf:/etc/mysql/my.cnf \
-v $CONTAINER_HOME/mysql-slave/conf/conf.d:/etc/mysql/conf.d \
-v $CONTAINER_HOME/mysql-slave/data:/var/lib/mysql \
-p 3308:3306 \
-p 33062:33060 \
-d \
mysql:8.4.5

配置master:

docker exec -it mysql-master /bin/bash
mysql -uroot -pChina@1949

CREATE USER 'sync'@'%' IDENTIFIED BY 'China@1949' PASSWORD EXPIRE NEVER;

ALTER USER 'sync'@'%' PASSWORD EXPIRE NEVER;

GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';

FLUSH PRIVILEGES;

查看同步状态：

SHOW BINARY LOG STATUS;

配置slave同步：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST='192.168.5.103',
  SOURCE_PORT=3307,
  SOURCE_USER='sync',
  SOURCE_PASSWORD='China@1949',
  SOURCE_AUTO_POSITION = 1,
  SOURCE_SSL = 0,
  GET_SOURCE_PUBLIC_KEY = 1;

启动同步：

START REPLICA;

查看同步状态：

#主节点
SHOW REPLICAS;
#从节点
SHOW REPLICA STATUS\G

设置server_id:

SET PERSIST server_id = 2; 
  
  
SHOW VARIABLES LIKE 'gtid_mode';
SET GLOBAL ENFORCE_GTID_CONSISTENCY = ON;
SET GLOBAL GTID_MODE = OFF_PERMISSIVE; 
SET GLOBAL GTID_MODE = ON_PERMISSIVE;
SET GLOBAL GTID_MODE = ON;


STOP REPLICA;
RESET REPLICA ALL;
START REPLICA;


SHOW REPLICA STATUS\G