说明: 第一次安装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 server on '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