Installing MySQL with Docker

This straightforward tutorial outlines the process of installing MySQL using Docker and connecting it with MySQL Workbench.

1. Let’s pull MySQL image from Docker Hub. From the docker shell, execute docker pull cytopia/mysql-8.0. Docker will start downloading the latest mysql image from the docker hub. Be aware this image is around 2GB.

docker pull cytopia/mysql-8.0

2. Let’s download some data that we can load in a new table in MySQL Workbench. We will complete the following steps.

• Create superstore directory
• Download the product_new.csv dataset using curl to your current directory.
• /var/lib/mysql-files is the default datadir in the container for mysql8

mkdir superstore
cd superstore
curl -O https://weclouddata.s3.amazonaws.com/datasets/retail/superstore/product_new.csv 

cd ..

3. Now, we can deploy our mysql image using docker run command.

docker run -p 3307:3306 --name mysql8 -e MYSQL_ROOT_PASSWORD=root -d -v $(pwd)/superstore:/var/lib/mysql-files cytopia/mysql-8.0:lates

• -p 3307:3306: port 3307 on host os maps to port 3306 in the container, since you might have another instance of MySQL running on your MySQL Workbench on 3306 port. So, we need to map this new instance using another port.
• --name mysql8: container name is set to mysql8
• -d: start the container in detached mode
• -e: environment variable to set default mysql password
• -v: volume mapping (directory with product_new.csv file to the default datadir in /var/lib/mysql-files)

4. Creating a new connection in MySQL Workbench, use root for username and password. Note (if you are running Docker on virtual machine, don’t forget to map ports)

Now we can run the following queries to create a database and table and load the data

-- create the product table (an empty table)create database superstore;
drop database if exists superstore ;
create database superstore;

drop table if exists superstore.product;
create table superstore.product (
    ProductID            int,
    ProductName          varchar(200),
    ProductCategory      varchar(20),
    ProductSubCategory   varchar(50),
    ProductContainer     varchar(20),
    ProductBaseMargin    decimal(4,2)
);


-- load data into the product table (please change the file path accordingly)
load data infile '/var/lib/mysql-files/product_new.csv'
into table superstore.product character set 'latin1'
fields terminated by '\t'
lines terminated by '\n'
;

select *  from superstore.product limit 5;

After running all commands you will see the following output.

So, we successfully deployed MySQL with Docker, connected to the instance using MySQL Workbench and loaded the data set.

Additionally, we can SSH into the container with bash access – -i, --interactive : Keep STDIN open
– -t, --tty : Allocate pseudo-tty (terminal)

docker exec -it mysql8 /bin/bash

And access MySQL in terminal with the following command and manipulate data…

mysql --local-infile -u root -p