DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • The Ultimate Guide To Repair MySQL Database
  • SQL Query Performance Tuning in MySQL
  • SQL Commands: A Brief Guide
  • Non-blocking Database Migrations

Trending

  • A Modern Stack for Building Scalable Systems
  • Ensuring Configuration Consistency Across Global Data Centers
  • Monolith: The Good, The Bad and The Ugly
  • Agile and Quality Engineering: A Holistic Perspective
  1. DZone
  2. Data Engineering
  3. Databases
  4. Containerizing MySQL With Docker and DbVisualizer

Containerizing MySQL With Docker and DbVisualizer

Learn how to containerize a database with Docker and DbVisualizer to simplify your deployment process.

By 
Ochuko Onojakpor user avatar
Ochuko Onojakpor
DZone Core CORE ·
Oct. 05, 23 · Tutorial
Likes (2)
Comment
Save
Tweet
Share
4.6K Views

Join the DZone community and get the full member experience.

Join For Free

Docker is an open-source platform for building, shipping, and running applications in containers. Containers provide a lightweight and portable way to package and deploy software, making it easier to move applications between environments and platforms. By using Docker to containerize your database application, you can ensure that it runs consistently across different environments, making it easier to deploy and manage.

In this tutorial, we will walk through the process of containerizing a MySQL database using Docker and connecting to it using DbVisualizer. We will start with a simple example and then move on to more complex scenarios, including using Docker Compose to orchestrate multiple containers and using environment variables to configure our container.

Containerizing MySQL With Docker and DbVisualizer

Prerequisites

To follow this tutorial, you will need:

  • Docker
  • DbVisualizer
  • A text editor

Getting Started

Let's start by creating a simple Dockerfile for our MySQL database. The Dockerfile specifies the base image to use, any additional software packages to install, and any files to copy into the container.

Create a new file named Dockerfile with the following contents:

MySQL
 
FROM mysql:latest

ENV MYSQL_ROOT_PASSWORD=password

COPY my-database.sql /docker-entrypoint-initdb.d/


This Dockerfile uses the official MySQL Docker image as the base image, sets the root password to "password" using the ENV instruction, and copies a SQL script named my-database.sql to the /docker-entrypoint-initdb.d/ directory inside the container. This script will be executed when the container starts up, creating our database and any tables or data we need.

Save the Dockerfile to a new directory named my-database.

Next, let's build our Docker image using the docker build command:

MySQL
 
docker build -t my-database .


This command builds a Docker image with the name my-database using the Dockerfile in the current directory.

Now that we have our Docker image let's start a container from it using the docker run command:

MySQL
 
docker run -p 3306:3306 --name my-database-container -d my-database


This command starts a container named my-database-container from the my-database image, maps port 3306 to the host, and runs the container in a detached mode.

Connecting to the MySQL Database With DbVisualizer

With our container up and running, let's connect to our MySQL database using DbVisualizer.

Open DbVisualizer and go to the Connection tab. Click the "Create a Connection" button to create a new connection.


Creating a database connection in DbVisualizer.

Creating a database connection in DbVisualizer.

Select your database server type. For this tutorial, we will be choosing MySQL 8(Connector/J) as the driver.

Choosing the database driver in DbVisualizer.

Choosing the database driver in DbVisualizer.

In the Driver Properties tab, select MySQL and enter the following information:

  • Database server: localhost
  • Database Port: 3306
  • Database UserId: root
  • Database Password: password( the password you set in the MySQL deployment YAML file)


Connection details for MySQL database server in DbVisualizer.

Click the "Connect" button to test the connection.

If the connection is successful, you should see a message indicating that the connection was established. You can now browse the database and run queries using DbVisualizer.


Connection successful message.

Successful connection message.

Using Docker Compose With MySQL

So far, we have only worked with a single container. In a real-world scenario, we may need to deploy multiple containers that work together to form a larger application. Docker Compose is a tool for defining and running multi-container Docker applications, making it easier to orchestrate multiple containers and manage dependencies between them.

Let's create a Docker Compose file that defines our MySQL database container and a web application container that depends on it.

Create a new file named docker-compose.yml with the following contents:

MySQL
 
version: "3"
services:
db:
image: mysql:latest
environment:
MYSQL_ROOT_PASSWORD: password
volumes:
- ./my-database.sql:/docker-entrypoint-initdb.d/my-database.sql
web:
build: .
ports:
- "8000:8000"
depends_on:
- db


This Docker Compose file defines two services: a MySQL database service named db and a web application service named web. The db service uses the official MySQL Docker image and sets the root password using an environment variable. It also mounts the “my-database.sql” file from the current directory as a volume so that it can be executed when the container starts up.

The web service builds an image from the current directory and maps port 8000 to the host. It also depends on the db service so that the database is started before the web application.

To start the containers using Docker Compose, run the following command in the same directory as the docker-compose.yml file:

MySQL
 
docker-compose up


This command starts the containers defined in the Docker Compose file and outputs their logs to the console. Press Ctrl+C to stop the containers.

To start the containers in detached mode, add the -d option:

MySQL
 
docker-compose up -d


This command starts the containers in the background.

MySQL Dockerfile Environment Variables

In some cases, we may need to configure our container using environment variables. For example, we may want to specify the database name, username, and password as environment variables rather than hard-coding them in our Dockerfile.

Let's modify our Dockerfile to use environment variables for the database name, username, and password.

Create a new Dockerfile named Dockerfile-env with the following contents:

MySQL
 
FROM mysql:latest
ENV MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
ENV MYSQL_DATABASE=${MYSQL_DATABASE}
ENV MYSQL_USER=${MYSQL_USER}
ENV MYSQL_PASSWORD=${MYSQL_PASSWORD}
COPY my-database.sql /docker-entrypoint-initdb.d/


This Dockerfile uses environment variables to set the root password, database name, username, and password. The COPY instruction remains the same.

Save the Dockerfile to a new directory named “my-database-env”.

MySQL Docker Image

To build the Docker image, we need to pass in the values of the environment variables. We can do this using the --build-arg option:

MySQL
 
docker build --build-arg MYSQL_ROOT_PASSWORD=password --build-arg MYSQL_DATABASE=my_database --build-arg MYSQL_USER=my_user --build-arg MYSQL_PASSWORD=my_password -t my-database-env .


This command builds a Docker image with the name `my-database-env` using the Dockerfile in the current directory, passing in the values of the environment variables using the `--build-arg` option.

To start a container from the image, use the same docker run command as before:

MySQL
 
docker run -p 3306:3306 --name my-database-container -d -e MYSQL_ROOT_PASSWORD=password -e MYSQL_DATABASE=my_database -e MYSQL_USER=my_user -e MYSQL_PASSWORD=my_password my-database-env


This command starts a container named my-database-container from the my-database-env image, maps port 3306 to the host, and sets the values of the environment variables using the -e option.

Conclusion

In this tutorial, we walked through the process of containerizing a MySQL database using Docker and connecting to it using DbVisualizer. We started with a simple example and then moved on to more complex scenarios, including using Docker Compose to orchestrate multiple containers and using environment variables to configure our container.

By using Docker to containerize our database application, we can guarantee that it will function uniformly across various environments, which makes it simpler to deploy and manage. Additionally, using DbVisualizer to connect to our containerized database allows us to explore the database and execute queries just as we would with a typical database.

Docker and DbVisualizer are powerful tools that can simplify the process of developing, deploying, and managing database applications. By combining these tools, we can create a seamless development and deployment workflow that ensures consistency and reliability across all environments.

I hope this tutorial has been helpful in getting you started with Docker and DbVisualizer. If you have any questions or feedback, feel free to leave a comment below. Happy coding!

 FAQs(Frequently Asked Questions)

1. What is Docker, and why should I containerize my database?

Docker is an open-source platform for building, shipping, and running applications in containers. Containerizing your database provides a lightweight and portable way to package and deploy your software, ensuring consistent performance across different environments.

2. How do I containerize a MySQL database with Docker?

To containerize a MySQL database, create a Dockerfile specifying the base image, set environment variables, and copy SQL scripts into the container. Build the Docker image and run a container from it using Docker commands.

3. How do I connect to a containerized MySQL database with DbVisualizer?

Open DbVisualizer, create a new database connection, select the MySQL driver, and enter the connection details (server, port, user, password). Test the connection, and if successful, you can browse the database and execute queries in DbVisualizer.

4. What is Docker Compose, and how can I use it with MySQL?

Docker Compose is a tool for defining and running multi-container Docker applications. It helps orchestrate multiple containers and manage dependencies. To use Docker Compose with MySQL, define services for the database and any related services in a docker-compose.yml file and run the containers using the `docker-compose up` command.

5. How can I use environment variables in my MySQL Docker image?

To use environment variables in a MySQL Docker image, modify the Dockerfile to include `ENV` instructions for the desired variables. Then, pass the values of the environment variables during the image build or container run using the `-e` or `--build-arg` options.

Database Database connection MySQL Web application Command (computing) Docker (software)

Published at DZone with permission of Ochuko Onojakpor. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • The Ultimate Guide To Repair MySQL Database
  • SQL Query Performance Tuning in MySQL
  • SQL Commands: A Brief Guide
  • Non-blocking Database Migrations

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!