Docker installation and configuration steps for MySQL

Preface

MySQL is the most popular open source database in the world. So~ this article will demonstrate how to install and configure MySQL on Docker.

environment

• CentOS 7
• Docker 20.10.10

Install

Pull the image

docker pull mysql

If you want to specify a version, add : +版本號after mysql, for example:

docker pull mysql:8.0.16

Pull the latest version of MySQL directly here

View Mirror

docker images

Create and start the MySQL container

Create data directories and configuration files

Create the directory for MySQL configuration files and data directory on the host in advance, and grant permissions to avoid startup failure when mounting external configurations and data:

Create a directory for placing mysql configuration files and data directories

mkdir -p /mydata/mysql/

Set folder permissions

chmod -R 755 /mydata/mysql

The first number indicates the permissions of the file owner, the second number indicates the permissions of other users who belong to the same user group as the file owner, and the third number indicates the permissions of other user groups.
There are three types of permissions: read (r=4), write (w=2), and execute (x=1).
In summary, there are readable and executable (rx=5=4+1), readable and writable (rw=6=4+2), and readable, writable and executable (rwx=7=4+2+1). So, chmod
755 sets the user's permissions to:
1. The file owner can read, write and execute --7
2. Other users who belong to the same user group as the file owner can read and execute --5
3. Other user groups can read and execute

Create my.cnf configuration file

mkdir -p /mydata/mysql/conf
touch /mydata/mysql/conf/my.cnf

Edit the my.cnf configuration file

vi /mydata/mysql/conf/my.cnf

Add the following configuration content

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
init_connect = 'SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
skip-name-resolve
secure_file_priv=/var/lib/mysql

remind

Regardless of whether you use my configuration or not, if you install a new version of MySQL, be sure to copy this sentence:

secure_file_priv=/var/lib/mysql

When you create and start the MySQL container for the first time, MySQL will access the /var/lib/mysql folder. If you do not have permission, it will fail to start. After using docker ps, you cannot see the MySQL container running. You need to set the value of secure_file_priv to /var/lib/mysql so that you have permission to access and read and write the /var/lib/mysql directory normally.

Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Suppliedvalue : /var/lib/mysql-files
Translation: Unable to access directory with --secure-file-priv. Please make sure that the directory exists and is accessible by the MySQL server. Provided value: /var/lib/mysql file

• The value of secure_file_priv is null, which means that mysqld is restricted from importing or exporting.
• The value of secure_file_priv is /tmp/, which means that mysqld import and export can only occur in the /tmp/ directory.
• The value of secure_file_priv is empty, indicating that there is no restriction on mysqld import and export

Create and start the MySQL container command

sudo docker run -p 3306:3306 --name mysql \
-v /mydata/mysql/log:/var/log/mysql \
-v /mydata/mysql/data:/var/lib/mysql \
-v /mydata/mysql/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=root \
-d mysql:latest

Parameter Description:

• -p 3306:3306 : Map the container's port 3306 to the host's port 3306
• --name mysql : Define the container name as mysql
• -v /mydata/mysql/log:/var/log/mysql : mount the MySQL log folder to the host
• -v /mydata/mysql/data:/var/lib/mysql : Mount the MySQL data folder to the host
• -v /mydata/mysql/conf:/etc/mysql : mount the MySQL configuration folder to the host
• -e MYSQL_ROOT_PASSWORD=root : Initialize the root user password
• -d mysql:latest : Select the latest MySQL version to build the container

View running containers

docker ps

Enter the MySQL container for configuration

Enter command

docker exec -it container id ./bin/bash

Connecting to MySQL

Here, because we set the default password of MySQL to root , the password after p is root

mysql -uroot -proot

Change MySQL Password

Using the mysql library

use mysql

Modify the access host and password, etc., and set it to be accessible to all hosts

 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

注意：mysql_native_password，mysql8.x版本必須使用這種模式，否則navicate無法正確連接

Test the connection

Please make sure the firewall is turned off before testing. If it is a cloud server, remember to open the 3306 rule

Disable firewall in Linux

# Close systemctl stop firewalld
# Disable the firewall from starting up systemctl disable firewalld

Cloud service opens port 3306

Test the connection using Navicat

Test the connection using SQLyog

The Docker installation and configuration of MySQL tutorial is over!

The above are the details of the implementation steps for installing and configuring MySQL with Docker. For more information about installing MySQL with Docker, please pay attention to other related articles on 123WORDPRESS.COM!