Create a MySQL Database using Docker

MySQL is an open-source relational database owned by Oracle Corporation. The instructions I'm going to share in this article demonstrate how to install and set up a MySQL server along with the popular phpMyAdmin management application. As a bonus, I will also show you how to set up Python to access your MySQL database.

This article is an excerpt from my book SQLAlchemy 2 In Practice, where I show how to work with relational databases using Python and the SQLAlchemy library.

Installing MySQL with Docker

The easiest way to get a SQL database up and running is to use Docker and Docker Compose. If you would like to follow the instructions below to install MySQL, first install Docker Desktop.

Copy the following definitions to a file named docker-compose.yml in your project directory:

version: '3'

services:
  db:
    image: mysql
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: changethis!
    ports:
      - "3306:3306"
    volumes:
      - db-data:/var/lib/mysql
  admin:
    image: phpmyadmin
    restart: always
    environment:
      - PMA_ARBITRARY=1
    ports:
      - 8080:80
volumes:
  db-data:

This Docker Compose configuration file starts a service called db that runs a MySQL server connected to port 3306 of your computer, plus a second service called admin that runs phpMyAdmin on port 8080. The database storage is configured on a separate volume called db-data, to make it possible to upgrade the database container without losing data.

Note the MYSQL_ROOT_PASSWORD line, which has the value changethis!. This line defines the administrator password for the MySQL server. Edit this line to set a secure password of your liking.

Once you have this file saved in your project directory, return to the terminal and run the following command to start your MySQL server:

$ docker-compose up -d

The first time you run this command it will take a while, as Docker has to download the MySQL and phpMyAdmin container images from the Docker Hub repository. Once the images are downloaded, it should take a few seconds for the containers to be launched, and at that point MySQL should be deployed on your computer and ready to be used.

You can open the phpMyAdmin database management tool by typing http://localhost:8080 in the address bar of your web browser.

To log in, enter the following credentials:

• Server: db
• Username: root
• Password: the root password that you entered in the docker-compose.yml file

Once you access the phpMyAdmin interface, click on the "Databases" tab. Near the top you should see the "Create Database" section.

Enter a name for the new database, for example retrofun, and click the "Create" button.

A good practice when creating a new database is to also define a user specifically assigned to it. Using the root database user for day-to-day operations is too risky, because this account is too powerful and should only be used for important administration tasks.

Click on the "Privileges" tab for the new database. Near the bottom of the page there is a section titled "New" with an "Add user account" link. Click it to create a new user.

For the username you can choose any name that you like, but a naming convention that I find useful is to use the same name for the database and the user, so in this case it would be retrofun. Leave the host set to "%", then enter a password for the new user.

Confirm that the "Grant all privileges on database retrofun" option is enabled, and then scroll all the way to the bottom of the page and click the "Go" button to create the user. This user will have full access to the database, but it will not be able to access or create other databases, which is a good security principle to follow.

From now on, you can log in to phpMyAdmin using the user you just created, and your view of the database server will be constrained to only what's relevant to manage this particular database.

If you'd like to stop the MySQL server, you can do so with this command, issued from the directory in which you have your docker-compose.yml file:

$ docker-compose down

To start the server again, repeat the "up" command as before:

$ docker-compose up -d

Stopping and restarting the server as shown above does not cause any data loss.

Setting up Python for MySQL

To access your MySQL database you have to install a Python client, sometimes also called driver. There are several MySQL drivers for Python that can be used here, so you should use your favorite if you have one.

If you need a recommendation, my driver of choice is pymysql, which you can install into your Python virtual environment as follows:

(venv) $ pip install pymysql cryptography

The cryptography package installed above is an optional dependency of pymysql that is needed to perform authentication against the MySQL database.

Congratulations! You now have a complete set up, including a blank MySQL database that is ready to be used. If you followed the installation procedure described above, the connection settings for your database are:

• Hostname: localhost (but use db as hostname to connect from the phpMyAdmin container)
• Port: 3306
• Database: retrofun
• Username: retrofun
• Password: the password that you selected for the user
• Python driver: pymysql

Do you want to learn how to work with this database using the popular SQLAlchemy library? Consider my SQLAlchemy 2 In Practice book!