Synology DS218: MariaDB 10 enabling remote connection.

We discuss how to enable remote connection for MariaDB 10.3.32-1040 on Synology DS218, DSM 7.1-42661 Update 1. That is, using a Windows 10 client tool, we are able to connect to a MariaDB database on Synology DS218.


Synology DS218: MariaDB 10 enabling remote connection.

We'll carry out the changes with little explanations. Then we'll go into detail. But first, please see the Disclaimer below.

Disclaimer
Environments
Steps to enable remote access for MariaDB 10
Elaborating of the steps outlined previously
Other system commands relating to MariaDB 10
Concluding remarks

Disclaimer

The procedures discussed in this post are of an experimental nature, they've never been tested for production usage.
I take no responsibilities for any damages or losses resulting from applying the procedures outlined in this post.

Environments

Synology DS218 -- it's accessed via its device name omphalos-nas-01 instead of its IP address.
Windows 10 PC -- it's accessed via its device name DESKTOP-7BA02KU also. It's the client machine which we'll enable to remotely connect to MariaDB 10 on omphalos-nas-01. Client tools mysql and MySQL Workbench 6.3 CE are installed on this machine.
DSM 7.1-42661 Update 1.
MariaDB 10.3.32-1040 -- I installed it on its own from Package Center. I did not change any default settings. This is a fresh install. There are no other prior configurations before this.
Synology DSM user “behai” is the user I set up when first installed DSM. This is not the Linux root user.
MariaDB 10 user “behai” is the user I set up to allow remote access to MariaDB 10 on the Synology DS218 box. This is not the MariaDB 10 root user.

Steps to enable remote access for MariaDB 10

❶ Check bind-address -- on my installation, the default configuration file for MariaDB 10 is:

/usr/local/mariadb10/etc/mysql/my.cnf

This file already has bind-address under [mysqld] set to the required value. I didn't have to do anything to bind-address.

...
[mysqld]
bind-address = 0.0.0.0
...

❷ Turn skip_networking to OFF -- modify:

/var/packages/MariaDB10/etc/synology.cnf

Under [mysqld], change skip_networking to 0. The final content of /var/packages/MariaDB10/etc/synology.cnf is listed below:

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
skip_networking=0

❸ Create remote access database and user.

From a Windows command prompt, run SSH to access DSM command line:

E:\>ssh behai@omphalos-nas-01

Launch mysql:

$ mysql -u root -p

Create database:

MariaDB [(none)]> CREATE DATABASE ompdb;

Create MariaDB 10 user “behai”:

MariaDB [(none)]> CREATE USER 'behai'@'localhost' IDENTIFIED BY '<,U#n*m:5QB3_zbQ';

Grant local and remote access to MariaDB 10 user “behai”:

MariaDB [(none)]> GRANT ALL ON ompdb.* to 'behai'@'localhost' IDENTIFIED BY '<,U#n*m:5QB3_zbQ' WITH GRANT OPTION;

MariaDB [(none)]> GRANT ALL ON ompdb.* to 'behai'@'DESKTOP-7BA02KU' IDENTIFIED BY '<,U#n*m:5QB3_zbQ' WITH GRANT OPTION;

MariaDB [(none)]> FLUSH PRIVILEGES;

Verify MariaDB 10 user “behai” has been created, and with allowed access from specified hosts:

MariaDB [(none)]> SELECT User, Host FROM mysql.user;

There should be two ( 2 ) entries for MariaDB 10 user “behai”:

+-------+-----------------+
| User  | Host            |
+-------+-----------------+
| root  | 127.0.0.1       |
| root  | ::1             |
| behai | desktop-7ba02ku |
| behai | localhost       |
| root  | localhost       |
+-------+-----------------+
5 rows in set (0.001 sec)

MariaDB [(none)]>

❹ Restart MariaDB 10 using the following commands:

$ sudo synopkg stop MariaDB10
$ sudo synopkg start MariaDB10
$ sudo synopkg status MariaDB10

Remote access should now be enabled.

❺ Test remote access

⓵ From Windows 10 PC, DESKTOP-7BA02KU command prompt, run:

F:\>"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql" -u behai -p -h omphalos-nas-01

Enter password for “behai”, then run:

mysql> show databases;

We should get the following:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| ompdb              |
+--------------------+
2 rows in set (0.00 sec)

mysql>

⓶ From Windows 10 PC, launch MySQL Workbench 6.3 CE, create a new connection with:

Connection Name: mariadb-on-nas
Connection Method: Standard (TCP/IP)
Hostname: omphalos-nas-01
Port: 3306
Username: behai
Password: <,U#n*m:5QB3_zbQ
Default Schema: ompdb

Hit the Test Connection button, it should connect. Note that, depending on your installations, there might be a warning about the client and the server versions differences, just ignore it.

⓷ From Synology DS218 command line, run:

$ mysql --protocol=tcp --port=3306 --user=behai --password --database ompdb

Enter password. It should connect. Prompt changed to MariaDB [ompdb]>.

⓸ From Synology DS218 command line, run:

$ mysql --protocol=tcp --host=omphalos-nas-01 --port=3306 --user=behai --password --database ompdb

Enter password. It should connect. Prompt changed to MariaDB [ompdb]>.

This makes sense, since from within, omphalos-nas-01 should get translated to 127.0.0.1 which is localhost.

⓹ From Synology DS218 command line, run:

$ mysql --protocol=tcp --host=127.0.0.1 --port=3306 --user=behai --password --database ompdb

Enter password. It should connect. Prompt changed to MariaDB [ompdb]>.

Elaborating of the steps outlined previously

I've worked with MySQL quite in depth before. In Windows, I've installed MySQL servers, set up replication, automating daily back up and test restore ( onto different machines ), etc. Some replication servers were LAN Linux boxes. I did not set up those Linux boxes, the extend I've worked on them was modifying configuration files to make them slave servers: AND FURTHERMORE, I WAS TOLD which configuration files to modify! I can find my ways around Linux, but it's not my cup of tea. ( During my university years, I'd used Unix for six [ 6 ] straight years. )

In Windows, we've set up MySQL servers for remote access both in-house ( LAN ) and as dedicated private database servers ( i.e. also LAN ) within data centres -- I do not recall we'd any major problems.

This's the first time I've attempted this on a Linux box. There's just no answer from Synology... But there're plenty of posts on the subject, related to different flavours of Linux. I'm listing below the main posts that I've used.

References

What have to be done...

From the existing posts, it seems to be a straightforward configuration process, albeit commands' differences across Linux flavours. For me, the main issues were:

Identify the right config file to update. They're different across Linux distros. And unlike in Windows, there's only a single .ini file used. In Linux, there can several different config files loaded by the running instance.
Change bind-address to 0.0.0.0; add entry skip-bind-address; change skip_networking and / or skip-networking to 0.
Possibly adding a firewall rule to allow access to MariaDB 10's port of 3306.

Also, there're some inconsistencies among the existing posts, notably for me:

skip_networking and skip-networking -- underscore ( _ ) and hyphen ( - ).
skip-bind-address -- some posts mention this, some don't.

These're not major problems. We can progressively eliminate them via trial and error. The main problem's identifying the right config file.

Working out the config files

The below command lists files where default options are loaded from:

$ mysql --verbose --help | grep -A 1 "Default options"

It gives the following output:

Default options are read from the following files in the given order:
/usr/local/mariadb10/etc/mysql/my.cnf ~/.my.cnf
behai@omphalos-nas-01:~$

/usr/local/mariadb10/etc/mysql/my.cnf is the default installation file.
~/.my.cnf -- in my understanding, its full path is $HOME, which is /var/services/homes/behai/.my.cnf. It does not exist.

First lines and last lines of /usr/local/mariadb10/etc/mysql/my.cnf:

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
#
[client]
socket = /run/mysqld/mysqld10.sock

[mysqld]
bind-address = 0.0.0.0
socket = /run/mysqld/mysqld10.sock
...
!include /var/packages/MariaDB10/etc/my.cnf
!include /var/packages/MariaDB10/etc/my_port.cnf
!include /var/packages/MariaDB10/etc/synology.cnf

The instructions on the first three ( 3 ) lines are very clear. The target config file is /var/packages/MariaDB10/etc/my.cnf.

First try with /var/packages/MariaDB10/etc/my.cnf

/var/packages/MariaDB10/etc/my.cnf did not exist. I created it with the following content:

[mysqld]
skip-networking=0
skip-bind-address

Then restarted MariaDB 10 for the change to take effect. From here onwards, whenever we mention a config file changed or created, it's implicit that we restarted MariaDB 10 immediately.

Please note skip-networking -- it was a - ( hyphen ) in between. I restarted MariaDB 10 -- needless to say, it did not work. Not realising my mistake, I went ahead and set up Firewall rule: under Control Panel > Security > Firewall tab. Still did not work. I played around with this Firewall rule a few times, still did not work! I am confident that I've got the Firewall rule right, since this was a simple rule.

I removed /var/packages/MariaDB10/etc/my.cnf.

Check loaded “skip networking” option

Based on one of the posts that I've come across, we can use mysql command line tool to query skip_networking value</span>: _ ( underscore ) in between. Run:

$ mysql -u root -p

Then, run:

MariaDB [(none)]> SHOW VARIABLES LIKE 'skip_networking';

The output was:

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| skip_networking | ON    |
+-----------------+-------+
1 row in set (0.003 sec)

MariaDB [(none)]>

Attempts with /var/services/homes/behai/.my.cnf

At this point, the Firewall rule was still in place.

I created /var/packages/MariaDB10/etc/my.cnf, and tried different contents:

[mysqld]
bind-address=0.0.0.0
skip_networking=0
skip-bind-address

I couldn't remember why I did include bind-address! Then:

[mysqld]
skip_networking=0
skip-bind-address

Finally:

[mysqld]
skip_networking=0

None worked. skip_networking was still ON.

I removed /var/services/homes/behai/.my.cnf.

Subsequent tries with /var/packages/MariaDB10/etc/my.cnf

At this point, the Firewall rule was still in place.

I recreated /var/packages/MariaDB10/etc/my.cnf, and tried with same contents as in previous section Attempts with /var/services/homes/behai/.my.cnf.

It did not work.

I removed /var/packages/MariaDB10/etc/my.cnf.

/var/packages/MariaDB10/etc/my_port.cnf

Recall from section Working out the config files, the last three ( 3 ) lines of the default config file are:

Last three lines of /usr/local/mariadb10/etc/mysql/my.cnf:

...
!include /var/packages/MariaDB10/etc/my.cnf
!include /var/packages/MariaDB10/etc/my_port.cnf
!include /var/packages/MariaDB10/etc/synology.cnf

Up to this point, I've not looked at either /var/packages/MariaDB10/etc/my_port.cnf or /var/packages/MariaDB10/etc/synology.cnf.

Content of /var/packages/MariaDB10/etc/my_port.cnf

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
port=3306
[client]
port=3306

It's easy enough to understand... Let's look at /var/packages/MariaDB10/etc/synology.cnf next.

/var/packages/MariaDB10/etc/synology.cnf

At this point, the Firewall rule was still in place.

Content of /var/packages/MariaDB10/etc/synology.cnf

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
skip_networking=1

So I made the change as discussed previously in Steps to enable remote access for MariaDB 10 | ❷ Turn skip_networking to OFF; then restarted the server.

Check loaded “skip networking” option now shows skip_networking is OFF.

Next, I tried to connect to it from my Windows machine. It works. Next, I proceeded to remove the Firewall rule. I have no Firewall rule at all. Finally, I carried out all the tests as discussed in Steps to enable remote access for MariaDB 10 | ❺ Test remote access.

In summary, there is only a single change needed! It took me a lot of hours to get it -- not all wasted: I've learned some other stuff along with the hours spending on it.

Another point, I can't help but feel that SYNOLOGY'S INLINE DOCUMENTATIONS IN THOSE CONFIGURE FILES ARE POSSIBLY MISLEADING?

Other system commands relating to MariaDB 10

❶ The below command shows the starting arguments:

$ sudo mysql --print-defaults

Output:

mysql would have been started with the following arguments:
--socket=/run/mysqld/mysqld10.sock --no-auto-rehash --port=3306
behai@omphalos-nas-01:~$

❷ Query MariaDB 10 services and port numbers:

$ cat /etc/services | grep mysql*

Output:

mysql           3306/tcp
mysql           3306/udp
mysql-proxy     6446/tcp                        # MySQL Proxy
mysql-proxy     6446/udp
behai@omphalos-nas-01:~$

❸ Query MariaDB 10 processes info:

$ ps xa | grep mysqld

Output:

?        S      0:00 /bin/sh /usr/local/mariadb10/bin/mysqld_safe --datadir=/var/packages/MariaDB10/target/mysql --pid-file=/run/mysqld/mysqld10.pid
?        Sl     0:05 /usr/local/mariadb10/bin/mysqld --basedir=/usr/local/mariadb10 --datadir=/var/packages/MariaDB10/target/mysql --plugin-dir=/usr/local/mariadb10/lib/mysql/plugin --user=mysql --log-error=/var/packages/MariaDB10/target/mysql/omphalos-nas-01.err --pid-file=/run/mysqld/mysqld10.pid --socket=/run/mysqld/mysqld10.sock --port=3306
pts/0    S+     0:00 grep --color=auto mysqld
behai@omphalos-nas-01:~$

❹ Some other netstat commands:

$ sudo netstat -anp | grep 3306
$ netstat -na | grep mysql*
$ netstat -ln | grep mysql

Concluding remarks

This process is an experiment to satisfy my own curiosities. Also, there've been a lot of questions on this topic, it's satisfying to figure out the answer. Also, it's always good to have a separate development database server. Please note, I've ignored all security considerations. I'll think about it later.

I'm not at all sure if I've got everything correctly... I've included the detail description of my working progress with the hope that it might help with similar problems on some other Linux distros.

Thank you for reading... and I hope you find this post helpful somehow.

Table of contents