Running a local MySQL instance with Apptainer

In case you need a local MySQL instance running on a Pony, e.g. if you have a MySQL dump file and you want to change/remove data before further processing, you can do that with Apptainer.

Preparation

First, create an empty directory. I suggest you use the local disk /scratch so that the MySQL process does not have go over the network:

mkdir /scratch/<username>/mysql-apptainer
cd /scratch/<username>/mysql-apptainer

Replace <username> with your username.

Next, create a file that contains an initial MySQL command that sets the root password:

echo "SET PASSWORD FOR 'root'@'localhost' = '<root_password>';" > .mysqlrootpw

Replace <root_password> with something more secure.

Create the directory tree for the MySQL daemon to put its data in:

mkdir -p ./mysql/var/lib/mysql/ ./mysql/run/mysqld

The Apptainer container

Create a new container instance by pulling the MySQL-Docker image:

apptainer pull --name mysql.simg docker://mysql

and start it:

apptainer instance start --bind ${PWD} --bind ${PWD}/mysql/var/lib/mysql/:/var/lib/mysql --bind ${PWD}/mysql/run/mysqld:/run/mysqld ./mysql.simg mysql

--bind makes sure that some directories in the current directory are also available in the container.

Initialize MySQL:

apptainer exec instance://mysql mysqld --initialize --init-file=${PWD}/.mysqlrootpw

and run the daemon:

apptainer exec instance://mysql mysqld --init-file=${PWD}/.mysqlrootpw

Connecting to the MySQL instance

Normally the MysQL client would connect to the local MySQL instance using a socket located at /var/run/mysqld/mysqld.sock. Of course, this socket does not exist. Instead, you can connect using the socket at mysql/run/mysqld/mysqld.sock:

mysql -S mysql/run/mysqld/mysqld.sock -u root -p

After entering the root password you chose earlier, you can now set up the database as you wish, e.g. with CREATE DATABASE, CREATE USER, GRANT PRIVILEGES etc.