Dogfooding it, Pt 2 - MySQL

So it's technically going to be Percona Server. No real reason, it's just what I've been using for a long time. Could just as easily go MySQL 8. First, though, there's a few other things we have to take care of.

Number one: Set up an alias to generate strong passwords.

echo alias pwgen=\'cat /dev/urandom | tr -cd [:graph:] | tr -d '\\' | fold -w 24 | head -n 1\' >> ~/.bashrc
$ source ~/.bashrc

The command creates an alias "pwgen" that reads from /dev/urandom, removes all non-printable characters and whitespace from the resulting read, forces the line length to 24 characters, and outputs the first line found, resulting in a random 24-character password. This will see quite a bit of use.

Second, we need to make use of this feature to generate the root password for MySQL and store it in a Docker secret.

$ echo -n $(pwgen) | docker secret create percona-root -

This creates a secret named percona-root and stores it in Docker's internal storage. It can be made available to containers, but not easily extracted outside the context of a container with permission.

Third, the persistent storage location for the database needs to be set up.

$ mkdir -p /srv/data/docker/percona/{data,config,initdb}
$ sudo chown -R 1001:1001 /srv/data/docker/percona
$ sudo chmod -R g-rwx,o-rwx /srv/data/docker/percona

The image defaults to running the database server as UID:GID 1001:1001, noone else has access to that space, and we create subdirectories to hold the database itself, configuration overlays if we need them, and any initialization scripts that might be required. And now we can create our service.

$ docker service create --hostname percona --mount "type=bind","source=/srv/data/docker/percona/data","target=/var/lib/mysql" --name percona --network homelab --replicas 1 --secret percona-root -e PERCONA_TELEMETRY_DISABLE=1 -e MYSQL_ROOT_PASSWORD_FILE=/run/secrets/percona-root percona:ps-8

That's a bit of a huge command. We're creating a service with the hostname "percona", a bind mount to the directory we created earlier, the service name "percona", on the network "homelab" (from part 1), with one replica, permission to access the secret "percona-root", and set some environment variables. The image we are using is "percona," tagged with "ps-8", so we are getting the most recent release in major version 8. And that's it, the database is up and running. If you need to access it from a command line, you can do that like this:

$ docker container ps
(( find the container ID for percona ))
$ docker container exec -it <container ID> bash
$ mysql -u root --password=$(cat /run/secrets/percona-root)