Mysql 8.4 tries to use mysql_native_password instead of caching_sha2_password ater master firewall reconfiguration

Question

Faced strange mysql 8.4.3 behaviour with replication, using docker on both master and slaves. Replication was initially set with caching_sha2_password and GET_SOURCE_PUBLIC_KEY=1 and everything was fine. I don't know what exactly caused strange behaviour, the only thing I changed was firewall rules. After firewall reconfiguration on master replication stopped with 1524 error saying 'Error connecting to source ... Plugin 'mysql_native_password' is not loaded. I had tried 'SET GLOBAL sql_slave_skip_counter = 1;' with no luck. I had tried to run replica with --mysql-native-password=OFF with no luck either.

I had tried to do RESET REPLICA and set replication params with GET_SOURCE_PUBLIC_KEY=1 without success.

All replicas have dedicated users with caching_sha2_password.

The most interesting part is that two of the four replicas behave like this, while the other two are fine and still use caching_sha2_password.

My question is: why did replicas start using the old password plugin and how force them to use caching_sha2_password when connecting to the master?

Answer 1

I still don't know the root of cause, but...

Workaround which worked for me:

1. Restart master with --mysql-native-password=ON
2. Stop replica; start replica on every slave;
3. make sure replication started. Stop replica on all slaves;
4. restart master without --mysql-native-password=ON
5. start replica on every slave.