How to lock tables of a database server running in a docker container before taking a snapshot?

Apps and Virtualization
,
1

[Edit: added cronjob requirement]

I am running a MariaDB server in a docker container on TrueNAS 25.10.1 - Goldeye.

I would like to run a cronjon to issue FLUSH TABLES WITH READ LOCK to the MariaDB before taking a snapshot and release the lock afterwards. Is there a way to do it?

Thanks in advance.

2

There’s docker exec so if a client binary for your DB is part of the container, that should be a straightforward way.

3

Thanks for your suggestion.

I am sorry that I have forgotten to mention that I would like it to be done as a cronjob.

The tricky part is, the snapshot command has to be executed between the sql FLUSH TABLES WITH READ LOCK ; and UNLOCK TABLES ; It could be done as the following script if MariaDB were not running inside a container.

mysql << EOF
    FLUSH TABLES WITH READ LOCK;
    system zfs snapshot data/db@snapname
    UNLOCK TABLES;
EOF

However, I don’t know how to create a ZFS snapshot inside a container. :persevering_face:

4

All from outside, of course:

docker exec <container id> 'echo "FLUSH TABLES WITH READ LOCK;" | mysql'
zfs snapshot ...
docker exec <container id> 'echo "UNLOCK TABLES;" | mysql'
5

As far as I know, the tables are unlocked when the client disconnect. https://dev.mysql.com/doc/refman/9.1/en/lock-tables.html

If the connection for a client session terminates, whether normally or abnormally, the server implicitly releases all table locks held by the session (transactional and nontransactional).

Hence your example is taking a snapshot without locking the tables.

6

It seems I have to bite the bullet and run multiprocesses in bash. :confused:

7

You have the same issue when snapshotting a VM. A snapshot of a live VM is not crash safe but better than not having a snapshot.

8

I did not know that. Is there a sleep command in SQL to keep the process hanging for e.g. 30 seconds?

So you could change the first command to:

docker exec <container id> 'echo "FLUSH TABLES WITH READ LOCK; SLEEP 30" | mysql' &

next take the snapshot, drop the third command altogether, the lock will be released when the first command exits.

Or go back to your first approach doing everything in SQL with a system exit for the snapshot. Find out how to connect to the MySQL DB inside the container from the host - maybe create a special user for that or some such - and then copy a statically linked mysql binary to the host and create a matching shell script.

I’d consider stopping the entire app, taking the snapshot, starting the app once per day. Should be doable with midctl.

9

This topic is similar to Script to stop and start an app with cron task.

In my personal opinion, you can just (zfs) snapshot db without stopping it. Especially if this db is having non-production workloads. And I’m citing myself:

1 Like
10

We do that for >1000 production systems all the time. Snapshots at 3 am local time. Never had a problem with restores. mysqlcheck --auto-repair after restore recommended.

1 Like
11

In my recent experience, MariaDB is pretty robust on resilient itself but it is just the backend of the Nextcloud. Both the Nextcloud data and MariaDB state should be in sync. Nextcloud is pretty fragile and messed up easily in my humble opinion.

I am taking data consistency serious although the service is just a personal pet project and it serves few users. After paying extra effort to make the whole system resilience and reliable, i.e redundant CPUs, redundant power supplies, redundant UPS, ECC RAM, and so on, I would not leave it to chance.

Here is my solution. 4 files for the whole snapshot task running in multi-process. I share them here for someone in the future who needs a similar solution.

Be caution!! Use at your risk. I don’t responsible for any lost they may cause.

Assumptions and behavior:

  • Written for TrueNAS SCALE Community Edition Version 25.04 Fangtooth or higher. It is tested on 25.10.1 Goldeye only.
  • They should be run hourly as a cronjob.
  • Hourly snapshots and daily snapshots are taken separately.
  • Emails are sent with standalone-tn-send-email in case of errors.
  • Logs are written in for the main script as well as the sql run inside MariaDB.
  • Auto removal of the old snapshots are provided. Old snapshots are removed only if new snapshots are taken.
  • Auto removal of the old logs are provided.
  • Please review all the variables declared and change them to suit your environment.

Workflow:

  1. The main script checks for other running instances. It sends errors and quits if there is another copy running.
  2. The main script removes flag files.
  3. The main script turns on Nextcloud maintenance mode.
  4. The main script calls a sub script as a background job and waits for the backup stage flag file.
  5. The sub script callsBACKUP STAGE commands in MariaDB inside the MariaDB docker container.
  6. The sub script creates the backup stage flag file and waits for the snapshot taken flag file.
  7. The main script takes snapshots.
  8. The main script creates the snapshot taken flag file and waits for the removal of the backup stage flag file.
  9. The sub script ends BACKUP STAGE and removes the backup stage flag files. Then it exits.
  10. The main script removes the snapshot taken flag file only if backup stage flag file is removed. It is left behind in case the sub script failed somewhere before the backup stage flag file removal.
  11. The main script turns off Nextcloud maintenance mode.
  12. The main script sends an email in case of any error.

Files:

  1. The main script (to be called from crontab): nc_snapshot.sh (11.8 KB)

  2. The SQL for MariaDB (it should be stored in a docker mount directory) [remove the .txt extension due to fact that a file name ends in .sql is not allowed to upload]: backup_stage.sql.txt (584 Bytes)

  3. The synchronization shell script called inside the MariaDB sql: zfs_nc_snapshot_wait.sh (624 Bytes)

  4. The MariaDB client configuration file for credentials. [remove the .txt extension due to the fact that a file name ends in .cnf is not allowed to upload]: backup_stage.cnf.txt (34 Bytes)

I have run them for a couple days. They seem to be working without side effect.

[Edited for: adding more variables in scripts for generalization]

[Edited again for: adding the MariaDB client config file]

[Edited yet again for: fixing typos]

[Edited another time for: fixing little errors in file nc_snapshot.sh]

2 Likes