MySql 9 in Docker 利用克隆插件搭建主从

环境说明

1. Docker
2. Windows 11
3. MySql 9.1.0

搭建步骤

1. 准备主库

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 

2. 执行 Docker run 启动主库容器

docker run --name mysql-master --restart=unless-stopped -p 5300:3306 -e MYSQL_ROOT_PASSWORD=123456 -v C:UsersAdministratordockerMySqlreplicationmasterconfmaster.cnf:/etc/mysql/conf.d/master.cnf:ro -v C:UsersAdministratordockerMySqlreplicationmasterdata:/var/lib/mysql -d mysql 

3. 进入容器并登陆mysql

docker exec -it mysql-master bash  mysql -u root -p 

4. 创建复制用户

CREATE USER 'repl'@'%' IDENTIFIED with caching_sha2_password BY '123456';   GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';   FLUSH PRIVILEGES; 

5. 退出

EXIT; 

2. 准备从库

1. 准备一个从库的配置文件 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 

2. 执行 Docker run

docker run --name mysql-slave --restart=unless-stopped -p 5301:3306 -e MYSQL_ROOT_PASSWORD=123456 -v C:UsersAdministratordockerMySqlreplicationslaveconfslave.cnf:/etc/mysql/conf.d/slave.cnf:ro -v C:UsersAdministratordockerMySqlreplicationslavedata:/var/lib/mysql -d mysql 

3. 进入容器并登陆mysql

docker exec -it mysql-slave bash  mysql -u root -p 

4. 允许克隆来源

SET GLOBAL clone_valid_donor_list = '172.17.0.6:3306'; 

5. 执行克隆命令

CLONE INSTANCE FROM 'root'@'172.17.0.6':3306 IDENTIFIED BY '123456'; 

6. 克隆完成，从库会自动重启

7. 重新进入后可以查看克隆状态

select * from performance_schema.clone_status; 

8. 从库即可开启主从复制

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 STATUSG; 

验证

1. 主库执行下列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', 'tsj@example.com');  -- 插入数据   SELECT * FROM users;  -- 查看数据