mysql client 连接远程_第一次使用MySQL client连接到远程MySQL server时的常见报错及处理...

说明： 第一次安装MySQL client，并尝试连接到远程的MySQL server时，可能遇到一些报错，这篇文章主要讲解了这些报错的解决，主要包括：

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.16.128' (111)

和

ERROR 1130 (HY000): Host 'db2b' is not allowed to connect to this MySQL server

环境：Ubuntu 14.04, MySQL client 5.7.18

1. MySQL client安装

下载并安装client，需要下载并安装两个包

$sudo dpkg -i mysql-{common,community-client}_*.deb

注意：不能只安装mysql-community-client包，否则会报错，说依赖mysql-common，如下：

qingsong@db2b:~$sudo dpkg -i mysql-community-client_5.7.18-1ubuntu14.04_amd64.deb

Selecting previously unselected package mysql-community-client.

(Reading database ... 57417 files and directories currently installed.)

Preparing to unpack mysql-community-client_5.7.18-1ubuntu14.04_amd64.deb ...

Unpacking mysql-community-client (5.7.18-1ubuntu14.04) ...

dpkg: dependency problems prevent configuration of mysql-community-client:

mysql-community-client depends on mysql-common (>= 5.7.18-1ubuntu14.04); however:

Package mysql-common is not installed.

dpkg: error processing package mysql-community-client (--install):

dependency problems - leaving unconfigured

Processing triggers for man-db (2.6.7.1-1ubuntu1) ...

Errors were encountered while processing:

mysql-community-client

2. 安装完成之后，尝试连接到远程的MySQL server时，报错：

qingsong@db2b:~$ mysql -h 192.168.16.128 -u root -p

Enter password:

ERROR 2003 (HY000): Can't connect to MySQL serveron '192.168.16.128' (111)

直接使用telnet尝试，也无法连接：

qingsong@db2b:~$ telnet 192.168.16.128 3306

Trying 192.168.16.128...

telnet: Unable to connect to remote host: Connection refused

在Mysql server上，查看3306端口，发现是127.0.0.1：

root@db2a:~#netstat -an | grep -i 3306

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN

进入/etc/mysql/mysql.conf.d目录，发现mysqld.cnf文件里有说明# By default we only accept connections from localhost，于是将bind-address=127.0.0.1这一行加上注释之后，重启mysql server：

root@db2a:/etc/mysql/mysql.conf.d# ls

mysqld.cnf

[mysqld]

pid-file        = /var/run/mysqld/mysqld.pid

socket          = /var/run/mysqld/mysqld.sock

datadir         = /var/lib/mysql

log-error       = /var/log/mysql/error.log

# By default we only accept connections from localhost

bind-address  = 127.0.0.1

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

root@db2a:/etc/mysql/mysql.conf.d# service mysql restart

* Stopping MySQL Community Server 5.7.18

...

* MySQL Community Server 5.7.18 is stopped

* Re-starting MySQL Community Server 5.7.18

..

* MySQL Community Server 5.7.18 is started

再次查看端口监控状态：

qingsong@db2a:~$ netstat -an | grep -i 3306

tcp6       0      0 :::3306                 :::*                    LISTEN

3.再次在MySQL client上连接时，报出新的错误：

qingsong@db2b:~$ mysql -h 192.168.16.128 -u root -pqingsong --port 3306

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1130 (HY000): Host 'db2b' is not allowed to connect to this MySQL server

在MySQL server上，查看user表，发现只允许localhost的连接

root@db2a:/etc/mysql/mysql.conf.d# mysql -pqingsong

mysql: [Warning] Using a password on the command line interface can be insecure.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 4

Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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>use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

mysql> select user,host from user;

+-----------+-----------+

| user      | host      |

+-----------+-----------+

| mysql.sys | localhost |

| root      | localhost |

+-----------+-----------+

2 rows in set (0.00 sec)

于是赋予MySQL client相应的权限：

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.16.129' IDENTIFIED BY 'qingsong' WITH GRANT OPTION;

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from user;

+-----------+----------------+

| user      | host           |

+-----------+----------------+

| root      | 192.168.16.129 |

| mysql.sys | localhost      |

| root      | localhost      |

+-----------+----------------+

3 rows in set (0.00 sec)

MySQL client再次尝试，成功连接：

qingsong@db2b:~$ mysql -h 192.168.16.128 -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 5

Server version: 5.7.18 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> use sample Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed