如何获取mysql帮助信息

爱被打了一巴掌 2022-08-12 01:27 245阅读 0赞

在开发或测试环境在碰到mysql相关故障时，大多数朋友可能会通过论坛发帖，QQ群讨论方式来获取帮助。该方式是获取帮助的有效途径之一。然而如果在生产环境，在没有网络的环境下，这些方式就无助于问题的解决。无论何种数据库，从官方网站获取帮助是最直接最有效的方式。其次没有网络的环境下，我们可以通过MySQL客户端工具自带的帮助信息来解决问题。

1)MySQL官方手册  
    和Oracle官方文档一下，MySQL官方手册是获取MySQL帮助最直接最效的方式。该手册包含很多个部分，比如有关SQL的语法，MySQL安装方式，MySQL的系统变量，状态变量，命令行的常用工具，数据库的管等等。总之是一个MySQL数据相关的大合集。支持PDF及html方式下载。

下载位置：[http://dev.mysql.com/doc/][http_dev.mysql.com_doc]

2)MySQL客户端工具自带的帮助

获取mysql有关的帮助信息，直接在mysql提示符下输入help即可获得有关在mysql客户端相关的帮助信息。
    这个方式与Oracle SQL*plus下的help 是类似的。
    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.   --重新连接，通常用于被剔除或异常断开后重新连接，SQL*plus下也有这样一个connect命令
    delimiter (\d) Set statement delimiter.                                       --设置命令终止符，缺省为；，比如我们可以设定为/来表示语句结束 
    edit      (\e) Edit command with $EDITOR.                                     --编辑缓冲区的上一条SQL语句到文件，缺省调用vi，文件会放在/tmp路径下
    ego       (\G) Send command to mysql server, display result vertically.       --控制结果显示为垂直显示
    exit      (\q) Exit mysql. Same as quit.                                      --退出mysql
    go        (\g) Send command to mysql server.                                  --发送命令到mysql服务
    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.       --设置pager方式，可以设置为调用more,less等等，主要是用于分页显示
    print     (\p) Print current command.                     
    prompt    (\R) Change your mysql prompt.                                      --改变mysql的提示符  
    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.
    --上面的所有命令，扩号内的为快捷操作，即只需要输入“\”+ 字母即可执行
    
    For server side help, type 'help contents'    --注意这里的描述help contents将获得服务器端的相关帮助信息
    
    --演示部分，演示常用命令
    --connect命令
    mysql> connect chardb localhost;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Connection id:    5
    Current database: chardb
    
    --设置分页，在多余一个页面显示时会不停的翻滚，用该命令可以设置分页，设置为调用系统命令
    mysql> pager more           --设置为more方式
    PAGER set to 'more'
    mysql> select table_name,table_type,engine from information_schema.tables;   --该查询会超出一屏显示后按空格键会自动翻滚到下一屏
    mysql> pager tail -5;       --设置输出尾部5行
    PAGER set to 'tail -5'
    mysql> select table_name,table_type,engine from information_schema.tables;
    | setup_timers                                 | BASE TABLE  | PERFORMANCE_SCHEMA |
    | threads                                      | BASE TABLE  | PERFORMANCE_SCHEMA |
    | animals                                      | BASE TABLE  | InnoDB             |
    | shop                                         | BASE TABLE  | InnoDB             |
    +----------------------------------------------+-------------+--------------------+
    92 rows in set (0.02 sec)
    
    mysql> pager;                --查看当前的pager设置
    PAGER set to 'tail -5'
    mysql> nopager;              --切换到标准(缺省)pager方式
    PAGER set to stdout
    
    --tee命令，输出日志文件
    mysql> tee /tmp/query.log                    --开启输出到文件，相当与SQL*plus下的spool
    Logging to file '/tmp/query.log'
    mysql> select table_name,table_type,engine from information_schema.tables;
    +----------------------------------------------+-------------+--------------------+
    | table_name                                   | table_type  | engine             |
    +----------------------------------------------+-------------+--------------------+
    | CHARACTER_SETS                               | SYSTEM VIEW | MEMORY             |
    | COLLATIONS                                   | SYSTEM VIEW | MEMORY             |
           .............
    mysql> notee;                                --关闭输出到文件，相当于SQL*Plus下的spool off
    Outfile disabled.
    mysql> system tail /tmp/query.log            --查看输出的日志文件
    | setup_consumers                              | BASE TABLE  | PERFORMANCE_SCHEMA |
    | setup_instruments                            | BASE TABLE  | PERFORMANCE_SCHEMA |
    | setup_timers                                 | BASE TABLE  | PERFORMANCE_SCHEMA |
    | threads                                      | BASE TABLE  | PERFORMANCE_SCHEMA |
    | animals                                      | BASE TABLE  | InnoDB             |
    | shop                                         | BASE TABLE  | InnoDB             |
    +----------------------------------------------+-------------+--------------------+
    92 rows in set (0.02 sec)       
    
    --改变mysql提示符
    mysql> prompt SessionA> 
    PROMPT set to 'SessionA> '
    
    --恢复到缺省提示符
    SessionA> prompt;
    Returning to default PROMPT of mysql> 
    
    --执行sql脚本文件
    mysql> system more query.sql   --注意，此时为当前目录
    use chardb
    select * from tb_isam;
    mysql> source query.sql
    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
    +------+-------+
    | id   | value |
    +------+-------+
    |    1 | a     |
    |    2 | b     |
    |    3 | c     |
    |    4 | f     |
    +------+-------+
    4 rows in set (0.00 sec)
    
    --获取状态信息
    mysql> status;
    --------------
    mysql  Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using readline 5.1
    
    Connection id:          6
    Current database:       chardb
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          less
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         5.5.37-log MySQL Community Server (GPL)
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    utf8
    Client characterset:    latin1
    Conn.  characterset:    latin1
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 3 hours 10 min 59 sec
    
    Threads: 1  Questions: 97  Slow queries: 0  Opens: 313  Flush tables: 1  Open tables: 51  Queries per second avg: 0.008
    --------------
    
    --修改客户端字符集
    mysql> charset gbk;
    Charset changed
    mysql> \s
    --------------
    mysql  Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using readline 5.1
    
    Connection id:          6
    Current database:       chardb
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          less
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         5.5.37-log MySQL Community Server (GPL)
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    utf8
    Client characterset:    gbk     ---客户端和conn端字符集都变成gbk了。
    Conn.  characterset:    gbk
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 3 hours 13 min 33 sec
    
    Threads: 1  Questions: 105  Slow queries: 0  Opens: 313  Flush tables: 1  Open tables: 51  Queries per second avg: 0.009
    --------------
    
    mysql> warnings;
    Show warnings enabled.
    mysql> selecs 1;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selecs 1' at line 1
    mysql> show warnings;
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                    |
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selecs 1' at line 1 |
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> show errors;
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level | Code | Message                                                                                                                                                    |
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selecs 1' at line 1 |
    +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    mysql> nowarning;
    Show warnings disabled.

3、服务端的相关帮助

--获取服务器管理相关的帮助，输入help contents
    mysql> help contents;
    You asked for help about help category: "Contents"
    For more information, type 'help <item>', where <item> is one of the following
    categories:
       Account Management
       Administration
       Compound Statements
       Data Definition
       Data Manipulation
       Data Types
       Functions
       Functions and Modifiers for Use with GROUP BY
       Geographic Features
       Help Metadata
       Language Structure
       Plugins
       Procedures
       Storage Engines
       Table Maintenance
       Transactions
       User-Defined Functions
       Utility
    
    --要查询那一个部分的内容，直接输入help + 内容，如下
    mysql> help administration;
    You asked for help about help category: "Administration"
    For more information, type 'help <item>', where <item> is one of the following
    topics:
       BINLOG
       CACHE INDEX
       FLUSH
       FLUSH QUERY CACHE
       HELP COMMAND
       KILL
       ..........
    
    --接下来，我们查看administration部分下的flush命令用法，直接输入help flush;即可
    mysql> help flush;
    Name: 'FLUSH'
    Description:
    Syntax:
    FLUSH [NO_WRITE_TO_BINLOG | LOCAL]
        flush_option [, flush_option] ...
    
    The FLUSH statement has several variant forms that clear or reload
    various internal caches, flush tables, or acquire locks. To execute
    FLUSH, you must have the RELOAD privilege. Specific flush options might
    require additional privileges, as described later.      
    
    --查看cache index的帮助信息
    mysql> help CACHE INDEX;
    Name: 'CACHE INDEX'
    Description:
    Syntax:
    CACHE INDEX
      tbl_index_list [, tbl_index_list] ...
      [PARTITION (partition_list | ALL)]
      IN key_cache_name
    
    tbl_index_list:
      tbl_name [[INDEX|KEY] (index_name[, index_name] ...)]
    
    partition_list:
      partition_name[, partition_name][, ...]
      ........................
    
    --总结，即通过逐级help的方式即可获得与其主题相关的详细信息。  --Author: Leshami --Blog: http://blog.csdn.net/leshami --比较常用的show 命令，通常查看系统变量，状态变量等
    mysql> help show;
    Name: 'SHOW'
    Description:
    SHOW has many forms that provide information about databases, tables,
    columns, or status information about the server. This section describes
    those following:
    
    SHOW AUTHORS
    SHOW {BINARY | MASTER} LOGS
    
    --设置系统变量，用set 命令
    mysql> help set;
    Name: 'SET'
    Description:
    Syntax:
    SET variable_assignment [, variable_assignment] ...
    
    variable_assignment:
          user_var_name = expr
        | [GLOBAL | SESSION] system_var_name = expr
        | [@@global. | @@session. | @@]system_var_name = expr
    
    The SET statement assigns values to different types of variables that
    affect the operation of the server or your client. Older versions of
    MySQL employed SET OPTION, but this syntax is deprecated in favor of
    SET without OPTION.
    
    URL: http://dev.mysql.com/doc/refman/5.5/en/set-statement.html

[![鹏城DBA总群][DBA]][DBA_DBA]

[http_dev.mysql.com_doc]: http://dev.mysql.com/doc/
[DBA]: http://pub.idqqimg.com/wpa/images/group.png
[DBA_DBA]: /images/20220810/a45e493028ab423ba6f24a31ad4b9ff6.png