在使用Navicat连接远程数据库报2003错误

原因

出现2003错误是因为远程的数据库不允许非localhost（127.0.0.1）的IP连接

解决方案

一、使用远程工具（xshell）进入mysql命令（输入密码的时候就是没有提示）

[root@izm5e8nyz28v9zr7lhb7moz default]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.28-log MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> 

1，对任意IP的 root 用户登录授权：

MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root用户密码' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)

2，对指定的IP，进行root用户登录授权

mysql> GRANT ALL PRIVILEGES ON *.* TO root@'指定的IP地址' IDENTIFIED BY 'root用户密码' WITH GRANT OPTION;

1跟2执行一个就好了。

3，刷新数据库

MySQL [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

4，exit退出数据库，回车。

MySQL [(none)]> exit;

5，重启数据库服务器

[root@iZm5e8nyz28v9zr7lhb7moZ conf]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL... SUCCESS! 
[root@iZm5e8nyz28v9zr7lhb7moZ conf]# 

二、找到/etc/mysql/mysql.conf.d/mysqld.cnf文件，打开并且加#注释掉bind-address= 127.0.0.1。

-注意：根据自己的配置文件寻找，不一定都会在这个路径下面。

我的配置文件路径：

[root@izm5e8nyz28v9zr7lhb7moz /]# vim /etc/my.cnf

把bind-address = 0.0.0.0，前面加一个#注释一下。也可以不用注释。

三、关闭Linux防火墙

关闭防火墙：（不同的系统配置提示的不一样）
centos6.5

[root@iZ28qtbhs9vZ default]# service iptables stop
iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
iptables: Flushing firewall rules:                         [  OK  ]
iptables: Unloading modules:                               [  OK  ]

四，重启Nginx服务器

方法1：service nginx restart

[root@izm5e8nyz28v9zr7lhb7moz /]# service nginx restart
Redirecting to /bin/systemctl restart  nginx.service

方法2：/etc/init.d/nginx restart

[root@iZ28qtbhs9vZ default]# /etc/init.d/nginx restart
nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful
Stopping nginx:                                            [  OK  ]
Starting nginx:                                            [  OK  ]
[root@iZ28qtbhs9vZ default]# 

五、在次尝试连接远程数据库