Lately, we've seen customers install the latest innovation release of MySQL (i.e. MySQL-9.0) and then have problems connecting to a Dolt SQL server due to MySQL-9.0 removing mysql_native_password auth support. In this post, we'll explain what's changing in MySQL, announce Dolt's new support for caching_sha2_password authentication, and explain what to do if you run into any compatibility issues.

Background

In July 2024, MySQL released MySQL-9.0 – an "innovation" release, separate from their current Long Term Support (LTS) release, MySQL-8.4. Generally, most developers will want to use MySQL-8.4, since it's the supported, stable version of MySQL, while the MySQL-9.0 innovation release contains more recent changes and backwards incompatible changes, such as removing the mysql_native_password authentication plugin.

The mysql_native_password plugin has been around in MySQL for a very looong time. This auth method is about 20 years old now and was marked as deprecated in MySQL-8.4, signaling Oracle's intent to eventually remove it. The MySQL-9.0 release removes support for it from the server side, and also changes the mysql client to not include support for it by default. Generally, this is a good change, particularly removing support for mysql_native_password from the server-side. However, dropping support from the mysql client in 9.0 has caused a lot of issues for people, who still need to be able to connect to existing MySQL servers that use the mysql_native_password auth method.

Removing mysql_native_password support in the MySQL-9.0 client by default appears to have been an accident, since the release notes mention that the MySQL-9.0 client still includes mysql_native_password support for backwards compatibility:

For backward compatibility, mysql_native_password remains available on the client; the client-side built-in authentication plugin has been converted into a dynamically loadable plugin.

The root issue seems to be that the MySQL build files don't include the flag to enable mysql_native_password support in the mysql client, now that it's packaged as a dynamically loaded plugin. The MySQL support team has acknowledged and accepted a bug report that proposes updating the build configuration to include the flag to enable mysql_native_password support in the mysql client by default. Hopefully we'll see another release of MySQL-9.0 soon that includes this fix.

Workarounds

In the meantime, there are several ways to work around this incompatibility if you do run into it.

Install MySQL-8.4 (LTS)

Many customers install MySQL using Homebrew, and because Homebrew installs the version with the highest version number, if you simply ask Homebrew to install mysql, you get the MySQL-9.0 innovation release, which may not be what you want, and won't currently be able to connect to older MySQL servers if you have accounts configured for mysql_native_password authentication.

By default, brew install mysql is going to give you mysql-9.0 since that's the highest version number. However, in most cases, people are going to want to use the latest stable and supported release, which is MySQL-8.4.

With homebrew, you can install MySQL-8.4 like this:

brew install mysql@8.4

Install MySQL-Client-8.4 (LTS)

Similar to the workaround above, there is a client-only package available in Homebrew called mysql-client that lets you install the mysql client, without having to install the full server. You can install it by running:

brew install mysql-client@8.4

Note that mysql-client is "keg-only", in Homebrew parlance, meaning installing it won't create symlinks in /opt/homebrew/bin/ and you'll need to use the full path to the binary to run it. Here's an example of invoking the mysql client from the Homebrew 8.4 directory:

/opt/homebrew/opt/mysql-client@8.4/bin/mysql --version 
/opt/homebrew/opt/mysql-client@8.4/bin/mysql  Ver 8.4.3 for macos14.4 on arm64 (Homebrew)

This is also a great way to manage multiple versions of the mysql client on a single system, too. In the commands below, I've installed multiple versions of mysql-client and I can easily switch back and forth between them to test different client versions simply by using a different path for the mysql client binary.

brew install mysql-client@8.0
brew install mysql-client@8.4
brew install mysql-client@9.0

ls /opt/homebrew/opt/ | grep mysql-client
lrwxr-xr-x    28 jason  3 Sep 15:13  mysql-client -> ../Cellar/mysql-client/9.0.1
lrwxr-xr-x    33 jason 10 Dec 17:40  mysql-client@8.0 -> ../Cellar/mysql-client@8.0/8.0.40
lrwxr-xr-x    32 jason 10 Dec 17:46  mysql-client@8.4 -> ../Cellar/mysql-client@8.4/8.4.3
lrwxr-xr-x    28 jason  3 Sep 15:13  mysql-client@9.0 -> ../Cellar/mysql-client/9.0.1 

Try out caching_sha2_password Authentication in Dolt

Last, but certainly not least... we're excited to announce Dolt's new support for caching_sha2_password authentication in version 1.44.2!

The default authentication method is still mysql_native_password, meaning when you create user accounts, they are configured for authentication via the mysql_native_password auth plugin, but as of Dolt version 1.44.2, you can opt-in to using caching_sha2_password authentication.

To try out caching_sha2_password authentication in Dolt, you'll need to:

• Run your Dolt SQL server with a certificate, since the caching_sha2_password authentication plugin requires a secure, encrypted connection.
• CREATE or ALTER users to use the caching_sha2_password authentication plugin.

Let's walk through a quick demo of using Dolt's new caching_sha2_password support, starting with creating a new Dolt database:

# Make sure you're running Dolt 1.44.2 or higher
> dolt version
dolt version 1.44.2

# Create and initialize a new Dolt database named "mydb"
> mkdir mydb && cd mydb
> dolt init
Successfully initialized dolt data repository.

# Create a configuration file that configures TLS support
# See also https://www.dolthub.com/blog/2024-12-03-ssl-mode/
> cat << EOF > config.yaml
log_level: debug

user:
  name: root

listener:
  host: "0.0.0.0"
  port: 11229
  tls_cert: chain_cert.pem
  tls_key: chain_key.pem
  require_secure_transport: true
EOF

For more details on how to configure TLS support, check out our recent blog post on Dolt's TLS support. I copied the sample chain_cert.pem and chain_key.pem files from the Dolt repository, but you can use your own certificates if you have them.

Next, from that same terminal, let's create a user that is configured to authenticate via the caching_sha2_password auth plugin:

> dolt sql -q "CREATE USER newUser1@localhost IDENTIFIED WITH caching_sha2_password BY 'myPass';"
Query OK, 0 rows affected (0.01 sec)

Note that we explicitly specified caching_sha2_password, otherwise the default auth plugin (currently mysql_native_password) would have been used. This is also a good time to grant privileges to the new account (e.g. GRANT ALL PRIVILEGES ON *.* to newUser1@localhost;). Since we're only interested in testing out this user's ability to log in, we'll skip that for this demo.

Now let's start up the Dolt SQL server:

> dolt sql-server --config config.yaml
Starting server with Config HP="0.0.0.0:11229"|T="28800000"|R="false"|L="debug"

And then in a new terminal window, use the mysql client to connect to the Dolt SQL server using the new user account we just created, entering the password myPass when prompted:

> mysql -unewUser1 --protocol TCP --port 11229 -p
Enter password: 

And voila! Our new user is able to log into the Dolt SQL server using caching_sha2_password authentication!

Summary

The MySQL-9.0 innovation release gets installed by default when you install MySQL from homebrew, but chances are you probably want to use the more stable and supported Long Term Support (LTS) version of MySQL, which is MySQL-8.4. If you do want to use the MySQL-9.0 innovation release, be aware that the client included in that release does not currently include support for the mysql_native_password auth plugin (even though the release notes state that it does). Hopefully MySQL will update their build files soon so that the client includes mysql_native_password support by default, since that will help many customers as they move from 8.x to 9.x versions of MySQL.

In the meantime, there are many ways to work around the lack of mysql_native_password auth support in the default MySQL-9.0 client from Homebrew. We showed how to get different client versions through Homebrew, and also announced Dolt's support for caching_sha2_password auth if you want to be an early adopter there.

If you want to talk about MySQL, authentication, database development, or version control, come join us on the DoltHub Discord server! We're always happy to help people get started with Dolt and discuss ideas for using Dolt to manage your versioned data.