Relational databases provide a structured approach for storing and organizing large volumes of data into tables consisting of rows for records, and columns for attributes. They use primary keys which are unique identifiers for records and foreign keys, to establish well defined relationships between tables.<\/p>\n\n\n\n
These databases are widely used since they are reliable, consistent, and highly adaptable to various applications. They also provide complex database queries and operations capabilities using a structured query language (SQL)<\/a>. They are backed up with a strong and mature ecosystem; with extensive tooling and community support.<\/p>\n\n\n\n MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle databases are all examples of relational databases. You can run them within virtual machines or Docker containers. This article, on the other hand, will include the steps to run MySQL, which is one of the widely used relational databases in a Docker container.<\/p>\n\n\n\n You might be wondering why one would run a complex system like a relational database in a Docker container in the first place, well hang on. There is no doubt that containers are designed primarily for stateless applications. That is to say, containers are ephemeral in nature and without proper configuration, the operational status of a container could be disrupted, resulting in data loss.<\/p>\n\n\n\n Databases, on the other hand, are stateful and require persistent storage to run optimally, especially in times of restarts and even crashes. Yes, Docker volumes are designed to persist data beyond the lifecycle of a container; however, running a relational database is fine for development and testing purposes but is not recommended for production. Instead, for production environments you should run them on standalone servers, managed database services like AWS RDS or Azure SQL database, or store data on an external volume like cloud storage or block storage (AWS EBS, Azure managed Disks, etc).<\/p>\n\n\n\n Here are some of the reasons why you might need to run a relational database with Docker in detail:<\/p>\n\n\n In this article, I will show you how to get MySQL up and running, and show you how to set it up and use MySQL On Docker for your non-production environments.<\/p>\n\n\n\n To follow along, you are required to have Docker engine installed on your machine. This tutorial uses the latest versions of Docker (28.0.1) on an Ubuntu 24.04 LTS server from when this piece was written. There may be subtle differences as time passes, and if you are using a different operating system.<\/p>\n\n\n\n Whether it’s a relational or non-relational database, or something else entirely, you need to pull a base image if you would like to run anything with Docker. In the case of a relational database like MySQL, the official Docker image from DockerHub<\/a> is maintained by the MySQL team. It is pre-configured with all dependencies to run MySQL effectively. It is also properly documented to help you quickly install and make configurations for MySQL on your host system.<\/p>\n\n\n\n Execute the following command to pull the official MySQL docker image from DockerHub:<\/p>\n\n\n\n This should pull the latest version from DockerHub. Once the MySQL image has been pulled successfully, you are expected to have the following output:<\/p>\n\n\n\n Confirm that the MySQL image is available on your machine using the following command:<\/p>\n\n\n\n You are good to go if it is listed among the Docker images available on your machine:<\/p>\n\n\n\n Networks in Docker represent a communication environment that allows containers to communicate and interact with each other in a secure manner. Regardless of what kind of image or application you are running with Docker, you should always consider creating a Docker network to ensure isolated communication between your containers.<\/p>\n\n\n\n Docker supports seven network drivers by default, you can find them on the official Docker documentation<\/a>. If other network drivers are not set, the bridge network driver is the default. Additionally, a Docker network is automatically created when you run a Docker image as a container. In our case, we will create a custom docker network using the bridge network driver and give it a generic name mysql-net<\/em> with the following command:<\/p>\n\n\n\n Confirm that You should see it listed in the list like so:<\/p>\n\n\n\n Run the MySQL image as a Docker container, connect to the Take note, you can also initialize the MySQL database with two more authentication-related environment variables. For example:<\/p>\n\n\n For our case, our current setup will suffice. Verify that the MySQL database is up and running using the command below:<\/p>\n\n\n\n You should have the output similar to the following:<\/p>\n\n\n\n Dates, times and the Again, containers are stateless, a database that runs in a container risks losing all its data if the container is deleted whether on purpose or unexpectedly. Therefore, it is mandatory to use data persistence mechanisms to ensure that data is retained even if the container stops or is removed. In this case, you\u2019ll consider common persistence mechanisms like Docker volumes or bind mounts.<\/p>\n\n\n\n To begin, let\u2019s confirm data is not persistent when a container is deleted. Go into the MySQL container using the following command, replacing For example, in my case it would be:<\/p>\n\n\n\n Input your password and you should get a prompt to enter MySQL commands. So to start, we will create a database named: You should see messages after most commands. Like after this one you will see \u201cQuery OK, 1 row affected\u201d for example. Next, select the Create a table Next, add some data into the Confirm that all values have been inserted (in addition to the message you will have received):<\/p>\n\n\n\n You should have the following output:<\/p>\n\n\n\n Exit out of the MySQL container and exec <\/em>back into the MySQL container:<\/p>\n\n\n\n The aim here is to check if the data is still available even after exiting out of the MySQL container.<\/p>\n\n\n\n Now select the You should see the contents of the Publisher table outputted to you which is expected. This simply means that exiting out of the MySQL container doesn\u2019t incur data loss. To simulate data loss, you\u2019ll need to remove the MySQL container and run a new one.<\/p>\n\n\n\n Stop and remove the MySQL container:<\/p>\n\n\n\n Note: While containers do not lose data when you exit it, or even if you restart the container (which you can do with the Docker start <\/a>and stop <\/a>commands), being able to destroy and recreate the container makes it easier to test new features or updates without affecting the data you have stored. Some care may need to be taken to use a compatible version of MySQL (or any tool you are using), and it may be best to make a copy of your data before attaching that new container. <\/p>\n\n\n\n Next, Initialize a new MySQL instance:<\/p>\n\n\n\n Then connect to it by finding the container Id:<\/p>\n\n\n\n Then connecting to it by using the container id.:<\/p>\n\n\n\n Input your password and attempt to use the book_hub<\/em> database:<\/p>\n\n\n\n You should see an error message similar to the one below:<\/p>\n\n\n\n ERROR 1049 (42000): Unknown database ‘book_hub’<\/p>\n\n\n\n<\/a><\/a>Why run a Relational Database with Docker<\/h2>\n\n\n\n
\n
Prerequisites<\/h2>\n\n\n\n
Pulling the Official MySQL Docker Image<\/h2>\n\n\n\n
docker pull mysql<\/pre><\/div>\n\n\n\n
Using default tag: latest\nlatest: Pulling from library\/mysql\n43759093d4f6: Pull complete\nd255dceb9ed5: Pull complete\n23d22e42ea50: Pull complete\n431b106548a3: Pull complete\n2be0d473cadf: Pull complete\nf56a22f949f9: Pull complete\n277ab5f6ddde: Pull complete\ndf1ba1ac457a: Pull complete\ncc9646b08259: Pull complete\n893b018337e2: Pull complete\nDigest: sha256:146682692a3aa409eae7b7dc6a30f637c6cb49b6ca901c2cd160becc81127d3b\nStatus: Downloaded newer image for mysql:latest\ndocker.io\/library\/mysql:latest<\/pre><\/div>\n\n\n\n
docker images<\/pre><\/div>\n\n\n\n
REPOSITORY TAG IMAGE ID CREATED SIZE\nmysql latest 5568fddd4f66 6 weeks ago 797MB<\/pre><\/div>\n\n\n\n
<\/a>Creating a Docker Network<\/h2>\n\n\n\n
docker network create -d bridge mysql-net<\/pre><\/div>\n\n\n\n
mysql-net<\/code> <\/em>is listed amongst the list of Docker networks on your machine:<\/p>\n\n\n\ndocker network ls<\/pre><\/div>\n\n\n\n
NETWORK ID NAME DRIVER SCOPE\n29931b56ee5a mysql-net bridge local<\/pre><\/div>\n\n\n\n
mysql-net<\/code> <\/em>network and initialize the MySQL database with a root password. If you are going to use this instance for any work, use a proper password:<\/p>\n\n\n\ndocker run -d --network=mysql-net -e MYSQL_ROOT_PASSWORD=superadminuser mysql<\/pre><\/div>\n\n\n\n
\n
MYSQL_ALLOW_EMPTY_PASSWORD<\/code>: This environment variable allows the MySQL database to start without a root password; which is not proper for production environments. <\/li>\n\n\n\nMYSQL_RANDOM_ROOT_PASSWORD<\/code>: This environment variable allows the MySQL database to start with a random root password and then print it in the logs. <\/li>\n<\/ul>\n<\/div>\n\n\ndocker ps<\/pre><\/div>\n\n\n\n
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES\n9c8b1f4a9b57 mysql \"docker-entrypoint.s\u2026\" 9 seconds ago Up 8 seconds 3306\/tcp, 33060\/tcp blissful_noether<\/pre><\/div>\n\n\n\n
CONTAINER ID<\/code> value will be different, but the output should look very similar.<\/p>\n\n\n\n<\/a>Checking for Data Persistence<\/h2>\n\n\n\n
<container-id><\/code> <\/em>with your MySQL container ID:<\/p>\n\n\n\ndocker exec -it <container-id> mysql -uroot -p<\/pre><\/div>\n\n\n\n
docker exec -it 9c8b1f4a9b57 mysql -uroot -p<\/pre><\/div>\n\n\n\n
book_hub<\/code>:<\/p>\n\n\n\nCREATE DATABASE book_hub;<\/pre><\/div>\n\n\n\n
book_hub<\/code> <\/em> database:<\/p>\n\n\n\nUSE book_hub;<\/pre><\/div>\n\n\n\n
Publishers<\/code> <\/em>in the book_hub<\/code> <\/em>database:<\/p>\n\n\n\nCREATE TABLE Publisher(pubID varchar(255) not NULL, bname text(50) not NULL,city varchar(255) not NULL, country text(50) not NULL, PRIMARY KEY(pubID));<\/pre><\/div>\n\n\n\n
Publishers<\/code> <\/em>table, the following values:<\/p>\n\n\n\nINSERT INTO Publisher (pubID, bname, city, country) \nVALUES\n ('p22', 'Random House', 'Las Vegas', 'United States'),\n ('p10', 'Dell Books', 'Colorado', 'United States'),\n ('p078', 'Pen Books', 'London', 'United Kingdom'),\n ('p279', 'Signet Books', 'Aberdeen', 'United Kingdom'),\n ('p12', 'Macmillan', 'London', 'United Kingdom'),\n ('p29', 'Madhavan', 'Aberdeen', 'United Kingdom');<\/pre><\/div>\n\n\n\nSELECT * FROM Publisher;<\/pre><\/div>\n\n\n\n
+-------+--------------+-----------+----------------+\n| pubID | bname | city | country |\n+-------+--------------+-----------+----------------+\n| p078 | Pen Books | London | United Kingdom |\n| p10 | Dell Books | Colorado | United States |\n| p12 | Macmillan | London | United Kingdom |\n| p22 | Random House | Las Vegas | United States |\n| p279 | Signet Books | Aberdeen | United Kingdom |\n| p29 | Madhavan | Aberdeen | United Kingdom |\n+-------+--------------+-----------+----------------+<\/pre><\/div>\n\n\n\n
exit\n\ndocker exec -it <container-id> mysql -uroot -p<\/pre><\/div>\n\n\n\n
book_hub<\/code> database and view the publishers<\/em> table with the following commands:<\/p>\n\n\n\nUSE book_hub;\n\nSELECT * FROM Publisher;<\/pre><\/div>\n\n\n\n
docker rm <container-id> -f<\/pre><\/div>\n\n\n\n
docker run -d --network=mysql-net -e MYSQL_ROOT_PASSWORD=superadminuser mysql<\/pre><\/div>\n\n\n\n
docker ps<\/pre><\/div>\n\n\n\n
docker exec -it <container-id> mysql -uroot -p<\/pre><\/div>\n\n\n\n
USE book_hub;<\/pre><\/div>\n\n\n\n