MySQL入门02：关于MySQL连接的ABC

Oracle数据库技术 2021-02-25

946

编者按：

纸上得来终觉浅，元知此事要躬行。

【免责声明】本公众号文章仅代表个人观点，与任何公司无关。

创建数据库后，第一件事是连接。

以下简单连接和断开MySQL服务器的方法，以及常见问题解决。

连接命令

可以通过如下命令连接和断开MySQL服务器。

--连接远程的数据库
shell> mysql -h host -u user -p
--连接本地数据库
shell> mysql -u user -p

例：

-bash-4.1$ mysql -uroot -pMyNewPass4!
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 18
Server version: 8.0.23 MySQL Community Server - GPL


Copyright (c) 2000, 2021, Oracle and/or its affiliates.


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>

断开命令

可以通过输入exit或者quit（\q）或者Control+D退出MySQL连接。

例：

mysql> exit
Bye


或者
mysql> quit
Bye


或者
mysql> \q
Bye


或者 Control+D
mysql> ^DBye

常见问题及解决

错误1：ERROR 2002 Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'(2)

连接MySQL可能会报如下错误：

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

这个可能是由于MySQL服务没有启动导致的，可以通过service或者systemctl（Linux7以后）启动服务。

例：

-bash-4.1$ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]


--连接报错
-bash-4.1$ mysql -uroot -pMyNewPass4!
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)


--启动mysql
-bash-4.1$ sudo service mysqld start
Starting mysqld:                                           [  OK  ]


--正常连接
-bash-4.1$ mysql -uroot -pMyNewPass4!
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 9
Server version: 8.0.23 MySQL Community Server - GPL


Copyright (c) 2000, 2021, Oracle and/or its affiliates.


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>

错误2：远程无法连接 ERROR 1130 (HY000): Host ' ' is not allowed to connect to this MySQL server

远程不能访问或者通过host名连接报错：

-bash-4.1$ mysql -h <hostname>  -uroot -pMyNewPass4!
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host '<hostname>' is not allowed to connect to this MySQL server

这是由于MySQL初装后，默认是禁止远程连接数据库的。

解决方法：

更改 "mysql" 数据库里的 "user" 表里的 "host" 列值，从"localhost"改为"%"。

本地登录MySQL后，执行如下：

$ mysql -u root -p
mysql> show databases;
mysql> use mysql;
mysql> select host,user from user;
mysql> update user set host = '%' where user = 'root';
mysql> flush privileges; 或者重启MySQL


--成功登录
$ mysql -h <hostname> -uroot -p


例：
-bash-4.1$ mysql -u root -p -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)


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


Database changed
mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.01 sec)

由于默认的user表中的host是localhost，所以仅允许本地连接。

mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0


mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.01 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)


mysql> exit
Bye


--成功登录
-bash-4.1$ mysql -h <hostname> -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.23 MySQL Community Server - GPL


Copyright (c) 2000, 2021, Oracle and/or its affiliates.


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>

错误3：ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client

远程连接MySQL报错。

-bash-4.1$ mysql -h <hostname> -uroot -p
Enter password:
ERROR 1251 (08004): Client does not support authentication protocol requested by server; consider upgrading MySQL client

解决方法：

升级客户端

MySQL其他常见错误

其他常见错误可以参考如下URL官方文档，或点击阅读原文查看。
https://dev.mysql.com/doc/refman/8.0/en/common-errors.html

B.3.2 Common Errors When Using MySQL Programs
B.3.2.1 Access denied
B.3.2.2 Can't connect to [local] MySQL server
B.3.2.3 Lost connection to MySQL server
B.3.2.4 Password Fails When Entered Interactively
B.3.2.5 Too many connections
B.3.2.6 Out of memory
B.3.2.7 MySQL server has gone away
B.3.2.8 Packet Too Large
B.3.2.9 Communication Errors and Aborted Connections
B.3.2.10 The table is full
B.3.2.11 Can't create/write to file
B.3.2.12 Commands out of sync
B.3.2.13 Ignoring user
B.3.2.14 Table 'tbl_name' doesn't exist
B.3.2.15 Can't initialize character set
B.3.2.16 File Not Found and Similar Errors
B.3.2.17 Table-Corruption Issues