How to Run MySQL 8.0 with Native Password Authentication

This article will show you the steps to run MySQL 8.0 in your macOS development environment with mysql_native_password rather than caching_sha2_password

**NOTE** This article focuses primarily on running MySQL 8.0 in a Docker container. You can learn how to do this by first visiting my other article, “How to Run MySQL in a Docker Container on macOS with Persistent Local Data”. However, the tips you learn here will help you if you’re running MySQL locally on your Mac.

**SECOND NOTE** Update Dec 14, 2019: I updated the linked article above to make permissions handling much easier. Please go back and review that article before proceeding.

Recently, I wanted to up my database game by moving my projects to the latest version of MySQL 8.0. I ran into a snag however, because in MySQL 8.0 caching_sha2_password is the default authentication plugin rather than mysql_native_password, which is the default method in MySQL 5.7 and prior. The moment I tried to use MySQL Workbench or DBeaver to connect, it would throw an error Unable to load plugin 'caching_sha2_password'.

As it turns out, there are a number of ways to solve this problem.

• You can modify your applications to just use the newer (and better performing) caching_sha2_password method and then use the compatible version of MySQL Workbench found here. However just switching everything up may not be an easy (or convenient) thing to do in your development environment, particularly if some of your projects continue to rely on MySQL 5.7 or earlier.
• Next, you can CREATE or ALTER your database user to use mysql_native_password with the command shown below, however this method only affects a particular user, and if you don’t also ALTER your root user, then in your development environment you’ll almost certainly run into the plugin error above when trying to work with your dev tools and it’ll become irritating.

CREATE USER 'nativeuser'@'localhost'IDENTIFIED WITH mysql_native_password BY 'password';

• Next, if you run MySQL locally you can modify your my.cnf file by adding the line shown below and restarting your MySQL service. This will validate all database users (including root) using mysql_native_password authentication.

[mysqld]
default-authentication-plugin=mysql_native_password

However, in my DEV environment I run MySQL in Docker containers so I can have multiple versions available at my disposal, and I want my MySQL 8.0 container to use native authentication just like my MySQL 5.7 container does.

When running MySQL 8.0 in a Docker container, you do have the option of simply modifying the my.cnf file inside the container. However you would have to do this task every time you recreated the container. Instead, you can include the necessary config parameters in your initial “docker run” command so that every time you start a MySQL 8.0 container, it builds and fires up with mysql_native_password authentication in place.

Here’s the steps you need to accomplish that:

Create a Local MySQL Config File

Create a local folder that can contain a simple config file that we’ll include in our “docker run” command. I will use the /Users/[your_username]/Develop/ folder I created in my other article linked above.

$ mkdir /Users/[your_username]/Develop/docker_configs
$ mkdir /Users/[your_username]/Develop/docker_configs/mysql

Because this new “/docker_configs” folder is inside the “/Users” folder it is already recognized as a shared folder to Docker.

Now we’re going to create a local my.cnf file that contains the settings we want to use to modify the config inside the MySQL container during “docker run” time.

$ nano /Users/[your_username]/Develop/docker_configs/mysql/my.cnf

Add the following two lines to your new config file:

[mysqld]
default-authentication-plugin=mysql_native_password

Control + o to save.

Control + x to exit.

Create a MySQL 8.0 Docker Container Using New Config

If you’re replacing an existing MySQL 8.0 Docker container with this new one, you’ll want to stop and remove it before running the following command.

In the following command remember to substitute in your Mac user name for “[your_username]” and your favorite MySQL root password for “[your_password]”.

**NOTE** The following is a single command that has wrapped to multiple lines due to its length. Make sure to copy the whole thing.

$ docker run --restart always --name mysql8.0 --net dev-network -v /Users/[your_username]/Develop/mysql_data/8.0:/var/lib/mysql -v /Users/[your_username]/Develop/docker_configs/mysql:/etc/mysql/conf.d -p 3306:3306 -d -e MYSQL_ROOT_PASSWORD=your_password mysql:8.0

Note that we’ve added the following binding to the “docker run” statement:

-v /Users/[your_username]/Develop/docker_configs/mysql:/etc/mysql/conf.d

This binds the local “/docker_configs/mysql” folder you created to the “/conf.d” folder inside the container, and the my.cnf settings you’ve added will be applied after the config files inside the container have run, and override the default caching_sha2_password setting with mysql_native_password.

Now the MySQL 8.0 instance will not only operate as your localhost, but will also use the native password authentication scheme you’re used to. And any time you stop and remove your containers, as long as that additional parameter is included in the run, your containers will always start that way.

There’s more!

Interested in MySQL tips? Check out my story here on creating a query in MySQL 5.7 and 8.0 that returns the top records across a range or category.

Check out my other stories to find more helpful guides for setting up a development environment on your Mac!