当前位置: 首页 > 工具软件 > MySQL Client > 使用案例 >

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

濮佑运
2023-12-01

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

 类似资料: