As a part of creating training material, or perhaps making a vulnerable machine for a CTF, I often need to enable MySQL root access with a password, and often a poor password which you should never * use in a production environment! Doing this on Ubuntu has become a bit more tricky (although really that’s a good thing) but it’s also something I need to do often enough that I forget the correct way to do it on an up-to-date system!
By default Ubuntu does not configure the MySQL root account to authenticate with a password - rather, you access a new installation by running either sudo mysql or spawning a root shell and just running mysql. Incidentally, this approach also breaks the mysql_secure_installation script which is worth running for a production environment as it does pretty much what it says on the tin! Once you’ve access the root account, the ‘normal’ approach most people take to changing the password (and this is the error I usually make) is to run:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
And while this command will work, it won’t give you access on Ubuntu, since you also need to allow the root user to access mysql via password - therefore, we need to run:
sudo mysql
Then the following ALTER USER command to change the password and set the root user’s authentication method to one that uses a password. The following example changes the authentication method to mysql_native_password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
After making this change, exit the MySQL prompt:
exit
Following that, you can run the mysql_secure_installation script without any errors, or if you’re making a vulnerable / training system, you can now login with
mysql -u root -p
If you’d like to revert to the default setting on Ubuntu (perhaps after running mysql_secure_installation) simply use the command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;