Here are two ways Navicat can connect to the database on Tencent cloud server:

Methods a

Remote client connection permissions do not need to be changed

  1. Click the installed navicat icon on the desktop and enter as follows:

  1. Connection method: In SSH, enter the external IP address, account, and password for logging in to the server

  1. To set the general properties of the database connection, set the host as localhost, since mysql is installed on the local server, and enter the user name and password of mysql

Method 2

Example Modify the connection permission of the MySQL remote client

You are advised to run the following commands as the root user:

  1. We need to change port 3306 to check whether port 3306 is open to the public. Generally, MySQL does not open the external access function by default.

Use the following statement to query

netstat -an | grep 3306
Copy the code

If the query results are as follows, we need to change the MySQL configuration file.

MySQL port 3306 only listens for local connections, which blocks external IP addresses from accessing the database

/etc/mysql/mysql.conf.d/mysqld.cnf
Copy the code

Found in the MySQL configuration fileThe bind – address = 127.0.0.1This line will beThe bind – address = 127.0.0.1Comment out this line or change it to the client host IP you want to use. As shown below:So, our MySQL remote access port is enabled successfully.

  1. Enter the MySQL command interface and run the following SQL statement:

Run the following command to log in to the mysql server as user root

# mysql -uroot -p
Copy the code

Then enter the root user password to log in to the mysql database using the following command

mysql> use mysql; 
Copy the code

Use the following command to check whether the user has access permission:

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

As shown below:

Select * from host where user root is allowed to access all IP addresses. Select * from host where user root is allowed to access all IP addresses

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

As shown below:

If the following exception is thrown:

Duplicate entry ‘%-root’ for key ‘PRIMARY’

Mysql > alter table host % = host %; mysql > alter table host % = host %

mysql> select host from user where user = 'root';
Copy the code

We implement:

mysql> flush privileges;
Copy the code

MySQL > alter table system permissions;

The next thing we need to confirm is that the client users have permissions, and we give them access to them accordingly:

mysql> grant all privileges on *.* to username@"%" identified by "password";
Copy the code

Mysql > select * from mysql;

mysql> exit;
sudo mysql restart
Copy the code
  1. With the server set up, let’s set up the connection in the Navicat client:

Open Navicat, click “Connect” in the upper left, set the connection name of the database, server external IP address, mysql user name and password, etc.

To remotely operate MySQL on the server in Navicat, click ok after the test connection is successful.

As shown below:

Here, Navicat connect to Tencent cloud server on the two methods of the database is over, hope to help you…