Docker deploys mysql remote connection to solve 2003 problems

Connecting to MySQL

Here I use navicat to connect remotely. Before connecting to MySQL, you need to open the firewall port or close the firewall.

Open Ports

firewall-cmd --add-port=3306/tcp --permanent
// --permanent is permanently effective. Without this parameter, it will become invalid after restart.

firewall-cmd --reload
// Reload

Turn off firewall

systemctl stop firewalld

systemctl enable iptables
// Set up boot

firewall-cmd --reload
// Reload

Basic usage of firewalld

Start: systemctl start firewalld

To shut down: systemctl stop firewalld

Check the status: systemctl status firewalld

Disable at boot: systemctl disable firewalld

Enable at boot: systemctl enable firewalld

Systemctl is the main tool in CentOS7's service management tool, which integrates the functions of the previous service and chkconfig.

Start a service: systemctl start firewalld.service

Shut down a service: systemctl stop firewalld.service

Restart a service: systemctl restart firewalld.service

Display the status of a service: systemctl status firewalld.service

Enable a service at boot: systemctl enable firewalld.service

Disable a service at boot: systemctl disable firewalld.service

Check systemctl is-enabled firewalld.service

View the list of enabled services: systemctl list-unit-files|grep enabled

View the list of services that failed to start: systemctl --failed

Configure firewalld-cmd

Check the version: firewall-cmd --version

View help: firewall-cmd --help

Show status: firewall-cmd --state

View all open ports: firewall-cmd --zone=public --list-ports

Update firewall rules: firewall-cmd --reload

View zone information: firewall-cmd --get-active-zones zones

Check firewall-cmd --get-zone-of-interface=eth0

Deny all packets: firewall-cmd --panic-on

Cancel the deny state: firewall-cmd --panic-off

Check whether it is rejected: firewall-cmd --query-panic

Supplement: Docker deployment mysql remote connection solution 1251

reason:

MySQL 8.0 uses the caching_sha2_password authentication mechanism by default, and the client does not support the new encryption method.

Solution:

Modify the encryption method of the user (root)

step:

1. Enter the mysql container

[root@localhost ~]# docker exec -it javakfmysql bash ## javakfmysql is the alias of the container, you can also use the container id instead

2. Log in to mysql

root@e285125c99d6:/#mysql -u root -p

3. Set user configuration items

View user information

mysql> select host,user,plugin,authentication_string from mysql.user; 
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | caching_sha2_password | $A$005$HF7;krfwhkKHp5fPenQm4J2dm/RJtbbyjtCUVdDCcboXQw3ALxsif/sS1 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+

Note: host is % which means no restriction on IP address. localhost means the local machine uses plugin other than mysql_native_password.

Modify the encryption method

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; ### 123456 mysql login password flush privileges;

Then check the user information

mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | mysql_native_password | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.

You may also be interested in:

Docker enables secure TLS remote connection access
How to set up vscode remote connection to server docker container
Detailed example of remotely connecting to Docker using TLS encrypted communication
Tutorial on installing MySQL with Docker and implementing remote connection
Docker deploys mysql to achieve remote connection sample code
Detailed explanation of docker daemon remote connection settings
Implementation example of Docker remote connection settings

<<: Getting Started Guide to Converting Vue to React

>>: How to use custom tags in html