Ubuntu 20.04 LTS: Installing MySQL
Published at 17:56 on 15 June 2021
This is probably going to be part of a series about the curve balls Ubuntu 20.04 LTS throws at the veteran Ubuntu Linux user.
When you install MySQL with:
apt-get install mysql-client mysql-server
You will get an oddly-configured MySQL server that uses a newfangled thing called auth_socket authentication for the root user. The upshot is that you will not be able to log in to mysql as root unless you are already the root Linux user, and in the latter case you will always be able to log in, regardless of what password you supply, or even if you supply a password at all.
If, like me, you are logged in as the root Linux user (and why wouldn’t you be, if you are doing a system install), then it appears as if authentication is completely disabled, and your mysql server’s root account is wide-open. At that point, you will try doing Internet searches to uncover the cause of the problem, and if you are like me, you will spend hours trying different keyword variations and finding exactly nothing pertinent.
The fix is to change the root user to use caching_sha2_password authentication and set a password for it, e.g.:
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'iSpQ7U9c8kGz'; FLUSH PRIVILEGES;
(And no, that is not my actual root password.)