caharkness.com

Give MySQL Server A Root Password

After upgrading this server’s version of Ubuntu from 16.04 LTS to 20.04 LTS via a completely clean installation of it, installing mysql-server did not automatically offer to set up a root password, so here’s what I did (as root):

apt-get purge mysql-server mysql-common mysql-client-*
rm -rf /var/lib/mysql
rm -rf /etc/mysql*

The three commands above will completely torch MySQL and all its configuration from your system. If you accidentally lock yourself out and set a password in MySQL incorrectly by following an outdated guide like I did, merely removing mysql-server will not get rid of that mistake. You must also remove all the configuration.

Next, install MySQL Server using the following:

apt-get install mysql-server

In the past, immediately after mysql-server was installed, you would be walked through the setup process and create a root password for MySQL Server. I had to read this guide located here to figure out that this is no longer the case and that you had to take some additional steps to give the root user a password.

mysql -u root

As root in Ubuntu, run the above command to enter an interactive MySQL shell and run the following queries line by line:

USE mysql;
UPDATE user SET plugin='mysql_native_password' WHERE User='root';
FLUSH PRIVILEGES;
exit;

You will be returned to the Ubuntu shell where you will need to then restart mysql and run the configuration utility. The configuration utility is pretty self-explanatory and won’t need any explaining here.

service mysql restart
mysql_secure_installation

Congratulations! You can now use something like Adminer to log into your newly configured MySQL Server and restore the databases that you should have regularly backed up.