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: error connecting to master 'email@example.com: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:
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.
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.
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 :
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.
Thank you for your reply Giuseppe, we will check what can be improved in our documentation to cover such scenario.
[…] I blogged about the new default authentication plugin in MySQL 8.0 and I got some comments complaining that this new authentication plugin is breaking half of […]