Remote Root Access for MariaDB on Ubuntu 16.04 LTS

Animated image of professor Emmett Brown from Back to the Future, he looks shocked. Greetings Time Traveler!

It looks like you found a post that is several years old. Keep in mind that technology changes fast and some of the information in this post might no longer be relevant.

If you're stuck, leave a comment and I'll try to help if I can.

Image via GIPHY

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.


You can add a comment via Email. Or use the embedded discourse form shown below: