mysql主从复制只能root，使用 repl 报错：Authentication plugin ‘caching_sha2_password‘ reported error

学习主从复制，之前使用 root 和 repl ，也遇到过 root 可以， repl 不行，后来莫名其妙的可以了。今天重新来过的时候，再次遇到这个问题！

问题

mysql主从复制只能root，使用 repl 报错：

mysql> show replica status\G;
......
          Read_Source_Log_Pos: 157
               Relay_Log_File: ubuntu2004-103-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Source_Log_File: binlog.000001
           Replica_IO_Running: Connecting
          Replica_SQL_Running: Yes
......
                Last_IO_Error: Error connecting to source 'repl@192.168.0.101:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
 ......

如果按照网上建议，改成 mysql_native_password ，那是一种退步！
一定是别的原因造成的！

我的环境

主服务器	
Ubuntu 20.04 ：
192.1568.0.101
$ mysql -V
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

从服务器
Ubuntu 20.04 ： 
192.168.0.103
$ mysql -V
mysql  Ver 8.0.32 for Linux on x86_64 (MySQL Community Server - GPL)

解决过程

1. 在主从服务器上分别确认 repl 权限

    mysql> show grants for repl;
    +----------------------------------------------+
    | Grants for repl@%                            |
    +----------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO `repl`@`%` |
    +----------------------------------------------+
    1 row in set (0.00 sec)
   

   权限没有问题！

2. 在从服务器上，远程登录试试

   $ mysql -urepl -h192.168.0.101 -pPassword@123

   OK!

3. 从服务器上再来
   $ mysql -uroot -pPassword@123

    mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.101',SOURCE_USER='repl123',SOURCE_PASSWORD='Password@123',SOURCE_LOG_FILE='binlog.000001',SOURCE_LOG_POS= 157;
    Query OK, 0 rows affected, 2 warnings (0.26 sec)
    
    mysql> start replica;
    Query OK, 0 rows affected (0.10 sec)
    
    mysql> show replica status\G;
    ......
                  Source_Log_File: binlog.000001
              Read_Source_Log_Pos: 1087
                   Relay_Log_File: ubuntu2004-103-relay-bin.000002
                    Relay_Log_Pos: 323
            Relay_Source_Log_File: binlog.000001
               Replica_IO_Running: Yes
              Replica_SQL_Running: No
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 1396
                       Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000001, end_log_pos 478. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                     Skip_Counter: 0
              Exec_Source_Log_Pos: 157
             
      ......
   

这次报错已经不一样了！
难道远程登录也会改变主服务器上的 binlog ？

4. 上主服务器上重新查看

    mysql> show master status;
    +---------------+----------+--------------+------------------+-------------------+
    | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    +---------------+----------+--------------+------------------+-------------------+
    | binlog.000001 |     1087 |              |                  |                   |
    +---------------+----------+--------------+------------------+-------------------+
    1 row in set (0.00 sec)
   

   确实 Position 改变了！

5. 从服务器上换成新的 Position 再来

    mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.101',SOURCE_USER='repl123',SOURCE_PASSWORD='Password@123',SOURCE_LOG_FILE='binlog.000001',SOURCE_LOG_POS= 1087;
    Query OK, 0 rows affected, 2 warnings (0.21 sec)
    
    mysql> start replica;
    Query OK, 0 rows affected (0.12 sec)
    
    mysql> show replica status\G;
    *************************** 1. row ***************************
                 Replica_IO_State: Waiting for source to send event
                      Source_Host: 192.168.0.101
                      Source_User: repl123
                      Source_Port: 3306
                    Connect_Retry: 60
                  Source_Log_File: binlog.000001
              Read_Source_Log_Pos: 1087
                   Relay_Log_File: ubuntu2004-103-relay-bin.000002
                    Relay_Log_Pos: 323
            Relay_Source_Log_File: binlog.000001
               Replica_IO_Running: Yes
              Replica_SQL_Running: Yes
    ......
   

   这次 OK 了！

后记

1… 这之后，再次远程使用 repl 登录， 也不会改变 master 的 binlog 和 position 了！

2. 难道真的是这么简单？只是远程登录一次即可？