因为mysql 8新的认证插件导致主从复制的IO线程失败

1、错误信息

Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

2、在主库检查复制用户的plugin信息

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | repuser          | caching_sha2_password |
| localhost | debian-sys-maint | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | auth_socket           |
| localhost | wordpress        | caching_sha2_password |
+-----------+------------------+-----------------------+
7 rows in set (0.00 sec)

3、解决问题

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_host='192.168.5.130',master_user='repuser',master_password='repuser123',master_port=3306,master_log_file='mydb-binlog.000038',master_log_pos=157,get_master_public_key=1;
Query OK, 0 rows affected, 10 warnings (0.02 sec)

mysql> start slave;

4、解决其他警告

mysql> reset replica all;
Query OK, 0 rows affected (0.00 sec)

mysql> change replication source to source_host='192.168.5.130',source_port=3306,source_log_file='mydb-binlog.000038',source_log_pos=157,get_source_public_key=1;
Query OK, 0 rows affected (0.02 sec)

mysql> start replica user='repuser' password='repuser123';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+------------------------------------------------------------------------+
| Level | Code | Message                                                                |
+-------+------+------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
+-------+------+------------------------------------------------------------------------+
1 row in set (0.00 sec)

5、在mysql 8中如何启停主从复制

mysql> start replica;
Query OK, 0 rows affected (0.02 sec)

mysql> stop replica;
Query OK, 0 rows affected (0.01 sec)