Docker Deploy mysql Remote Connection to Solve 2003 Problems

Connect MySQL

Here, I use navicat remote connection. Before connecting MySQL, I need the firewall to open the port or close the firewall.

Open port

firewall-cmd --add-port=3306/tcp --permanent
// --permanent  Effective permanently , Failure after restart without this parameter 

firewall-cmd --reload
//  Reload 

Close the firewall

systemctl stop firewalld

systemctl enable iptables
//  Set up startup 

firewall-cmd --reload
//  Reload 

Basic Use of firewalld

Start: systemctl start firewalld

Close: systemctl stop firewalld

View status: systemctl status firewalld

Startup disabled: systemctl disable firewalld

Startup enabled: systemctl enable firewalld

systemctl is the main service management tool of CentOS 7, which combines the functions of service and chkconfig in one body.

Start 1 service: systemctl start firewalld.service

Shut down 1 service: systemctl stop firewalld.service

Restart 1 service: systemctl restart firewalld.service

Displays the status of 1 service: systemctl status firewalld.service

Enable 1 service at boot time: systemctl enable firewalld.service

Disable 1 service at boot time: systemctl stop firewalld0

Check whether the service is booted: systemctl is-enabled firewalld.service

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

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

Configuring firewalld-cmd

View version: firewall-cmd --version

View Help: firewall-cmd --help

Display status: firewall-cmd --state

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

Update firewall rules: firewall-cmd --reload

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

View the area to which the specified interface belongs: systemctl status firewalld0

Reject all packages: systemctl status firewalld1

Cancel the rejection status: firewall-cmd --panic-off

See if you refuse: firewall-cmd --query-panic

Additional: Docker Deployment mysql Remote Connection Resolution 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)

Steps:

1. Enter the mysql container

[root@localhost ~]# docker exec -it javakfmysql bash ## javakfmysql Is the alias of the container, and you can also use the container's id Substitute 

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        |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+

Remarks: host is% to mean no restriction ip localhost to mean native use plugin non-mysql_native_password

Modify the encryption method

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

Then view 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)