1. The background

After mysql is installed, client connections are usually used (Navcat is required). This article describes how to set up remote connections.

2. The train of thought

  1. Example Change the host value of user root to %
  2. Added port 3306 to the firewall
  3. Handles the encryption of passwords

3. The operating

3.1 Changing the host value of the root account to %

Let’s take a look at the user table

use mysql;
select host, user from user;
Copy the code

image.png

\

Note: the host value of the root account is localhost

To start the modification, run:

mysql> use mysql;
mysql> update user set host = '%' where user = 'root';
Copy the code

Revised:

image.png

Note: the root account’s host value has been changed to % %, indicating that any machine can be connected to, and that the fixed IP of your machine can be cached.

3.2 restart mysql

systemctl stop mysqld.service
systemctl start mysqld.service
Copy the code

3.3 Adding Port 3306 for the Firewall

Firewall-cmd --zone=public --add-port=3306/ TCP --permanentCopy the code

Does not support authentication Protocol

image.png

As a last resort, continue to set

select host,user,plugin,authentication_string from mysql.user;

3.4 Handling Password Encryption Modes

The cause of this problem is the encryption of the password.

Let’s take a look at how passwords are now encrypted

select host,user,plugin,authentication_string from mysql.user;
Copy the code

Note: Plugin = cacheing_sha2_password in the figure below

image.png

Run the following command to change the password and encryption mode

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Spri636! ';Copy the code

Take a look at the modified version

Execute the select the host, the user, the plugin, authentication_string from mysql. The user;

image.png

Note: Plugin = cacheing_sha2_password in the figure above

3.5 complete