Configure MariaDB for remote client access

By default MariaDB is listening to 127.0.0.1 using bind-address directive for the security reasons. If you were using old MySQL and just upgraded to MariaDB, it is possible that TCP/IP networking is disabled on your server using skip-networking directive.

skip-networking tells MariaDB to run without any of the TCP/IP networking options.

Allow remote access to MariaDB databases

MariaDB is bound to the loopback interface by default because it makes it impossible to connect to the TCP port on the server from a remote host (the bind-address must refer to a local IP address, or you will receive a fatal error and MariaDB will not start). This of course is not desirable if you want to use the TCP port from a remote host, so you must remove this bind-address directive (MariaDB only supports one bind-address, but binds to 0.0.0.0, or :: (every IP) if the bind-address directive is left out).

To enable MariaDB to listen to remote connections, you need to edit your defaults file.

Common locations for defaults files:

/etc/my.cnf                              (*nix/BSD)
$MYSQL_HOME/my.cnf                       (*nix/BSD) *Most Notably /etc/mysql/my.cnf
SYSCONFDIR/my.cnf                        (*nix/BSD)
DATADIR\my.ini                           (Windows)

Once you have located the defaults file, use a text editor to open the file and try to find lines like this under the [mysqld] section:

[mysqld]
...
skip-networking
...
bind-address = <some ip-address>
...

It is possible that lines may not be in order but it doesn’t matter.

If you are able to locate these lines, make sure they are both commented out (prefaced with hash (#) characters), so that they look like this:

 [mysqld]
...
#skip-networking
...
#bind-address = <some ip-address>
...

Save the file and restart the mysqld daemon or service.

Grant permissions to user to allow remote connection:

Now it’s time to add a user to allow remote connection.

[1] Log into the mysql command line tool (or you can use GUI tool like phpMyAdmin or any other tool).

[2] Execute the following queries to grant remote access on all databases for root user:

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.1.%’ IDENTIFIED BY ‘my-new-password’ WITH GRANT OPTION;

% indicates wildcard. This will allow remote access to 192.168.1.0/24. If you just want to allow to specific IP address. You can mentioned complete IP address.

Now, root user can access all database remotely. If you have firewall on your server, you will have to open port 3306 (or other port if you have changed it) for remote connection.

Leave a Reply