Docker installation of MySQL (8 and 5.7)

This article will introduce how to use Docker to deploy MySQL database and remote access configuration

Install MySQL

Pull the image

Use the following command to pull the MySQL database image:

$ sudo docker pull mysql # Pull the latest version of the image, currently MySQL 8, tag is latest

$ sudo docker pull mysql:5.7 # Specify to pull MySQL version 5.7

You can also use the search command to find other MySQL-related mirrors, which are marked with the number of Stars, that is, the popularity.

$ sudo docker search mysql

Running MySQL

$ sudo docker run -p 3306:3306 \
  --name mysql \
  -v $PWD/conf:/etc/mysql/conf.d \
  -v $PWD/logs:/logs \
  -v $PWD/data:/var/lib/mysql \
  -e MYSQL_ROOT_PASSWORD=your-password \
  -d mysql

Command Explanation:

• -p 3306:3306 : Map the container's port 3306 to the host's port 3306.
• -v $PWD/conf:/etc/mysql/conf.d : Mount conf/my.cnf in the current directory of the host to /etc/mysql/my.cnf in the container.
• -v $PWD/logs:/logs: Mount the logs directory under the current directory of the host to the /logs of the container.
• -v $PWD/data:/var/lib/mysql : Mount the data directory under the current directory of the host to /var/lib/mysql of the container.
• -e MYSQL_ROOT_PASSWORD=your-password: Initialize the password of the root user. It is recommended to use a highly complex password.
• -d mysql: The image name to be deployed. If it is version 5.7, it is mysql:5.7

Configuring Remote Access

Remote access to MySQL is a basic configuration, but you must pay attention to security issues when configuring it, otherwise there will be security risks, especially for enterprise servers.

Also, please note that port 3306 must be enabled in the server firewall and in the server provider's security group, otherwise it will be inaccessible.

To configure remote access, first open the MySQL control terminal and use the following command to open it:

$ sudo docker exec -it mysql bash # Enter the MySQL container $ mysql -uroot -p # Log in to MySQL and enter the password after execution

$ use mysql; # Choose to use mysql database

MySQL 8 Configuration

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
# Create an account for remote access;
# {usernama} is the username for remote access login. It is not recommended to use root;
# {password} is the login password for remote access;
# '%' represents all IPs. If possible, try to set a specific IP or IP segment. GRANT ALL ON *.* TO 'username'@'%';
# Grant all permissions to the previously created account ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
# Confirm to use the password here to log in to this account FLUSH PRIVILEGES;
# Refresh permissions

The complete command is as follows:

CREATE USER 'james'@'%' IDENTIFIED BY '123456asd';
# Create an account - james for remote access;

GRANT ALL ON *.* TO 'james'@'%';
# Grant all permissions to the previously created account: james

ALTER USER 'james'@'%' IDENTIFIED WITH mysql_native_password BY '123456asd';
# Confirm to use password {123456asd} to log in to this account {james}
# Make the password as complex as possible for higher security.

FLUSH PRIVILEGES;
# Refresh permissions

After configuration, use Navicat or other tools to test the connection.

MySQL 5.7 Configuration

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
# {usernama} is the username for remote access login. It is not recommended to use root;
# {password} is the login password for remote access;
# '%' represents all IPs. If possible, try to set a specific IP or IP segment FLUSH PRIVILEGES;
# Refresh permissions

After configuration, use Navicat or other tools to test the connection.

Note: If you cannot access it, please check whether the firewall port 3306 is open and whether the port in the security group of the server provider is open.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.