MySQL 8.4 & 8.5 主从复制配置
环境说明
WSL 2 作为开发环境，配置两台 Linux 服务器系统，一个系统是 Rocky Linux 9.5，安装 MySQL 8.4.5；另一系统是 Rocky Linux 10，安装 MySQL 8.4.6。
如何安装 MySQL，本篇文章不在赘述。
主库：
Rocky Linux 9.5：MySQL 8.4.5
从库：
Rocky Linux 10：MySQL 8.4.6
下面直接进入 MySQL 主从复制配置环节。
MySQL 主库配置
服务器：Rocky Linux 9.5，MysSQL 8.4.5
第一步：添加配置
编辑 /etc/my.cnf 添加如下配置内容：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 主从配置
server-id=1
log_bin=binlog
第二步：重启 MySQL
$ sudo systemctl restart mysqld.service
第三步：创建用于复制的用户
# 创建从库复制的用户
mysql> create user 'myslave'@'127.0.0.1' identified by '123456';
# 分配权限
mysql> grant replication slave on *.* to 'myslave'@'127.0.0.1';
# 刷新
mysql> flush privileges;
第四步：查看master主库 binary log文件名和position位置
# 注意命令
mysql> show binary log status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000065 | 670 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
到这里，主库配置完毕。以下是扩展。
show master status 在 MySQL 8.4.5 中已经成为了过去式，需要使用 show binary log status;。如果继续使用 show master status 则会报如下错误：
mysql> show master status;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master status' at line 1
MySQL 8.4 有了一套新的命令，请参考官方文档 ：https://dev.mysql.com/doc/refman/8.4/en/mysql-nutshell.html#mysql-nutshell-deprecations
MySQL 从库配置
服务器：Rocky Linux 10，MysSQL 8.4.6
第一步：添加配置
编辑 /etc/my.cnf 添加如下配置内容：
[mysqld]
port=3307
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 主从复制
server-id=2
log_bin=binlog
第二步：重启 MySQL
$ sudo systemctl restart mysqld.service
第三步：连接主库
mysql> CHANGE REPLICATION SOURCE TO
SOURCE_HOST='127.0.0.1',
SOURCE_USER='myslave',
SOURCE_PASSWORD='000000',
SOURCE_LOG_FILE='binlog.000063',
SOURCE_LOG_POS=158,
SOURCE_SSL=0,
GET_SOURCE_PUBLIC_KEY=1;
第四步：启动复制
mysql> start replica;
Query OK, 0 rows affected (0.06 sec)
第五步：查看复制生效
mysql> show replica status \G;
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 127.0.0.1
Source_User: myslave
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binlog.000065
Read_Source_Log_Pos: 158
Relay_Log_File: yangyu-relay-bin.000004
Relay_Log_Pos: 369
Relay_Source_Log_File: binlog.000065
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 158
Relay_Log_Space: 792
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID: f5675aff-2f4a-11f0-8593-00155df5ca74
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 10
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)
这样 MySQL 8.4.x 中主从复制就成啦。
第六步：命令学习
# 停止主从复制
mysql> stop replica;
MySQL 8.4 主从复制的问题
MySQL 8.4 密码默认使用 caching_sha2_password，并移除了 mysql_native_password 插件，因此，很容易遇到一个问题，当从库启动主从复制时，会报如下错误：
# 默认使用 caching_sha2_password 插件
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| localhost | mysql.session | caching_sha2_password |
+-----------+------------------+-----------------------+
# 从库启动复制时，报错
mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Connecting to source
Last_IO_Error: Error connecting to source 'myslave@127.0.0.1:3306'. This was attempt 1/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
解决方案：添加 SOURCE_SSL=0, GET_SOURCE_PUBLIC_KEY=1;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='127.0.0.1',
SOURCE_USER='myslave',
SOURCE_PASSWORD='000000',
SOURCE_LOG_FILE='binlog.000063',
SOURCE_LOG_POS=158,
SOURCE_SSL=0,
GET_SOURCE_PUBLIC_KEY=1;
好了，主从复制完毕。
请登录后再评论