I am moving from our web sites to a new host. Our database cluster (PCS) now runs MySQL 8. We have two database servers (active and failover) with a floating virtual IP that we connect to MySQL to.
We also have replication running from the cluster to another server (using binary logs). I have setup the replication in the same manner as when we were using MySQL 5.x, including using SSL with self-signed CA and certificates.
Once the certificates are made, I use this configuration on the master:
ssl
ssl-ca=/var/lib/mysql/ssl/ca.pem
ssl-cert=/var/lib/mysql/ssl/server-cert.pem
ssl-key=/var/lib/mysql/ssl/server-key.pem
Replication is now running fine on the slave. However, when I try to connect to the mysql cluster on the command line (from the active DB server) I get the following error:
ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.
From reading up on this, I thought it was due to the fact that caching_sha2_password is now the default and the docs say that a secure connection is needed. I tried the following:
mysql --ssl-mode=DISABLED
and that didn't make any difference. I also tried to change the user:
mysql> ALTER USER 'root'@'db-01' IDENTIFIED WITH mysql_native_password BY 'passwordhere'
and that didn't work either. So far, the only way I've been able to login to MySQL is by removing SSL. I found that I can get this to connect like so:
mysql --get-server-public-key
I'm not sure if this is the best solution though.
What is the best way to make my connections work properly in MySQL 8 when using SSL in this manner? There will be a number of accounts from both the DB servers that make up the cluster, on the web servers on the same internal network and from externally (such as replication and remote db management).
답변1
I was wondering if you were able to solve this. I am running into the same or similar issue. Here's my Reddit thread: https://www.reddit.com/r/mysql/comments/10pa60k/group_replication_and_ssl_configuring_issues/
Any suggestion would be great. Thanks. DD
답변2
After struggling with this for an eternity, I finally found the setting that fixed it for me. YMMV
All I had to do was add the following line to my mysql config file with the rest of my group replication settings
group_replication_recovery_get_public_key = on
As far as I can tell that's not documented anywhere. I just stumbled across it as I was looking through mysql variables in hopes of finding ANYTHING helpful.