Remote MySQL/MariaDB root Access

Open the command-line mysql client on the server using the root account.

mysql -u root

Then you will want to run the following two commands, to see what the root user host is set to already:

use mysql;
select host, user from user;

grant all privileges on *.* to ‘root’@’%’ identified by password ‘secret‘ with grant option;

IDENTIFIED VIA mysql_native_password USING PASSWORD('secret');

Here’s an example of the output on my database, which is pretty much the default settings. Note that ubuntuserv is the hostname of my server.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> select host,user from user;
+—————+——————+
| host | user |
+—————+——————+
| ubuntuserv | root |
| localhost | debian-sys-maint |
| localhost | root |
+—————+——————+
3 rows in set (0.00 sec)

Now I’ll update the ubuntuserv host to use the wildcard, and then issue the command to reload the privilege tables. If you are running this command, substitute the hostname of your box for ubuntuserv.

update user set host=’%’ where user=’root’ and host=’ubuntuserv’;
flush privileges;

That’s all there is to it. Now I was able to connect to that server from any other machine on my network, using the root account.

Now tell MySQL to allow remote logins.

sudo nano /etc/mysql/my.cnf
or
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Out-of-the-box, MySQL only allows connections from the localhost identified by the IP Address of 127.0.0.1.  We need to remove that restriction, so find the line that says

bind-address = 127.0.0.1

and comment it out.