转载：https://blog.csdn.net/wawa8899/article/details/86689618 

CentOS7.3使用yum安装了MySQL8.0.14，根据官网教程创建了主从同步账号、设置主从同步后启动同步报错2061，错误日志

[root@ldap1 modules]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 72
Server version: 8.0.14 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.3
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 734
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Connecting
Slave_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_Master_Log_Pos: 734
Relay_Log_Space: 155
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2061
Last_IO_Error: error connecting to master 'replication@192.168.1.3:3306' - retry-time: 60 retries: 12
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 190129 14:09:22
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

mysql>


问题排查：检查了主机和从机的网络、防火墙都没有问题，重新创建了同步账号再设置同步，问题仍然存在。再次查看官方文档，在网页的下方有很明确的提示：



MySQL8.0默认指定使用需要SSL的身份验证插件caching_sha2_password，而我们在创建同步复制账号时候没有指定REQUIRE SSL。为了降低这件事情的复杂性，我们选择了社区的解决方法，选择绕过SSL插件的验证，改为mysql_native_password验证来做同步复制。



解决：
重新创建MySQL复制账号，创建账号时指定身份验证方法

mysql> CREATE USER 'repl'@'192.168.1.3' IDENTIFIED WITH 'mysql_native_password' BY 'replicationPassword2019!';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.3';
mysql> FLUSH PRIVILEGES;
此时再查看该用户的密码加密方式，plugin为mysql_native_password

mysql> select user,host,plugin,authentication_string from user \G
*************************** 1. row ***************************
user: repl
host: 192.168.1.3
plugin: mysql_native_password
authentication_string: *47C25685DDCE2754F1175C38F437961FF6C5AC53


再次设置同步成功

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.3
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1035
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_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_Master_Log_Pos: 1035
Relay_Log_Space: 530
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 84db9645-2372-11e9-8d9a-000c29d69195
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

mysql>


参考：

https://blog.csdn.net/wangxiaotongfan/article/details/81870258

https://forums.mysql.com/read.php?26,663846,663880#msg-663880





文章知识点与官方知识档案匹配，可进一步学习相关知识
MySQL入门技能树首页概览63996 人正在系统学习中


显示推荐内容


cindysz110
关注

4


10
觉得还不错?
一键收藏



1

专栏目录

cindysz110
码龄8年
暂无认证
205
原创
3万+
周排名
3623
总排名
63万+
访问

等级
7160
积分
47
粉丝
102
获赞
49
评论
417
收藏








私信
关注
举报