December 18th, 2018

MySQL Remote Connection Issues on Digital Ocean

I recently spun up a new LEMP One-Click server from Digital Ocean and went to connect remotely to the MySQL database. However, I received an error message I hadn’t gotten before:

MySQL said: Access denied for user 'root'@'localhost'

I’d never had issues with connecting remotely before. I confirmed that the program I was using (Sequel Pro) was working by connecting to a few other servers without issues. I dug deeper into my new server.

After some digging, I solved my issue. By default, the root MySQL user uses auth_socket for it’s plugin, which doesn’t play nice when connecting remotely. Here’s how to fix it:

First, print out the MySQL root password and save it. You’ll need it in little bit. If you’re using Digital Ocean, here’s the command to find it:

cat ~/.digitalocean_password

Then open up MySQL:

mysql

Next, let’s confirm the plugin is the issue by printing all current MySQL users:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;

You should get something like this:

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *EFBDB02467A8AB572944B873296089BB6607492C | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

As you can see, the plugin is auth_socket, which is causing the issue. Let’s update it to use the root password we copied earlier.

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'DO_PASSWORD_HERE';

Then flush the privileges to have the updates take effect.

mysql> FLUSH PRIVILEGES;

Then exit mysql by typing:

mysql> exit

And finally, restart mysql:

service mysql restart

You’re all done! Try connecting remotely with the new password you set and you should be good to go.