Docker搭建Mysql主从

1、本地创建文件用于挂载

主mysql-master

mkdir -p /usr/mysql/master/cnf

mkdir -p /usr/mysql/master/data

从mysql-slaver1

mkdir -p /usr/mysql/slaver1/cnf

mkdir -p /usr/mysql/slaver1/data

从mysql-slaver2

mkdir -p /usr/mysql/slaver2/cnf

mkdir -p /usr/mysql/slaver2/data

2、创建容器

docker run -d \
-p 3306:3306 \
-v /usr/mysql/master/cnf:/etc/mysql/conf.d \
-v /usr/mysql/master/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
--name mysql-master \
mysql:8.0.31

3、编辑主cnf文件

vim /usr/mysql/master/cnf/mysql.cnf

讲下面的内容复制到mysql.cnf
[mysqld]
设置server_id,注意要唯一
server-id=1
开启binlog
log-bin=mysql-bin
binlog缓存
binlog_cache_size=1M
binlog格式(mixed、statement、row,默认格式是statement)
binlog_format=statement
设置字符编码为utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

4、修改校验

docker exec -it mysql-master env LANG=C.UTF-8 /bin/bash (进入指定容器)

mysql -u root -p

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

如果报错：ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded
查看插件
SHOW PLUGINS; // 是否有auth_socket或unix_socket
没有就下载
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
执行
ALTER USER 'root'@'%' IDENTIFIED WITH auth_socket;
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;
最后再执行
ALTER USER 'root'@'%' IDENTIFIED WITH auth_socket BY '123456';

5、主机中创建slave用户

CREATE USER 'slave'@'%';

ALTER USER 'slave'@'%' IDENTIFIED WITH auth_socket BY '123456';(版本高使用)
或者
ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON . TO 'slave'@'%';

FLUSH PRIVILEGES;

6、编辑从cnf文件

[mysqld]
设置server_id,注意要唯一
server-id=2
开启binlog
log-bin=mysql-slave-bin
relay_log配置中继日志
relay_log=edu-mysql-relay-bin
如果需要同步函数或者存储过程
log_bin_trust_function_creators=true
binlog缓存
binlog_cache_size=1M
binlog格式(mixed、statement、row,默认格式是statement)
binlog_format=statement
设置字符编码为utf8mb4
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
slave_skip_errors=1062
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

7、查看主pos

SHOW BINARY LOGS;

SHOW BINARY LOG STATUS;

8、编辑

change master to
master_host='192.168.88.128',
master_user='slave',
master_password='123456',
master_port=3307,
master_log_file='mysql-bin.000003',
master_log_pos=545,
SOURCE_SSL=1;

(高版本使用)
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.88.132',
SOURCE_USER='slave',
SOURCE_PASSWORD='123456',
SOURCE_PORT=3306,
SOURCE_LOG_FILE='mysql-bin.000005',
SOURCE_LOG_POS=158,
SOURCE_SSL=1;

9、启用

SHOW MASTER STATUS;

SHOW SLAVE STATUS\G;

(高版本使用)
START REPLICA; 启动从服务器复制

STOP REPLICA; 停止从服务器复制

SHOW REPLICA STATUS\G; 使用 \G 格式美化输出

加粗文字
docker restart mysql-master