MySQL is an open-source Database management system used for cloud native applications. It is used for different kind of applications including ecommerce, data warehouse and other different data management apps. It is open-source and can be easily used for storing data for applications. There are different ways to setup a mysql instance and connect and use it with applications. In this tutorial, we will look into docker to use mysql database.

First you need to install docker which we will use for creating instance for MySQL. Docker has an official image for MySQL database which can be used for development and production. 

https://hub.docker.com/_/mysql

Now first we run a docker container using MySQL image, it will automatically pull image from docker hub if it is already not available on your system.

Create MySQL Container

Open cmd/shell on your system and run following command to create a simple MySQL container. It will create and run a docker container named mysql1 with root password in detached mode.

# create a docker container instance
docker run --name mysql1 -e MYSQL_ROOT_PASSWORD=Admin-123456 -d mysql:latest

Once docker container is created, we need to perform some more steps to use it in our application.

Basic Configuration

We need to perform some more steps in ourder to use it. First we connect to container instance and login to mysql using root user.

# Enter password on prompt
docker exec -it mysql1 mysql -uroot -p

Now we first create a database named testdb, you can also create in later steps.

CREATE DATABASE testdb;

Currently, it has only root user with priviliges, so we will create another user which we will use to access mysql server and perform operations.

# create a user named 'faizan'
CREATE USER 'faizan'@'%%' IDENTIFIED BY 'MyPass_12345678';

# Grant Privileges
GRANT ALL PRIVILEGES ON *.* TO 'faizan'@'%%' WITH GRANT OPTION;

# Reload Grant Tables
FLUSH PRIVILEGES;

Connect and Use

Now, we can connect to MySQL server using any supported programming language and query data. We will be using python to connect and perform some operations on database. First install required libraries using pip.

pip install mysql-connector-python

Now we connect to database using credentials.

import mysql.connector

# Make Connection to MySQL Server
db = mysql.connector.connect(
  host="localhost", # host
  user="faizan", # user
  password="MyPass_12345678", #password
  database='testdb' # database you want to connect
)
# create cursor
cursor = db.cursor(buffered=True)

We will create a table with two columns and insert and query data.

# create a table
cursor.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(30), email VARCHAR(50))")

Insert some data to table using python.

cursor.execute("INSERT INTO users (name, email) VALUES ('Faizan', 'faizan@gmail.com')")
cursor.execute("INSERT INTO users (name, email) VALUES ('Ali', 'ali@gmail.com')")
# commit changes
db.commit()

Now we can query data using select statements. For further details related to query data view SQL documentation.

cursor.execute("SELECT * FROM users")
for row in cursor.fetchall(): # iterate on all rows based on query
    print(row) # will return all columns for a row

For more details related to MySQL, view details on official docker image details.