mysql 错误 2061_[MySQL] MySQL8.0主从同步报错2061问题解决

最新推荐文章于 2023-07-06 10:42:04 发布

蒂埃里亨利

最新推荐文章于 2023-07-06 10:42:04 发布

阅读量606

点赞数

文章标签： mysql 错误 2061

本文链接：https://blog.csdn.net/weixin_35836501/article/details/113126897

版权

背景：

ubuntu使用docker安装了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

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

再次设置同步成功