basforge

Search titles, text and the “If it doesn’t work” cases.

    RU

    MariaDB in a container: compose, explained

    A compose file for a small database behind one or two sites on a small server: it takes little memory and has none of the settings that trade data safety for speed. Plus a dump and a root password change without the password on the command line.

    The compose file, explained

    docker-compose.yaml
    services:
      db:
        image: mariadb:12.3
        container_name: db

    Pin 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: 512M

    The container’s memory limit: the InnoDB buffer plus connections and overhead.

        stop_grace_period: 2m
    Key part

    How 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-bin

    The 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=96M

    The 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=2

    1 (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=64M

    Redo 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_ci

    utf8mb4 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/mysql

    Data 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=128M

    Temporary 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 connections in the log.

    ERROR 1045 Access denied after a password change

    Cause
    The password in the database and the one in .env no 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_period and check integrity: mariadb-check.

    deprecated in the log

    Cause
    There’s a parameter in command that 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 .env files: MARIADB_PASSWORD for the database, WORDPRESS_DB_PASSWORD for WordPress.
    Fix
    Change both and run ALTER USER for 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