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).
