Using NAS as your Database Server

When trying to improve ZurmoCRM performance on NAS, I have separate PHP programs and MySQL running on different machine. It is a good idea to run MySQL with RAID 1 to protect data from hardware failure.

I also explain how to harden your NAS to protect MySQL service from remote connection.

Enable MySQL from Remote Connection

When you enable MySQL on NAS, it is for local connection only on some NAS. To enable remote connection, you need to enable TCP/IP networking on MySQL, too. The default port number is 3306.

In ADM on Asustor, open [MySQL Server] in Services. Select [Enable MySQL server] and [Enable MySQL remote access]. Click [Apply] button to confirm.

In QTS on QNAP, open [MySQL Server] in [Control Panel]→[Applications]. Select [Enable MySQL Server] and [Enable TCP/IP networking] and click [Apply] button to confirm. MySQL remote access on QNAP NAS community forum is also a good reference.

In DSM on Synology with versions prior to 4.3, open [Web Applications] tab in [Control Panel]→[Network Services]→[Web Services]. Select [Enable MySQL] and click [Apply] button to confirm. Remote connection is always enabled.

In DSM on Synology with versions 5.0, MySQL is no longer available. You need to install MariaDB from Package Center.

MySQL Account Privilege

Next, you need to create or allow an existing account for remote access. It would be easier with phpMyAdmin than using mysql -e to execute SQL query. Read Shell Script : Interactive Way To Create Mysql User And Grant Access on LinOxide if you preferred later.

Following example will grant an account called [root] with password [admin] from IP 192.168.0.100 with all privilege on all database. You may add extra restriction for security concern.

GRANT ALL ON *.* TO 'root'@'192.168.0.100' IDENTIFIED BY 'admin' WITH GRANT OPTION;

MySQL GRANT Syntax provides more control on accounts. Remember to reference with matched MySQL version.

NAS Security Settings

I have explained how to harden your NAS in Synology Security Issue and How-to Harden your NAS. But with remote connection, you need to allow others to connect to MySQL on your NAS.

In ADM on Asustor, open [Firewall] tab in [Settings]→[ADM Defencer]. First select [Allow all connections] for [Basic Policy] and create deny policies for all IP to access MySQL on the NAS. Then select [Deny all connections] for [Basic Policy] and create allow policies for IP or IP range to access MySQL. Click on [Apply] button to confirm. It is port level control. [Network Defender] doesn’t support to protect MySQL from Brute-force attack yet.

In QTS on QNAP, you may open [Security Level] tab in [Control Panel]→[Security] and allow only certain IP but block all others to keep MySQL secure. Click on [Apply] button to confirm. [Network Access Protection] doesn’t support MySQL yet. There is no port level control, either.

In DSM on Synology with versions prior to 4.3, open [Firewall and QoS] in [Control Panel]→[Network Services]. You may create rules for IP or IP range to access MySQL on the NAS and select [Deny access] for [If no rules are matched]. Click on [Save] button to confirm. It is port level control. [Auto Block] doesn’t support to protect MySQL from Brute-force attack yet.

In DSM on Synology 5.0, open [Firewall] tab in [Control Panel]→[Connectivity]→[Security]. You may create rules for IP or IP range to access MariaDB on the NAS and select [Deny access] for [If no rules are matched]. Click on [Save] button to confirm. It is port level control. [Auto Block] doesn’t support to protect MariaDB from Brute-force attack yet.

More Database Server on NAS

MySQL is not the only choice for database on NAS. PostgreSQL is available on ADM and QTS, MongoDB on QTS, and MariaDB on DSM. Using a Synology NAS as PostgreSQL Server for mAirListDB from MairList explains how to setup Synology NAS as a PostgreSQL database server.

Reference

1. ZurmoCRM
2. PHP
3. MySQL
4. Wiki: RAID 1
5. Asustor ADM
6. QNAP: QTS
7. QNAP NAS Community Forum: MySQL remote access
8. Synology: DSM for Business
9. Synology Security Issue and How-to Harden your NAS
10. MariaDB
11. phpMyAdmin
12. LinOxide.com: Shell Script : Interactive Way To Create Mysql User And Grant Access
13. MySQL: MySQL 5.7 Reference Manual: 13 SQL Statement Syntax: 13.7 Database Administration Statements: 13.7.1 Account Management Statements: 13.7.1.4 GRANT Syntax
14. Wiki: Brute-force attack
15. PostgreSQL
16. MongoDB
17. MairList: Using a Synology NAS as PostgreSQL Server for mAirListDB