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).
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
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
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
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
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.