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

忘是亡心i 2022-03-22 19:22 250阅读 0赞

### 背景： ###

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>

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

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhd2E4ODk5_size_16_color_FFFFFF_t_70][]

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_blog.csdn.net_wangxiaotongfan_article_details_81870258]

[https://forums.mysql.com/read.php?26,663846,663880\#msg-663880][https_forums.mysql.com_read.php_26_663846_663880_msg-663880]

[Link 1]: https://dev.mysql.com/doc/refman/8.0/en/replication-howto-repuser.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhd2E4ODk5_size_16_color_FFFFFF_t_70]: /images/20220322/459ee87febe44859bba85399352bcb78.png
[https_blog.csdn.net_wangxiaotongfan_article_details_81870258]: https://blog.csdn.net/wangxiaotongfan/article/details/81870258
[https_forums.mysql.com_read.php_26_663846_663880_msg-663880]: https://forums.mysql.com/read.php?26,663846,663880#msg-663880