DB-유

MariaDB 10 Package on Synology

· synology, mariadb, mysql

Every time I set up Web Station apps on a Synology I end up needing a database, and for a small LAN server I'd rather not run a container for it. The MariaDB 10 package is Synology's supported MySQL-compatible server, and WordPress and phpMyAdmin from Package Center depend on it.

It has quirks compared with a stock install: it listens on port 3307, the config file lives under the package directory, and the root password is set from the package UI. If the old MariaDB 5.5 package is installed, MariaDB 10 sits alongside it and you can migrate later. You'll want SSH for my.cnf and the command-line client.

Install and set the root password

Package Center → search "MariaDB 10" → Install. Open the package from the main menu. The first run asks for a root password; set one. If the old MariaDB 5.5 package is present, the UI also offers to migrate its databases into MariaDB 10.

The relevant paths:

  • Binaries: /usr/local/mariadb10/bin/ (mysql, mysqldump, mysqladmin).
  • Data directory: /volume1/@database/mariadb10/.
  • Configuration file: /volume1/@database/mariadb10/my.cnf (created empty; anything you put there is read at start).
  • Socket: /run/mysqld/mysqld10.sock.

The command-line client is not in the default PATH, so either use the full path or add an alias.

Enable TCP/IP

By default MariaDB 10 only listens on the Unix socket. In the package UI tick "Enable TCP/IP connection". The default port is 3307, because 3306 is taken by the MariaDB 5.5 package if it exists; you can change it here if 5.5 is not installed.

Then allow the port in Control Panel → Security → Firewall for the LAN subnet only. Do not forward it on the router.

Test from SSH:

/usr/local/mariadb10/bin/mysql -uroot -p -h 127.0.0.1 -P 3307 -e "SELECT VERSION();"

A database and an application user

/usr/local/mariadb10/bin/mysql -uroot -p -S /run/mysqld/mysqld10.sock

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'192.168.1.%' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'192.168.1.%';
FLUSH PRIVILEGES;

Restricting the host part to the LAN range means a leaked password is useless from outside, on top of the firewall. For applications running on the NAS itself (Web Station), use 'appuser'@'localhost' and connect over the socket.

Tuning my.cnf

The package ships with defaults sized for a tiny machine. On a unit with 4 GB or more of RAM, add a [mysqld] section:

[mysqld]
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 2
max_connections = 100
slow_query_log = 1
slow_query_log_file = /volume1/@database/mariadb10/slow.log
long_query_time = 2

Restart the package from Package Center or with sudo synoservicectl --restart pkgctl-MariaDB10 for the changes to take effect. Changing innodb_log_file_size on MariaDB 10.0 requires a clean shutdown first; the package stop does that.

I keep the buffer pool modest. DSM, Web Station's PHP and any Docker containers share the same RAM, and a swapping NAS is worse than a smaller pool.

Backups

Storage Manager snapshots or Hyper Backup copying /volume1/@database while the server is running give you inconsistent files. I don't trust them for this. Use a logical dump from DSM's Task Scheduler (Control Panel → Task Scheduler → Create → Scheduled Task → User-defined script, run as root, daily):

#!/bin/sh
DEST=/volume1/backup/mariadb
mkdir -p $DEST
/usr/local/mariadb10/bin/mysqldump -uroot -pROOTPASSWORD -S /run/mysqld/mysqld10.sock \
  --single-transaction --routines --triggers --all-databases \
  | gzip > $DEST/all-$(date +%F).sql.gz
find $DEST -name 'all-*.sql.gz' -mtime +14 -delete

Then let Hyper Backup pick up /volume1/backup.

Did it work?

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'port';
SHOW GRANTS FOR 'appuser'@'192.168.1.%';

From a workstation, connect with HeidiSQL or mysql -h <nas-ip> -P 3307 -u appuser -p appdb. Check the dump file exists and restores into a scratch database:

zcat /volume1/backup/mariadb/all-*.sql.gz | head -20

Errors I've run into

  • ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.20' (111): TCP/IP is not enabled in the package UI, or the firewall blocks 3307. Also check you passed -P 3307; clients default to 3306.
  • ERROR 1130 (HY000): Host '192.168.1.55' is not allowed to connect to this MariaDB server: the user was created for localhost only. Create it again with a host pattern.
  • The package fails to start after editing my.cnf: check /volume1/@database/mariadb10/ for an error log, or run /usr/local/mariadb10/bin/mysqld --print-defaults to catch a typo in an option name.
  • phpMyAdmin from Package Center shows only MariaDB 5.5: in its settings, choose the MariaDB 10 server; the package supports both.

For anything heavier, or when I need a specific version, I reach for a mariadb container. For Web Station and a couple of LAN apps, the package is less to maintain.