MySql 9 in Docker 使用克隆插件建立主从
环境阐明
- Docker
- Windows 11
- MySql 9.1.0
建立过程
1. 预备主库
- 预备一个主库的配置文件
master.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
binlog-format=ROW
gtid_mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
read_only=OFF
plugin-load=clone=mysql_clone.so
- 履行 Docker run 发动主库容器
docker run --name mysql-master --restart=unless-stopped -p 5300:3306 -e MYSQL_ROOT_PASSWORD=123456 -v C:\Users\Administrator\docker\MySql\replication\master\conf\master.cnf:/etc/mysql/conf.d/master.cnf:ro -v C:\Users\Administrator\docker\MySql\replication\master\data:/var/lib/mysql -d mysql
- 进入容器并登陆mysql
docker exec -it mysql-master bash
mysql -u root -p
- 创立仿制用户
CREATE USER 'repl'@'%' IDENTIFIED with caching_sha2_password BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
- 退出
EXIT;
2. 预备从库
- 预备一个从库的配置文件
slave.cnf
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-format=ROW
gtid_mode=ON
enforce-gtid-consistency=ON
read_only=ON
plugin-load=clone=mysql_clone.so
- 履行 Docker run
docker run --name mysql-slave --restart=unless-stopped -p 5301:3306 -e MYSQL_ROOT_PASSWORD=123456 -v C:\Users\Administrator\docker\MySql\replication\slave\conf\slave.cnf:/etc/mysql/conf.d/slave.cnf:ro -v C:\Users\Administrator\docker\MySql\replication\slave\data:/var/lib/mysql -d mysql
- 进入容器并登陆mysql
docker exec -it mysql-slave bash
mysql -u root -p
- 答应克隆来历
SET GLOBAL clone_valid_donor_list = '172.17.0.6:3306';
- 履行克隆指令
CLONE INSTANCE FROM 'root'@'172.17.0.6':3306 IDENTIFIED BY '123456';
-
克隆完结，从库会主动重启
-
从头进入后能够检查克隆状况
select * from performance_schema.clone_status;
- 从库即可敞开主从仿制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='172.17.0.6',
SOURCE_PORT = 3306,
SOURCE_USER='repl',
SOURCE_PASSWORD='123456',
GET_SOURCE_PUBLIC_KEY=1,
SOURCE_AUTO_POSITION=1;
start REPLICA;
SHOW REPLICA STATUS\G;
验证
- 主库履行下列SQL，完结后检查从库是否同步
CREATE DATABASE test_db2; -- 创立数据库
USE test_db2; -- 切换数据库
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL
); -- 创立表
INSERT INTO users (name, email) VALUES ('tsj', '[email protected]'); -- 刺进数据
SELECT * FROM users; -- 检查数据
