TheLazy.Dev
Portfolio GitHub

Blog

synology |ds218 |mariadb10 |mariadb

Synology DS218: MariaDB 10 enabling remote connection.

behainguyen

Be Hai Nguyen

Posted on July 1, 2022

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.

026-feature-image.png
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.

Table of contents

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

  1. Synology DS218 -- it's accessed via its device name omphalos-nas-01 instead of its IP address.
  2. 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.
  3. DSM 7.1-42661 Update 1.
  4. 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.
  5. Synology DSM user “behai” is the user I set up when first installed DSM. This is not the Linux root user.
  6. 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
Enter fullscreen mode Exit fullscreen mode

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
...
Enter fullscreen mode Exit fullscreen mode

❷ Turn skip_networking to OFF -- modify: 



/var/packages/MariaDB10/etc/synology.cnf
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

❸ Create remote access database and user.

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



E:\>ssh behai@omphalos-nas-01
Enter fullscreen mode Exit fullscreen mode

Launch mysql: 



$ mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Create database: 



MariaDB [(none)]> CREATE DATABASE ompdb;
Enter fullscreen mode Exit fullscreen mode

Create MariaDB 10 user “behai”: 



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

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;
Enter fullscreen mode Exit fullscreen mode 


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


MariaDB [(none)]> FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

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



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

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)]>
Enter fullscreen mode Exit fullscreen mode

❹ Restart MariaDB 10 using the following commands: 



$ sudo synopkg stop MariaDB10
$ sudo synopkg start MariaDB10
$ sudo synopkg status MariaDB10
Enter fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

Enter password for “behai”, then run: 



mysql> show databases;
Enter fullscreen mode Exit fullscreen mode

We should get the following: 



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

mysql>
Enter fullscreen mode Exit fullscreen mode

⓶ 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 fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

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 fullscreen mode Exit fullscreen mode

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

  1. Configuring MariaDB for Remote Client Access
  2. Determine which MySQL configuration file is being used
  3. Configuring MariaDB with Option Files
  4. Set mysql skip-networking to off
  5. ERROR 2002 (HY000): Can't connect to MySQL server on '192.168.1.15' (115)
  6. MariaDB 10.0.33 Configuring MariaDB for Remote Client Access
  7. Allow Remote Access to MariaDB Database Server on Ubuntu 18.04

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:

  1. 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.
  2. Change bind-address to 0.0.0.0; add entry skip-bind-address; change skip_networking and / or skip-networking to 0.
  3. 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:

  1. skip_networking and skip-networking -- underscore ( _ ) and hyphen ( - ).
  2. 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"
Enter fullscreen mode Exit fullscreen mode

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:~$
Enter fullscreen mode Exit fullscreen mode
  • /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:
Enter fullscreen mode Exit fullscreen mode 


# 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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: _ ( underscore ) in between. Run: 



$ mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

Then, run:



MariaDB [(none)]> SHOW VARIABLES LIKE 'skip_networking';
Enter fullscreen mode Exit fullscreen mode

The output was: 



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

MariaDB [(none)]>
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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



[mysqld]
skip_networking=0
skip-bind-address
Enter fullscreen mode Exit fullscreen mode

Finally: 



[mysqld]
skip_networking=0
Enter fullscreen mode Exit fullscreen mode

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:
Enter fullscreen mode Exit fullscreen mode 


...
!include /var/packages/MariaDB10/etc/my.cnf
!include /var/packages/MariaDB10/etc/my_port.cnf
!include /var/packages/MariaDB10/etc/synology.cnf
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 


# 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 


# 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Output:



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

❷ Query MariaDB 10 services and port numbers: 



$ cat /etc/services | grep mysql*
Enter fullscreen mode Exit fullscreen mode

Output:



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

❸ Query MariaDB 10 processes info: 



$ ps xa | grep mysqld
Enter fullscreen mode Exit fullscreen mode

Output:



  347 ?        S      0:00 /bin/sh /usr/local/mariadb10/bin/mysqld_safe --datadir=/var/packages/MariaDB10/target/mysql --pid-file=/run/mysqld/mysqld10.pid
  502 ?        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
10131 pts/0    S+     0:00 grep --color=auto mysqld
behai@omphalos-nas-01:~$
Enter fullscreen mode Exit fullscreen mode

❹ Some other netstat commands: 



$ sudo netstat -anp | grep 3306
$ netstat -na | grep mysql*
$ netstat -ln | grep mysql
Enter fullscreen mode Exit fullscreen mode

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.

💖 💪 🙅 🚩
behainguyen
Be Hai Nguyen

Posted on July 1, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Synology DS218: MariaDB 10 enabling remote connection.
synology Synology DS218: MariaDB 10 enabling remote connection.

July 1, 2022