Docker MySQL Container

Step 1: Create an Environment File

The first step is to create an environment file that will store our database password.

.env

DB_PASSWORD=Password1234

This file will be used to set the MYSQL_ROOT_PASSWORD environment variable in our Docker container.

Step 2: Create a Docker Compose File

Next, we'll create a Docker compose file that defines our MySQL container.

mysql-docker-compose.yml

version: '3.9'

services:
  mysql-db:
    # Apple M1 Chip
    # platform: linux/amd64
    image: mysql:5.7
    container_name: mysql-db
    restart: always
    env_file:
      - .env
    environment:
      MYSQL_ROOT_HOST: '%'
      MYSQL_ROOT_PASSWORD: $DB_PASSWORD
    ports:
      - 3308:3306
    command:
    - --log-bin=binlog
    - --binlog-format=ROW
    - --server-id=1
    - --sql_mode=
    volumes:
      - mysql_datadir:/var/lib/mysql
    networks:
      - backend-network

networks:
  backend-network:
    driver: bridge

volumes:
  mysql_datadir:

This file defines a single service, mysql-db, which uses the official MySQL 5.7 image and maps port 3308 on your local machine to port 3306 in the container.

Step 3: Start the Container

Now that we have our environment file and Docker compose file set up, it's time to start the container! Run the following command:

docker-compose -f mysql-docker-compose.yml up -d

This will start the container in detached mode, meaning it will run in the background.

Step4: Test MySQL Database Container

Once the container is running, we can test our database by downloading some data files. Run the following commands:

curl -L https://s3-ap-southeast-1.amazonaws.com/dwbi-datalake/dataset/showroom.sql -o showroom.sql
curl -L https://s3-ap-southeast-1.amazonaws.com/dwbi-datalake/dataset/customer.sql -o customer.sql
curl -L https://s3-ap-southeast-1.amazonaws.com/dwbi-datalake/dataset/product.sql -o product.sql
curl -L https://s3-ap-southeast-1.amazonaws.com/dwbi-datalake/dataset/sales.sql -o sales.sql
curl -L https://s3-ap-southeast-1.amazonaws.com/dwbi-datalake/dataset/stocks.sql -o stocks.sql

Now we will create a database and a few tables.

Note: you should have MySQL Client installed locally.

db_password=Password1234

mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "create database sales;"
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} sales < showroom.sql
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} sales < customer.sql
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} sales < product.sql
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} sales < sales.sql
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} sales < stocks.sql

Now we alter some table definition.

mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "alter table showroom modify column id int auto_increment primary key;" sales
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "alter table customer modify column id int auto_increment primary key;" sales
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "alter table product modify column id int auto_increment primary key;" sales
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "alter table stocks modify column id int auto_increment primary key;" sales

Test the database objects-

mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "show tables;" sales
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "desc showroom;" sales
mysql --host=127.0.0.1 --port=3308 --user root -p${db_password} -e "select * from showroom limit 10;" sales

Step 5: Stop the Container

Finally, we can stop the container by running the following command:

docker-compose -f mysql-docker-compose.yml down

This will stop the container and remove it from memory.

That's it! We've successfully created and configured a Docker MySQL container.