Replication issue between our 2 servers

MySQL & MariaDB Percona Server for MySQL 8.0
1

We recently upgraded our server MySQL version from 8.0.20-11 to version 8.4.5-5 for Linux on x86_64 (Percona Server (GPL), Release 5, Revision 3d3abca6).

We imported the MySQL dump, transferring it to the DR DB servers, and then adjusting the replication settings in E2 server/slave server. Unfortunately, the synchronization issue persists.

Slave server

image
image1270×658 110 KB

Currently the mysql_native_password is disabledbecause if it is turned ON, then it the Mysql will fail to start.

2

What Auth Plugin are you using for your replication user GB_ORC? In 8.4 the password for your replication user needs to be “caching_sha2_password”

3

mysql_native_password is removed from 8.4. You cannot turn it ON. You have to use the caching_sha2_password for replication user.

4

Yes. We are currently using caching_sha2_password. If mysql_native_password is ON, the MySQL service does not come up. SO we are currently using caching_sha2_password

5

If mysql_native_password is turned ON, the MySQL fails to start. So we are using caching_sha2_password

6

This error, Authentication requires secure connection, indicates that your MySQL 8.4 replica is attempting to connect to the master server, but the user account being used is configured to require an encrypted, secure connection (SSL/TLS), and the replication client is not providing one.

This is a common issue because MySQL 8.4 has enhanced security features and stricter default settings. A user might be created with REQUIRE SSL, or the server’s require_secure_transport variable might be enabled.

On your replica try the following:

  1. stop replica;
  2. CHANGE REPLICATION SOURCE TO SOURCE_SSL = 1;
  3. start replica;
7

One more point to check along with what Wayne mentioned

https://dev.mysql.com/doc/refman/8.4/en/caching-sha2-pluggable-authentication.html#:~:text=For%20replicas%2C%20use,the%20same%20purpose.

8

Hi @Wayne_Leutwyler ,

Thanks for your support. We were able to fix the replication. Unfortunately, we face an issue while trying to login to MySQL. The access gets denied multiple times due to unknown reason.

we also face another issue when we connect to mysql. We get connection time out when we restart the mysql service and then try to connect to mysql. Please find the screenshots of the error attached.

image
image1198×694 37.8 KB

9

Hi @Yunus

Thanks for your support. We were able to fix the replication. Unfortunately, we face an issue while trying to login to MySQL. The access gets denied multiple times due to unknown reason.

we also face another issue when we connect to mysql. We get connection time out when we restart the mysql service and then try to connect to mysql. Please find the screenshots of the error attached.

image
image1198×694 37.8 KB

10

This is the current replication status.

image
image1882×1084 105 KB

11

I just tried to copied the public_key.pem file of my servers and copied it the replica server. When I do this, the mysql service fails. When we start the replica service and then access the SQL, it shows this result:

“mysql> show replica status\G;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect…
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)
ERROR:
Can’t connect to the server“

12

What do you see in the error logs of replica host? You should check if the service is started properly first.