如何擷取mysql協助資訊，擷取mysql協助資訊

如何擷取mysql協助資訊，擷取mysql協助資訊

    在開發或測試環境在碰到mysql相關故障時，大多數朋友可能會通過論壇發帖，QQ群討論方式來擷取協助。該方式是擷取協助的有效途徑之一。然而如果在生產環境，在沒有網路的環境下，這些方式就無助於問題的解決。無論何種資料庫，從官方網站擷取協助是最直接最有效方式。其次沒有網路的環境下，我們可以通過MySQL用戶端工具內建的協助資訊來解決問題。

 

1)MySQL官方手冊
    和Oracle官方文檔一下，MySQL官方手冊是擷取MySQL協助最直接最效的方式。該手冊包含很多個部分，比如有關SQL的文法，MySQL安裝方式，MySQL的系統變數，狀態變數，命令列的常用工具，資料庫的管等等。總之是一個MySQL資料相關的大合集。支援PDF及html方式下載。

    下載位置：http://dev.mysql.com/doc/

 

2)MySQL用戶端工具內建的協助

擷取mysql有關的協助資訊，直接在mysql提示符下輸入help即可獲得有關在mysql用戶端相關的協助資訊。這個方式與Oracle SQL*plus下的help 是類似的。mysql> helpFor 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.                                      --退出mysqlgo        (\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 namesYou can turn off this feature to get a quicker startup with -AConnection id:    5Current 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下的spoolLogging 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 offOutfile 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 chardbselect * from tb_isam;mysql> source query.sqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase 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.1Connection id:          6Current database:       chardbCurrent user:           root@localhostSSL:                    Not in useCurrent pager:          lessUsing outfile:          ''Using delimiter:        ;Server version:         5.5.37-log MySQL Community Server (GPL)Protocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    utf8Client characterset:    latin1Conn.  characterset:    latin1UNIX socket:            /var/lib/mysql/mysql.sockUptime:                 3 hours 10 min 59 secThreads: 1  Questions: 97  Slow queries: 0  Opens: 313  Flush tables: 1  Open tables: 51  Queries per second avg: 0.008----------------修改用戶端字元集mysql> charset gbk;Charset changedmysql> \s--------------mysql  Ver 14.14 Distrib 5.5.37, for Linux (x86_64) using readline 5.1Connection id:          6Current database:       chardbCurrent user:           root@localhostSSL:                    Not in useCurrent pager:          lessUsing outfile:          ''Using delimiter:        ;Server version:         5.5.37-log MySQL Community Server (GPL)Protocol version:       10Connection:             Localhost via UNIX socketServer characterset:    latin1Db     characterset:    utf8Client characterset:    gbk     ---用戶端和conn端字元集都變成gbk了。Conn.  characterset:    gbkUNIX socket:            /var/lib/mysql/mysql.sockUptime:                 3 hours 13 min 33 secThreads: 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 1mysql> 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 contentsmysql> help contents;You asked for help about help category: "Contents"For more information, type 'help <item>', where <item> is one of the followingcategories:   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 followingtopics:   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 reloadvarious internal caches, flush tables, or acquire locks. To executeFLUSH, you must have the RELOAD privilege. Specific flush options mightrequire 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_nametbl_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 describesthose following:SHOW AUTHORSSHOW {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 = exprThe SET statement assigns values to different types of variables thataffect the operation of the server or your client. Older versions ofMySQL employed SET OPTION, but this syntax is deprecated in favor ofSET without OPTION.URL: http://dev.mysql.com/doc/refman/5.5/en/set-statement.html

怎擷取mysql版本

直接在命令列運行Mysql -V,可以查看mysql版本資訊和發行資訊。
 
mysql 怎擷取 表頭資訊

use information_schema
select * from columns where table_name='表名';

show columns from 表名;