MySQL9.1.0实现最基础主从复制的步骤

 [root@chucong mysql_zhucong]# tree /data/ -l 3
 /data/
 ├── master              # master的目录
 │   ├── conf            # 配置文件存放目录
 │   │   └── my.cnf      # 配置文件
 │   └── master-data     # 存放数据的地方
 ├── slave1
 │   ├── conf
 │   │   └── my.cnf
 │   └── slave1-data
 └── slave2
     ├── conf
     │   └── my.cnf
     └── slave2-data
 ​
 # mysql 配置文件
 [root@chucong mysql_zhucong]# cat /data/master/conf/my.cnf 
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 symbolic-links=0
 log_bin=mysql-bin  # 开启bin-log日志，注意只有主配置文件需开启
 server_id=10
 #gtid_mode=on    # 开启gtid
 #enforce-gtid-consistency=on    # 保证gtid的强一致性 ​
 ​
 [root@chucong mysql_zhucong]# cat /data/slave1/conf/my.cnf 
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 symbolic-links=0
 server_id=20
 #super_read_only=on  #只读
 #gtid_mode=on    # 开启gtid
 #enforce-gtid-consistency=on    # 保证gtid的强一致性
 ​
 [root@chucong mysql_zhucong]# cat /data/slave2/conf/my.cnf 
 [mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 symbolic-links=0
 server_id=30
 #super_read_only=on  #只读
 #gtid_mode=on    # 开启gtid
 #enforce-gtid-consistency=on    # 保证gtid的强一致性

[root@chucong mysql_zhucong]# docker pull mysql:9.1.0
[root@chucong mysql_zhucong]# vim docker-compose.yml 
 version: '2.27.1'
 services:
   mysql-master:
     image: mysql:9.1.0
     container_name: mysql-master
     environment:            # 定义系统值
       mysql_root_password: root     # root密码
       mysql_user: repl      # 创建复制用户
       mysql_password: root  # 设置密码
     ports:
       - 3306:3306
     volumes:
       - /data/master/master-data:/var/lib/mysql # 挂载宿主机目录到容器
       - /data/master/conf/my.cnf:/etc/my.cnf
     restart: always
     networks:
       mynet1:
         ipv4_address: 192.168.1.2
 ​
   mysql-slave1:
     image: mysql:9.1.0
     container_name: mysql-slave1
     environment:
       mysql_root_password: root
       mysql_user: repl
       mysql_password: root
     ports:
       - 3307:3306
     volumes:
       - /data/slave1/slave1-data:/var/lib/mysql # 挂载宿主机目录到容器
       - /data/slave1/conf/my.cnf:/etc/my.cnf
     restart: always
     networks:
       mynet1:
         ipv4_address: 192.168.1.3
 ​
   mysql-slave2:
     image: mysql:9.1.0
     container_name: mysql-slave2
     environment:
       mysql_root_password: root
       mysql_user: repl
       mysql_password: root
     ports:
       - 3308:3306
     volumes:
       - /data/slave2/slave2-data:/var/lib/mysql # 挂载宿主机目录到容器
       - /data/slave2/conf/my.cnf:/etc/my.cnf
     restart: always
     networks:
       mynet1:
         ipv4_address: 192.168.1.4
 ​
 networks:
   mynet1:                       # 使用自定义桥接模式
     driver: bridge
     ipam:
       config:
       - subnet: 192.168.1.0/24  # 定义网段
         gateway: 192.168.1.1    # 定义网关

 [root@chucong zhucong]# docker compose up -d  

# 查看容器是否运行
 [root@chucong zhucong]# docker ps 
 container id   image         command                   created          status          ports                                                  names
 e5776a980697   mysql:9.1.0   "docker-entrypoint.s…"   59 minutes ago   up 43 minutes   33060/tcp, 0.0.0.0:3307->3306/tcp, :::3307->3306/tcp   mysql-slave1
 0bb3a700077c   mysql:9.1.0   "docker-entrypoint.s…"   59 minutes ago   up 43 minutes   33060/tcp, 0.0.0.0:3308->3306/tcp, :::3308->3306/tcp   mysql-slave2
 af2f63aa00e8   mysql:9.1.0   "docker-entrypoint.s…"   59 minutes ago   up 4 seconds    0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql-master

[root@chucong mysql_zhucong]# docker exec -it mysql-master bash
bash-5.1# mysql -uroot -proot
 ​
 ​
 # 假如说没有设置变量自动创建可以手动创建，设置了可以省略以下命令
 mysql> create user 'repl'@'%' identified by 'root';
 ​
 # 赋予所有库所有表 repl 用户 replication slave 的权限
 mysql> grant replication slave on *.* to 'repl'@'%';
 mysql> flush privileges;
 ​
 mysql> show binary log status\g         # 与过往版本不一样了，以前为 show master status\g
 *************************** 1. row ***************************
              file: mysql-bin.000034         # 注意此处的
          position: 198                      # 还有此出处，这两行在从库操作的时候有至关重要的作用
      binlog_do_db: 
  binlog_ignore_db: 
 executed_gtid_set: d83de5a7-b723-11ef-a65b-0242c0a80104:1-2
 1 row in set (0.00 sec)

 [root@chucong mysql_zhucong]# docker exec -it mysql-slave1 bash
 bash-5.1# mysql -uroot -proot
 ​
 # 假如说没有设置变量自动创建可以手动创建，设置了可以省略以下命令
 mysql> create user 'repl'@'%' identified by 'root';
 ​
 # 以下这整条命令也是发生了巨大的改变，并且不需要指定端口了
 mysql> change replication source to
 source_host='192.168.1.2',          # master 的 ip
 source_user='repl',                 # 创建的用户
 source_password='root',             # 密码
 source_log_file='mysql-bin.000034', # 为主库使用 show binary log status\g 中的file值
 get_source_public_key=1,            # 此处注意 自动获取主服务器的公钥（适用于caching_sha2_password）
 source_log_pos=198;                 # 为主库使用 show binary log status\g 中的position值
 ​
 # 旧版本的命令如下，可指定端口
 # mysql> change master to 
 #    -> master_host='192.168.1.2',
 #    -> master_port=3306,
 #    -> master_user='repl',
 #    -> master_password='root',
 #    -> master_log_file='mysql-bin.000034',
 #    -> master_log_pos=198;
 ​
 ​
 ​
 mysql> start replica;       # 启动复制  9.1.0 变化的命令 以前版本为 start slave;
 mysql> show replica status; # 启动复制  9.1.0 变化的命令 以前版本为 show slave status;
 ​
 mysql> show replica status\g
 *************************** 1. row ***************************
              replica_io_state: waiting for source to send event
                   source_host: 192.168.1.2
                   source_user: repl
                   source_port: 3306
                 connect_retry: 60
               source_log_file: mysql-bin.000034
           read_source_log_pos: 7061047
                relay_log_file: e5776a980697-relay-bin.000002
                 relay_log_pos: 7061177
         relay_source_log_file: mysql-bin.000034
            replica_io_running: yes      # io 线程yes表示已经连接成功，如果不是会显示no或者connect
           replica_sql_running: yes      

[root@chucong mysql_zhucong]# docker exec -it mysql-slave2 bash
 bash-5.1# mysql -uroot -proot
 ​
 # 假如说没有设置变量自动创建可以手动创建，设置了可以省略以下命令
 mysql> create user 'repl'@'%' identified by 'root';
 ​
 # 以下这整条命令也是发生了巨大的改变，并且不需要指定端口了
 mysql> change replication source to
 source_host='192.168.1.2',          # master 的 ip
 source_user='repl',                 # 创建的用户
 source_password='root',             # 密码
 source_log_file='mysql-bin.000034', # 为主库使用 show binary log status\g 中的file值
 get_source_public_key=1,            # 此处注意 自动获取主服务器的公钥（适用于caching_sha2_password）
 source_log_pos=198;                 # 为主库使用 show binary log status\g 中的position值
 ​
 # 旧版本的命令如下，可指定端口
 # mysql> change master to 
 #    -> master_host='192.168.1.2',
 #    -> master_port=3306,
 #    -> master_user='repl',
 #    -> master_password='root',
 #    -> master_log_file='mysql-bin.000034',
 #    -> master_log_pos=198;
 ​
 mysql> start replica;       # 启动复制  9.1.0 变化的命令 以前版本为 start slave;
 mysql> show replica status; # 启动复制  9.1.0 变化的命令 以前版本为 show slave status;
 ​
 mysql> show replica status\g
 *************************** 1. row ***************************
              replica_io_state: waiting for source to send event
                   source_host: 192.168.1.2
                   source_user: repl
                   source_port: 3306
                 connect_retry: 60
               source_log_file: mysql-bin.000034
           read_source_log_pos: 7061047
                relay_log_file: 0bb3a700077c-relay-bin.000002
                 relay_log_pos: 7061177
         relay_source_log_file: mysql-bin.000034
            replica_io_running: yes
           replica_sql_running: yes

 # master
 [root@chucong ~]# docker cp smart_work_v3.sql mysql-master:/
 successfully copied 6.53mb to mysql-master:/
 [root@chucong ~]# docker exec -it mysql-master bash
 bash-5.1# ls 
 afs  boot  docker-entrypoint-initdb.d  home  lib64  mnt  proc  run   smart_work_v3.sql  sys  usr
 bin  dev   etc                         lib   media  opt  root  sbin  srv                tmp  var
 ​
 bash-5.1# mysql -uroot -proot
 mysql> create database shuyan;  # 创建数据库
 mysql> use shuyan   # 切换到数据库
 database changed
 ​
 mysql> source smart_work_v3.sql # 导入sql脚本
 ​
 mysql> show tables;
 +----------------------------------------+
 | tables_in_shuyan                       |
 +----------------------------------------+
 | aaa                                    |
 | abi_http_log                           |
 | act_app_appdef                         |
 | act_app_databasechangelog              |
 | act_app_databasechangeloglock          |
 | act_app_deployment                     |
 | act_app_deployment_resource            |
 | act_cmmn_casedef                       |
 +----------------------------------------+

mysql> use shuyan;
 reading table information for completion of table and column names
 you can turn off this feature to get a quicker startup with -a
 ​
 database changed
 mysql> show tables;
 +----------------------------------------+
 | tables_in_shuyan                       |
 +----------------------------------------+
 | aaa                                    |
 | abi_http_log                           |
 | act_app_appdef                         |
 | act_app_databasechangelog              |
 | act_app_databasechangeloglock          |
 | act_app_deployment                     |
 | act_app_deployment_resource            |
 | act_cmmn_casedef                       |
 | act_cmmn_databasechangelog             |
 | act_cmmn_databasechangeloglock         |
 | act_cmmn_deployment                    |
 +----------------------------------------+