Switch MySQL 8 Authentication Mode to native_password

modern computer placed near server racks
Photo by Brett Sayles on Pexels.com

This past weekend, I was fiddling with some “devopsy” things, and I had to figure out how to switch the default MySQL 8 authentication method from auth_socket to native_password.

One of the big changes to MySQL in version 8 is the default authentication method. In older MySQL versions, when you installed the mysql-server package, it would default to password-based authentication, and ask you to set a root user password. You could then use that to log in to the MySQL terminal from the command line:

mysql -uroot -proot_password

Now, however, it defaults to the auth_socket authentication method. This essentially means if you want to login to the MySQL terminal on the command line as the root MySQL user, you have to login via the root system user:

sudo mysql

This is a perfectly acceptable change, as it reduces the possibility of someone hacking the root user password and somehow accessing the MySQL server using an external MySQL client. If you need to create separate users for individual databases on your server, you can still configure them to use password-based authentication. On the other hand, if you’re just setting up MySQL on a local web development environment, like me, and you just need the root user to authenticate with a password, it’s a major PITA.

Fortunately, there is a way around this, you can set the default authentication method to native_password in the mysql.cnf file, and then update the password for the root user.

First, once you’ve installed the MySQL server, stop the service.

sudo service mysql stop

Then, edit the following file:

/etc/mysql/conf.d/mysql.cnf

This is the default location for this file on an Ubuntu system, so your mileage may vary.

Add the following to that file:

[mysqld]
default_authentication_plugin=mysql_native_password

and save it. Then restart the MySQL server.

sudo service mysql start

Once that’s done, you can log in to the MySQL terminal as the root user, using the -u switch. The root user doesn’t yet have an assigned password, so you don’t have to specify one.

mysql -uroot

Then, update the root user to your desired password

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root_password';
FLUSH PRIVILEGES;
exit;

Now, you will be required to use the root user password when logging into the MySQL terminal

mysql -uroot -proot_password

Finally, because the main reason I first went looking for this solution was that I was trying to do this from a bash script, here is a little bash script you can use to automate this process.

#!/bin/sh

service mysql stop
echo "" >> /etc/mysql/conf.d/mysql.cnf
echo "[mysqld]" >> /etc/mysql/conf.d/mysql.cnf
echo "default_authentication_plugin=mysql_native_password" >> /etc/mysql/conf.d/mysql.cnf
service mysql start
mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root_password';"
mysql -e "FLUSH PRIVILEGES;"

Leave a Reply

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