在開(kāi)發(fā)或測(cè)試環(huán)境在碰到mysql相關(guān)故障時(shí)，大多數(shù)朋友可能會(huì)通過(guò)論壇發(fā)帖，QQ群討論方式來(lái)獲取幫助。該方式是獲取幫助的有效途徑之一。然而如果在生產(chǎn)環(huán)境，在沒(méi)有網(wǎng)絡(luò)的環(huán)境下，這些方式就無(wú)助于問(wèn)題的解決。無(wú)論何種數(shù)據(jù)庫(kù)，從官方網(wǎng)站獲取幫助是最直接最有效的方式。其次沒(méi)有網(wǎng)絡(luò)的環(huán)境下，我們可以通過(guò)MySQL客戶端工具自帶的幫助信息來(lái)解決問(wèn)題。

1)MySQL官方手冊(cè)

和Oracle官方文檔一下，MySQL官方手冊(cè)是獲取MySQL幫助最直接最效的方式。該手冊(cè)包含很多個(gè)部分，比如有關(guān)SQL的語(yǔ)法，MySQL安裝方式，MySQL的系統(tǒng)變量，狀態(tài)變量，命令行的常用工具，數(shù)據(jù)庫(kù)的管等等?？傊且粋€(gè)MySQL數(shù)據(jù)相關(guān)的大合集。支持PDF及html方式下載。

下載位置：

2)MySQL客戶端工具自帶的幫助

?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192 獲取mysql有關(guān)的幫助信息，直接在mysql提示符下輸入help即可獲得有關(guān)在mysql客戶端相關(guān)的幫助信息。 這個(gè)方式與Oracle SQL*plus下的help 是類似的。 mysql> help

For information about MySQL products and services, visit:

For developer information, including the MySQL Reference Manual, visit:

To buy MySQL Enterprise support, training, or other products, visit:

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.

--清除當(dāng)前輸入的語(yǔ)句 connect

(\r) Reconnect to the server. Optional arguments are db and host.

--重新連接，通常用于被剔除或異常斷開(kāi)后重新連接，SQL*plus下也有這樣一個(gè)connect命令 delimiter (\d) Set statement delimiter.

--設(shè)置命令終止符，缺省為；，比如我們可以設(shè)定為/來(lái)表示語(yǔ)句結(jié)束

edit

(\e) Edit command with $EDITOR.

--編輯緩沖區(qū)的上一條SQL語(yǔ)句到文件，缺省調(diào)用vi，文件會(huì)放在/tmp路徑下 ego

(\G) Send command to mysql server, display result vertically.

--控制結(jié)果顯示為垂直顯示 exit

(\q) Exit mysql. Same as quit.

--退出mysql go

(\g) Send command to mysql server.

--發(fā)送命令到mysql服務(wù) help

(\h) Display this help. nopager

(\n) Disable pager, print to stdout.

--關(guān)閉頁(yè)設(shè)置，打印到標(biāo)準(zhǔn)輸出

notee

(\t) Don't write into outfile.

--關(guān)閉輸出到文件 pager

(\P) Set PAGER [to_pager]. Print the query results via PAGER.

--設(shè)置pager方式，可以設(shè)置為調(diào)用more,less等等，主要是用于分頁(yè)顯示 print

(\p) Print current command.

prompt

(\R) Change your mysql prompt.

--改變mysql的提示符

quit

(\q) Quit mysql.

rehash

(\#) Rebuild completion hash.

--自動(dòng)補(bǔ)齊相關(guān)對(duì)象名字

source

(\.) Execute an SQL script file. Takes a file name as an argument. --執(zhí)行腳本文件 status

(\s) Get status information from the server.

--獲得狀態(tài)信息 system

(\!) Execute a system shell command.

--執(zhí)行系統(tǒng)命令

tee

(\T) Set outfile [to_outfile]. Append everything into given outfile.--操作結(jié)果輸出到文件

use

(\u) Use another database. Takes database name as argument.

--切換數(shù)據(jù)庫(kù) charset

(\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. --設(shè)置字符集 warnings (\W) Show warnings after every statement.

--打印警告信息 nowarning (\w) Don't show warnings after every statement. --上面的所有命令，擴(kuò)號(hào)內(nèi)的為快捷操作，即只需要輸入“\”+ 字母即可執(zhí)行

For server side help, type 'help contents'

--注意這里的描述help contents將獲得服務(wù)器端的相關(guān)幫助信息

--演示部分，演示常用命令 --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

--設(shè)置分頁(yè)，在多余一個(gè)頁(yè)面顯示時(shí)會(huì)不停的翻滾，用該命令可以設(shè)置分頁(yè)，設(shè)置為調(diào)用系統(tǒng)命令 mysql> pager more

--設(shè)置為more方式 PAGER set to 'more'mysql> select table_name,table_type,engine from information_schema.tables;

--該查詢會(huì)超出一屏顯示后按空格鍵會(huì)自動(dòng)翻滾到下一屏 mysql> pager tail -5;

--設(shè)置輸出尾部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;

--查看當(dāng)前的pager設(shè)置 PAGER set to 'tail -5'mysql> nopager;

--切換到標(biāo)準(zhǔn)(缺省)pager方式 PAGER set to stdout

--tee命令，輸出日志文件 mysql> tee /tmp/query.log

--開(kāi)啟輸出到文件，相當(dāng)與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;

--關(guān)閉輸出到文件，相當(dāng)于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> '

--恢復(fù)到缺省提示符 SessionA> prompt; Returning to default PROMPT of mysql>

--執(zhí)行sql腳本文件 mysql> system more query.sql

--注意，此時(shí)為當(dāng)前目錄 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)

--獲取狀態(tài)信息 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:

SSL:

Not in use Current pager:

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

SSL:

Not in use Current pager:

lessUsing 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、服務(wù)端的相關(guān)幫助

?1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798 --獲取服務(wù)器管理相關(guān)的幫助，輸入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

--要查詢那一個(gè)部分的內(nèi)容，直接輸入help + 內(nèi)容，如下 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

..........

--接下來(lái)，我們查看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][, ...]

........................

--總結(jié)，即通過(guò)逐級(jí)help的方式即可獲得與其主題相關(guān)的詳細(xì)信息。 --Author: Leshami --Blog: <a target="_blank" href="

</a>--比較常用的show 命令，通常查看系統(tǒng)變量，狀態(tài)變量等 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

--設(shè)置系統(tǒng)變量，用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:

更多信息請(qǐng)查看IT技術(shù)專欄