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 MySQL server configuration file.
/etc/mysql/conf.d/mysql.cnf
Depending on your setup, it might be located in a different place. This is the default location for this file on an Ubuntu system, so your mileage may vary. On my MacBook air, it’s located here:
/usr/local/etc/my.cnf
It’s also important to note that the file should have a [mysqld] section, you don’t want to edit the MySQL client configuration.
Add the following to that file, so it’s under the [mysqld] section:
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 "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 -uroot -proot_password "FLUSH PRIVILEGES;"
Leave a Reply