Remote Root Access for MariaDB on Ubuntu 16.04 LTS

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.

  1. Log into MariaDB as the Root user
    sudo mysql -u root
    
  2. Disable the Auth Plugin
    use mysql;
    update user set plugin='' where User='root';
    
  3. Create a password for the root user
    grant all privileges on *.* to 'root'@'127.0.0.1' identified by '$STRONG_PASSWORD';
    flush privileges;
    
  4. Restart MariaDB
    sudo service mysql Restart
    

You can now access the root account with a password, and also over an SSH tunnel remotely.

If you made it this far, you should probably follow me on twitter. 🙂

This entry was posted in Databases and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *