【MySQL附录】A1：客户端工具使用详解（上）：客户端mysql

最新推荐文章于 2024-09-11 01:53:41 发布

zyplanke

最新推荐文章于 2024-09-11 01:53:41 发布

阅读量348

点赞数 1

分类专栏： MySQL 文章标签： MySQL

本文链接：https://blog.csdn.net/zyplanke/article/details/103004133

版权

MySQL 专栏收录该内容

26 篇文章 3 订阅

订阅专栏

概述

MySQL是经典的Client/Server两层架构体系。在两端都有不同的程序和进程：

Server端

最主要的服务进程是mysqld。在Linux的有两种方式启动停止mysql服务：

使用Linux的service来管理。通过service来调度服务器启动脚本mysql.server。（CentOS这类System V及变种，系统默认是用过service来管理mysql启动停止）mysqld.service，

运行mysqld_safe脚本。不通过linux的service，直接运行mysqld_safe，该脚本使用相对比较安全的方式尝试启动mysqld。

Client端

最常见的就是我们连接数据库所使用的mysql命令，它是一个客户端程序，用于以交互方式将输入SQL语句或从文件中以批处理模式发送到服务器执行，从从服务器接收结果并展示。除此以外，常见的客户端工具还有：

mysqladmin 执行管理操作，例如创建或删除数据库，重新加载授权表，将表刷新到磁盘以及重新打开日志文件。还可以用于从服务器检索版本，进程和状态信息。

perror 显示操作系统或MySQL错误代码的含义的工具

mysqldump 将MySQL数据库以SQL，文本或XML格式转储到文件中的客户端工具

mysqlimport 使用将文本文件导入到各自的表中Load Data的客户端工具

mysqlpump 将MySQL数据库作为SQL转储到文件中的客户端工具

mysqlcheck 对表维护，用于检查，修复，分析和优化表的客户端工具

还有更多的工具，具体见官方文档：https://dev.mysql.com/doc/refman/8.0/en/programs-overview.html

由于客户端工具比较多，因此分为上下两篇：上篇只介绍客户端mysql，下篇介绍上面列的其他客户端工具。本文为上篇。

客户端mysql使用初步

客户端mysql是能接受输入和编辑功能的简单易用的SQL Shell。它支持交互和非交互使用。交互使用时，查询结果以ASCII表格式显示。非交互使用（例如，用作过滤器）时，结果以制表符分隔的格式显示。可以使用命令选项更改输出格式。

通过客户端连接

在客户端（）Windows中或者Linux）中的shell上，使用mysql的命令格式为：

mysql -h host -P port -u user -p

其中host、port、user代表运行MySQL服务器的IP、端口、MySQL帐户名。在MySQL服务器上本地连接。则可以不指定host信息，只需使用以下命令：

mysql -u user -p

命令中不指定host，该命令host指自动赋值为localhost，然后去连接MySQL。

提示1：连接MySQL数据库有两种方式：TCP/IP（一般用IP+端口）和Unix套接字（一般叫socket或者sock）。这种不指定-h选项既localhost的连接，是通过服务端操作系统本地的套接字文件/var/lib/mysql/mysql.sock去访问MySQL数据库，不走TCP/IP。

提示2：通常我们认为，localhost就等价于本机127.0.0.1（hosts文件）。但是在MySQL中，二者是不同的，在MySQL中user权限设置中localhost与127.0.0.1也是分开设置的。当设置连接是指定-h为127.0.0.1时，系统通过TCP/IP方式连接数据库；当-h指定为localhost时，系统通过socket方式连接数据库。

这时尝试测试可以发现，在MySQL服务正常运行情况下，如果缺少mysql.sock文件。不指定-h选项或者-h指定为localhost时，连接MySQL，会报ERROR 2002 (HY000)错误。只要加上-h选项显示指定IP，则可以正常连接。

也在在成功连接后，输入status命令查看。

运行SQL（及\G结束符）

使用mysql非常容易，在成功连接后，直接输入完整的SQL语句或者命令，默认最后需要英文分号(;) 或者 \g 或 \G 结束。按Enter键后执行。其中\g作用等价于英文分号(;)；而 \G 除了作为结束符号，并将结果集旋转90度变成纵向进行展示。\G比较常用。

运行脚本（批量处理）

可以像这样运行脚本文件（批处理文件script_file.sql），并将执行结果自动存放到output.tab文件中。

shell $> mysql -u user -p -D db_name < script_file.sql > output.tab

shell $> mysql -h host -P port -u user -p -D db_name < script_file.sql > output.tab

断开连接并推出

成功连接后，您可以随时在提示符下键入QUIT（或\q）断开连接mysql：

mysql> quit

MySQL客户端选项

mysql客户端工具涉及的选项比较多，可参见官方文档：https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html

下面介绍常用的几个：

--help, -?

显示帮助，然后退出。这个help很有用，信息分为两部分：第一部分是对各种选项的说明，第二部分是mysqldump的各种选项的默认值。

提示：注意其中有一句话：Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 。

既，对于第二部分选项的默认值【标题为： Value (after reading options)】。其中after reading options是指，读取以上*.cnf文件中[mysql]节下面的客户端参数配置。该cnf文件中[mysqld]是服务端的配置，[mysql]是客户端的配置。也就是说，--help显示的默认值会显示读取*.cnf文件后配置的值。

--auto-rehash

如果mysql客户端中不能使用tab键自动补全。可在mysql客户端工具连接时加选项“--auto-rehash”，这样可以使用TAB键进行补全。这里的补全内容是：表名，列名，数据库名（schema名），不能补全命令，变量、选项等。默认是true。

--bind-address=ip_address

如果客户端机器上具有多个网络接口，使用此选项选择用于使用哪个客户端IP去连接到MySQL服务器

--database=db_name， -D db_name

指定连接后，要使用的数据库（schema）。这样避免在连接成功后，再使用use进行切换。

--default-character-set=charset_name

使用charset_name作为客户端和连接的默认字符集。一般应设置为与服务端字符集移植（推荐都使用utf8mb4）。一般直接配置到cnf文件中，不必每次连接都指定。

--html， -H

结果集以HTML格式输出。
--xml， -X

结果集以XML格式输出。

mysql客户端内置命令

客户端mysql除了除了将SQL语句发送到服务端执行并展示返回的结果集外，自身内置一些命令。在成功连接到mysql后，在mysql提示符后，输入help，可以看到内置命令列表及描述。

提示：前面的选项是随mysql执行时需要的参数，而本节的命令是已经运行mysql后并连接到数据库。在mysql交互命令环境中，可以运行的命令。两者是不同的。

mysql> help

For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

常用的介绍如下：

edit， \e

编辑当前输入语句。mysql 检查EDITOR和 VISUAL环境变量的值，以确定要使用哪个编辑器。如果未设置任何变量，则默认编辑器为 vi。该edit命令仅在Unix中有效。

status

提供当前的连接信息，和正在使用的服务器的状态信息。比较有用。

system command， \! command

使用默认命令解释器执行给定命令。该system命令仅在Unix中有效。

use db_name

切换当前数据库（schema）

quit， \q

退出mysql。

数据库管理语句

本节介绍的数据库管理语句不是mysql客户端基本的命令，它需要与数据库服务端交互，可以视为SQL语法中的一部分。但是它经常在mysql客户端工具中使用，大家可能对它不太熟悉（不像日常看到的增删改查SQL语句）。因此专门拿一节来说明。涉及用户账号管理、表维护、插件管理、SET语法、SHOW语法、其他语句。

用户账户管理

用户账户管理涉及的管理语句如下，由于比较简单，举例使用说明如下：

CREATE USER
ALTER USER
RENAME USER
GRANT
REVOKE
SET PASSWORD
DROP USER

mysql>  CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'Pwd@#123' PASSWORD EXPIRE INTERVAL 180 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'jeffrey'@'localhost' IDENTIFIED BY 'newPwd@#123';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'jeffrey'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE INSERT ON test.* FROM 'jeffrey'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> RENAME USER 'jeffrey'@'localhost' TO 'jeff'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

表维护语句

表维护涉及的管理语句如下

ANALYZE TABLE
CHECK TABLE
CHECKSUM TABLE 求表数据的SUM值，简单高效地比较表数据是否一致的工具
OPTIMIZE TABLE
REPAIR TABLE 仅针对MyISAM

插件管理

INSTALL PLUGIN INSTALL PLUGIN plugin_name SONAME 'shared_library_name'
UNINSTALL PLUGIN UNINSTALL PLUGIN plugin_name

SET语法（变量）

SET var_name=value 设置用户自定义变量(一个@开通)和系统变量（两个@@开头）。
SET CHARACTER SET 将设置三个变量：character_set_client 和character_set_results 置为用户指定值。并将 character_set_connection设置为与 character_set_database一行。

mysql> SET @name = 43;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @total_rows = (SELECT count(*) FROM articles);
Query OK, 0 rows affected (0.00 sec)

mysql> select @name, @total_rows;  
+-------+-------------+
| @name | @total_rows |
+-------+-------------+
|    43 |           7 |
+-------+-------------+
1 row in set (0.00 sec)

上面是定义用户变量。对于系统变量：分为全局变量和会话变量。全局变量需要在前面增加GLOBAL限定词。

下面两条设置全局变量的语句是等价的：

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;

下面设置会话变量是等价的：

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';

注：上面的@表示为用户自定义变量。 @@表示为系统变量，系统变量分为全局变量和会话变量。

SHOW语法

show是查看各种信息，非常丰富，如下。不建议大家记住，随意通过help show在线查看帮助：

SHOW BINARY LOGS
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE EVENT
SHOW CREATE FUNCTION
SHOW CREATE PROCEDURE
SHOW CREATE TABLE
SHOW CREATE TRIGGER
SHOW CREATE USER
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINE
SHOW ENGINES
SHOW ERRORS
SHOW EVENTS
SHOW FUNCTION CODE
SHOW FUNCTION STATUS
SHOW GRANTS
SHOW INDEX
SHOW MASTER STATUS
SHOW OPEN TABLES
SHOW PLUGINS
SHOW PRIVILEGES
SHOW PROCEDURE CODE
SHOW PROCEDURE STATUS
SHOW PROCESSLIST
SHOW PROFILE
SHOW PROFILES
SHOW RELAYLOG EVENTS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW TRIGGERS
SHOW VARIABLES
SHOW WARNINGS

其他管理语句

BINLOG
CACHE INDEX 该CACHE INDEX语句将表索引分配给特定的键高速缓存。它仅适用于 MyISAM表
FLUSH 可以清除或重新加载各种内部缓存，刷新表或获取锁
KILL 杀死指定的链接，或该连接正在执行的语句 KILL [CONNECTION | QUERY] processlist_id
LOAD INDEX INTO CACHE 仅适用于MyISAM 表
RESET 该RESET语句用于清除各种服务器操作的状态，比FLUSH更强。
SHUTDOWN 停止MySQL服务端进程。

#修改用户的属性SQL只改变了表中的数据，需要通过flush命令将表中的数据同步刷新到内存中使其立即生效。
mysql> update mysql.user set host = '%' where user = 'root';
mysql> flush privileges;


mysql> select * from information_schema.PROCESSLIST;
+----+------+-----------------+--------------------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST            | DB                 | COMMAND | TIME | STATE     | INFO                                         |
+----+------+-----------------+--------------------+---------+------+-----------+----------------------------------------------+
|  4 | root | localhost       | test               | Sleep   |   89 |           | NULL                                         |
|  5 | root | localhost:57118 | information_schema | Sleep   |   83 |           | NULL                                         |
|  6 | root | localhost       | test               | Query   |    0 | executing | select * from information_schema.PROCESSLIST |
+----+------+-----------------+--------------------+---------+------+-----------+----------------------------------------------+
3 rows in set (0.00 sec)

mysql> kill 5;
Query OK, 0 rows affected (0.00 sec)

mysql>  select * from information_schema.PROCESSLIST;
+----+------+-----------+------+---------+------+-----------+----------------------------------------------+
| ID | USER | HOST      | DB   | COMMAND | TIME | STATE     | INFO                                         |
+----+------+-----------+------+---------+------+-----------+----------------------------------------------+
|  4 | root | localhost | test | Sleep   |  101 |           | NULL                                         |
|  6 | root | localhost | test | Query   |    0 | executing | select * from information_schema.PROCESSLIST |
+----+------+-----------+------+---------+------+-----------+----------------------------------------------+
2 rows in set (0.00 sec)



#停止数据库服务端
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> select date();
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
ERROR: 
Can't connect to the server