DB-유

Running MariaDB in Docker on Synology

· synology, docker, mariadb

The MariaDB 10 package in Package Center is fine for a WordPress site under Web Station. It's also pinned to whatever version Synology picked and shares its config with other packages, and that annoyed me the first time I needed a specific release for development. So now I run MariaDB in Docker, with the data directory on the NAS volume so it survives image updates.

This is my setup on DSM 6.1 with docker-compose over SSH, so the whole thing is one file I can keep in version control. It assumes the Docker package works already (see the earlier post) and SSH is enabled under Control Panel → Terminal & SNMP.

Folders first

Everything for this container lives under /volume1/docker/mariadb:

sudo mkdir -p /volume1/docker/mariadb/data
sudo mkdir -p /volume1/docker/mariadb/conf.d
sudo mkdir -p /volume1/docker/mariadb/initdb
  • data becomes /var/lib/mysql inside the container.
  • conf.d is mounted to /etc/mysql/conf.d, where the official image reads extra .cnf files.
  • initdb is mounted to /docker-entrypoint-initdb.d; any .sql or .sh file there runs once when the data directory is empty.

A config override

The official image runs with defaults suited to a small VPS. For a NAS with 4 to 8 GB of RAM I cap the buffer pool and set a sane character set:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
innodb_buffer_pool_size = 512M
innodb_log_file_size = 128M
innodb_flush_method = O_DIRECT
max_connections = 100
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2

Save it as /volume1/docker/mariadb/conf.d/nas.cnf. The container reads it at start, so changes require a restart of the container, not a rebuild.

The compose file

The Docker package ships with docker-compose 1.x in /usr/local/bin. Create /volume1/docker/mariadb/docker-compose.yml:

version: '2'
services:
  mariadb:
    image: mariadb:10.2
    container_name: mariadb
    restart: unless-stopped
    ports:
      - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: change-me-now
      MYSQL_DATABASE: appdb
      MYSQL_USER: appuser
      MYSQL_PASSWORD: also-change-me
    volumes:
      - /volume1/docker/mariadb/data:/var/lib/mysql
      - /volume1/docker/mariadb/conf.d:/etc/mysql/conf.d
      - /volume1/docker/mariadb/initdb:/docker-entrypoint-initdb.d

I pin the minor version (10.2) rather than latest. A latest tag will one day pull the next major release and start an in-place upgrade you did not plan for.

If the Synology MariaDB 10 package is installed it already listens on 3307 and the older MariaDB 5 package on 3306, so change the host side of the port mapping to something like 3316:3306 in that case.

Start it

cd /volume1/docker/mariadb
sudo docker-compose up -d
sudo docker-compose logs -f

The first start takes 20 to 30 seconds while the entrypoint initialises the system tables, creates the database and user from the environment variables, and runs anything in initdb. The log ends with mysqld: ready for connections. Press Ctrl-C to leave the log; the container keeps running.

The container shows up in the Docker GUI under Container, and you can stop and start it there as well. Just do not use the GUI's Edit function on a compose-managed container.

Connect, then lock down root

From the NAS itself, use the client inside the container:

sudo docker exec -it mariadb mysql -uroot -p

Then tighten the root account so it can only be used from inside the container:

SELECT user, host FROM mysql.user;
DELETE FROM mysql.user WHERE user='root' AND host='%';
FLUSH PRIVILEGES;

The official image creates root@% because it assumes you will connect from other containers. If only your application on another machine needs access, keep the application user and restrict its host, for example appuser@192.168.1.%.

Checking from another machine

From a workstation on the LAN:

mysql -h nas-ip -P 3306 -uappuser -p appdb -e "SELECT VERSION(), @@character_set_server, @@innodb_buffer_pool_size/1024/1024;"

You should see 10.2.x-MariaDB, utf8mb4 and 512. Stop the container, start it again with docker-compose restart, and confirm the tables you created are still there. That proves the data volume is doing its job.

Backup

I don't trust a database I haven't dumped. A logical dump from the host is the easiest backup and can be run from Control Panel → Task Scheduler as a user-defined script:

/usr/local/bin/docker exec mariadb sh -c 'exec mysqldump --all-databases --single-transaction -uroot -p"$MYSQL_ROOT_PASSWORD"' \
  | gzip > /volume1/backup/mariadb/all-$(date +%F).gz

Hyper Backup then picks up /volume1/backup along with everything else.

Notes on things that break

  • [ERROR] InnoDB: Cannot allocate memory for the buffer pool: the container hit its memory limit or the NAS is low on RAM. Lower innodb_buffer_pool_size or remove the resource limitation in the container settings.
  • The container restarts in a loop with [ERROR] mysqld: Can't create/write to file '/var/lib/mysql/aria_log_control' (Errcode: 13 "Permission denied"): the data folder was created with restrictive ownership. Run sudo chown -R 999:999 /volume1/docker/mariadb/data; 999 is the mysql user inside the official image.
  • Changing MYSQL_ROOT_PASSWORD in the compose file has no effect. The environment variables are only applied to an empty data directory. Change the password with ALTER USER inside the database instead.
  • ERROR 2003 (HY000): Can't connect to MySQL server on 'nas-ip' (111) from the LAN while docker exec works: the DSM firewall is blocking 3306. Add an allow rule for the port under Control Panel → Security → Firewall.

Upgrading later means changing the image tag, docker-compose pull, docker-compose up -d, then mysql_upgrade inside the container. I'll wait for 10.3 to settle before I try that.