In the first part of this article, I explained how MariaDB Server can authenticate users using caching_sha2_password, and more importantly, how it is possible to migrate an existing MySQL user to MariaDB while keeping the same password.
The solution works, but there is one obvious problem: it is mostly a manual process.
For one user, this is fine.
For tens or hundreds of users, not so much.
This is exactly what I encountered when I decided to migrate my last environments from MySQL 8.4 to MariaDB Server.
The problem became very real
MySQL 8 uses caching_sha2_password as its default authentication method, so naturally I had many accounts using it.
Migrating the data itself was not the difficult part. User accounts were more annoying.
As we saw in Part 1, we cannot simply take the output of SHOW CREATE USER from MySQL and execute it on MariaDB.
For example, MySQL can produce something similar to:
ALTER USER `lefred`@`%`
IDENTIFIED WITH 'caching_sha2_password' AS ...
REQUIRE NONE
PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT;There are several problems here.
First, MySQL and MariaDB don’t use exactly the same syntax to define authentication plugins.
MySQL uses:
IDENTIFIED WITH 'plugin' AS 'authentication_string'while MariaDB uses:
IDENTIFIED VIA plugin USING 'authentication_string'There are also MySQL-specific password management clauses such as:
PASSWORD HISTORY
PASSWORD REUSE INTERVAL
PASSWORD REQUIRE CURRENTthat you can’t simply replay on MariaDB.
And finally, caching_sha2_password needs some special treatment because the authentication value exported by MySQL contains binary data.
This was the ugly part we handled manually in Part 1.
When you only have one account to migrate, you can live with it.
When you have many, it’s time to automate.
pt-show-grants to the rescue
I’ve been using Percona Toolkit for a very long time, and pt-show-grants is one of those small utilities that is extremely useful during migrations.
It dumps users and grants in a form that can later be replayed.
Interestingly, pt-show-grants already contains logic for portability in the other direction: dealing with differences between MariaDB and MySQL.
I looked to see if there was the other way around, but no luck. So I decided to add the reverse operation too.
I submitted a contribution to Percona Toolkit:
PR #1168 – Add a new option to support MariaDB Server syntax
The pull request introduces a new option:
--convert-to-MariaDBWhen pt-show-grants connects to a MySQL server with this option enabled, it converts the generated account statements into syntax MariaDB Server can consume.
At the time I’m writing this article, the pull request is still open, so this is not yet something you should expect to find in an official Percona Toolkit release, but you can get it on my branch: convert-to-mariadb.
But let’s see what it does.
Converting MySQL user syntax
For standard authentication plugins, the conversion is relatively straightforward.
For example, a MySQL account using mysql_native_password:
ALTER USER `wp_lefred`@`127.0.0.1`
IDENTIFIED WITH 'mysql_native_password'
AS '*FBA161068101DB224E8BE9350119DBF34A897DA4'
REQUIRE NONE
PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK
PASSWORD HISTORY DEFAULT
PASSWORD REUSE INTERVAL DEFAULT
PASSWORD REQUIRE CURRENT DEFAULT;is converted to:
ALTER USER `wp_lefred`@`127.0.0.1`
IDENTIFIED VIA mysql_native_password
USING '*FBA161068101DB224E8BE9350119DBF34A897DA4'
REQUIRE NONE
PASSWORD EXPIRE DEFAULT
ACCOUNT UNLOCK;We can immediately see two things happening.
The authentication syntax is converted:
IDENTIFIED WITH ... AS ...becomes:
IDENTIFIED VIA ... USING ...and clauses that only make sense in MySQL are removed.
The contribution currently removes:
PASSWORD HISTORY
PASSWORD REUSE INTERVAL
PASSWORD REQUIRE CURRENTThis already makes migrations significantly easier.
auth_socket becomes unix_socket
There are also authentication plugins that are conceptually equivalent but don’t have the same name.
A good example is auth_socket.
On MySQL:
ALTER USER `root`@`localhost`
IDENTIFIED WITH 'auth_socket';The MariaDB equivalent uses unix_socket, so the tool translates it to:
ALTER USER `root`@`localhost`
IDENTIFIED VIA unix_socket;Again, this is a small detail when migrating one account, but exactly the kind of thing you don’t want to fix manually in a large users dump.
And now the interesting one: caching_sha2_password
Of course, the main reason I started working on this was caching_sha2_password.
If you read Part 1, you already know that this one is more complicated.
The authentication string stored by MySQL isn’t something we can always safely put into a regular quoted SQL string.
MySQL can expose it as hexadecimal:
0x244124...This is particularly useful because some bytes contained in a caching_sha2_password authentication string are not safe to transport as normal SQL text.
Unfortunately, we cannot simply do:
ALTER USER ...
IDENTIFIED VIA caching_sha2_password
USING UNHEX('...');on MariaDB.
So the generated migration needs a different approach.
Automating the workaround from Part 1
This is where the new code essentially automates the process I described manually in the previous article.
For a caching_sha2_password account, pt-show-grants first creates or alters the account without trying to pass the incompatible authentication value through ALTER USER.
Then it generates an update similar to this:
UPDATE mysql.global_priv
SET Priv = JSON_SET(
Priv,
'$.plugin',
'caching_sha2_password',
'$.authentication_string',
UNHEX('2441...')
)
WHERE User = 'lefred'
AND Host = '%';followed by:
FLUSH PRIVILEGES;Yes, this means touching mysql.global_priv.
And yes, as I wrote in the first article, this is normally a table you should avoid modifying manually.
The difference here is that the tedious and error-prone conversion is generated for you.
The code also targets both the username and host, which is obviously important because:
'user'@'localhost'and:
'user'@'%'are different accounts in both databases.
Don’t forget the authentication plugin
There is another important point.
pt-show-grants is converting users. It doesn’t configure the destination MariaDB Server for you.
The destination server still needs to support caching_sha2_password.
Depending on how MariaDB Server was packaged, that authentication plugin may already be available or it may need to be installed.
As we saw in Part 1, on systems where it is provided as a module, you can load it with:
INSTALL SONAME 'auth_mysql_sha2';It is then registered in MariaDB using the authentication plugin name:
caching_sha2_passwordYou can verify it with:
SELECT plugin_name, plugin_status
FROM information_schema.plugins
WHERE plugin_name = 'caching_sha2_password';The migration tool deliberately doesn’t install the plugin for you.
I think this separation is important: changing server configuration should remain an explicit DBA decision.
Using it for a migration
With my branch of Percona Toolkit, the idea is to run something similar to:
pt-show-grants \
--host=mysql84 \
--user=root \
--ask-pass \
--convert-to-MariaDB \
> users.sqlThe resulting file can then be reviewed before being applied on the MariaDB Server:
mariadb < users.sqlAnd I really recommend the review step.
This is user and privilege migration. Don’t blindly pipe output from one production database server into another.
What about unsupported authentication plugins?
The conversion also tries to be conservative.
For authentication plugins where there is no known direct MariaDB equivalent, the syntax can still be rewritten, but pt-show-grants emits a warning.
For example, sha256_password doesn’t have the same straightforward migration path.
In that case, the account may require a manual password reset or another authentication method on the MariaDB side.
I prefer that behavior over trying to silently invent a conversion that might not work.
Why contribute this to Percona Toolkit?
Of course, I could have written a small Perl, Python or shell script for my own migration.
But that didn’t feel like the right solution.
pt-show-grants already exists.
People already use it during migrations.
And MySQL → MariaDB is a perfectly valid migration path.
Adding the conversion there means the solution can potentially be useful to everybody facing the same problem instead of remaining another script somewhere in my $HOME/bin directory.
This is one of the things I enjoy about Open Source: I had a concrete problem, an existing tool was almost able to solve it, so I added the missing piece and sent it upstream.
Right now, the contribution is available as Percona Toolkit PR #1168 and is still under review.
So please don’t interpret this article as announcing a Percona Toolkit feature that’s already released.
If you need it today, you can test the branch from the pull request.
And of course, reviews and feedback are welcome.
Conclusion
In Part 1, we saw that migrating a MySQL account using caching_sha2_password to MariaDB while retaining its existing password is possible, but requires a somewhat uncomfortable workaround.
That workaround is manageable for one account.
It becomes annoying very quickly when you’re migrating an actual server.
My migration from MySQL 8.4 to MariaDB was the motivation to automate this process and contribute --convert-to-MariaDB to pt-show-grants.
The goal is simple: make user migrations between MySQL and MariaDB less manual and less error-prone, especially now that many MySQL installations have a large number of users relying on caching_sha2_password.
Hopefully this contribution will make that part of the migration a little easier for others too.
Enjoy MariaDB Server!