如何擷取MySQL協助資訊

如何擷取MySQL協助資訊

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

1)MySQL官方手冊

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

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

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

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: l

本文永久更新連結地址：