Mysql命令行工具

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Mysql命令行工具，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14033字，纯文字阅读大概需要21分钟。

内容图文

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.  重新连接到服务器。可选参数有db和host。
delimiter (\d) Set statement delimiter.       设置语句分隔符
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.
notee     (\t) Don't write into outfile.
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'

默认mysql》提示符可以重新配置。定义提示符的字符串可以包含下面特殊序列：

‘\’后面跟随的其它字母则变为该字母。

mysql客户程序一般交互使用：

shell> mysql db_name

还可以将SQL语句放到一个文件中然后告诉mysql从该文件读取输入。要想实现，创建一个文本文件text_file，并包含你想要执行的语句。然后按如下所示调用mysql：

shell> mysql db_name < text_file

还可以用一个USE db_name语句启动文本文件。在这种情况下，不需要在命令行中指定数据库名：

shell> mysql < text_file

如果正运行mysql，可以使用source或\.命令执行SQL脚本文件：

mysql> source filename

mysql> \. filename

有时想要使用脚本来向用户显示进度信息；为此可以插入下述行：

SELECT '<info_to_display>' AS ' '；

将输出<info_to_display>。

我理解的脚本含义：

符合特定语法的完成某个目的的某种语言写成的文件。

垂直显示查询结果：

一些查询结果如果垂直显示而不用通常的水平表格式显示，则更容易读取。用\G而不用分号

结束查询可以垂直显示查询。例如，包括新行的更长的文本值垂直输出时通常更容易读取：

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G
*************************** 1. row ***************************
  msg_nro: 3068
     date: 2000-03-01 23:29:50
time_zone: +0200
mail_from: Monty
    reply: monty@no.spam.com
  mail_to: "Thimble Smith" <tim@no.spam.com>
      sbj: UTF-8
      txt: >>>>> "Thimble" == Thimble Smith writes:

Thimble> Hi.  I think this is a good idea.  Is anyone familiar
Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my
Thimble> TODO list and see what happens.

Yes, please do that.

Regards,
Monty
     file: inbox-jani-1
     hash: 190402944
1 row in set (0.09 sec)

使用--safe-updates选项

对于新手，有一个有用的启动选项--safe-updates(或--i-am-a-dummy，具有相同的效果)。当你已经发出一个DELETE FROM tbl_name语句但忘记了WHERE子句时很有用。通常情况，这样的语句从表中删除所有行。用--safe-updates，可以通过指定可以识别它们的键值只删除某些行。这样可以帮助防止事故。

若使用--safe-updates选项，mysql连接MySQL服务器时发出下面的语句：

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000,SQL_MAX_JOIN_SIZE=1000000;

SET语句有下面的效果：

·         不允许你执行UPDATE或DELETE语句，除非在WHERE子句中指定一个键值约束或提供一个LIMIT子句(或二者皆使用)。例如：

　　UPDATE tbl_name SET not_key_column=val WHERE key_column=val;

　　UPDATE tbl_name SET not_key_column=val LIMIT1;

所有大的SELECT结果自动限制到1,000行，除非语句包括一个LIMIT子句。

放弃可能需要检查1,000,000多行组合的多表SELECT语句。

要将限制指定为1,000和1,000,000之外的值，可以使用--select_limit和--max_join_size选项覆盖默认值：

shell> mysql --safe-updates --select_limit=500 --max_join_size=10000

禁用mysql自动连接

如果mysql客户程序发送查询时断开与服务器的连接，它立即并自动尝试重新连接服务器并再次发送查询。然而，即使mysql重新连接成功，你的第1个连接也已经结束，并且以前的会话对象和设定值被丢失：包括临时表、自动提交模式，以及用户和会话变量。该行为很危险，如下面的例子所示，服务器将在你不知道的情况下关闭并重启：

Mysqladmin:用于管理MYSQL服务器的客户端

mysqladmin是一个执行管理操作的客户程序。可以用它来检查服务器的配置和当前的状态，创建并删除数据库等等。

C:\Windows\system32>mysqladmin -u root -p --help
EnterPassword:

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin  Ver 8.0.19 for Win64 on x86_64 (MySQL Community Server - GPL)
Copyright (c) 2000, 2020, 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.

Administration program for the mysqld daemon.
Usage: mysqladmin [OPTIONS] command command....
  --bind-address=name IP address to bind to.
  -c, --count=#       Number of iterations to make. This works with -i
                      (--sleep) only.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  --debug-info        This is a non-debug version. Catch this and exit.
  -f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs.
  -C, --compress      Use compression in server/client protocol.
  --character-sets-dir=name
                      Directory for character set files.
  --default-character-set=name
                      Set the default character set.
  -?, --help          Display this help and exit.
  -h, --host=name     Connect to host.
  -b, --no-beep       Turn off beep on error.
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -W, --pipe          Use named pipes to connect to server.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -r, --relative      Show difference between current and previous values when
                      used with -i. Currently only works with extended-status.
  --shared-memory-base-name=name
                      Base name of shared memory.
  -s, --silent        Silently exit if one can't connect to server.
  -S, --socket=name   The socket file to use for connection.
  -i, --sleep=#       Execute commands repeatedly with a sleep between.
  --ssl-mode=name     SSL connection mode.
  --ssl-ca=name       CA file in PEM format.
  --ssl-capath=name   CA directory.
  --ssl-cert=name     X509 cert in PEM format.
  --ssl-cipher=name   SSL cipher to use.
  --ssl-key=name      X509 key in PEM format.
  --ssl-crl=name      Certificate revocation list.
  --ssl-crlpath=name  Certificate revocation list path.
  --tls-version=name  TLS version to use, permitted values are: TLSv1, TLSv1.1,
                      TLSv1.2, TLSv1.3
  --ssl-fips-mode=name
                      SSL FIPS mode (applies only for OpenSSL); permitted
                      values are: OFF, ON, STRICT
  --tls-ciphersuites=name
                      TLS v1.3 cipher to use.
  --server-public-key-path=name
                      File path to the server public RSA key in PEM format.
  --get-server-public-key
                      Get server public key
  -u, --user=name     User for login if not current user.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.
  -E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
  -w, --wait[=#]      Wait and retry if connection is down.
  --connect-timeout=#
  --shutdown-timeout=#
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.
  --enable-cleartext-plugin
                      Enable/disable the clear text authentication plugin.
  --show-warnings     Show warnings after execution
  --compression-algorithms=name
                      Use compression algorithm in server/client protocol.
                      Valid values are any combination of
                      'zstd','zlib','uncompressed'.
  --zstd-compression-level=#
                      Use this compression level in the client/server protocol,
                      in case --compression-algorithms=zstd. Valid range is
                      between 1 and 22, inclusive. Default is 3.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
bind-address                      (No default value)
count                             0
force                             FALSE
compress                          FALSE
character-sets-dir                (No default value)
default-character-set             auto
host                              (No default value)
no-beep                           FALSE
port                              0
relative                          FALSE
shared-memory-base-name           (No default value)
socket                            (No default value)
sleep                             0
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
tls-version                       (No default value)
tls-ciphersuites                  (No default value)
server-public-key-path            (No default value)
get-server-public-key             FALSE
user                              root
verbose                           FALSE
vertical                          FALSE
connect-timeout                   43200
shutdown-timeout                  3600
plugin-dir                        (No default value)
default-auth                      (No default value)
enable-cleartext-plugin           FALSE
show-warnings                     FALSE
compression-algorithms            (No default value)
zstd-compression-level            3

Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf D:\D\Mysql\mysql-8.0.19-winx64\mysql-8.0.19-winx64\my.ini D:\D\Mysql\mysql-8.0.19-winx64\mysql-8.0.19-winx64\my.cnf D:\D\Mysql\mysql-8.0.19-winx64\mysql-8.0.19-winx64\bin\my.ini D:\D\Mysql\mysql-8.0.19-winx64\mysql-8.0.19-winx64\bin\my.cnf
The following groups are read: mysqladmin client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

Where command is a one or more of: (Commands may be shortened)
  create databasename   Create a new database
  debug                 Instruct server to write debug information to log
  drop databasename     Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all logs
  flush-status          Clear status variables
  flush-tables          Flush all tables
  flush-threads         Flush the thread cache
  flush-privileges      Reload grant tables (same as reload)
  kill id,id,...        Kill mysql threads
  password [new-password] Change old password to new-password in current format
  ping                  Check if mysqld is alive
  processlist           Show list of active threads in server
  reload                Reload grant tables
  refresh               Flush all tables and close and open logfiles
  shutdown              Take server down
  status                Gives a short status message from the server
  start-slave           Start slave
  stop-slave            Stop slave
  variables             Prints variables available
  version               Get version info from server

C:\Windows\system32>mysqladmin -u root -p123456 proc stat
显示进程相关信息，服务器状态信息。

mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+
| Id | User            | Host            | db | Command | Time   | State                  | Info             |
+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+
| 4  | event_scheduler | localhost       |    | Daemon  | 363540 | Waiting on empty queue |                  |
| 77 | root            | localhost:52126 |    | Sleep   | 96     |                        |                  |
| 80 | root            | localhost:53183 |    | Query   | 0      | starting               | show processlist |
+----+-----------------+-----------------+----+---------+--------+------------------------+------------------+
Uptime: 363546  Threads: 3  Questions: 929  Slow queries: 0  Opens: 443  Flush tables: 6  Open tables: 106  Queries per second avg: 0.002

mysqladmin status命令的结果显示下面的值：

Uptime: 　　Mysql服务器已经运行的秒数。

Threads　　活动线程(客户)的数目。

Questions　　服务器启动以来客户的问题(查询)数目。

Slow queries　　执行时间超过long_query_time秒的查询的数量。

Opens　　服务器已经打开的数据库表的数量。

Flush tables　　服务器已经执行的flush..., refresh 和 reload命令的数量。

Open tables　　目前打开的表的数量。

内容总结

以上是互联网集市为您收集整理的Mysql命令行工具全部内容，希望文章能够帮你解决Mysql命令行工具所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端