MySQL containerization detailed tutorial

Preface:

The previous article introduced the installation of Docker tool and the use of common commands. In this article, we will introduce how to run MySQL instance in Docker. Some small partners may ask: why run MySQL in Docker? Because it is easy to deploy MySQL instance in Docker, it does not need to consider the dependency differences such as operating system, and it can be deployed in multiple instances. For example, if we have installed MySQL 5.7 in the original server, we only need to start MySQL 8.0 image with Docker if we want to run MySQL 8.0 instance again. Let's introduce how to start MySQL instance with Docker.

▍ 1. Pull the official MySQL image

In the last article, we introduced three basic concepts of Docker: image, container and warehouse. To deploy MySQL in Docker, the first step is to pull MySQL image from the official warehouse. Here we pull MySQL image from Docker Hub. Enter Docker Hub, search mysql, and you can see the following screen, which has different versions of images and introduction.

For example, if we want to pull the images of MySQL 5.7 and MySQL 8.0, we can do the following:

# Pull the images of versions 5.7 and 8.0 for a moment, and then pull them successfully
docker pull mysql:5.7.23
docker pull mysql:8.0.18

# View mirroring
docker images
REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
mysql               8.0.18              c8ee894bd2bd        2 weeks ago         456MB
mysql               5.7.23              1b30b36ae96a        12 months ago       372MB

In fact, MySQL official image also has some defects, such as time zone is not Beijing time, system character set problems, etc. Based on the image of MySQL 5.7.23, the following shows how to modify and build the image.

# The main function of creating Dockerfile is to change the contents of system character set and time zone as follows:
cat Dockerfile 
FROM mysql:5.7.23
MAINTAINER wang
RUN echo "alias ls='ls --color=auto'" >> /root/.bashrc \
    && ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

ENV LANG=C.UTF-8

# Constructing mirrors
docker build -t my-mysql:5.7.23 . 
Sending build context to Docker daemon  2.048kB
Step 1/4 : FROM mysql:5.7.23
 ---> 1b30b36ae96a
Step 2/4 : MAINTAINER wang
 ---> Running in a7cc94f95cc7
Removing intermediate container a7cc94f95cc7
 ---> d9590ed98de5
Step 3/4 : RUN echo "alias ls='ls --color=auto'" >> /root/.bashrc     && ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime
 ---> Running in e698bfdb3817
Removing intermediate container e698bfdb3817
 ---> 31c9ed9103c5
Step 4/4 : ENV LANG=C.UTF-8
 ---> Running in eefa296fef94
Removing intermediate container eefa296fef94
 ---> 10aa697936e9
Successfully built 10aa697936e9
Successfully tagged my-mysql:5.7.23

# View the image again to find our newly built image
docker images
REPOSITORY          TAG                 IMAGE ID            CREATED              SIZE
my-mysql            5.7.23              10aa697936e9        About a minute ago   372MB
mysql               8.0.18              c8ee894bd2bd        2 weeks ago          456MB
mysql               5.7.23              1b30b36ae96a        12 months ago        372MB

▍ 2. Start MySQL instance

Here is how to use the image built above to run MySQL instance. In fact, MySQL containerization requires mapping port, data persistence, loading configuration file and other operations. Next, I will show you the specific startup operation.

2.1 create data persistence path and configuration file

# Create profile directory and data directory
mkdir -p /data/mysql57/{cnf,data}

# Add profile my.cnf
cd /data/mysql57/cnf/
vim my.cnf

# The contents of the configuration file are as follows, which can be customized
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql

server-id = 33061
max_connections = 1000
sql_mode = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
innodb_file_per_table = 1
log_timestamps=SYSTEM
character-set-server = utf8
max_allowed_packet = 32M
sort_buffer_size = 4M
read_buffer_size = 4M
join_buffer_size = 4M
binlog_cache_size = 4M
tmp_table_size = 96M
max_heap_table_size = 96M
innodb_buffer_pool_size = 512M

#logs
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 3
log-bin = /var/lib/mysql/binlog
binlog_format = row
expire_logs_days = 15
log_bin_trust_function_creators = 1

2.2 docker run MySQL instance

# A command starts a MySQL instance
docker run -itd -p 33061:3306 --name mysql57 --hostname=mysql57 -v /data/mysql57/cnf:/etc/mysql -v /data/mysql57/data:/var/lib/mysql --privileged=true -e MYSQL_ROOT_PASSWORD=Asdf@123456  my-mysql:5.7.23

# Explain the meaning of each parameter
-d: Run the container in the background and return to the container ID
-i: Run the container in interactive mode, usually with -t Simultaneous use
-t: Reassign a pseudo input terminal to the container, usually with -i Simultaneous use
-p: Specifies the port mapping in the format host(Host)port:Container port
--name="mysql57": Specify a name for the container
--hostname=mysql57: Of the specified container hostname
-v: Bind a volume
--privileged=true: Start container in privileged mode

2.3 check the state of the container

# View container status
docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                                NAMES
04cd3d99d5cb        my-mysql:5.7.23     "docker-entrypoint.s..."   13 seconds ago      Up 11 seconds       33060/tcp, 0.0.0.0:33061->3306/tcp   mysql57

# Enter the container
docker exec -it mysql57 /bin/bash or docker exec -it [CONTAINER ID] /bin/bash

▍ 3. Other related operations

So far, we have successfully run MySQL instance in Docker, but it is not over. There are still many operations not mentioned, such as how to backup and restore, how to change the configuration, etc., which are briefly described below.

  • Backup recovery
# backups
docker exec mysql57 sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /tmp/all-databases.sql

# recovery
docker exec -i mysql57 sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /tmp/all-databases.sql
  • Change configuration
# To change the configuration, you only need to modify the host / data/mysql57/cnf/my.cnf file, and then restart the container
docker restart mysql57

If you want to connect mysql in the container locally through tools, you can use the host ip plus the mapping port to connect. For example, the MySQL instance we created above can be connected through the host ip+30661 port. If you cannot connect, please check the network and server firewall.

Conclusion:

This article mainly introduces the method and steps of deploying MySQL in Docker. You will find that it is very simple and convenient, and it can be deployed on a large scale with multiple instances. If you want to experience the latest version of 8.0.18, you can deploy it yourself. In fact, it can be simpler. For example, you can start an instance by running docker run mysql:5.7.23 directly. However, in order to be more standardized, it is recommended to mount the configuration file and data directory to the host.

Reference:

Broken reads:

Alibaba cloud server double 11 activity is still going on. You can buy an entry-level server for 86 yuan for one year. I recommend you to have a look. Buy a cloud server, you can learn Linux, MySQL, Docker, Git, etc. you can also deploy a personal website, and you can buy a small partner to test and play! Copy the following link to the browser to open to enter the official website page to purchase.

https://www.aliyun.com/1111/2019/group-buying-share?ptCode=F6718C2981638C225DDA9F79172CF1F4647C88CF896EF535&share_source=copy_link

Tags: MySQL Docker SQL vim

Posted on Thu, 07 Nov 2019 22:15:09 -0800 by syacoub