mysql caching_sha2_password 与 replication(master、slave、gtid、mgr、ssl)

os: centos 7.4
db: mysql 8.0.19

MySQL 8.0.4或更高版本的复制方案中，与主/主服务器的从/副本连接可以使用通过 caching_sha2_password 身份验证的帐户。对于此类连接，同样的要求适用于使用通过 caching_sha2_password 身份验证的帐户的其他客户端，使用安全连接或基于RSA的密码交换。

192.168.56.60 node1 # master
192.168.56.61 node2 # slave

版本

[root@node2 ~]# cat /etc/centos-release
CentOS Linux release 7.4.1708 (Core) 
[root@node2 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

创建复制账户

set sql_log_bin=0;
create user 'replicator'@'192.168.56.%' identified by 'mysqlmysql';
grant replication slave,replication client on *.* to 'replicator'@'192.168.56.%';
flush privileges;
set sql_log_bin=1;
show master status\G

连接到 caching_sha2_password 主/从复制的帐户

mysql> change master to
  master_host='192.168.56.100',
  master_port=3306,
  master_user='replicator',
  master_password='mysqlmysql',
  master_log_file='mysql-bin.000141',
  master_log_pos=154,
  master_connect_retry=10,
  master_retry_count=86400,
  MASTER_SSL = 1,
  GET_MASTER_PUBLIC_KEY = 1,
  MASTER_PUBLIC_KEY_PATH='/var/lib/mysql/public_key.pem';

连接到 caching_sha2_password GTID复制的帐户

mysql> change master to
  master_host='192.168.56.92',
  master_port=3306,
  master_user='replicator',
  master_password='mysqlmysql',
  master_auto_position=1,
  master_connect_retry=86400,
  MASTER_SSL = 1,
  GET_MASTER_PUBLIC_KEY = 1,
  MASTER_PUBLIC_KEY_PATH='/var/lib/mysql/public_key.pem';

连接到 caching_sha2_password 组复制的帐户

# vi /etc/my.cnf

# ssl for group replicaiotn;
loose-group_replication_recovery_use_ssl = ON;
loose-group_replication_recovery_get_public_key = 1;
loose-group_replication_recovery_public_key_path = '/var/lib/mysql/public_key.pem';

SET GLOBAL group_replication_recovery_use_ssl = ON;
SET GLOBAL group_replication_recovery_get_public_key = 1;
SET GLOBAL group_replication_recovery_public_key_path = '/var/lib/mysql/public_key.pem';