第一次使用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