Dockerize MySQL server for REST API

  1. Dockerize MySQL server
    A. launch a docker in local
    B. Hosting the MySQL server in Docker container in IMAGE
    C. Listening to the host port of MySQL server in local by whatever like Springboot
    Rest API or Django Rest API

Command:

1
2
3
4
5
6
docker run -d -p 3306:3306 --name=mysql-server --env="MYSQL_ROOT_PASSWORD=123456" mysql
# This runs on a detached mode
# Open port 3306:3306
# Name of the server: mysql-server
# root password: 123456
# Container is created from mysql image

Notes: Check what other app is listening to the port on 3306
Command:

1
2
3
-p 12345:3306
# This is one method to check for the port on MAC OS
netstat -vanp tcp | grep 3306

– If you cannot connect to MySQL from another docker and got this error:

to load authentication plugin 'caching_sha2_password'. ```
1
2
3
4
Then you can add the parameter below when running the container.
It is because the newer version of MySQL uses caching_sha2_password instead of mysql_native_password

``` mysqld --default-authentication-plugin=mysql_native_password

– you can access the MySQL through your host

1
2
3
4
5
6
# Through Host 
mysql -host 127.0.0.1 -P 3306 -protocol=tcp -u root -p

# Through container
Docker exec -ti mysql-server bash
mysql -u root -p

Create user and database for connection

1
2
3
4
5
CREATE DATABASE databaseName;
CREATE USER 'dnguyen'@'localhost' IDENITIFIED BY '123456';
GRANT ALL PRIVALEGES ON databaseName.* TO 'dnguyen'@'localhost';
FLUSH PRIVALEGES;
QUIT

If you are using Django, here is what you need to put in the settings.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DATABASES = {
      'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'databaseName',
        'USER': 'dnguyen', # or root
        'PASSWORD': '123456',
        'HOST': '127.0.0.1',
        'PORT': '3306', # or 12345
        'OPTIONS': {
            # Tell MySQLdb to connect with 'utf8mb4' character set
            'charset': 'utf8mb4',
        },
    }
}

That is that.

//Resources for mySQL and docker commands to practice.
//With Docker-composer set up docker-compose1.yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
version: "3"

services:
  mysql:
    image: mysql:5.6
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: findeasily
      MYSQL_ROOT_PASSWORD: 88888888
    volumes:
      - mysql_data:/var/lib/mysql/data
  redis:
    image: 'redis:5.0-alpine'
    command: redis-server --requirepass 88888888
    ports:
      - '6379:6379'

volumes:
  mysql_data:

or docker-compose2.yml
‘’’
version: ‘2’

services:

mysql:
image: mariadb:10.1.19
ports:

  - 8083:3306
volumes:
  - ./mysql:/var/lib/mysql
environment:
  MYSQL_ROOT_PASSWORD: wp

1
2

So when you luanch the docker: ``` docker-compose up

Access the mysql db with command:

1
mysql -p findeasily  --protocol=tcp -u root -p

for the first docker-compose1.yml.

And for second docker-compose2.yml, we can access the mysql database with
Command like:

1
2
docker-compose up
mysql -P 8083 --protocol=tcp -u root -p

– Running a Postgres using docker
Command lines:

laungch a docker container
1
2
# Create the docker container over a specific image
docker create -v /var/lib/postgresql/data --name postgres10.3-database alpine

if we already have the docker container running, then we can just launch an images and if there is not
local image it will pull from remote community server.

Let’s now run a specific process in the container. This is going to be postgres. Here are the operations are broken down:
We’re naming the container local-postgres10.3.
Set the password to “password” using the environment tag -e.
Set the port to 5432 so we can access it from Postico later using -p.
The –volumes-from tells the container to mount the /var/lib/postgresql/data volume from the postgres10.3-database container that we created in the previous step.

Lastly, use postgres:10.3 to launch the container.

1
docker run --name local-postgres10.3 -p 5432:5432 -e POSTGRES_PASSWORD=password -d --volumes-from postgres10.3-database postgres:10.3

this is running the images base on postgres in local, so since we do not have local-postgres10.3 images, it will just pull the image from remote and then we can run the image by the image id.

1
docker run dsf3edfdsfw

like that, so right now we running the postgres db in the docker, we can check by

ps ```
1
2
now when we access the db by psql command, we can take like:
 ``` docker exec -it cocky_varahamihira psql -U postgres

This is meaning that:

docker exec command psql in database name as cocky_varahamihira.

Notes: we don’t need to do like

1
2
3
4
5
6
7
8
docker run --name local-postgres10.3 -p 5432:5432 -e POSTGRES_PASSWORD=password -d --volumes-from postgres10.3-database postgres:10.3
>> Get into it
psql -h localhost -p 5432 -U postgres
>>
Password for user postgres:
psql (10.3 (Debian 10.3-1.pgdg90+1))
Type "help" for help.
postgres=#

Cauz it will run psql in local compouter instead of running in docker. so you will get in trouble of
install psql in your local computer, that will be not good I think.