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 :

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

3 Comments

  1. Fred,
    Thanks for explaining this thorny issue.
    It is possible that the bug I have reported is not a legitimate bug.
    However, given that
    a. the deployment out of the box is broken
    b. the error message does not explain anything
    c. To solve the issue we need to hunt the solution over several manual pages;
    there is at least a documentation issue. Even with all the links provided in this page, still I don’t have a clear cut example that I can use to fix my deployment.
    In short, the behavior that I reported is a bug, because it breaks compatibility with the past. The solution is to document the behavior so well, that users of MySQL 5.7 replication can upgrade to 8.0 without reading the whole manual.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.