2016-10-13 Tags: databases mysql
Typically, when you install MySQL on Ubuntu, it asks you for a root
password which you can then use to log into the database. It seems that
MariaDB uses a plugin instead to authenticate the root user. This means
that the only way to log into MariaDB by default as root is locally with
sudo mysql -u root In order to give the root user a password and login
the "traditional" way (which includes remote access via a tunnel) you
have to do the following.
Log into MariaDB as the Root user
sudo mysql -u root
Disable the Auth Plugin
use mysql; update user set plugin='' where User='root';
Create a password for the root user
grant all privileges on *.* to 'root'@'127.0.0.1' identified by '$STRONG_PASSWORD'; flush privileges;
sudo service mysql Restart
You can now access the root account with a password, and also over an SSH tunnel remotely.