MySQL 8.0 : more about new authentication plugin and replication

MySQL 8.0’s new default authentication plugin means more secure connections for users connections but also for replication… but you need to be aware of it !

Yesterday Giuseppe – the datacharmer – Maxia, submitted a bug related to a strange behavior of MySQL’s replication.

He observed that after creating a new user for replication (using the new authentication method, caching_sha2_password), when he created the slave as usual, replication was not starting… Slave_IO_Running was constantly in “Connecting” and the Last_IO_Error was:

Last_IO_Error: error connecting to master 'repl2@127.0.0.1:24011' - retry-time: 60 retries: 51

So, like almost everybody would do, Giuseppe, tried the credentials he created using the mysql client… and it worked ! He could connect to the master, so the credentials where correct ! But the most surprising thing is that replication restarted automatically at the next retry.

He didn’t change anything and all sudendly it worked. I’m sure he tried this scenario several times with always the same result before submitting the bug.

But this is not a bug… In fact, with the new default authentication plugin you need to have the public key of the master for RSA key pair-based password exchange.

With Asynchronous Replication, you need to add to your CHANGE MASTER statement one of the two options below:

•  MASTER_PUBLIC_KEY_PATH, which indicates the path name to a file containing a slave-side copy of the public key required.
•  GET_MASTER_PUBLIC_KEY, which notifies the SLAVE to request the public key to the MASTER when set to 1.

For Group Replication, there are also two options:

• group_replication_recovery_public_key_path
• group_replication_recovery_get_public_key

In Giuseppe’s case, why did the connection from the client helped ?

This is because caching_sha2_password creates an in-memory cache after first successful authentication through secure channel for a given user account. This cache allows fast authentication based on sha256 scramble for subsequent connection.

Warning

But if you setup replication like that without using the appropriate replication options listed above, your solution is temporary, because if the cache gets cleared (FLUSH PRIVILEGES, or restart of the server), the connection will fail again.

Also please note, that this is not the safer way, defining the path and let the DBA distribute the public key is of course more secure, and the the best remains to use SSL.

Conclusion

Indeed, sometimes safer rhymes with a little more difficulty, or at least with some more operations. This means that if you are using, and I recommend you do to so, the new authentication plugin, you will have to manage your keys or at least setup replication to get it from the master.

All this is already in the manual, you can find all this information in the following pages :

• https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html
• https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication
• https://dev.mysql.com/doc/refman/8.0/en/group-replication-user-credentials.html
• https://dev.mysql.com/doc/refman/8.0/en/group-replication-options.html
• https://dev.mysql.com/doc/refman/8.0/en/change-master-to.html