The compose file, explained
services:
db:
image: mariadb:12.3
container_name: dbPin the version. With latest (or a tag from .env that says latest) the database upgrades on the next pull — including to a version that dropped parameters from command.
mem_limit: 512MThe container’s memory limit: the InnoDB buffer plus connections and overhead.
stop_grace_period: 2mHow long Docker waits on stop. The default is 10 s, then SIGKILL: under load the database doesn’t finish writing, and the next start has to do crash recovery. At server shutdown, systemd limits the time too: docker.service sets no TimeoutStopSec of its own, so the global 90 s applies — less than two minutes. Give it room: sudo systemctl edit docker, add [Service] and TimeoutStopSec=3min.
command:
- --disable-log-binThe binary log is for replication and point-in-time recovery. A single database with daily dumps just loses disk to it. The cost: you can restore only to the moment of a dump.
- --innodb-buffer-pool-size=96MThe InnoDB buffer is the main memory consumer. A small database needs about the size of its “hot” data. 96M is a value from one particular server — size yours to your data.
- --innodb-flush-log-at-trx-commit=21 (the default) flushes the log to disk on every commit. 2 writes to the OS on commit and flushes to disk about once a second: if the server crashes (power, kernel) you lose up to a second of transactions, if only the database crashes — nothing. Fine for a blog, not for money.
- --innodb-log-file-size=64MRedo log size: smaller means faster crash recovery but more frequent page flushes to disk. 64M is also an example from one particular server.
- --character-set-server=utf8mb4
- --collation-server=utf8mb4_unicode_ciutf8mb4 is full Unicode, emoji included; utf8 in MySQL and MariaDB is a cut-down version.
restart: unless-stopped
networks: [backend]A network for the apps only. No port is published: the database has no ports:.
volumes:
- db_data:/var/lib/mysqlData lives in a named volume. Its name carries the project prefix (<project>_db_data), so don’t rename the stack directory without name: (The compose project name).
tmpfs:
- /tmp:size=128MTemporary tables (sorts, GROUP BY) live in memory, not on disk.
environment:
MARIADB_ROOT_PASSWORD: ${MARIADB_ROOT_PASSWORD}
MARIADB_DATABASE: wordpress
MARIADB_USER: wp
MARIADB_PASSWORD: ${MARIADB_PASSWORD}These work only on the first start: they create the database and users while the data directory is empty. Changing them later doesn’t change the password — that’s what ALTER USER is for (below). The image also understands the old MYSQL_* names. Values go into .env (600) or through *_FILE and a Docker secret (Secrets in Docker stacks).
volumes:
db_data:What’s not in command, and why
| Parameter | What it does | Why it’s gone |
|---|---|---|
--innodb-doublewrite=0 |
turns off the doublewrite buffer | doublewrite protects against torn pages if a crash hits mid-write. On SSD it costs next to nothing; turn it off only on a slow HDD and knowing the risk |
--innodb-use-native-aio=OFF |
turns off OS async I/O | helped on an overloaded HDD; on SSD it stays at the default (on) |
--innodb-flush-method=O_DIRECT |
writes bypass the OS cache | deprecated in MariaDB 11+; O_DIRECT is the default since 10.6 anyway |
--innodb-file-per-table=1 |
one file per table | deprecated in MariaDB 11+; on by default anyway |
Deprecated parameters log Warning: … is deprecated and will be removed in a future release. Remove them before
upgrading the image: the version that drops them won’t start with them.
Dump, password change, waiting for readiness
A dump without the password on the command line — it comes from the container’s environment:
docker exec db sh -c 'exec mariadb-dump -u root -p"$MARIADB_ROOT_PASSWORD" --single-transaction wordpress' | gzip > wordpress_$(date +%F).sql.gz
--single-transaction gives a consistent InnoDB snapshot without locking tables.
Changing the root password without printing it:
cd /opt/db
new=$(openssl rand -hex 20)
docker exec -e NEW="$new" db sh -c 'mariadb -u root -p"$MARIADB_ROOT_PASSWORD" -e "ALTER USER IF EXISTS root@localhost IDENTIFIED BY \"$NEW\"; ALTER USER IF EXISTS root@\"%\" IDENTIFIED BY \"$NEW\";"' \
&& sed -i "s/^MARIADB_ROOT_PASSWORD=.*/MARIADB_ROOT_PASSWORD='$new'/" .env && echo "password changed, .env updated"
unset new
docker compose up -d # recreate so the container has the new password
The && is there so .env changes only if the database accepted the new password: otherwise the two diverge.
Waiting for readiness after a restart:
for i in $(seq 1 30); do
docker exec db sh -c 'mariadb -u root -p"$MARIADB_ROOT_PASSWORD" -e "SELECT 1"' >/dev/null 2>&1 && { echo "database ready"; break; }
sleep 5
done
docker logs db --since 2m 2>&1 | grep -iE 'warn|error|recovery|ready'
For other hardware and other jobs
| Situation | What to change |
|---|---|
| slow HDD, database is sluggish | try --innodb-use-native-aio=OFF and a smaller innodb-log-file-size; doublewrite=0 only deliberately, with backups |
| SSD, little memory | the config above as is |
| a database with money or orders | --innodb-flush-log-at-trx-commit=1 and the binary log — for point-in-time recovery |
| several apps | a separate user and database for each; root only for maintenance |
If it doesn’t work
ERROR 2002 … Can't connect to local server through socket right after up -d
- Cause
- The database is still starting.
- Fix
- Wait — the loop in “Dump, password change, waiting for readiness” — and look for
ready for connectionsin the log.
ERROR 1045 Access denied after a password change
- Cause
- The password in the database and the one in
.envno longer match. - Fix
- Don’t touch anything else: find the step where they diverged — did the database password change? — and make them match again.
crash recovery in the log after a restart
- Cause
- The previous stop wasn’t clean: the database didn’t make it within the timeout, and Docker killed it.
- Fix
- Set
stop_grace_periodand check integrity:mariadb-check.
deprecated in the log
- Cause
- There’s a parameter in
commandthat a future version will remove. - Fix
- Remove it before upgrading the image: the version that drops it won’t start with it.
Changed the app user’s password — the site can’t connect to the database
- Cause
- The password is written in two
.envfiles:MARIADB_PASSWORDfor the database,WORDPRESS_DB_PASSWORDfor WordPress. - Fix
- Change both and run
ALTER USERfor that user.
command still holds settings for different hardware
- Cause
- The stack moved from HDD to SSD, but the HDD “rescue” parameters stayed. Comments in the file don’t know about the move.
- Fix
- Go by where the database runs now — see the table “For other hardware and other jobs”.
The root password ended up in shell history or a chat
- Cause
- Everyone with access to the history or the conversation has seen it.
- Fix
- Change it without printing it — the command is in “Dump, password change, waiting for readiness”.
How to check
docker logs db 2>&1 | grep -iE 'deprecated|recovery' # empty
docker inspect db --format '{{.Config.StopTimeout}}' # 120 — stop_grace_period took effect