Docker deploys MySQL remote connection to solve the problem of 2003

Time:2022-7-31

Connect to MySQL

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

Open port

firewall-cmd --add-port=3306/tcp --permanent
//--permanent takes effect. It will become invalid after restarting without this parameter
firewall-cmd --reload
//Reload

Turn off firewall


systemctl stop firewalld
systemctl enable iptables
//Set startup
firewall-cmd --reload
//Reload

Basic use of firewalld

Start:systemctl start firewalld

close:systemctl stop firewalld

View status:systemctl status firewalld

Power on disable:systemctl disable firewalld

Boot enable:systemctl enable firewalld

Systemctl is the main tool in the service management tools of centos7. It integrates the functions of 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 startup:systemctl enable firewalld.service

Disable a service at power on:systemctl disable firewalld.service

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

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

To view the list of services that failed to start:systemctl --failed

Configure 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 of the specified interface:firewall-cmd --get-zone-of-interface=eth0

Reject all packages:firewall-cmd --panic-on

Cancel reject status:firewall-cmd --panic-off

Check whether to reject: firewall-cmd --query-panic

Add: docker deploys MySQL remote connection solution 1251

reason:

MySQL 8.0 uses caching by default_ sha2_ Password authentication mechanism, the client does not support new encryption methods.

Solution:

Modify the encryption method of the user (root)

Steps:

1. Enter the MySQL container

[ [email protected] ~]#Docker exec -it javakfmysql bash \\\javakfmysql is the alias of the container, which can also be replaced by the ID of the container here

2. Log in to MySQL


[email protected]:/# 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% means unlimited IP. Localhost means that the local machine uses plugin instead of MySQL_ native_ password

Modify encryption method

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_ native_ password BY '123456'; ###  123456 login password of MySQL
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)

The above is my personal experience. I hope I can give you a reference, and I hope you can support developeppaer. If there is any mistake or failure to consider completely, please don’t hesitate to comment.