Managing a MySQL database for personal projects

Initializing, backing up and recovering a MySQL database using Docker

So you built a cool project that uses a MySQL database, now what? How do you manage it in a personal/home setting? You don't need a full enterprise setup, but still care about your data, and want to be able to recover it if something goes wrong.

This note discusses a simple procedure for setting up a MySQL database, exporting the data, and recovering it, using Docker. It assumes you have terminal access (either through a desktop interface or through ssh) to a Linux machine.

1. Setting it up

The ideal setup consists of single configuration file. This provides a single place for defining the setup, and enables you to spin up an instance using just a single command. That is exactly what docker and the docker-compose extension provides.

If you don't have the docker engine and docker-compose installed on your machine, follow the guides provided at: https://docs.docker.com/desktop/linux/install/. We'll use a standard docker-compose.yml (be sure to save it using that filename) file provided by MySQL:

version: '3.1'
services:
  db:
    image: mysql
    # (this is just an example, not intended to be a production configuration)
    command: --default-authentication-plugin=mysql_native_password
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: choose_your_password_wisely
      MYSQL_USER: main_user_name
      MYSQL_PASSWORD: main_user_password
    ports:
      - 3306:3306
  adminer:
    image: adminer
    restart: always
    ports:
      - 8080:8080

Note that you will want to change to the three environment variables. This docker-compose file starts two services: a mysql database on port 3306 and an adminer instance on port 8080.

Note that you’ll have to change the external port (one the left side of the “:”) if you already have services running on those ports (which you can check using the netstat -ntlp command).

1.1 Adminer

The adminer instance is a webserver that you can log into (at http://localhost:8080 if you are directly connected to the server, otherwise replace localhost with the server’s ip address, which you can find using the hostname -I command). This is a useful alternative to the command line for interacting with the server. If you are comfortable with the command line, feel free to remove this section from the config file.

1.2 Spinning it up

Having saved the file as docker-compose.yml, you can then run:

docker-compose up -d

You should now be able to login to the MySQL prompt using:

mysql -u <main_user_name> -h <my_hostname> -P 3306 -p

which will then prompt for the password you set up in the MYSQL_PASSWORD environment variable. Don’t forget to substitute the and variables appropriately. Note that as the database is running in a container, you’ll want to use either the appropriate docker network address or (much easier) the server’s ip address on the local network, which can be found using the hostname -I command.

You can now use the database in your application, creating database, tables and inserting/extracting data. Hurray!

2. Exporting Data

To perform a backup, we start by exporting the data. However, as the data is inside a docker container, we’ll also have to extract it from the container. Fortunately, we can combine that into one command:

docker exec mysqldump -u <main_user_name> -p<main_user_password> — databases <database_name> > <database_export.txt>

Again, remember to insert the correct username, password and database name. Note that there indeed is no space between -p and the , this is not a typo. This command creates a file with the name you substituted for which contains all the data in that database. This file is human readable.

Ideally, one would run this export procedure on a regular schedule (for example using cron) and then backing it either to another drive or even another server.

3. Importing the data

After having setup up your backup system, you should practice recovering from a backup. For this, I recommend setting up a second database.

3.1 Spinning up a second instance

To spin up a second instance, you can simply copy the docker-compose.yml file, and place it another folder. Note that if you want the second instance to run at the same time as your main database, you’ll have to change the port numbers (3036:3036 to 3037:3036 and 8080:8080 to 8081:8080, for example).

When you have a fresh container, first create the (empty) database, by logging in, and using CREATE DATABASE ;. Then, from outside the docker container, in the same folder as the exported database, run:

docker exec -i <container_name> mysql -u <main_user_name> -p<main_user_password> <database_name> < <database_export.sql>

replacing all the terms between brackets with the versions appropriate to you. That’s it. You can log into to your mysql to confirm that it works, by running USE ; and then SHOW TABLES; to confirm all you data is there (or using the adminer instance).

Conclusion

From this guide, you have learned how to spin up a MySQL database using Docker, export data from it, and import it into a second instance.

I highly recommend trying out this entire workflow, from spinning up, to exporting, to importing. Then you’ll be confident that you can recover your data, regardless of the state of the database. I also recommend automating the export step, so you ensure you have a recent backup of your data.

Fun fact: while verifying that all these commands work, the author accidentally dropped the production database. Fortunately, there was a backup available and no data was lost. I couldn't make a better case for it if I tried.