Outside of my standard docker-compose setup for Laravel, I find myself requiring one-off MySQL instances for short pieces of development, debugging or testing. Instead of building a whole container ecosystem to do this, if I can get away with it I simply run MySQL as a docker container, binding it to the local machine’s ports.
The MySQL docker container is available on docker hub https://hub.docker.com/_/mysql which provides some basic usage instructions. I’m going to outline how I use MySQL in docker for this one off work.
Almost everything I do is based on the older MySQL 5.7. That means I need to use that image specifically when running docker. In order to make sure my local container is up to date and ready and waiting for use I tend to run a docker pull command on my dev machine as soon as it’s setup.
docker pull mysql:5.7
Version information is available on the MySQL docker hub page if you need a different version of MySQL. Now that the docker image is held locally it’s much easier to just start up a container whenever you need it and it will not spend ages re-downloading.
Understanding the Basics of MySQL’s Docker Container
There’s a few things I do with my containers. Firstly I want to expose the container to the host machine. This allows database administration tools like MySQL Workbench or Datagrip to connect to the MySQL docker instance. It also allows code to talk to it, and often this is what I want to do. It’s important not to overlap these ports, but generally I don’t run a temporary MySQL container along side any development stacks or local installs so I bind to the default port (3306). To do this I add the -p 3306:3306 flag to the command. If you want to change the external port (the one you’re using to connect to MySQL inside docker), then change the port number before the colon (:) like so -p 1234:3306. This maps port 1234 on your machine to the docker containers port 3306 inside the container.
Next, a root password and default database should be created. You could skip database creation and do it later with the management tool of your choice, but I find this easier. There’s two environment variables to set and I usually pick a short, insecure password for MySQL’s root account as this is only a test locally and firewalled on my dev machine. -e MYSQL_ROOT_PASSWORD=toor sets the root password to “toor” (root backwards. This was a default on a few Linux distros for a while). Setting the default database is just as easy -e MYSQL_DATABASE=dev. In this case it’s creating a database called “dev”.
Finally, I tend to name the docker container so I can run it again easily if required. I do this long hand with --name mysql57 where “mysql57” is the name of the container I’m creating. You can name this per project if it makes more sense for you, but I do regularly delete and recreate this container as it’s outside my usual dev workflow and usually just for debugging/fixing something once.
Creating a Named MySQL Docker Container Exposed to the Host
Rolling it all together you can run this command to create a named MySQL 5.7 instance that is running in the background (-d).
docker run --name mysql57 -e MYSQL_ROOT_PASSWORD=toor -e MYSQL_DATABASE=dev -p 3306:3306 -d mysql:5.7
Restore Backups to a MySQL Docker Container
If you have a database backup you need to restore, then it’s reasonably easy to pass it into MySQL, although if it’s a big database then it can take some time to do. This can be done by using cat to read the backup file and feeding it into the MySQL docker container. If you’re a user who doesn’t have native docker permissions (like on Ubuntu, which requires sudo docker) then it may be best to change to a user that does have permissions (sudo -i to switch to root, then run the backup restore command).
cat database_backup.sql | docker exec -i mysql57 /usr/bin/mysql -u root --password=toor dev
Backing up a Database Inside MySQL’s Docker Container
If you need to backup your MySQL docker database from the container, you can do so by running the mysqldump command that the container has installed by default, passing it container name, username, password and database you’ve defined when creating the container and defining the output file to save the database dump to.
docker exec mysql57 /usr/bin/mysqldump -u root --password=toor dev > dev_backup.sql
Cleaning up
Once you’re done with your MySQL container, you can stop and delete it by running the following commands, making sure to replace the container name (“mysql57”) with the name of your container if you happened to change it:
docker stop mysql57 docker rm mysql57
That’s it! You’ve created a named docker container running MySQL 5.7. You’ve exposed it to the host machine using port binding and learned how to restore a database backup to it. It’s not as useful as a full docker-compose stack for development. If you’re interested in a docker-compose dev environment check out this article. It does, however give you quick and easy MySQL access when you just need to poke around a database.