mysql主从同步(复制)搭建

一、主服务器操作1.docker-compose文件version: 3 services: mysql: image: mariadb:10.5.28 container_name: mariadb_1 restart: always ports: - 13306:3306 environment: MARIADB_ROOT_PASSWORD: 123456 # 设置root用户密码 TZ: Asia/Shanghai LANG: en_US.UTF-8 MARIADB_DATABASE: test01 # 初始化的数据库名称 volumes: - ./mysql_conf:/etc/mysql - ./mysql_data:/var/lib/mysql2.配置文件编写vim /data/mariadb/mysql_conf/mariadb.conf.d/50-server.cnf # 启用binlog日志 server-id 1 log-binmysql13.重启服务4.创建一个主从复制用户并授权#用户授权 [rootmysql53 ~]# mysql mysql create user repluser% identified by 123456; mysql grant replication slave on *.* to repluser%; create user dbuser% identified by dbuser; grant replication slave on *.* to dbuser%; 主库查一下,是否使用了密码插件 SELECT user, host, plugin FROM mysql.user WHERE user dbuser;5.查看信息MariaDB [(none)] show master status; --------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | --------------------------------------------------------- | mysql1.000001 | 645 | | | --------------------------------------------------------- 1 row in set (0.000 sec) MariaDB [(none)] show master status; -------------------------------------------------------------------- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | -------------------------------------------------------------------- | mysql-bin.000057 | 342 | | mysql,information_schema | -------------------------------------------------------------------- 1 row in set (0.000 sec)二、从服务器操作1.docker-compose文件2.配置文件编写vim /data/mariadb/mysql_conf/mariadb.conf.d/50-server.cnf # 启用binlog日志 server-id 2 relay-logmysql-relay-bin # 中继日志3.重启服务4.登录mysql操作[rootmysql54 ~]# mysql mysql change master to master_host192.168.88.13, master_port13306, master_userrepluser, master_password123456, master_log_filemysql1.000001, master_log_pos645; GET_MASTER_PUBLIC_KEY 1; master_host192.168.88.13, # 主服务器的ip master_port13306, # 主服务器的端口 master_userrepluser, # 创建的同步用户名 master_password123456, # 创建的同步用户密码 master_log_filemysql1.000001, # 刚刚主查出来的 master_log_pos645; # 刚刚主查出来的 mysqlm1.000003 | 686 | CHANGE MASTER TO MASTER_HOST192.168.10.100, MASTER_PORT23306, MASTER_USERdbuser, MASTER_PASSWORDdbuser, MASTER_LOG_FILEmysqlm1.000003, MASTER_LOG_POS686, GET_MASTER_PUBLIC_KEY1; START SLAVE; SHOW SLAVE STATUS\G4.启动从库上的复制相关线程mysql start slave ;线程名称核心职责Slave IO ThreadIO 线程连接主库 → 读取主库的二进制日志binlog → 将 binlog 内容写入从库的中继日志relay logSlave SQL ThreadSQL 线程读取从库的中继日志 → 解析其中的 SQL 语句 → 在从库上执行这些 SQL实现数据和主库一致5.查看状态信息mysql show slave status \Gmysql show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.88.53 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql53.000001 Read_Master_Log_Pos: 667 Relay_Log_File: mysql54-relay-bin.000002 Relay_Log_Pos: 322 Relay_Master_Log_File: mysql53.000001 Slave_IO_Running: Yes //IO线程 Slave_SQL_Running: Yes //SQL线程三、测试1.在master服务器建库 、建表、添加用户 、授权[rootmysql53 ~]# mysql 连接服务 mysql create database gamedb; 建库 mysql create table gamedb.user(name char(10) , class char(3)); 建表 mysql create user plj% identified by 123456; 创建用户 mysql grant select,insert,update,delete on gamedb.* to plj% ; 授予权限 create database gamedb; create table gamedb.user(name char(10) , class char(3)); create user plj% identified by 123456; grant select,insert,update,delete on gamedb.* to plj% ;2.在slave服务器查看库、表、用户[rootmysql54 ~]# mysql 连接服务 mysql show databases; 查看库 mysql desc gamedb.user; 查看表头 mysql select user from mysql.user where userplj; 查看用户 mysql show grants for plj% ; 查看权限 show databases; desc gamedb.user; select user from mysql.user where userplj; show grants for plj% ;3.连接master服务器存储数据mysql -h192.168.88.53 -uplj -p123456 mysql insert into gamedb.user values (yaya,nsd); mysql select * from gamedb.user; ------------- | name | class | ------------- | yaya | nsd | ------------- 1 row in set (0.01 sec) mysql use gamedb; insert into gamedb.user values (yaya,nsd); select * from gamedb.user;4.连接从服务器查看数据[rootmysql50 ~]# mysql -h192.168.88.54 -uplj -p123456 Mysql select * from gamedb.user; ------------- | name | class | ------------- | yaya | nsd | ------------- Mysql四、问题解决问题已经很明确了不是网络不通而是复制账号认证插件问题 Last_IO_Errno: 2061 Authentication plugin caching_sha2_password reported error: Authentication requires secure connection. 意思是主库上的复制用户 dbuser 使用了 MySQL 8 默认的 caching_sha2_password 认证插件但从库连接时既没有 SSL也没有允许获取主库公钥所以认证失败。 推荐你用下面其中一种方式解决。 方案 1从库开启 GET_MASTER_PUBLIC_KEY推荐先试这个 在从库执行 STOP SLAVE; CHANGE MASTER TO GET_MASTER_PUBLIC_KEY 1; START SLAVE;