Installation of Docker Technology Series Multi-Version Mysql 5.6 and Mysql 5.7

Keywords: Java MySQL Docker Mac Database

Hello, what follows is not exclusive content about MAC, basically related to Java environment and basic technology.So this tutorial is common to both linux systems and macOS, so don't worry.

In the previous post, we found Docker convenient after installing it.Now let's install Mysql, which is often used in development.

MySQL is the most popular open source database in the world.With its reliability, ease of use, and performance, MySQL has become a database preference for Web applications.

The pain point is, if you're in windows, it's impossible to imagine that you're installing different versions of mysql for testing, and you don't want any intersection between their versions.For example, the company uses a stable version of 5.6, but the newer courses on the market now use mysql 5.7 to teach and try them.This installation is a nightmare, and with the docker, it's easy to solve this problem without conflicting ports and configurations.

It's better to install centos, but it's still a bit of a hassle if you're installing through source code compilation.So next, let's install mysql through docker.

1. View available versions of MySQL

Mode 1: Web-side access to MySQL mirror library address:

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

Mode 2: We can also use commands to view available versions.

 docker search mysql

Look at the picture:

2. Obtaining MySQL Mirror on Official Website

The following versions are available for you to choose from

# Pull and pull mysql 5.7
docker pull mysql:5.7

# Pull the latest version of mysql image, or automatically pull the latest version if no later version number is written
docker pull mysql

Take version 5.7 for example.

docker pull mysql:5.7

The first time I pull it, it will be a little longer and I need to download the corresponding mirror.

3. View Local Mirrors

Use the following command to see if mysql is installed

docker images

4. Run Mysql Container

Once the installation is complete, we can run the mysql container using the following commands:

1. Create mount directory

Creating a mount directory is mainly to facilitate modification of configuration files for easy control purposes.

mkdir -p $HOME/docker/mysql57

It is designed to facilitate the management of the directories that are built, primarily to represent some configuration files placed under the docker project.mysql57 denotes an application name

cd $HOME/docker/mysql57

2. Run Mysql Container

Mode 1: No need to mount directory (not recommended)

docker run -p 3306:3306 --name mysql57 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

Mode 2: Directory needs to be mounted (recommended)

docker run -p 3306:3306 --name mysql57 \
-v $PWD/conf:/etc/mysql \
-v $PWD/logs:/var/log/mysql \
-v $PWD/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci

Parameter Description

- name: Container name, named mysql57 here
-v: mount directory
-e: Configuration information, where the login password of the root user of mysql is configured
-p: Port mapping, where port 3306 of host is mapped to port 3306 of container
-d: Source mirror name, mysql:5.7 here and set default encoding for MySQL after running in the background

3. Option execution, if mysql56 is not required, you may not need execution.

Create directory

mkdir -p $HOME/docker/mysql56

Enter the directory

cd $HOME/docker/mysql56

Pull mirror

docker pull mysql:5.6

MySQL 5.6 installation

docker run -p 3307:3306 --name mysql56 \
-v $PWD/conf:/etc/mysql \
-v $PWD/logs:/var/log/mysql \
-v $PWD/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.6 \
--character-set-server=utf8mb4 \
--collation-server=utf8mb4_unicode_ci

Problem you may encounter is that if you do not first execute the command to pull the mirror, it will automatically pull the mirror, and then a mirror mysql56 has been created by default. We will find it by command and start it.

View all containers

docker ps -a

Delete Container

docker rm container id

Start Container

docker start container id

Two extra quick commands

#Stop all containers
docker stop $(docker ps -a -q)
# remove deletes all containers
$ docker rm $(docker ps -a -q)

5. Successful Installation

Check the docker ps command to see if the installation was successful.

docker ps -a

The Up state of the container state, indicating that the container is running and that you can see the port mapping relationship between the host and the container.

Restart mysql container

docker restart mysql57

View mysql log

docker logs -f mysql57

6. Mysql Test

Enter mysql container

docker exec -ti mysql57 /bin/bash

Parameter Description

-t Produces a pseudo-terminal in a container
-i Interact with standard input (STDIN) in a container

Log on to mysql server

Mode 1

mysql -h 127.0.0.1 -u root -p

Mode 2

mysql -u root -p123456

Query Test Statement

use mysql;
SELECT VERSION(), CURRENT_DATE;

If you need to login remotely, you need to execute Authorization Command 1 and allow root to login remotely as well (not recommended)

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;

2. Create a user admin remote login (recommended)

GRANT ALL PRIVILEGES ON *.* TO admin@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;

3. Refresh Permissions

FLUSH PRIVILEGES;

4. View Users

select host,user from user;

5. Backup Data

$ docker exec mysql57 sh -c 'exec mysqldump --all-databases -uroot -p"123456"' > /some/path/on/your/host/all-databases.sql

6. Restore Data

$ docker exec -i mysql57 sh -c 'exec mysql -uroot -p"123456"' < /some/path/on/your/host/all-databases.sql

7. Other Configurations

1. only_full_group_by problem

If the installed version is version 5.7, the following error occurred when querying data

this is incompatible with sql_mode=only_full_group_by

The following can be used to solve the problem

2. Query sql_mode

select @@sql_mode

give the result as follows

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3. Reset

Delete the ONLY_FULL_GROUP_BY configuration and reset it to config-file.cnf

[mysqld]
# Table names are case insensitive
lower_case_table_names=1
#server-id=1
datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysqlx.sock
#symbolic-links=0
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

For more in-depth sections, if you have the opportunity to update the mysql tutorial, let's move on.

summary

1. One command will complete the installation of mysql, which is convenient and fast.

2. Multiple instances, different ports do not affect each other.

Programmer Group