Podman (or docker) running a MySQL container and a shared data directory

Question

I am running a Ubuntu 20.04.5 LTS virtual machine for my development environment. In the past I have run multiple VM's to host databases, web servers, etc, as I need them for projects. So in short, I am a container noob!

What I am attempting to do is to create ONE Ubuntu virtual machine running Podman containers.

My current blocker is MySQL, or any other RDB.

If I use the container default locations for storage, I will need to load a new database dump every time I start up the container, since storage is, by nature ephemeral. This is clearly not ideal.

I have read, many use cases that specify creating an attached or mounted drive/directory to store the MySQL directories so that the data does not disappear after every restart of the container. What I have NOT found is how to set that up correctly.

Again, my goal is to mount a HOST directory as the MySQL data dir.

So first, I created a shared data location with completely open permissions:

echo "STATUS: Creating data directory for MySQL container"
sudo mkdir -p /opt/mysql/server/data
sudo chmod 777 /opt/mysql -R

Then I mapped the drive and specified the data directory:

echo "STATUS: Run container with mapped host drive"
podman run -d --replace --name=MySQL -h=mysql-pod -p=63306:3306 /
      -v="/opt/mysql/server:/var/lib/mysql" docker.io/mysql/mysql-server:8.0 /
      --basedir=/var/lib/mysql --datadir=/var/lib/mysql/data

Executing this command leaves me with the following log entries:

podman:/vagrant/podman$ podman logs MySQL
[Entrypoint] MySQL Docker Image 8.0.30-1.2.9-server
2022-09-14T23:03:17.922048Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/var/lib/mysql/share/mysql-8.0/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
[Entrypoint] Initializing database
[Entrypoint] No password option specified for new database.
[Entrypoint]   A random onetime password will be generated.
mysqld: Can't create/write to file '/var/lib/mysql/data/is_writable' (OS errno 13 - Permission denied)
2022-09-14T23:03:17.954340Z 0 [Warning] [MY-011068] [Server] The syntax '--skip-host-cache' is deprecated and will be removed in a future release. Please use SET GLOBAL host_cache_size=0 instead.
2022-09-14T23:03:17.954386Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.30) initializing of server in progress as process 12
2022-09-14T23:03:17.954403Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/var/lib/mysql/share/mysql-8.0/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-09-14T23:03:17.954836Z 0 [ERROR] [MY-010460] [Server] --initialize specified but the data directory exists and is not writable. Aborting.
2022-09-14T23:03:17.954840Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/data/ is unusable. You can remove all files that the server added to it.
2022-09-14T23:03:17.954859Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-09-14T23:03:17.954918Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30)  MySQL Community Server - GPL.

I have tried multiple permission sets, creating a mysql user on the host and giving it the directory ownership... and a bunch of other things, because I can't find a good explanation of how to do this correctly.

Again, I am a container noob so I am pretty sure I am missing something regarding the shared resource permissions, but I have been through a hundred iterations of mapping and permissions and have not found anything online that addresses this with a non-root OCI like Podman.

AND maybe the answer is that I have to enforce a root account for Podman, but I figured I would give a shout out to the community before I threw my laptop out the window.

Thanks in Adv.

G...C

UnKulMunki

Answer 1

If I use the container default locations for storage, I will need to load a new database dump every time I start up the container, since storage is, by nature ephemeral.

It is true that your container's main filesystem is ephemeral and you shouldn't store data there. However, that isn't your only option for storage.

Container runtimes like Docker and Podman support the concept of "volumes" -- named (or anonymous) storage that is managed by the container runtime, mounted in your container when they run, and separate from the container filesystem. Volumes have a lifetime that is independent of the associated container.

All major database images are already configured to store database data on a volume. For example, if you run podman volume ls before running the postgres image:

$ podman volume ls
<no output here>

Then start (and stop) a postgres container:

$ podman run --rm -e POSTGRES_PASSWORD=secret docker.io/postgres:14

And then inspect the list of volumes again:

$ podman volume ls
DRIVER      VOLUME NAME
local       7b33982f61ec36601371803f9f4dcca66dc0a326cad1b2260ab30136b7ef6b40

You'll see that a volume has been created, and that it persists despite the container having been removed. This is an "anonymous" volume: it hasn't been assigned an explicit name, which means it's not particularly useful (you can refer to it using that long volume id, but that's not really meant to be how you interact with volumes). To make something easier to manage, you can create named volumes and use those instead. For example:

$ podman run --rm -v postgres_data:/var/lib/postgres -e POSTGRES_PASSWORD=secret postgres:14

This creates a volume named postgres_data and mounts it at the appropriate point in the container. If you stop the container, you can create a new one with the same volume argument and it will have access to the same data: your database will continue to serve the same data, despite running a new container.

---

It is also possible to create "bind mounts" -- that is, mounting a specific host directory into the container. That's what you're doing when you provide a filesystem path as the first argument to -v, as in:

podman run -v "/opt/postgres/server:/var/lib/postgres" docker.io/postgres:14

This can be useful if you need to share data between your host and the container, but for something like a database, where the underlying storage is effectively a "black box" (the only way you access it is through the database server), volumes are typically going to be easier to manage.

If you wanted to start your MySQL container with podman using a bind mount for storage, you need to ensure that the directory is writable by the mysql user in the container.

You created a directory like this:

sudo mkdir -p /opt/mysql/server/data
sudo chmod 777 /opt/mysql -R

This looks like it should work because the data directory is world writable, but there are two problems:

• Startup scripts in the MySQL container will attempt to change the owner of the data directory. Since we don't have permissions to do that, our container won't have that permission, either.

• Even if that weren't a problem, a mandatory access control system like selinux could still get in the way.

If we own the data directory, things will work better:

sudo mkdir /opt/mysql
sudo chown $UID:$UID /opt/mysql
mkdir -p /opt/mysql/server/data
podman run -d --replace --name=MySQL -p 63306:3306 \
      -v "/opt/mysql/server/data:/var/lib/mysql" \
      docker.io/mysql/mysql-server:8.0

If we're running selinux, we would need to modify the -v option to include the :z qualifier, like this:

      -v "/opt/mysql/server/data:/var/lib/mysql:z" \

We can simplify things a little bit by just using subdirectories of our home directory rather than create directories under /opt:

mkdir -p $HOME/data/mysql
podman run -d --replace --name=MySQL -p 63306:3306 \
      -v "$HOME/data/mysql:/var/lib/mysql:z" \
      docker.io/mysql/mysql-server:8.0

But using volumes, as I indicated earlier, eliminates most of this mess because the container runtime is responsible for allocating and managing the storage:

podman run -d --replace --name=MySQL -p 63306:3306 \
      -v mysql_data:/var/lib/mysql \
      docker.io/mysql/mysql-server:8.0

You can read more about these topics in Docker's "Manage data in Docker" documentation, and in the related "Use volumes" and "Use bind mounts" documentation. The material here applies equally well to Podman.