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. 重新连接到服务器。可选参数有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》提示符可以重新配置。定义提示符的字符串可以包含下面特殊序列：
|
选项 |
描述 |
|
\v |
服务器版本 |
|
\d |
当前的数据库 |
|
\h |
服务器主机 |
|
\p |
当前的TCP/IP端口或套接字文件 |
|
\u |
你的用户名 |
|
\U |
你的全user_name@host_name账户名 |
|
\\ |
‘\’反斜线字符 |
|
\n |
新行字符 |
|
\t |
Tab字符 |
|
\ |
空格(反斜线后面的空格) |
|
\_ |
空格 |
|
\R |
当前的时间，24-小时军用时间(0-23) |
|
\r |
当前的时间，标准12-小时(1-12) |
|
\m |
当前时间的分钟 |
|
\y |
当前的年，两位 |
|
\Y |
当前的年，四位 |
|
\D |
当前的日期 |
|
\s |
当前时间的秒 |
|
\w |
当前周的天，3字符格式(Mon，Tue，...) |
|
\P |
am/pm |
|
\o |
当前的月，数字格式 |
|
\O |
当前的月，3字符格式(Jan，Feb，...) |
|
\c |
随发出的每个语句递增的计数 |
|
\S |
分号 |
|
\' |
单引号 |
|
\" |
双引号 |
‘\’后面跟随的其它字母则变为该字母。
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 目前打开的表的数量。
