Navicat Remote connection to MySQL

Navicat Remote connection to MySQL

I believe that everyone has to develop on the remote server, where MySQL usage should also be very high, if using Navicat and other visual tools to operate the remote database is a good choice, avoid the command line to write SQL statements operations. The following is a brief introduction to the operation of Navicat connection to a remote database.

1

First we need to change port 3306 to see if Port 3306 is open to the public, and MySQL is not available for external access by default. The statements are as follows:

netstat -an | grep 3306

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

As you can see, MySQL's port 3306 only listens for local connections, which prevents external IP access to the database and modifies the MySQL configuration file my.conf:

vim /etc/mysql/my.cnf

Found it

# Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.bind-address  

bind-address = 127.0.0.1comment out this line or change to the client host IP you want to use.

The MySQL remote access port has been successfully opened.

2

We enter the MySQL command interface and run the following SQL statement to see if the user has access rights:

use mysql;select user, host from user;

The returned results are as follows:

We use the wildcard% to modify the host field for the root user so that you have access to all IP addresses:

update user set host = ‘%‘ where user = ‘root‘;

If you throw the following exception:

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

Note that there are multiple root user records in the user table, we re-execute:

select host from user where user = ‘root‘;

You can see the% value of the field host.

We do:

flush privileges;

Refresh the MySQL system permissions related table.

Finally, restart the MySQL service:

sudo restart mysql

3

The server is set up, we set up the connection in the Navicat client:

Open Navicat, click "Connect" in the upper left, set the database user name, address, password, etc., can be in the Navicat remote operation of MySQL on the server.

Navicat Remote connection to MySQL