记一次充满坎坷的mysql8.0.27 主从配置

本文主要是介绍记一次充满坎坷的mysql8.0.27 主从配置，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

由于我是直接克隆的 虚拟机，忘了改 

1. 创建 replica 用户：create user 'replica'@'%' identified by 'KevinWu123!';

这里 create user 'replica'@'%' identified  with 'mysql_native_password' BY 'XXXX';  这样写会报错：

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 'use 'replica'@'%' identified with 'mysql_native_password' BY 'KevinWu123!'' at line 1

2. 授权：grant replication slave, replication client on *.* to 'replica'@'%'  with grant option;

3. 刷新权限：flush privileges;

4. show master status;

5. 配置主从：change master to master_host='192.168.78.102',master_user='replica', master_password='KevinWu123!', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=1944, master_connect_retry=30;

 6. 启动slave：start slave;

 7. 查看 slave 状态： show slave status \G;

（1）报错：Last_IO_Error: error connecting to master 'replica@192.168.78.102:3306' - retry-time: 30 retries: 6 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

原因：https://www.cnblogs.com/zgrey/p/15398633.html

即 在从库连接主库的时候使用的是不被 caching_sha2_password认可的RSA公钥，所以主库MySQL拒绝了数据库连接的请求，从而，从库报错’caching_sha2_password’ reported error:Authentication require secure connection。

然后尝试了博主给出的 第一种方案，执行  mysql -u replica -pKevinWu123! -h 192.168.78.102 -P3306 --get-server-public-key，但此时是用 replica 用户登录的，如果不授权 所有操作，直接执行：change master to master_host='192.168.78.102',master_user='replica', master_password='KevinWu123!', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=1944, master_connect_retry=30;  会报无权限：  ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or REPLICATION_SLAVE_ADMIN privilege(s) for this operation

 但出于安全考虑，又不想给 replica 太大权限，因此放弃

尝试 第三种方案，报 1中的错，放弃

解决办法：手动更改 replica 密码的加密规则，改为 密码认证：https://blog.csdn.net/alicelmx/article/details/82181730  

ALTER USER 'replica'@'%' IDENTIFIED WITH mysql_native_password BY 'KevinWu123!';

重新操作：

 stop slave;

reset slave;

change master to master_host='192.168.78.102',master_user='replica', master_password='KevinWu123!', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=1944, master_connect_retry=30;

报错：Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

原因：我是直接 克隆的虚拟机镜像，且 没有重新生成  auto.cnf 文件，造成 server-uuid 重复

解决办法：https://blog.csdn.net/dsl59741/article/details/107361800

停止mysql 服务：systemctl stop mysqld

删除/auto.cnf  文件： rm -rf /var/lib/mysql/auto.cnf

启动mysql 服务：systemctl start mysqld

重新操作：

 stop slave;

reset slave;

change master to master_host='192.168.78.102',master_user='replica', master_password='KevinWu123!', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=1944, master_connect_retry=30;

解决：

另外：

如果是删除了 mysql.user 表的所有用户，重新添加 root、replica 时，还需要创建 mysql.infoschema 用户：

（1） CREATE USER 'mysql.infoschema'@'localhost' IDENTIFIED BY 'KevinWu123!';

（2） GRANT SELECT ON *.* TO `mysql.infoschema`@`localhost`;

（3）flush privileges;

否则，执行 如：show tables; 命令时，会报错：

ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

这篇关于记一次充满坎坷的mysql8.0.27 主从配置的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！