It’s been a little while since I’ve rolled out a new server. Today I decided I wanted to roll out a fresh web server and start to migrate some of my sites over.
The first thing I do on any fresh mariadb/mysql install is to run the
mysql_secure_installation script to keep things as secure as possible. Part of that script involves setting a root password for the database instance. I’ve done this plenty of times before so follow the steps. At the end I have a nice and secure mariadb installation.
So secure that I couldn’t login with the newly minted root password. What on earth had I done?
After half an hour of searching I worked out that there was a change a few years ago to incorporate the passwordless root login using the
unix_socket authentication plugin in Debian (and therefore Ubuntu) mariadb releases. This means that by default the root account in mariadb can only be authenticated by the root user (or a sudoer) and that any password set or provided is ignored.
Things change when you don’t pay attention.
I could go back and remove the password that I have added but I’ll leave it for now in case there is a good reason to remove
unix_socket in the future. What this means is that to login to mariadb as the root user is as simple as
This is possible as the user defaults to the unix username. Too easy!