Recipe: MySQL

Root vs app user, init scripts, secrets.

A database server is a building full of locked rooms. The root account is the master key: it opens every room, and it opens the caretaker's office where rooms get created and destroyed. Your application does not need the master key. It needs one key that opens one door. MySQL builds that key for you, and so does MariaDB, which grew out of the same code and still takes the same startup variables, though the two have drifted far enough apart that you cannot point a MariaDB image at a data directory MySQL 8 wrote. Both read a handful of environment variables (settings you hand a process when it starts) on the very first boot and cut the keys from them. The official image will also read passwords out of files rather than out of plain variables, so the values never turn up in docker inspect or in your shell history. Get the key model right and the rest of this recipe is a named volume and a healthcheck.

services:
  db:
    image: mysql:8.4
    environment:
      MYSQL_ROOT_PASSWORD_FILE: /run/secrets/mysql_root_pw
      MYSQL_ROOT_HOST: localhost             # else root@'%' is created too
      MYSQL_DATABASE: payments               # created on first run
      MYSQL_USER: app                        # a non-root app user...
      MYSQL_PASSWORD_FILE: /run/secrets/mysql_app_pw   # ...with its own password
    secrets: [mysql_root_pw, mysql_app_pw]
    volumes:
      - "mysqldata:/var/lib/mysql"
      - "./initdb:/docker-entrypoint-initdb.d:ro"   # 02-grants.sql lives here
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s
volumes: { mysqldata: {} }
secrets:
  mysql_root_pw: { file: ./secrets/mysql_root_pw.txt }
  mysql_app_pw:  { file: ./secrets/mysql_app_pw.txt }

$ docker compose up -d
[+] Running 3/3
 ✔ Network payments_default    Created
 ✔ Volume payments_mysqldata   Created
 ✔ Container payments-db-1      Started
$ docker compose ps
NAME            IMAGE       STATUS
payments-db-1   mysql:8.4   Up 8 seconds (health: starting)
$ docker compose ps
NAME            IMAGE       STATUS
payments-db-1   mysql:8.4   Up 51 seconds (healthy)

Look closely at what the healthcheck does. It runs mysqladmin ping against 127.0.0.1, the container's own loopback address, instead of against the Unix socket, which is a special file on disk that lets programs on the same machine talk without touching the network at all. Knocking on a network port tells you more than knocking on the side door, because it proves the network listener is up and not only the socket. Be honest about how far that goes: your app arrives on the container's own network address under the name db, so a server set to bind only to loopback would still answer this ping while refusing every client on the network. Up (healthy) means the server answered that knock. First boot is slow, because the server has to build the data directory from nothing, so the first reading above still says health: starting and only the second one, most of a minute later, says healthy. start_period is the grace window: pings that fail inside it do not burn any of the five retries. One thing worth carrying around: ping answers even when the client's password is wrong. It proves the server is up and answering, which is why its exit status is 0 even on Access denied. It says nothing about whether a particular login works. Proving a login is what the verify step below is for.

Root stays inside, the app gets one door

MYSQL_USER and MYSQL_PASSWORD create an application account with rights on MYSQL_DATABASE and nowhere else. That is the account that belongs in your app's connection string. MYSQL_ROOT_PASSWORD sets the master key, and the master key is for humans and scheduled jobs running migrations and backups. Never for the app. Root does not stay inside on its own, though. Left alone, the entrypoint reads MYSQL_ROOT_HOST as %, so it creates root@'%' next to root@'localhost', with every privilege on every database and the right to hand those out again. Anything on the same network can then knock on that account and start guessing. The MYSQL_ROOT_HOST: localhost line in the compose file above is what makes the heading of this section true: root becomes an account you can only use from a shell inside the container, which is where administration belongs anyway. On MariaDB the variables are spelled MARIADB_USER and friends, though that image still honors the MYSQL_ names as aliases. If full rights on one database is still more power than your app needs, drop a SQL (Structured Query Language, the language databases take their orders in) file into the image's init directory and grant exactly the statements your code issues.

-- runs once on first startup, as root; tighten the app user beyond the default grant
REVOKE ALL PRIVILEGES ON payments.* FROM 'app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON payments.* TO 'app'@'%';
-- no FLUSH PRIVILEGES here: GRANT and REVOKE reload the in-memory grant tables
-- on their own. The flush is only for hand-edits to the mysql.* tables.
 
-- app has no CREATE left, so the schema is built here, while root still can.
-- One table rides along in this file; a real schema earns its own 01-schema.sql.
CREATE TABLE payments.ledger (id INT PRIMARY KEY, note VARCHAR(64));

Out of the box, the image hands the app user ALL PRIVILEGES on its own database, and that bundle includes DROP TABLE, ALTER, and CREATE. A payments service that reads and writes rows has no business dropping tables. The script above takes the broad grant back and returns the four statements the code actually runs. The difference shows up on your worst day. A SQL injection bug that reaches this connection can now scramble rows, which is bad and costs you a restore from backup. It cannot drop the schema or wander into another database on the same server, which is the kind of bad you write a public incident report about.

Four verbs is where a grant starts, not where it ends. The first real deploy is where you learn that your ORM (object-relational mapper, the library that turns objects into SQL statements) wants CREATE TEMPORARY TABLES for a join it builds behind your back, that a nightly mysqldump run as this account needs LOCK TABLES on top of SELECT, and that calling a stored procedure needs EXECUTE. Expect one or two of those, and add each one deliberately, a line at a time, with the error that demanded it written down beside it. What you must not do is hand ALL PRIVILEGES back the first time something says access denied, because that undoes this whole section in one statement.

Here is the detail that catches people in production. Everything the image bootstraps from those variables, the database, the accounts, the init scripts, happens on exactly one condition: /var/lib/mysql is empty. First start only. If the named volume already holds data and you edit MYSQL_PASSWORD in the compose file and bring the stack back up, nothing changes. The account keeps its old password. The init scripts do not rerun. You get an access-denied error that makes no sense at all until you remember the rule. Rotating a credential on a volume that already has data in it is an ALTER USER statement you type yourself, not an environment edit.

Prove the scoped user really is scoped

A grant you have never looked at is a grant you are trusting on faith. So log in as the app user and ask MySQL to its face what it is allowed to do. Write a row, read it back, confirm the account works at all. The ledger table is already sitting there, built by the init script while the server was still doing its own bootstrap as root. The app user could not create it now even if it tried, which is exactly the point. Then reach for something only an administrator should touch and watch the server shut the door. One line of that output looks alarming and is not: SHOW GRANTS always opens with GRANT USAGE ON *.*, which is how MySQL writes "this account exists and holds no server-wide privileges". It is the absence of a grant, not a grant on everything. Passing a password on the command line makes mysql print a warning to stderr (standard error, the side channel a command uses for complaints). That warning is expected here. In a real script you would let the container read its own secret, which is what the last command below does: MYSQL_PWD is the variable the client takes a password from, and the cat runs inside the container, so the value never reaches your terminal or your history and the warning goes away. For anything long-lived, a client config file the container reads is better still.

$ docker compose exec db \
    mysql -uapp -p"$(cat secrets/mysql_app_pw.txt)" payments \
    -e "SHOW GRANTS;
        INSERT INTO ledger VALUES (1,'first payment');
        SELECT * FROM ledger;"
mysql: [Warning] Using a password on the command line interface can be insecure.
Grants for app@%
GRANT USAGE ON *.* TO `app`@`%`
GRANT SELECT, INSERT, UPDATE, DELETE ON `payments`.* TO `app`@`%`
id	note
1	first payment

$ docker compose exec db \
    mysql -uapp -p"$(cat secrets/mysql_app_pw.txt)" \
    -e "CREATE DATABASE hostile;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'app'@'%' to database 'hostile'
$ docker compose down
[+] Running 2/2
 ✔ Container payments-db-1   Removed
 ✔ Network payments_default  Removed
$ docker compose up -d --wait
[+] Running 2/2
 ✔ Network payments_default   Created
 ✔ Container payments-db-1     Healthy
$ docker compose exec db sh -c 'MYSQL_PWD=$(cat /run/secrets/mysql_app_pw) \
    mysql -uapp payments -e "SELECT COUNT(*) AS n FROM ledger;"'
n
1

Two things happened there. The app user tried to create a brand new database and the server refused, which is the exact wall you want standing around a scoped account. Then the whole stack came down and went straight back up, and the row from a minute ago was still sitting in ledger. That is the named volume earning its keep. docker compose down leaves volumes alone unless you add -v, and the rows live in the volume rather than in the container, so throwing the container away and building a fresh one costs you nothing.

Accounts, init scripts and secrets

The official MySQL image sets a root password on the first boot of an empty volume, and creates an app database and app user too if you asked for them. Every password variable has a _FILE twin that reads the value out of a mounted file instead, which is what keeps it out of docker inspect and out of your shell history. Files in /docker-entrypoint-initdb.d run in plain alphabetical order, which is the only reason the grants file above is named 02-grants.sql: the leading number keeps the 01 slot free for a schema file, which is where the CREATE TABLE moves once there is more than one of them. With a single table it rides along in the grants file instead, which is why you saw it there. That sort is on text rather than on value, so 10-late.sql would run before 2-early.sql. For the healthcheck, mysqladmin ping is the cheap option and a one-line query such as SELECT 1 is the stricter one, because it proves the server will run statements rather than merely answer the door.

One thing here catches drivers rather than people, and it did not start with 8.4. Since 8.0 the server has created accounts with caching_sha2_password, the newer password-checking plugin, so an image on any 8.x tag already does this to an old client. What 8.4 changed is the way out: mysql_native_password, the older plugin, is no longer switched on at startup, and the --default-authentication-plugin option that used to pick it has been removed. Connect from the mysql client inside the container and it works. Connect with a years-old library and the same username and password come back as an authentication error, usually one naming a plugin the client cannot load. Update the driver first. Starting the server with --mysql-native-password=ON gets you moving again, but treat that as a stay of execution, because MySQL 9 removed the plugin outright.

Init scripts and migration tools such as Flyway or Liquibase pull in different directions. An init script is the cheapest way to get a schema onto an empty volume, and it runs exactly once, so it can never be your answer to the second change. A migration tool keeps every change in its own numbered file and records which ones it has applied, which is what lets a fresh environment catch up to the schema you already have. Use init for the first boot and for seed data, migrations for everything after.

MySQL listens on port 3306, and there is rarely a reason to publish it to the host at all. Leave it unpublished and let the app reach the database over the user-defined network by name, which in this recipe means the hostname db, the name you gave the service, not mysql, the name of the image.

Plan the version jump before you need it. Going from 8.0 to 8.4 upgrades the data directory the first time the new server starts, and there is no supported way to put it back, so the road back to 8.0 runs through a dump you took beforehand. mysqldump is what takes it, while the old server is still running. What it writes is a logical backup, meaning a text file of the SQL statements that rebuild your data rather than a byte copy of /var/lib/mysql, and being plain SQL is exactly what lets it load into a server of a different version.

When you run this against a real environment, write down the exact ALTER USER or GRANT you issued and the image tag you issued it against. A short paper trail beats memory when somebody asks at midnight why app@% lost SELECT. Keep the SHOW GRANTS output from before and after in the same note, because that is the thing that settles the argument. And read the host part of the account name every time: 'app'@'%' and 'app'@'localhost' are two separate accounts as far as MySQL is concerned, so rotating the password on one leaves the other exactly as it was.

Try this

Run this on a lab engine you can throw away. There is no version floor worth quoting, because every flag below has been in Docker for years. Plain docker run has no secrets of its own, so the two password files get bind-mounted to the paths the _FILE variables name, which is the same job compose secrets do with less typing. The init directory comes along too, because the point of the exercise is to watch a scoped account get told no. The health flags spell out the same budget the compose file used, thirty seconds of grace and then five tries ten seconds apart, because docker run on its own allows three tries, and a first boot that takes most of a minute would be marked unhealthy before it ever finished. Read the sample output first, so you know what a good result looks like before you lean on the command somewhere that matters. The last three commands tear the lab down, volume included, and that part matters: leave the volume behind and the next run bootstraps nothing at all, because the data directory will not be empty.

$ docker network create appnet
$ docker volume create mysqldata
$ mkdir -p secrets initdb
$ openssl rand -base64 24 > secrets/mysql_root.txt
$ openssl rand -base64 24 > secrets/mysql_app.txt
$ cat > initdb/02-grants.sql <<'SQL'
REVOKE ALL PRIVILEGES ON app.* FROM 'app'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON app.* TO 'app'@'%';
SQL
$ docker run -d --name db --network appnet -v mysqldata:/var/lib/mysql \
    -v "$PWD/initdb:/docker-entrypoint-initdb.d:ro" \
    -v "$PWD/secrets/mysql_root.txt:/run/secrets/mysql_root:ro" \
    -v "$PWD/secrets/mysql_app.txt:/run/secrets/mysql_app:ro" \
    -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql_root \
    -e MYSQL_DATABASE=app -e MYSQL_USER=app \
    -e MYSQL_PASSWORD_FILE=/run/secrets/mysql_app \
    -e MYSQL_ROOT_HOST=localhost \
    --health-cmd='mysqladmin ping -h 127.0.0.1' \
    --health-start-period=30s --health-interval=10s --health-retries=5 mysql:8.4
$ docker inspect db --format '{{.State.Health.Status}}'
starting          # first boot is still building the data directory
$ docker inspect db --format '{{.State.Health.Status}}'
healthy
$ docker exec db \
    mysql -uapp -p"$(cat secrets/mysql_app.txt)" -e "SHOW GRANTS;"
mysql: [Warning] Using a password on the command line interface can be insecure.
Grants for app@%
GRANT USAGE ON *.* TO `app`@`%`
GRANT SELECT, INSERT, UPDATE, DELETE ON `app`.* TO `app`@`%`
$ docker exec db \
    mysql -uapp -p"$(cat secrets/mysql_app.txt)" -e "CREATE DATABASE hostile;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000): Access denied for user 'app'@'%' to database 'hostile'
$ docker rm -f db
db
$ docker volume rm mysqldata
mysqldata
$ docker network rm appnet
appnet

Takeaway

Go read the connection string your app is using right now. If the username on it is root, none of the rest of this hardening counts for anything. Then log in as an administrator and run SELECT user, host FROM mysql.user, because that table, and not your compose file, is the honest list of who can reach this server and from where.

Related