Navicat remote connection to MySQL implementation steps analysis

Preface

I believe that everyone has been developing on a remote server, and the usage rate of MySQL should be quite high. It is a good choice to use visual tools such as Navicat to operate the remote database, avoiding the operation of writing SQL statements on the command line. The following is a brief introduction to the operation of Navicat connecting to a remote database.

1

First, we need to change port 3306 and check whether it is open to the outside world. By default, MySQL does not allow external access. The statement is as follows:

netstat -an | grep 3306

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

It can be seen that the MySQL port 3306 only listens to local connections, which prevents external IP from accessing the database. Modify the MySQL configuration file my.conf:

vim /etc/mysql/my.cnf

turn up

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

Comment out the line bind-address = 127.0.0.1 or change it to the client host ip you want to use.

At this point, the MySQL remote access port has been successfully opened.

2

We enter the MySQL command interface and run the following SQL statement to check whether 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 corresponding to the root user so that he has access to all IP addresses:

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

If the following exception is thrown:

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

This indicates that there are multiple ROOT user records in the USER table. Let's re-execute:

select host from user where user = 'root';

You can see the % value of the host field.

We perform:

flush privileges;

Refresh the MySQL system privilege related tables.

Finally, restart the MySQL service:

sudo restart mysql

3

The server is set up, let's set up the connection in the Navicat client:

Open Navicat, click "Connect" in the upper left corner, set the database username, address, password, etc., and you can remotely operate MySQL on the server in Navicat.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.