# MySQL 學習筆記
整理：Jims of [肥肥世家](http://www.ringkee.com)
[yjnet@21cn.com](mailto:yjnet@21cn.com)
Copyright ? 2004 本文遵從GNU 的自由文檔許可證(Free Document License)的條款，歡迎轉載、修改、散布。
發布時間: 2004年11月06日
最近更新: 2005年01月12日
**Table of Contents**
+ [1\. MySQL數據庫的數據](#id2874769)
+ [1.1\. 數據值類型(data type)](#id2808339)
+ [1.1.1\. 數值](#id2811708)
+ [1.1.2\. 字符串](#id2811766)
+ [1.1.3\. 日期和時間](#id2809741)
+ [1.2\. 列類型(column type)](#id2810045)
+ [1.2.1\. 數值類的數據列類型](#id2810068)
+ [1.2.2\. 字符串類數據列類型](#id2810479)
+ [1.2.3\. 日期,時間型數據列類型](#id2861926)
+ [1.3\. 唯一編號](#id2808625)
+ [1.4\. 字符集支持](#id2809227)
+ [1.4.1\. MySQL4.1以前版本](#id2809248)
+ [1.4.2\. MySQL4.1以后版本](#id2809320)
+ [1.4.3\. 各級字符集的查詢方法](#id2809483)
+ [1.4.4\. Unicode支持](#id2875432)
+ [1.5\. 如何選擇數據列類型？](#id2875454)
+ [1.6\. 表達式操作符](#id2875598)
+ [1.7\. 類型轉換](#id2876479)
+ [2\. 查詢優化](#id2876523)
+ [2.1\. 索引](#id2876581)
+ [2.2\. 查詢優化程序](#id2876806)
+ [2.3\. 數據列類型與查詢效率](#id2876901)
+ [2.4\. 有效地加載數據](#id2877109)
+ [2.5\. 調度和鎖定](#id2877300)
+ [2.6\. 服務器優化](#id2877538)
+ [2.7\. 硬件優化](#id2877768)
+ [3\. 數據庫管理](#id2877831)
+ [3.1\. 數據目錄](#id2877912)
+ [3.2\. MySQL數據表在系統中表現形式](#id2878184)
+ [3.3\. 數據表最大尺寸限制](#id2878374)
+ [3.4\. 狀態文件和日志文件](#id2878524)
+ [3.5\. 調整MySQL數據目錄位置](#id2878733)
+ [4\. MySQL數據庫日常管理](#id2879192)
+ [4.1\. 數據庫安全管理](#id2879207)
+ [4.2\. 服務器的啟動和關閉](#id2879430)
+ [4.3\. 連接故障恢復](#id2879527)
+ [4.4\. MySQL用戶帳號管理](#id2879632)
+ [4.5\. 日志文件管理](#id2880322)
+ [4.5.1\. 日志失效處理](#id2880627)
+ [4.6\. MySQL服務器的一些優化配置](#id2880878)
+ [4.7\. 優化服務器](#id2881514)
+ [4.8\. 運行多個MySQL服務器](#id2881890)
+ [4.9\. MySQL服務器鏡像配置](#id2882214)
+ [5\. 數據庫安全](#id2882744)
+ [5.1\. 保護MySQL安裝程序文件](#id2882779)
+ [5.2\. 權限表](#id2882930)
+ [5.3\. 建立加密連接](#id2883576)
+ [6\. 數據庫的備份、維護和修復](#id2883760)
+ [6.1\. 檢查/修復數據表](#id2883844)
+ [6.2\. 備份數據庫](#id2884038)
+ [6.3\. 使用備份恢復數據](#id2884388)
+ [7\. MySQL程序介紹](#id2884565)
+ [8\. MySQL How-To](#id2884785)
+ [8.1\. 連接數據庫服務器](#id2884793)
+ [8.2\. 更新用戶密碼](#id2884871)
+ [8.3\. MySQL讀取配置文件的順序](#id2884889)
+ [8.4\. 重設置MySQL管理員密碼的方法](#id2884929)
+ [8.5\. NULL值](#id2885136)
+ [8.6\. 使用SQL變量](#id2885196)
+ [8.7\. 改變默認提示符](#id2885227)
+ [8.8\. 非優化的全數據表DELETE操作](#id2885256)
+ [8.9\. MySQL事務處理示例](#id2885280)
## Chapter 1\. MySQL數據庫的數據
MySQL數據庫是由數據組成的，為了能方便管理和使用這些數據，我們把這些數據進行分類，形成各種數據類型，有數據值的類型，有表中數據列的類型，有數據表的類型。理解MySQL的這些數據類型能使我們更好地使用MySQL數據庫。下面對各種數據類型進行簡單的介紹。
## 1.1\. 數據值類型(data type)
對MySQL中數據值的分類，有數值型、字符型、日期型和空值等，這和一般的編程語言的分類差不多。
### 1.1.1\. 數值
MySQL中的數值分整型和浮點型兩種。MySQL支持科學記數法。整型可以是十進制，也可是十六進制數。
### 1.1.2\. 字符串
MySQL支持以單或雙引號包圍的字符序列。如“MySQL tutorial”、‘Mysql Database’。
MySQL能識別字符串中的轉義序列，轉義序列用反斜杠(\)表示。下面是一個轉義序列列表。
**Table 1.1\. 轉義序列**
| 轉義序列 | 含義 |
| --- | --- |
| \0 | NUL(ASCII的0值) |
| \' | 單引號 |
| \" | 雙引號 |
| \b | 后退符 |
| \n | 換行符 |
| \r | 回車符 |
| \t | 制表符 |
| \\ | 反斜杠 |
| \Z | Ctrl+Z |
如果字符串本身包含有單雙引號，則用以下三種方法中的一種來表示：
* 字符串的引號和字符串兩端的引號雙同，則雙寫該引號。如：'mysql''s test'。
* 用與字符串的引號不同的引號把字符串引起來，如："mysql's test"。
* 用反斜杠轉義引號，如："mysql\' test"，'mysql\' test'。這樣就不用理會字符串兩端的是單引號還是雙引號了。
字符串可由一個十六進制數表示，如0x61表示字符"a"。由MySQL 4.0開始，字符串值也可用ANSI SQL表示法X'val'來表示。如X'61'表示字符"a"。
從MySQL 4.1開始，可以為字符串值專門指定一個字符集。
### 1.1.3\. 日期和時間
MySQL默認按“年-月-日”的順序顯示日期。
## 1.2\. 列類型(column type)
MySQL數據庫的表是一個二維表，由一個或多個數據列構成。每個數據列都有它的特定類型，該類型決定了MySQL如何看待該列數據，我們可以把整型數值存放到字符類型的列中，MySQL則會把它看成字符串來處理。MySQL中的列類型有三種：數值類、字符串類和日期/時間類。從大類來看列類型和數值類型一樣，都是只有三種。但每種列類型都還可細分。下面對各種列類型進行詳細介紹。
### 1.2.1\. 數值類的數據列類型
數值型的列類型包括整型和浮點型兩大類。
**Table 1.2\. 數值類數據列類型**
| 數據列類型 | 存儲空間 | 描述 |
| --- | --- | --- |
| TINYINT | 1字節 | 非常小的正整數，帶符號：-128~127，不帶符號：0~255 |
| SMALLINT | 2字節 | 小整數，帶符號：-32768~32767，不帶符號：0~65535 |
| MEDIUMINT | 3字節 | 中等大小的整數，帶符號：-8388608~8388607，不帶符號：0~16777215 |
| INT | 4字節 | 標準整數，帶符號：-2147483648~2147483647，不帶符號：0~4294967295 |
| BIGINT | 8字節 | 大整數，帶符號：-9223372036854775808~9233372036854775807，不帶符號：0~18446744073709551615 |
| FLOAT | 4字節 | 單精度浮點數，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38 |
| DOUBLE | 8字節 | 雙精度浮點數，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308 |
| DECIMAL | M+2字節 | 以字符串形式表示的浮點數，它的取值范圍可變，由M和D的值決定。 |
#### 1.2.1.1\. 整型數據列類型
MySQL有五種整型數據列類型，即TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。它們之間的區別是取值范圍不同，存儲空間也各不相同。在整型數據列后加上UNSIGNED屬性可以禁止負數，取值從0開始。
聲明整型數據列時，我們可以為它指定個顯示寬度M(1~255)，如INT(5)，指定顯示寬度為5個字符,如果沒有給它指定顯示寬度，MySQL會為它指定一個默認值。顯示寬度只用于顯示，并不能限制取值范圍和占用空間，如：INT(3)會占用4個字節的存儲空間，并且允許的最大值也不會是999,而是INT整型所允許的最大值。
#### 1.2.1.2\. 浮點型數據列類型
MySQL有三種浮點型數據列類型，分別是：FLOAT，DOUBLE和DECIMAL。浮點類數據類型有一個最大可表示值和一個最小非零可表示值，最小非零可表示值決定了該類型的精確度。
MySQL 4.0.2版之后，FLOAT和DOUBLE都可以指定UNSIGNED屬性。當指定該屬性時，取值范圍不平移到正數區間，而只是簡單地把浮點類型的負數部份去掉。
浮點類型也有M(1~255)和D(1~30，且不能大于M-2)。分別表示顯示寬度和小數位數。M和D在FLOAT和DOUBLE中是可選的，默認，當MySQL版本大于3.23.6時，FLOAT和DOUBLE類型將被保存為硬件所支持的最大精度。DECIMAL的M和D值在MySQL3.23.6后可選，默認D值為0,M值為10。
#### 1.2.1.3\. 如何選擇數值類數據列類型？
為了節省存儲空間和提高數據庫處理效率，我們應根據應用數據的取值范圍來選擇一個最適合的數據列類型。如果把一個超出數據列取值范圍的數存入該列，則MySQL就會截短該值，如：我們把99999存入SMALLINT(3)數據列里，因為SMALLINT(3)的取值范圍是-32768~32767，所以就會被截短成32767存儲。顯示寬度3不會影響數值的存儲。只影響顯示。
對于浮點數據列，存入的數值會被該列定義的小數位進行四舍五入。如把一個1.234存入FLOAT(6.1)數據列中，結果是1.2。
DECIMAL與FLOAT和DOUBLE的區別是：DECIMAL類型的值是以字符串的形式被儲存起來的，它的小數位數是固定的。它的優點是，不會象FLOAT和DOUBLE類型數據列那樣進行四舍五入而產生誤差，所以很適合用于財務計算；而它的缺點是：由于它的存儲格式不同，CPU不能對它進行直接運算，從而影響運算效率。DECIMAL(M，D)總共要占用M+2個字節。
#### 1.2.1.4\. 數值類數據列的屬性
* ZEROFILL屬性適用于所有數值類數據列類型，作用是，如果數值的寬度小于定義的顯示寬度，則在數值前填充0。
* UNSIGNED屬性不允許數據列出現負數。
* AUTO_INCREMENT屬性可生成獨一無二的數字序列。只對整數類的數據列有效。
* NULL和NOT NULL屬性設置數據列是否可為空。
* DEFAULT屬性可為數據列指定默認值。
### 1.2.2\. 字符串類數據列類型
字符串可以用來表示任何一種值，所以它是最基本的類型之一。我們可以用字符串類型來存儲圖象或聲音之類的二進制數據，也可存儲用gzip壓縮的數據。下表介紹了各種字符串類型：
**Table 1.3\. 字符串類數據列類型**
| 類型 | 最大長度 | 占用存儲空間 |
| --- | --- | --- |
| CHAR[(M)] | M字節 | M字節 |
| VARCHAR[(M)] | M字節 | L+1字節 |
| TINYBLOD，TINYTEXT | 2^8-1字節 | L+1字節 |
| BLOB，TEXT | 2^16-1字節 | L+2 |
| MEDIUMBLOB，MEDIUMTEXT | 2^24-1字節 | L+3 |
| LONGBLOB，LONGTEXT | 2^32-1字節 | L+4 |
| ENUM('value1','value2',...) | 65535個成員 | 1或2字節 |
| SET('value1','value2',...) | 64個成員 | 1,2,3,4或8字節 |
L+1、L+2是表示數據列是可變長度的，它占用的空間會根據數據行的增減面則改變。數據行的總長度取決于存放在這些數據列里的數據值的長度。L+1或L+2里多出來的字節是用來保存數據值的長度的。在對長度可變的數據進行處理時，MySQL要把數據內容和數據長度都保存起來。
如果把超出字符串最大長度的數據放到字符類數據列中，MySQL會自動進行截短處理。
ENUM和SET類型的數據列定義里有一個列表，列表里的元素就是該數據列的合法取值。如果試圖把一個沒有在列表里的值放到數據列里，它會被轉換為空字符串(“”)。
字符串類型的值被保存為一組連續的字節序列，并會根據它們容納的是二進制字符串還是非二進制字符而被區別對待為字節或者字符：
* 二進制字符串被視為一個連續的字節序列，與字符集無關。MySQL把BLOB數據列和帶BINARY屬性的CHAR和VARCHAR數據列里的數據當作二進制值。
* 非二進制字符串被視為一個連續排列的字符序列。與字符集有關。MySQL把TEXT列與不帶BINARY屬性的CHAR和VARCHAR數據列里的數據當作二進制值對待。
在MySQL4.1以后的版本中，不同的數據列可以使用不同的字符集。在MySQL4.1版本以前，MySQL用服務器的字符集作為默認字符集。
非二進制字符串，即我們通常所說的字符串，是按字符在字符集中先后次序進行比較和排序的。而二進制字符串因為與字符集無關，所以不以字符順序排序，而是以字節的二進制值作為比較和排序的依據。下面介紹兩種字符串的比較方式：
* 二進制字符串的比較方式是一個字節一個字節進行的，比較的依據是兩個字節的二進制值。也就是說它是區分大小寫的，因為同一個字母的大小寫的數值編碼是不一樣的。
* 非二進制字符串的比較方式是一個字符一個字符進行的，比較的依據是兩個字符在字符集中的先后順序。在大多數字符集中，同一個字母的大小寫往往有著相同的先后順序，所以它不區分大小寫。
二進制字符串與字符集無關，所以無論按字符計算還是按字節計算，二進制字符串的長度都是一樣的。所以VARCHAR(20)并不表示它最多能容納20個字符，而是表示它最多只能容納可以用20個字節表示出來的字符。對于單字節字符集，每個字符只占用一個字節，所以這兩者的長度是一樣的，但對于多字節字符集，它能容納的字符個數肯定少于20個。
#### 1.2.2.1\. CHAR和VARCHAR
CHAR和VARCHAR是最常用的兩種字符串類型，它們之間的區別是：
* CHAR是固定長度的，每個值占用相同的字節，不夠的位數MySQL會在它的右邊用空格字符補足。
* VARCHAR是一種可變長度的類型，每個值占用其剛好的字節數再加上一個用來記錄其長度的字節即L+1字節。
CHAR(0)和VARCHAR(0)都是合法的。VARCHAR(0)是從MySQL4.0.2版開始的。它們的作用是作為占位符或用來表示各種on/off開關值。
如何選擇CHAR和VARCHAR，這里給出兩個原則：
* 如果數據都有相同的長度，選用VARCHAR會多占用空間，因為有一位用來存儲其長度。如果數據長短不一，選用VARCHAR能節省存儲空間。而CHAR不論字符長短都需占用相同的空間，即使是空值也不例外。
* 如果長度出入不大，而且是使用MyISAM或ISAM類型的表，則用CHAR會比VARCHAR好，因為MyISAM和ISAM類型的表對處理固定長度的行的效率高。
> 在一個數據表里，只要有一個數據列的長度是可變的，則所有數據列的長度將是可變的。MySQL會進行自動地轉換。一個例外，CHAR長度小于4的不會進行自動轉換，因為MySQL會認為這樣做沒必要，節省不了多少空間。反而MySQL會把大量長度小的VARCHAR轉換成CHAR，以減少空間占用量。
#### 1.2.2.2\. BLOB和TEXT
BLOB是二進制字符串，TEXT是非二進制字符串。兩者都可存放大容量的信息。
有關BLOB和TEXT索引的建立：
* BDB表類型和MySQL3.23.2以上版本的MyISAM表類型允許在BLOB和TEXT數據列上建立索引。
* ISAM、HEAP和InnoDB表不支持大對象列的索引。
使用BLOB和TEXT應注意的問題：
* 由于這兩個列類型所存儲的數據量大，所以刪除和修改操作容易在數據表里產生大量的碎片，需定期運行OPTIMIZE TABLE以減少碎片和提高性能。
* 如果使用的值非常巨大，就需對服務器進行相應的優化調整，增加max_allowed_packet參數的值。對那些可會用到變些巨大數據的客戶程序，也需加大它們的數據包大小。
#### 1.2.2.3\. ENUM和SET
ENUM和SET都是比較特殊的字符串數據列類型，它們的取值范圍是一個預先定義好的列表。ENUM或SET數據列的取值只能從這個列表中進行選擇。ENUM和SET的主要區別是：
* ENUM只能取單值，它的數據列表是一個枚舉集合。它的合法取值列表最多允許有65535個成員。例如：ENUM("N","Y")表示，該數據列的取值要么是"Y"，要么就是"N"。
* SET可取多值。它的合法取值列表最多允許有64個成員。空字符串也是一個合法的SET值。
ENUM和SET的值是以字符串形式出現的，但在內部，MySQL以數值的形式存儲它們。
* ENUM的合法取值列表中的字符串被按聲明定義的順序被編號，從1開始。
* SET的編號不是按順序進行編號的，SET中每一個合法取值都對應著SET值里的一個位。第一個合法取值對應0位，第二個合法取值對應1位，以此類推，如果數值形式的SET值等于0,則說明它是一個空字符串，如果某個合法的取值出現在SET數據列里，與之對應的位就會被置位；如果某個合法的取值沒有出現在SET數據列里，與之對應的位就會被清零。正因為SET值與位有這樣的對應關系，所以SET數據列的多個合法取值才能同時出現并構成SET值。
#### 1.2.2.4\. 字符串類型數據列的字符集屬性
在MySQL 4.1以前的版本，字符串數據列的字符集由服務器的字符決定，MySQL 4.1版以后的版本可對每個字符串數據列指定不同的字符串。如果按默認方式設置，可按數據列、數據表、數據庫、服務器的順序關聯字符串的字符集，直到找一個明確定義的字符集。
### 1.2.3\. 日期,時間型數據列類型
MySQL的日期時間類型有：DATE，DATETIME，TIME，TIMESTAMP和YEAR，下表是這些類型的取值范圍和存儲空間要求：
**Table 1.4\. 日期，時間類型列**
| 類型 | 取值范圍 | 存儲空間 | 零值表示法 |
| --- | --- | --- | --- |
| DATE | 1000-01-01~9999-12-31 | 3字節(MySQL3.23版以前是4字節 ) | 0000-00-00 |
| TIME | -838:59:59~838:59:59 | 3字節 | 00:00:00 |
| DATETIME | 1000-01-01 00:00:00~9999-12-31 23:59:59 | 8字節 | 0000-00-00 00:00:00 |
| TIMESTAMP | 19700101000000~2037年的某個時刻 | 4字節 | 00000000000000 |
| YEAR | YEAR(4)：1901~2155 YEAR(2)：1970~2069 | 1字節 | 0000 |
> MySQL總是把日期和日期里的年份放在最前面，按年月日的順序顯示。
#### 1.2.3.1\. DATE、TIME、DATATIME數據列類型
DATE、TIME和DATATIME類型分別存放日期值、時間值、日期和時間值的組合。它們的格式分別是“CCYY-MM-DD”、“hh:mm:ss”、“CCYY-MM-DD hh:mm:ss”。
DATATIME里的時間值和TIME值是有區別的，DATATIME里的時間值代表的是幾點幾分，TIME值代表的是所花費的時間。當向TIME數據列插值時，需用時間的完整寫法，如12分30秒要寫成“00:12:30”。
#### 1.2.3.2\. TIMESTAMP數據列類型
TIMESTAMP數據列的格式是CCYYMMDDhhmmss，取值范圍從19700101000000開始，即1970年1月1號，最大到2037年。它的特點是能把數據行的創建或修改時間記錄下來：
* 如果把一個NULL值插入TIMESTAMP列，這個數據列就將自動取值為當前的日期和時間。
* 在創建和修改數據行時，如果沒有明確對TIMESTAMP數據列進行賦值，則它就會自動取值為當前的日期和時間。如果行中有多個TIMESTAMP列，只有第一個會自動取值。
* 如果對TIMESTAMP設置一個確定的日期和時間值，則會使TIMESTAMP的自動取值功能失效。
TIMESTAMP默認的列寬是14,可指定列寬，以改變顯示效果。但不論你指定的列寬如何，MySQL都是以4字節來存儲TIMESTAMP值，也總是以14位精度來計算。
如果需要把創建時間和最近一次修改時間同時記錄下來，可以用兩個時間戳來記錄，一個記錄創建時間，一個記錄修改時間。不過需記住兩件事，一是要把記錄修改時間的TIMESTAMP數據列放在最前面，這樣才會自動取值；二是創建一條新記錄時，要用now()函數來初始化創建時間TIMESTAMP數據列，這樣，該TIMESTAMP數據列就不會再變化。
#### 1.2.3.3\. YEAR
YEAR是一種單字節的數據列類型，YEAR(4)的取值范圍是1901~2155,YEAR(2)的取值范圍是1970~2069,但只顯示最后兩位數。MySQL能自動把兩位數字年份轉換成四位數字的年份，如97和14分被轉換成1997和2014。轉換規則是這樣的：
* 年份值00~69將被轉換成2000~2069；
* 年份值70~99將被轉換成1970~1999。
> 00被轉換成0000,而不是2000。因為數值00也就是0,而0值是YEAR的一個合法取值。
## 1.3\. 唯一編號
在數據庫應用，我們經常要用到唯一編號，以標識記錄。在MySQL中可通過數據列的AUTO_INCREMENT屬性來自動生成。MySQL支持多種數據表，每種數據表的自增屬性都有差異，這里將介紹各種數據表里的數據列自增屬性。
* ISAM表
* 如果把一個NULL插入到一個AUTO_INCREMENT數據列里去，MySQL將自動生成下一個序列編號。編號從1開始，并1為基數遞增。
* 把0插入AUTO_INCREMENT數據列的效果與插入NULL值一樣。但不建議這樣做，還是以插入NULL值為好。
* 當插入記錄時，沒有為AUTO_INCREMENT明確指定值，則等同插入NULL值。
* 當插入記錄時，如果為AUTO_INCREMENT數據列明確指定了一個數值，則會出現兩種情況，情況一，如果插入的值與已有的編號重復，則會出現出錯信息，因為AUTO_INCREMENT數據列的值必須是唯一的；情況二，如果插入的值大于已編號的值，則會把該插入到數據列中，并使在下一個編號將從這個新值開始遞增。也就是說，可以跳過一些編號。
* 如果自增序列的最大值被刪除了，則在插入新記錄時，該值被重用。
* 如果用UPDATE命令更新自增列，如果列值與已有的值重復，則會出錯。如果大于已有值，則下一個編號從該值開始遞增。
* 如果用replace命令基于AUTO_INCREMENT數據列里的值來修改數據表里的現有記錄，即AUTO_INCREMENT數據列出現在了replace命令的where子句里，相應的AUTO_INCREMENT值將不會發生變化。但如果replace命令是通過其它的PRIMARY KEY OR UNIQUE索引來修改現有記錄的(即AUTO_INCREMENT數據列沒有出現在replace命令的where子句中)，相應的AUTO_INCREMENT值--如果設置其為NULL(如沒有對它賦值)的話--就會發生變化。
* last_insert_id()函數可獲得自增列自動生成的最后一個編號。但該函數只與服務器的本次會話過程中生成的值有關。如果在與服務器的本次會話中尚未生成AUTO_INCREMENT值，則該函數返回0。
其它數據表的自動編號機制都以ISAM表中的機制為基礎。
* MyISAM數據表
* 刪除最大編號的記錄后，該編號不可重用。
* 可在建表時可用“AUTO_INCREMENT=n”選項來指定一個自增的初始值。
* 可用alter table table_name AUTO_INCREMENT=n命令來重設自增的起始值。
* 可使用復合索引在同一個數據表里創建多個相互獨立的自增序列，具體做法是這樣的：為數據表創建一個由多個數據列組成的PRIMARY KEY OR UNIQUE索引，并把AUTO_INCREMENT數據列包括在這個索引里作為它的最后一個數據列。這樣，這個復合索引里，前面的那些數據列每構成一種獨一無二的組合，最末尾的AUTO_INCREMENT數據列就會生成一個與該組合相對應的序列編號。
* HEAP數據表
* HEAP數據表從MySQL4.1開始才允許使用自增列。
* 自增值可通過CREATE TABLE語句的 AUTO_INCREMENT=n選項來設置。
* 可通過ALTER TABLE語句的AUTO_INCREMENT=n選項來修改自增始初值。
* 編號不可重用。
* HEAP數據表不支持在一個數據表中使用復合索引來生成多個互不干擾的序列編號。
* BDB數據表
* 不可通過CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n選項來改變自增初始值。
* 可重用編號。
* 支持在一個數據表里使用復合索引來生成多個互不干擾的序列編號。
* InnDB數據表
* 不可通過CREATE TABLE OR ALTER TABLE的AUTO_INCREMENT=n選項來改變自增初始值。
* 不可重用編號。
* 不支持在一個數據表里使用復合索引來生成多個互不干擾的序列編號。
在使用AUTO_INCREMENT時，應注意以下幾點：
* AUTO_INCREMENT是數據列的一種屬性，只適用于整數類型數據列。
* 設置AUTO_INCREMENT屬性的數據列應該是一個正數序列，所以應該把該數據列聲明為UNSIGNED，這樣序列的編號個可增加一倍。
* AUTO_INCREMENT數據列必須有唯一索引，以避免序號重復。
* AUTO_INCREMENT數據列必須具備NOT NULL屬性。
* AUTO_INCREMENT數據列序號的最大值受該列的數據類型約束，如TINYINT數據列的最大編號是127,如加上UNSIGNED，則最大為255。一旦達到上限，AUTO_INCREMENT就會失效。
* 當進行全表刪除時，AUTO_INCREMENT會從1重新開始編號。全表刪除的意思是發出以下兩條語句時：
```
delete from table_name;
or
truncate table table_name
```
這是因為進行全表操作時，MySQL實際是做了這樣的優化操作：先把數據表里的所有數據和索引刪除，然后重建數據表。如果想刪除所有的數據行又想保留序列編號信息，可這樣用一個帶where的delete命令以抑制MySQL的優化：
```
delete from table_name where 1;
```
這將迫使MySQL為每個刪除的數據行都做一次條件表達式的求值操作。
* 強制MySQL不復用已經使用過的序列值的方法是：另外創建一個專門用來生成AUTO_INCREMENT序列的數據表，并做到永遠不去刪除該表的記錄。當需要在主數據表里插入一條記錄時，先在那個專門生成序號的表中插入一個NULL值以產生一個編號，然后，在往主數據表里插入數據時，利用LAST_INSERT_ID()函數取得這個編號，并把它賦值給主表的存放序列的數據列。如：
```
insert into id set id = NULL;
insert into main set main_id = LAST_INSERT_ID();
```
* 可用alter命令給一個數據表增加一個具有AUTO_INCREMENT屬性的數據列。MySQL會自動生成所有的編號。
* 要重新排列現有的序列編號，最簡單的方法是先刪除該列，再重建該，MySQL會重新生連續的編號序列。
* 在不用AUTO_INCREMENT的情況下生成序列，可利用帶參數的LAST_INSERT_ID()函數。如果用一個帶參數的LAST_INSERT_ID(expr)去插入或修改一個數據列，緊接著又調用不帶參數的LAST_INSERT_ID()函數，則第二次函數調用返回的就是expr的值。下面演示該方法的具體操作：
先創建一個只有一個數據行的數據表：
```
create table seq_table (id int unsigned not null);
insert into seq_table values (0);
```
接著用以下操作檢索出序列號：
```
update seq_table set seq = LAST_INSERT_ID( seq + 1 );
select LAST_INSERT_ID();
```
通過修改seq+1中的常數值，可生成不同步長的序列，如seq+10可生成步長為10的序列。
該方法可用于計數器，在數據表中插入多行以記錄不同的計數值。再配合LAST_INSERT_ID()函數的返回值生成不同內容的計數值。這種方法的優點是不用事務或LOCK，UNLOCK表就可生成唯一的序列編號。不會影響其它客戶程序的正常表操作。
## 1.4\. 字符集支持
MySQL4.1以前版本服務器只能使用單一字符集，從MySQL4.1版本開始，不僅服務器能夠使用多種字符集，而且在服務器、數據庫、數據表、數據列以及字符串常數多個級別上設置不同的字符集。
### 1.4.1\. MySQL4.1以前版本
MySQL4.1以前版本的字符集由服務器默認指定，默認值是編譯系統時指定的字符集，該字符集也可通過在啟動服務器時指定--default-character-set來修改。這種修改會對數據表的索引造成影響，因為索引的順序是和字符集有關的，修改字符集會使這個已排序的順序產生錯誤。要解決該問題，我們要用修改后的字符集的排序順序重建表的索引。重建索引有以下幾種方法：
* 用mysqldump導出數據，再清除表里的內容，最后用導出文件重新導入。數據表的索引將在導入數時重建。該方法適用于所有數據表類型。
* 刪除索引，然后重建。用alter table命令或drop index和create index命令來完成。該方法也適用于所有數據表類型。但該方法需要我們了解重建索引的精確定義。
* MyISAM數據表的索引可以用myisamchk程序的--recover和--quick選項加上一個用來設定新字符集的--set-character-set選項進行重建。還可以用mysqlcheck程序的--repair和--quick選項或者一個帶QUICK選項的REPLACE TABLE語句來重建索引，這種方式較方便。
### 1.4.2\. MySQL4.1以后版本
MySQL4.1以后的版本對字符集的支持好了很多，具有以下新增功能：
* 支持服務器同時使用多種字符集。
* 允許在服務器，數據庫，數據表，數據列等多級別上設置不同的字符集。
* 服務器的默認字符集在編譯時選定，但可在啟動服務器時用--default-character-set選項來更改。
* 用ALTER DATABASE db_name DEFAULT CHARACTER SET charset來設置數據庫字符集。 如果只有default參數，則使用服務器的字符集。
* 用CREATE TABLE table_name(...) CHARACTER SET = charset設置數據表字符集。如果charset為default，則使用數據表所在數據庫的字符集作為數據表的字符集。
* 在數據列中，可用CHARACTER SET charset屬性來設置數據列的字符集。charset不能是default，如果沒有該屬性，則默認使用數據表的字符集。允許設置字符集的數據列有char，varchar(不帶binary屬性)及TEXT類型。
* 用_charset str轉換字符串常數的字符集。如：_utf8 'mysql'，_latinl 'oracle'。該方法只適用于括在引號內的字符串，其它十六進制常數 、字符串表達式等可用CONVERT()函數進行轉換，如：SELECT CONVERT( str USING charset)。
* 通過MySQL提供的函數可進行字符集轉換和查詢。
* 新增的COLLATE操作符使我們可按某一種字符集的排序順序來處理另一種字符集的數據。如：SELECT a from t ORDER BY a COLLATE utf-8；
* 用SHOW CHARACTER SET命令可顯示服務器支持的字符集列表。
* 當服務器轉換到另一種字符集時，會自動對索引進行重新排序。
* 通過UTF-8和UCS2字符集提供了Unicode支持。
MySQL現在還不支持：1,在同一個字符串里混用不同字符集的字符；2,在同一個數據列里混用不同的字符集。
### 1.4.3\. 各級字符集的查詢方法
* 服務器級
```
SHOW CHARACTER SET；可查出可供使用的所有字符集。
SHOW VARIABLES LIKE 'character_set'；可查出服務器的默認字符集。
```
* 可查出數據庫級的字符集。
```
SHOW CREATE DATABASE db_name；
```
* 兩條命令可查出數據表的字符集。
```
SHOW CREATE TABLE table_name；
SHOW TABLE STATUS LIKE 'table_name'
```
* 以下幾命令可查出數據列的字符集：
```
DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
SHOW CREATE TABLE table_name;
```
* 用CHARSET()函數可確定特定字符串，字符串表達式或數據列值相關聯的字符串的字符集。如：SELECT CHARSET(str)。
### 1.4.4\. Unicode支持
MySQL提供兩種字符集來支持Unicode。一個是UTF-8，一種可變長的編碼格式，需用1至4個字節來表示一個字符；另一個是UCS2，該字符集中的每個字符需要用兩個字節來表示。
## 1.5\. 如何選擇數據列類型？
選擇正確的數據列類型能大大提高數據庫的性能和使數據庫具有高擴展性。在選擇數據列類型時，請從以下幾個方面考慮：
* 存放到數據列中的數據類型。
* 數據值的取值范圍。
* 考慮性能和處理效率。
* 數值操作比字符操作快。
* 小類型的處理速度比大類型快。
* 不同數據表中固定長度類型和可變長度類型的處理效率是不同的。
可變長度類型在經過刪除和修改操作后容易產生碎片，降低系統性能，需定期運行OPTIMIZE TABLE命令以優化數據表。
固定長度類型由于有固定的長度，所以容易確定每條記錄的起始點，可加快數據表的修復速度。
在MyISAM和ISAM表中使用固定長度類型數據列有助改善數據庫性能。
在InnoDB表中，固定長度和可變長度數據列類型都以相同方式存儲，所以固定長度數據列類型并沒有性能優勢，反而由于可度長度數據列類型由于占用存儲空間較少，所以處理速度會快些。
* 可索引類型能加快數據的查詢速度。
* 明確指定數據列的NOT NULL屬性可使MySQL在檢索過程中不用去判斷數據列是否是NULL，所以可加快處理速度。
* 數據如何進行比較，是否區分大小寫。
* 是否要在數據列上建立索引。
## 1.6\. 表達式操作符
**Table 1.5\. 算術操作符**
| 操作符 | 語法 | 含義 |
| --- | --- | --- |
| + | a + b | 相加 |
| - | a - b | 相減 |
| - | - a | 求負 |
| * | a * b | 乘法 |
| / | a / b | 除法 |
| % | a % b | 求余 |
**Table 1.6\. 邏輯操作符**
| 操作符 | 語法 | 含義 |
| --- | --- | --- |
| AND 或 && | a AND b 或 a && b | 邏輯與，若兩個操作數同時為真，則為真 |
| OR 或 || | a OR b 或 a || b | 邏輯或，只要有一個操作數為真，則為真 |
| XOR | a XOR b | 邏輯異或，若有且僅有一個操作數為真，則為真 |
| NOT 或 ！ | NOT a 或 !a | 邏輯非，若操作數為假，則為真 |
**Table 1.7\. 位操作符**
| 操作符 | 語法 | 含義 |
| --- | --- | --- |
| & | a & b | 按位與，若操作數同位同為1,則該位為1 |
| | | a | b | 按位或，若操作數同位有一位為1,則該位為1 |
| ^ | a ^ b | 按拉異或，若操作數同一位分別為1和0,則該位為1 |
| << | a << b | 把a中的各個位左移b個位置 |
| >> | a >> b | 把a中的各個位右移b個位置 |
**Table 1.8\. 比較操作符**
| 操作符 | 語法 | 含義 |
| --- | --- | --- |
| = | a = b | 若兩個操作數相等，則為真 |
| <=> | a <=> b | 若兩個操作數相等，則為真，可用于NULL值比較 |
| != 或 <> | a != b 或 a <> b | 若兩個操用數不等，則為真 |
| < | a < b | 若a小于b，則為真 |
| <= | a <= b | 若a小于或等于b，則為真 |
| > | a > b | 若a大于b，則為真 |
| >= | a > b | 若a大于或等于b，則為真 |
| IN | a IN (b1,b2,...) | 若a等于b1,b2,...中的某一個，則為真 |
| BETWEEN | a BETWEEN b AND c | 若a在b和c之間(包括b和c)，則為真 |
| NOT BETWEEN | a NOT BETWEEN b AND c | 若a不在b和c之間(包括b和c)，則為真 |
| LIKE | a LIKE b | SQL模式匹配，若a匹配b，則為真 |
| NOT LIKE | a NOT LIKE b | SQL模式匹配，若a不匹配b，則為真 |
| REGEXP | a REGEXP b | 正則表達式匹配，若a匹配b，則為真 |
| NOT REGEXP | a NOT REGEXP b | 正則表達式匹配，若a不匹配b，則為真 |
| IS NULL | a IS NULL | 若a為NULL，則為真 |
| IS NOT NULL | a IS NOT NULL | 若a不為NULL，則為真 |
> LIKE模式匹配中的“%”匹配任意個字符，“_”匹配一個字符。匹配不區分大小寫字符。
**Table 1.9\. 操作符優先級(由高至低排列)**
| 操作符 |
| --- |
| BINARY，COLLATE |
| NOT、！ |
| ^ |
| XOR |
| -(一元求負操作符)、~(一元取反操作符) |
| *、/、% |
| +、- |
| <<、>> |
| & |
| | |
| <、<=、=、<=>、!=、<>、>=、>、IN、IS、LIKE、REGEXP、RLIKE |
| BETWEEN、CASE、WHEN、THEN、ELSE |
| AND、&& |
| OR、|| |
| := |
## 1.7\. 類型轉換
在MySQL的表達式中，如果某個數據值的類型與上下文所要求的類型不相符，MySQL則會根據將要進行的操作自動地對數據值進行類型轉換。如：
```
1 + '2' 會轉換成1 + 2 = 3
1+ 'abc' 會轉換成1 + 0 = 1 由于abc不能轉換成任何的值，所以默認為0
```
MySQL會根據表達式上下文的要求，把字符串和數值自動轉換為日期和時間值
對于超范圍或非法的值，MySQL也會進行轉換，但轉換出來的結果是錯誤的。出現該情況時，MySQL會提示警告信息，我們可捕獲該信息以進行相應的處理。
## Chapter 2\. 查詢優化
數據庫是數據的集合，與數學的集合論有密不可分的關系。
為提高查詢速度，我們可以：
* 對數據表添加索引，以加快搜索速度；
* 通過編程技巧最大限度地利用索引；
* 優化查詢語句，以使服務器最快響應多客戶的請求。
* 研究硬件處理過程，減少物理約束。
## 2.1\. 索引
索引技術是關系數據查詢中最重要的技術。如果要加提升數據庫的性能，索引優化是首先應該考慮的。因為它能使我們的數據庫得到最大性能方面的提升。
索引的優點：
* 沒有索引的表是沒有排序的數據集合，如果要查詢數據需進行全表掃描。有索引的表是一個在索引列上排序了數據表，可通過索引快速定位記錄。在MyISAM和ISAM數據表中，數據行保存在數據文件中，索引保存在索引文件中。BDB與InnoDB數據表把數據與索引放在同一個文件中。
* 在多表關聯查詢中，索引的作用就更大。如果沒有索引，在最壞的情況下，全表掃描的次數可能是各表數據行的組合個數，可能是一個天文數字。這樣的查詢是破壞性的，可能會造成數據庫癱瘓。
* 對于使用了MIN()或是MAX()函數的查詢，如果相關的數據列上有索引，MySQL能直接找到該最大、最小值的行，根本不用一個一個地去檢查數據行。
* 索引加快ORDER BY 和 GROUP BY子句的操作。
* 當在數值型數據列上查詢數據，而該列有索引，索引能使MySQL根本不用去讀取數據行，直接從索引取值。
索引的缺點：
* 索引需占用磁盤空間。
* 索引會減慢在索引數據列上的插入、刪除和修改操作。
索引列的選擇
* 索引應該創建在搜索、排序、分組等操作所涉及的數據列上。也就是說，在where子句，關聯檢索中的from子句、order by或group by子句中出現過的數據列最適合用來創建索引。
* 盡量使用唯一索引，它能使索引發揮最好的效能。
* 盡量用比較短的值進行索引。當對字符串進行索引時，應該指定一個前綴長度，比如對字符串的前10位或20位的字符進行排序，而不用把整個字符串幾十個字符用來索引排序。這樣能減少磁盤I/O，提高處理速度。最重要的一點是，鍵值越短，索引緩沖區里容納的鍵值也就越多，而MySQL同時保存在內存里的索引越多，索引緩沖區的命中率也就越高。當然，只對數據列第一個字符進行索引是沒什么意義的。
* 充分利用最左前綴。所謂最左前綴也就是在復合索引中最邊的索引列。如復合索引(a,b,c) ，其中a就是最左前綴。它是使用率最高的索引，需認真選擇。
* 不要建太多索引，索引是會消耗系統資源的，要適可而止。
* 索引主要用于<、<=、=、>=、>、BETWEEN等的比較操作中，所以索引應該建立在與這樣操作相關的數據列上。
* 利用慢查詢日志來找出性能差的查詢，通過mysqldumpslow可查看該日志。針對性能差的查詢可利用索引來加快查詢速度。
## 2.2\. 查詢優化程序
當我們發一條查詢命令時，MySQL分對它進行分析，以優化查詢。把explain語名放到查詢前面可顯示查詢的執行路線，對優化查詢提供有用的信息。以下幾個原則可幫助系統挑選和使用索引：
* 盡量對同類型的數據列進行比較。如：VARCHAR(5)和VARCHAR(5)是同類型的，CHAR(5)和VARCHAR(5)是不同類型的。
* 盡量讓索引的數據列在比較表達式中單獨出現，不要把它包含在函數或復雜表達式。否則索引會不起作用。
* 盡量不要在LIKE模式的開頭使用通配符。如：%string%。
* 對于MyISAM和BDB數據表，用ANALYZE TABLE語句讓服務器對索引鍵值的分布進行分析，為優化程序提供更有價值的信息。另一個方法是用myisamchk --analyze(適用于MyISAM表)或isamchk --analyze(適用于ISAM表)命令。
* 用EXPLAIN語句來分析查詢語句的執行效率。檢查查詢所使用的索引是不是能夠迅速地排除不符合條件的數據行，如果不是，可以試著用STRAIGHT_JOIN強制各有關數據表按指定順序進行關聯。
* 嘗試查詢的不同寫法，比較運行情況。
* 不要濫用MySQL的類型自動轉換功能。自動轉換會減慢查詢的速度并會使有關的索引失效。
## 2.3\. 數據列類型與查詢效率
選用適當的數據列類型有助于提高查詢命令的執行速度，下面是幾點關于如何選擇合適數據列類型的建議：
* 盡量選用尺寸較小的數據列。這樣能節約磁盤空間和加快查詢速度。如果較短的數據列上建有索引，則索引的處理速度會進一步提高。
* 針對數據列類型，盡量選擇最適用的數據表類型。如固定長度數據列在MyISAM或ISAM數據表中的速度是最快的，所以在這樣數據表中盡量使用char類型而不是varchar類型來保存字符串數據。對于InnoDB數據表類型，由于varchar類型可有效減少占用空間，從而減少磁盤I/O，所以使用varchar類型是有利的。對于BDB類型數據表，使用定長和不定長列類型的區別就不大，可任選一種。
* 盡量把數據列聲明為NOT NULL，以節約存儲空間和加快處理速度。
* 對于取值范圍有限的數據列，考慮使用ENUM數據列類型。ENUM數據列類型在MySQL中的處理速度是很快。
* 使用PROCEDURE ANALYSE()語句來分析數據表，它會對數據列的聲明提出建議，我們可根據建議進行修改。
```
select * from table_name PROCEDURE ANALYSE();
select * from table_name PROCEDURE ANALYSE(16,256); #(16,256)含義是：如果某列的不同取值在16個以上或長度超過256字節，就不提出使用ENUM的建議。
```
* 用OPTIMIZE TABLE語句對容易出現碎片的數據表進行整理。包含可變長數據列的數據表都會產生碎片，從而占用多余的磁盤空間和影響查詢速度。所以要定期運行OPTIMIZE TABLE語句以防止數據表查詢性能降低。但該語句只對MyISAM數據表有效。對各種數據表通用的碎片整理方法是這樣的：先用工具程序mysqldump導出數據表，再刪除數據表后重建，如：
```
$ mysqldump --opt db_name table_name > dump.sql
$ mysql db_name < dump.sql
```
* 把非結構化和變化大的數據放在BLOB數據列里，定期用OPTIMIZE TABLE命令優化。
* 人為地給數據表增加一個數據列，以充當索引。做法是這樣的，先根據數據表里的其它數據列計算出一個散列值，并保存在一個數據列里，然后通過搜索散列值來檢索數據行。注意，該技巧只適用于精確匹配型查詢。散列值在大于，小于等的操作中不起作用。散列值可以MD5()(適用于3.23及以上版本)，SHA1()(適用于4.0.1及以上版本)，CRC32()(適用于4.1及以上版本)等函數生成。使用散列值支檢索BLOB和TEXT值的做法比直接檢索BLOB和TEXT本身的做法快。
* 盡量避免對大尺寸的BLOB值進行檢索。如果要檢索都應該通過它的上面提到散列值先進行篩選。而不應該盲目地在網絡中傳送大量BLOB值。
* 如果把BLOB值剝離到另外一個數據表里去，可實現數據表中其它數據列轉變成固定長度數據列的話。就即可減少數據表碎片，又可使在原始表中的select *查詢不會把大尺寸的BLOB值不必要地通過網絡傳送。
## 2.4\. 有效地加載數據
有時我們需大量地把數據加載到數據表，采用批量加載的方式比一個一個記錄加載效率高，因為MySQL不用每加載一條記錄就刷新一次索引。下面介紹幾個有助于加快數據加載的操作：
* 使用LOAD DATA語句要比INSERT語句的加載速度快。
* LOAD DATA比LOAD DATA LOCAL語句的效率高。前者可由服務器直接從本地磁盤讀取加載數據，后者需由客戶程序去讀取文件并通過網絡傳送到服務器。
* 如果一定要用INSERT語句，應盡量在一條語句中插入多個數據行。
* 如果必須使用多條INSERT語句，則應盡量把它們集中在一起放到一個事務中進行處理，而不是在自動提交模式下執行它們：如：
```
BEGIN;
INSERT INTO table_name values (...);
INSERT INTO table_name values (...);
INSERT INTO table_name values (...);
...
COMMIT;
```
對于不支持事務的表，應對表進行寫鎖定，然后在表鎖定期間對表進行INSERT操作，如：
```
LOCK TABLES table_name WRITE;
INSERT INTO table_name ...;
INSERT INTO table_name ...;
INSERT INTO table_name ...;
...
UNLOCK TABLES;
```
* 利用客戶/服務器通信協議中的壓縮功能以減少網絡傳輸的數據量。但該壓縮會消耗大量的系統資源，所以小心使用。
* 盡量讓MySQL插入默認值。不要在INSERT中寫太多值，以減少網絡傳輸量和服務器端的語法分析時間。
* 對于MyISAM和ISAM數據表，如果需加載大量數據，應先建立一個沒索引的表，加載數據后再創建索引。該方法不適用于InnoDB或BDB數據表。
禁用和重新激活索引的方法有兩種：
* 使用ALTER TABLE語句的DISABLE KEYS和ENABLE KEYS命令，如：
```
ALTER TABLE table_name DISABLE KEYS;
ALTER TABLE table_name ENABLE KEYS;
```
* 使用myisamchk或isamchk工具。如：
```
$ myisamchk --keys-used=0 table_name #禁止
$ myisamchk --recover --quick --key-used=n table_name #激活
```
n是用來表明需要激活索引的位掩碼，第0位對應第一個索引，如果有三個索引，n值就是7(二進制111)。索引編號可以下命令確定：
```
$ myisamchk --description table_name
```
## 2.5\. 調度和鎖定
在很多客戶一起查詢數據表時，如果使客戶能最快地查詢到數據就是調度和鎖定做的工作了。在MySQL中，我們把select操作叫做讀，把對數據表修改增加的操作(INSERT,UPDATE,REPLACE...)叫做寫。MySQL的基本調度策略可以歸納為以下兩條：
* 寫入請求將按它們到達服務器的順序進行處理；
* 寫操作的優先級要高于讀操作。
MyISAM和ISAM數據表的調度策略是在數據表鎖的幫助下實現的，在客戶程序要訪問數據表之前，需獲得相應的鎖，在完成對數據表的操作后，再釋放該鎖。鎖的管理通常由服務器管理，也可人為地用LOCK TABLES和UNLOCK TABLES命令來申請和釋放鎖。寫操作時，需要申請一個獨占性的鎖，也就是說在寫操作其間，該表只能由寫操作的客戶使用。讀操作時，客戶必須申請一個允許其他客戶對數據表進行寫操作的鎖，以確保客戶在讀的過程中數據表不會發生改變。但讀操作鎖不是獨占的，可有多個讀操作同時作用于同一個數據表。
通過一些修飾符可影響調度策略，如LOW_PRIORITY(用于DELETE,INSERT,LOAD DATA,REPLACE,UPDATE語句)、HIGH_PRIORITY(用于SELECT語句)、DELAYED(用于INSERT和REPLACE語句)。它們的作用是這樣的：
* LOW_PRIORITY會使寫操作的優先級降低到讀操作以下，也就是說讀操作會阻塞該級別的寫操作，SELECT的HIGH_PRIORITY有類似的作用。
* INSERT語句中的DELAYED修飾會使插入操作被放入一個“延遲插入”隊列。并返回狀態信息給客戶，使客戶程序可在新數據行還沒插入到數據表之前繼續執行后面的操作。如果一直有客戶讀該數據表，新數據行會一直待在隊列中，直到數據表沒有讀操作時，服務器才會把隊列中的數據行真正插入到數據表中。該語句可用在以下場合，在一個有冗長查詢的數據表中插入數據，而你又不想被阻塞，你就可發出INSERT DELAYED語句，把插入操作放入服務器“延遲插入”隊列，你無需等待就馬上可進行接下來的操作。
* 當一個數據表里從未進行過刪除操作或剛剛對它進行過碎片整理的情況下，用INSERT語句插入的數據行只會被添加到數據表的末尾，而不會插入到數據表的中間位置。這樣，對于MyISAM表，MySQL允許在有其它客戶正在讀操作的時間進行寫操作。我們稱之這并發插入。要使用該技巧，需注意以下兩個問題：
* 不要在INSERT語句中使用LOW_PRIORITY修飾符。
* 讀操作應用LOCK TABLES ... READ LOCAL而不是用LOCK TABLES ... READ語句來進行數據表讀鎖定。LOCAL關鍵字只對數據表中已存在行進行鎖定，不會阻塞把新行添加到數據表末尾。
BDB數據表使用頁面級操作鎖，InnoDB數據表使用數據行級操作鎖。所以這兩種表的并發性比MyISAM和ISAM數據表這種表級鎖的并發性會好很多。其中InnoDB的并發性最好。綜上所述，我們可得出以下結論：
* MyISAM和ISAM數據表的檢索速度最快，但如果在檢索和修改操作較多的場合，會出鎖競爭的問題，造成等待時間延長。
* BDB和InnoDB數據表能在有大量修改操作的環境下提供很好的并發性，從而提供更好的性能。
* MyISAM和ISAM數據表由于進行表級鎖定，所以不會出現死鎖現象，BDB和InnoDB數據表則存在死鎖的可能性。
## 2.6\. 服務器優化
優化原則：
* 內存里的數據要比磁盤上的數據訪問起來快；
* 站數據盡可能長時間地留在內存里能減少磁盤讀寫活動的工作量；
* 讓索引信息留在內存里要比讓數據記錄的內容留在內存里更重要。
針對以上幾個原則，我們應該調整服務器：
* 增加服務器的緩存區容量，以便數據在內存在停留的時間長一點，以減少磁盤I/0。下面介紹幾個重要的緩沖區：
* 數據表緩沖區存放著與打開的數據表相的信息，它的大小可由服務器參數“table_cache”設置。Opened_tables參數記錄服務器進行過多少次數據表打開操作，如果該值變化很大，就可能是數據表緩沖區已滿，需把一些不常用的表移出緩沖區，以騰出空打開新的數據表。可用以下命令查看Opened_tables的值：
```
SHOW STATUS LIKE 'Opened_tables';
```
* 在MyISAM和ISAM數據表中，索引被緩存在“key buffer”里，它的大小由服務器參數“key_buffer_size”來控制。系統默認的大小是8M，如果內存充足的話可適當擴大該值，以使更多索引塊緩存在該區里，以加快索引的速度。
* InnoDB和BDB數據表也各有一個緩沖區，分別叫innodb_buffer_pool_size和bdb_cache_size。InnoDB還有一個日志緩沖區叫innodb_log_buffer_size。
* 自4.0.1開始，MySQL多了一個緩沖區，叫查詢緩沖區，主要用來存放重復執行的查詢文本和結果，當再次遇到相同的查詢，服務器會直接從緩沖區中返回結果。該功能是內建的功能，如不想支持該功能，可在編譯服務器時用configure腳本的--without-query-cache選項去掉該功能。
查詢緩沖區由三個服務器參數控制，分別是：
1、query_cache_size
控制緩沖區的大小，如果該值為0,則禁用查詢緩沖功能。設置方法是在選項文件中設置:
```
[mysqld]
set-variable = query_cache_size = 16M
```
這樣就設置了一個16M的查詢緩沖區
2、query_cache_limit
緩沖結果集的最大容量(以字節為單位)，如果查詢的結果集大于該值，則不緩沖該值。
3、query_cache_type
緩沖區的操作模式。
0表示不進行緩沖；
1表示除SELECT SQL_NO_CACHE開頭的查詢外，其余的都緩沖；
2表示只對以SELECT SQL_ON_CACHE開頭的查詢進行緩沖。
默認情況下，按服務器的設置進行緩沖，但客戶端也可通過命令改變服務器設置。客戶端可直接用SELECT SQL_NO_CACHE和SELECT SQL_CACHE命令來要求服務器緩沖或不緩沖查詢結果。如果不想每條查詢都寫參數，我們也可在客戶端用SET SQL_QUERY_CACHE_TYPE = val;來改變服務器的查詢緩沖行為。val可取值0，1,2或OFF，ON，或DEMAND。
* 禁用用不著的數據表處理程序。如服務器是從源碼創建，就可徹底禁用ISAM，InnoDB和BDB數據表。
* 權限表里的權限關系應盡可能簡單，當然了，是要在保證安全的前提下。
* 在從源碼創建服務器時，盡量使用靜態庫而不是共享庫來完成其配置工作。靜態庫的執行速度更快，但如果要加載用戶定義函數(UDF)的話，就不能使用靜態庫，因為UDF機制必須依賴動態庫才能實現。
## 2.7\. 硬件優化
為了提高數據運行速度，升級硬件是最直接的解決方案。針對數據庫應用的特點，在升級硬件時應考慮以下內容：
* 對于數據庫服務器，內存是最重要的一個影響性能因素。通過加大內存，數據庫服務器可把更多的數據保存在緩沖區，可大大減少磁盤I/O，從而提升數據庫的整體性能。
* 配置高速磁盤系統，以減少讀盤的等待時間，提高響應速度。
* 合理分布磁盤I/O，應把磁盤I/O分散在多個設備上，以減少資源競爭，提高并行操作能力。
* 配置多處理器，MySQL是多線程的數據庫，多處理器可同時執行多個線程。
## Chapter 3\. 數據庫管理
數據庫是一個復雜而又關鍵的系統，為確保系統安全、高效運行，需熟悉數據庫內部的運作機制，掌握各種維護工具，并做好日常的管理工作。下面列舉幾項主要工作職責：
* 服務器的關閉和啟動；
* 管理用戶帳號；
* 管理日志文件；
* 數據庫備份恢復；
* 數據庫優化；
* 確保數據庫數據安全；
* 數據庫軟件升級。
## 3.1\. 數據目錄
數據目錄是用來存放數據表和相關信息的地方，是數據庫的核心。在MySQL中的數據目錄根據不同平臺的有一些差異：
* 在UNIX/Linux系統上，如果用源碼編譯安裝，數據目錄的位置默認是在/usr/local/mysql/var中；
* 在UNIX/Linux系統上，如果用二進制發行版安裝，數據目錄的位置默認是在/usr/local/mysql/data中；
* 在WINDOWS系統上，數據目錄的位置默認是在c:/mysql/data中；
> 在服務器啟動時，可用--datadir=dir_name來指定數據目錄，也可把它寫到配置文件中。
我們還可用命令向服務器查詢數據目錄的位置，數據目錄的變量名是datadir，如：
* ```
% mysqladmin variables
```
如果在一臺機器上同時運行多個服務器，則可根據端口的不時來查詢每個服務器的數據目錄，如：
```
% mysqladmin --host=127.0.0.1 --port=port_number variables
```
如果--host是localhost，系統則會用一個UNIX套接字去連接數據庫服務器，這時要使用--socket選項，所以查詢語句變成：
```
% mysqladmin --host=localhost --socket=/path/to/socket variables
```
* ```
mysql> SHOW VARIABLES LIKE 'datadir';
```
* 在windows NT平臺上可以使用“.”作為一條命名管道連接的主機名，用--socket選項給出命名管道的名字，如：
```
c:\ mysqladmin --host=. --socket=pipe_name variables
```
* 配置文件的中[mysqld]段中的datadir=/path/to/datadir設置也可查詢到數據目錄。
* 在mysqld程序的幫助信息里也有程序編譯時默認的數據目錄信息，可用以下命令顯示：
```
% mysqld --help
```
數據目錄是存放數據文件的地方，每個數據庫對應目錄的不同文件。InnoDB數據表由于用表空間來管理數據庫，所以就沒這種對應關系。但也是保存在數據目錄中的，在數據目錄除保存數據庫文件外，還可能會保存以下幾類文件：
* 服務器的配置文件，my.cnf；
* 服務器的進程ID(PID)文件；
* 服務器的日志文件和狀態文件，這些文件對管理數據庫有重要的價值；
* DES密鑰文件或服務器的SSL證書與密鑰文件。
數據目錄中的所有數據庫全部由服務器(mysqld)來管理，客戶端不直接操作數據。服務器是客戶使用數據的唯一通道。
在MySQL中，每個數據庫其實就是在數據目錄下一個子目錄，show databases命令相當于列出數據目錄中的目錄清單。create database db_name命令會在數據目錄下新建一個db_name的目錄，以存放數據庫的數據文件。所以我們也可下面的shell命令方式來建立一個空數據庫：
```
% cd datadir
% mkdir db_name
% chmod u=rwx,go-rwx db_name
```
同理，刪除數據庫drop database db_name也就是刪除數據目錄中一個名為db_name的目錄及目錄中的數據表文件。我們也可用shell這進行操作：
```
% cd datadir
% rm -rf db_name
```
> 比較shell方式與drop database方式，drop database db_name命令不能刪除db_name目錄中創建的其它非數據表文件；由于InnoDB是表空間來管理數據表，所以不能用rm或del命令刪除InnoDB的數據表。
## 3.2\. MySQL數據表在系統中表現形式
MySQL數據表類型有：ISAM、MyISAM、MERGE、BDB、InnoDB和HEAP。每種數據表在文件系統中都有不同的表示方式，有一個共同點就是每種數據表至少有一個存放數據表結構定義的.frm文件。下面介紹每種數據表文件：
* ISAM數據表是最原始的數據表，有三個文件，分別是：
.frm，存放數據表的結構定義；
.ISD，數據文件，存放數據表中的各個數據行的內空；
.ISM，索引文件，存放數據表的所有索引信息。
* MyISAM數據表是ISAM數據表的繼承者，也有三個文件，分別是：
.frm，結構定義文件；
.MYD，數據文件；
.MYI，索引文件。
* MERGE數據表是一個邏輯結構，代表一組結構完全相同的MyISAM數據表構成的集合。它在文件系統中有二個文件，分別是：
.frm，結構定義文件；
.MRG，構成MERGE表的MyISAM數據表清單，每個MyISAM數據表名占一行。也就是說可通過改變該表的內容來改變MERGE數據表的結構。修改前請先刷新緩存(flush tables)，但不建議這樣修改MERGE數據表。
* BDB數據表用兩個文件來表示，分別是：
.frm，結構定義文件；
.db，數據表數據和索引文件
* InnoDB由于采用表空間的概念來管理數據表，所以它只有一個與數據表對應.frm文件，同一目錄下的其它文件表示為表空間，存儲數據表的數據和索引。
* HEAP數據表是一個存在于內存中的表，所以它的數據和索引都存在于內存中，文件系統中只有一個.frm文件，以定義結構。
了解MySQL數據表在文件系統中表現形式后，我們可知道，創建、修改或刪除數據表，其實就是對這些文件進行操作。例如一些數據表(除InnoDB和HEAP數據表外)，我們可直接在文件系統中刪除相應的文件來刪除數據表。
```
% cd datadir
% rm -f mydb/mydb.*
```
以上命令可刪除mydb數據庫中的mydb數據表。
## 3.3\. 數據表最大尺寸限制
在MySQL中影響數據表尺寸的因素有很多，下面分別進行介紹：
* MySQL數據表類型的不同對數據表尺寸的限制：
* ISAM數據表中單個.ISD和.ISM文件的最大尺寸為4G；
* MyISAM數據表中單個.MYD和.MYI文件的默認最大尺寸也是4G，但可在創建數據表時用AVG_ROW_LENGTH和MAX_ROWS選項把這個最值擴大到800萬TB。
* MERGE數據表的最大尺寸是它的各組成MyISAM數據表的最大尺寸之和。
* BDB數據表的尺寸受限于BDB處理程序所允許的.db文件的最大尺寸。這個最大尺寸隨著數據表頁面尺寸(編譯時確定)而變化，但即使是最小的頁面尺寸(512字節)，.db文件的最大尺寸也可達2TB。
* InnoDB數據表的表空間的最大尺寸是40億個頁面，默認的頁面尺寸是16K，該值可在8K到64K之間，在編譯時確定。InnoDB數據表的最大尺寸也就是表空間的最大尺寸。
* 操作系統對文件的尺寸限制，一般文件系統都對單個文件不得超過2G的限制。該約束會對數據庫文件造成限制。InnoDB數據表可通過利用未格式化硬盤作為表空間來繞過該限制。
* 對于數據和索引分開兩個文件存放的數據表，其中任何一個文件達到操作系統文件的最大限制，數據庫表也就達到最大尺寸。
* 包含AUTO_INCREMENT數據列的表受到該數據列類型最大上限值的限制。
* 由于InnoDB數據表用表空間來管理，一個表空間可同時空納多個數據表，所以數據表的最大尺寸受系統文件和同一表空間中數據表空間的約束。
## 3.4\. 狀態文件和日志文件
在MySQL數據目錄中還包含著許多狀態文件和日志文件，這些文件的文件名都是以主機名加上相關后綴來命名的。下面是這些文件的一個說明列表：
**Table 3.1\. 狀態文件和日志文件**
| 文件類型 | 默認名 | 文件內容 |
| --- | --- | --- |
| 進程ID文件 | hostname.pid | MySQL服務器進程的ID |
| 常規查詢日志 | hostname.log | 連接/斷開連接事件和查詢信息 |
| 慢查詢日志 | hostname-slow.log | 記錄查詢時間很長的命令信息 |
| 變更日志 | hostname.nnn | 創建或修改數據表結構和內容的查詢命令信息 |
| 二進制變更日志 | hostname-bin.nnn | 創建或修改數據表結構和內容的查詢命令的二進制表示法 |
| 二進制變更日志的索引文件 | hostname-bin.index | 使用中的“二進制變更日志”列表 |
| 錯誤日志 | hostname.err | 記錄“啟動/關閉”事件和異常情況 |
變更日志和二進制變更日志主要用于MySQL數據庫服務器的崩潰恢復中，由于變更日志記錄了數據庫的所有變更操作，所以可以進行事件重放。具體操作請參考相關數據庫備份恢復章節。對于變更日志，我們可用--log-long-format選項來讓它以擴展方式記錄有關事件。擴展方式可記錄誰發出查詢和什么時候發出查詢的信息。可使我們更好地掌握客戶端的操作情況。日志記錄著查詢命令的所有操作，里面可能會有一些敏感信息。所以我們要確保日志文件的安全。
## 3.5\. 調整MySQL數據目錄位置
MySQL數據庫的數據目錄位置，包括目錄里的各種文件的位置)可根據實際情況進行調整。調整的方法有兩種，一種是使用符號鏈接；一種用服務器啟動選項。下面一個列表說明了數據目錄及目錄中文件各自適宜采用的方法：
**Table 3.2\. MySQL數據目錄及目錄中文件位置的調整方法**
| 調整對象 | 適用方法 |
| --- | --- |
| 整個數據目錄 | 啟動選項和符號鏈接 |
| 數據庫目錄 | 符號鏈接 |
| 數據表 | 符號鏈接 |
| InnoDB數據表空間 | 啟動選項 |
| PID文件 | 啟動選項 |
| 日志文件 | 啟動選項 |
下面是各種調整方法的具體操作過程：
* 在調整MySQL的數據目錄時，要先停止服務器，再把數據目錄移動到新的位置。接著，我們可選擇在原來目錄下創建一個符號鏈接指向新的位置，或者用啟動選擇--datadir指向新的數據目錄。推薦用創建符號鏈接的方法，因為如果那個數據目錄中有my.cnf文件，相應的服務器還能找到它。
* 數據庫只能存在于MySQL數據目錄中，所以只能使用符號鏈接的方法調整它的位置。在Linux系統的操作步驟如：
1. 關閉服務器；
2. 把數據庫目錄拷貝到新的位置；
3. 刪除原來的數據庫目錄；
4. 在原來的MySQL數據目錄中創建一個同名符號鏈接指向新的位置；
5. 重新啟動服務器。
在windows下的操作方法不些不同，操作方法如下：
1. 關閉服務器；
2. 把數據庫目錄移動新的位置；
3. 刪除原來的數據庫目錄；
4. 在原來數據目錄下建一個同名的.sym文件，在文件中輸入數據庫新目錄的全路徑，如c:\mysql\newdir\mydb。這個文件就相當于Linux下的符號鏈接；
5. 重啟服務器。
> 為了支持符號鏈接功能，必須用--use-symbolic-links選項啟動服務器；或在選項文件的[mysqld]節中添加use-symbolic-links選項。
> MySQL必須是3.23.16以上版本且是max服務器(mysqld-max或mysqld-max-nt)。
* 要移動數據表，必須滿足以下所有條件才行：
* MySQL的版本必須是4.0或以上的版本；
* 操作系統必須有一個可用的realpath()調用；
* 移動的數據表必須是MyISAM類型的數據表。
在滿足以上所有條件后，我們就可把.MYD數據文件和MYI索引文件移到新位置，再在原來位置創建兩個同名符號鏈接指定新的位置。注意，.frm定義文件仍需留在原來的數據庫目錄中。
如以上條件不能全部滿足，最好不要移動數據表文件。否則一旦你運行ALTER TABLE、OPTIMIZE TABLE、REPAIR TABLE語句對移動過的數據表進行優化或修改，這樣數據表就會回到原來的位置，使移動操作失效。因為這些命令的執行過程是這樣的：它會先在數據目錄中創建一個臨時數據表并對這個臨時數據表進行優化或修改，然后刪除原來的數據表(這里是你為了移動數據表而創建的一個符號鏈接)，再把臨時數據表更名為原來的數據表名稱。這樣一來，你移走的數據表就和這個數據庫完全沒有關系了。基于以下的不穩定因素，如無特殊必要，不建議移動數據表。
* InnoDB表空間是通過在選項文件中使用innodb_data_home_dir和innodb_data_file_path選項列出InnoDB表空間組成文件清單的方法來配置的，所以我們可通過修改這些選項來重新安置InnoDB表空間的組成文件。步驟如下：
* 關閉服務器；
* 移動組成表空間的文件；
* 修改選項文件，指出組成表空間的文件的新位置；
* 重啟服務器。
* 狀態文件和日志文件的位置可通過選項文件或啟動服務器時指定。
## Chapter 4\. MySQL數據庫日常管理
為了確保數據庫平穩可靠運行，我們需進行維護和管理，這是每一位數據庫管理員的職責。下面分幾個專題分別介紹。
## 4.1\. 數據庫安全管理
MySQL數據庫通過用戶和密碼來控制用戶對數據庫的訪問，當我們新安裝了一個數據庫服務器時，MySQL的權限表設置是很不安全，它默認允許任何人不需要密碼就可訪問數據庫。所以我們安裝好服務器后第一件需要做的就是設置用戶密碼。
在MySQL中的mysql數據庫的user數據表中存有用戶的帳號信息，在初始狀態下已存在root和一些匿名用戶，且所有用戶都沒有設置密碼。該數據表的這些用戶信息是通過一個mysql_install_db腳本安裝的。該表的主要列有：
* User，連接數據庫的用戶名。
* Host，允許連接到數據庫服務器的主機名，“%”通配符代表所有主機。
* Password，連接密碼，已加密。
* 其它權限列，以“Y”或“N”標識是否有效。
在這種狀態下的數據庫是極不安全的，我們可用以下命令輕易地訪問數據庫：
```
% mysql -h localhost -u root #通過本地主機，root用戶訪問，不需要密碼驗證
% mysql -h localhost #通過本地主機，匿名用戶訪問，不需要密碼驗證
```
設置MySQL用戶帳號密碼的方法有三種：
* 使用mysqladmin程序：
```
% mysqladmin -h localhost -u root password "password" #設置在本地以root身分登錄的密碼
% mysqladmin -h remote -u root password "password" #設置遠程主機以root身分登錄的密碼
```
在初始設置時，這兩條語句都要運行，以確保數據庫本地訪問和網絡訪問的安全。
* 通過set password這條SQL語句設置：
```
mysql> set password for 'root'@'localhost' = password('password');
mysql> set password for 'root'@'remote' = password('password');
```
* 直接修改user權限表：
```
mysql> use mysql;
mysql> update user set password=password('password') where user='root';
mysql> flush privileges; #重載權限表，使修改馬上生效
```
MySQL使用駐留在內存中的權限表拷貝來進行訪問控制，當使用mysqladmin和set password設置密碼，MySQL會監察到權限表已被修改，它自動重載該表。而用update的方式，MySQL就監察不到變化，需手動用flush privileges命令刷新內存中的權限表，以使它馬上生效。
為root用戶設置密碼后，如果需以root身份連接數據庫，就需驗證密碼。我們可用以下語句連接數據庫：
```
% mysql -u root -p
Enter password: #輸入root的密碼
```
在user表中，user列為空的為匿名用戶。它也是沒有密碼的，我們需為它們設置一個密碼，或干脆把它們刪除。在windows系統上的本地匿名用戶帳號和root用戶有著同樣的權限，這是一個很大的安全漏洞。應該把它刪除或把權限削弱。
## 4.2\. 服務器的啟動和關閉
在Linux和windows平臺下MySQL服務器的啟動方式有很大不同，這里將分開介紹：
* Linux平臺：
Linux平臺下，每一個進程都需由一個用戶來運行，MySQL最好不要以root用戶來運行。我們可創建一個mysql用戶和mysql組，MySQL服務器程序目錄和數據目錄由這個用戶和組所擁有，其它用戶沒有任何權限。以mysql用戶來運行MySQL服務器。
```
% mysqld --user=mysql #即使以root用戶執行該命令，MySQL數據庫還是會與mysql用戶ID關聯。
```
為了使服務器在系統啟動時自動以mysql用戶運行，需配置my.cnf配置文件 ，把user=mysql包含在[mysqld]段中。
關閉服務器可用% mysql.server stop或% mysqladmin -u root -p shutdown
* windows平臺：
手動方式：直接運行c:\mysqld命令。
作為服務方式：運行c:\mysqld-nt --install命令，把mysqld-nt安裝為windows的服務，此后，每當windows啟動時，它就會自動運行。mysqld-nt是一個支持命名管道的MySQL服務器。運行c:\mysqld-nt --remove可把服務刪除。手動啟動關閉服務的方法是運行c:\net start mysql和c:\net stop mysql命令。
## 4.3\. 連接故障恢復
當由于誤刪mysql套接字時(/tmp/mysql.sock)，我們就不能通過套接字連接服務器。這時我們可通過tcp/ip來連接服務器，要建立一個tcp/ip連接，需用127.0.0.1代替locahost作為-h參數的值來連接服務器。如：
```
% mysqladmin -h 127.0.0.1 -u root -p shutdown #關閉服務器再重啟會重建套接字
```
當我們因為忘記root用戶密碼而不能連接服務器時，重設置密碼的步驟如：
* 用 % kill -TERM PID關閉服務器，用-TERM信息可使服務器在關閉前把內存中的數據寫入磁盤。如果服務器沒有響應，我們可用% kill -9 PID來強制刪除進程，但不建議這樣做。這時內存中的數據不會寫入磁盤，造成數據不完整。如果你是用mysql_safe腳本啟動MySQL服務器的，這個腳本會監控服務器的運行情況并在它被終止時重啟服務器，所以如需關閉服務器，要先終止該進程，然后再真正終止mysqld進程。
* 接著用--skip_grant-tables啟動服務器。這時MySQL服務器將不使用權限表對連接操作進行驗證。你就可在不提供root密碼的情況下連接上服務器，并獲得root的權限。這樣你就可用上面介紹的修改密碼的方法重設root用戶的密碼。注意：連接上服務器后，要馬上執行flush privileges命令，使權限表讀入內存并生效，以阻止其他的連接。該語句還重新激活grant語句，在MySQL服務器不使用權限表時，grant語句被禁用。
* 修改完root用戶密碼后，我們就可關閉服務器并重啟使所有配置正常運作。
## 4.4\. MySQL用戶帳號管理
MySQL用戶帳號管理主要用grant(授權)和revoke(撤權)兩個SQL指令來管理。這兩個指令實質是通過操作user(連接權限和全局權限)、db(數據庫級權限)、tables_priv(數據表級權限)、columns_priv(數據列級權限)四個權限表來分配權限的。host權限表不受這兩個指令影響。下面將會詳細介紹用戶權限管理的內容。
* GRANT語法說明：
```
GRANT privileges (columns) #privileges表示授予的權限，columns表示作用的列(可選)
ON what #設置權限級別，全局級、數據庫級、數據表級和數據列級
TO account #權限授予的用戶，用"user_name"@"host_name"這種用戶名、主機名格式
IDENTIFIED BY 'password' #設置用戶帳號密碼
REQUIRE encryption requirements #設置經由SSL連接帳號
WITH grant or resource management options; #設置帳號的管理和資源(連接服務器次數或查詢次數等)選項
```
示例：
```
mysql>grant all on db.* to 'test'@'localhost' identified by 'test';
```
上例運行后的效果是，test用戶只能通過‘test’密碼從本機訪問db數據庫
```
mysql>grant all on db.* to 'test'@'%' identified by 'test';
```
上例運行后的效果是，test用戶可通過‘test’密碼從任意計算機上訪問db數據庫。‘%’代表任意字符，‘_’代表一個任意字符。主機名部份還可以是IP地址。
> 如果沒有給定主機部份，則默認為任意主機，也就是'test'和'test'@'%'是等價的。
* **Table 4.1\. 訪問權限表**
```
| 權限 | 權限說明 |
| --- | --- |
| CREATE TEMPORARY TABLES | 創建臨時數據表 |
| EXECUTE | 執行存儲過程(暫不支持) |
| FILE | 操作系統文件 |
| GRANT OPTION | 可把本帳號的權限授予其它用戶 |
| LOCK TABLES | 鎖定指定數據表 |
| PROCESS | 查看運行著的線程信息 |
| RELOAD | 重新加載權限表或刷新日志及緩沖區 |
| REPLICATION CLIENT | 可查詢主/從服務器主機名 |
| REPLICATION SLAVE | 運行一個鏡像從服務器 |
| SHOW DATABASES | 可運行SHOW DATABASES指令 |
| SHUTDOWN | 關閉數據庫服務器 |
| SUPER | 可用kill終止線程以及進行超級用戶操作 |
| ALTER | 可修改表和索引的結構 |
| CREATE | 創建數據庫和數據表 |
| DELETE | 刪除數據表中的數據行 |
| DROP | 刪除數據表和數據行 |
| INDEX | 建立或刪除索引 |
| INSERT | 插入數據行 |
| REFERENCES | (暫時不支持) |
| SELECT | 查詢數據行 |
| UPDATE | 更新數據行 |
| ALL | 所有權限，但不包括GRANT。 |
| USAGE | 無權限權限 |
```
* **Table 4.2\. 權限作用范圍(由ON子句設置)**
```
| 權限限定符 | 作用范圍 |
| --- | --- |
| ON *.* | 全局級權限，作用于所有數據庫 |
| ON * | 全局級權限，若未指定默認數據庫，其作用范圍是所有數據庫，否則，其作用范圍是當前數據庫 |
| ON db_name.* | 數據庫級權限，作用于指定數據庫里的所有數據表 |
| ON db_name.tbl_name | 數據表級權限，作用于數據表里的所有數據列 |
| ON tbl_name | 數據表級權限，作用于默認數據庫中指定的數據表里的所有數據列 |
```
* USAGE權限的用法：修改與權限無關的帳戶項，如：
```
mysql>GRANT USAGE ON *.* TO account IDENTIFIED BY 'new_password'; #修改密碼
mysql>GRANT USAGE ON *.* TO account REQUIRE SSL; #啟用SSL連接
mysql>GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 10; #設置資源
```
* 擁有WITH GRANT OPTION權限的用戶可把自已所擁用的權限轉授給其他用戶，如：
```
mysql>GRANT ALL ON db.* TO 'test'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
這樣test用戶就有權把該權限授予其他用戶。
```
* 限制資源使用，如：
```
mysql>GRANT ALL ON db.* TO account IDENTIFIED BY 'password' WITH MAX_CONNECTIONS_PER_HOUR 10 MAX_QUERIES_PER_HOUR 200 MAX_UPDATES_PER_HOUR 50;
```
允許account用戶每小時最多連接20次服務器，每小時最多發出200條查詢命令(其中更新命令最多為50條)
默認都是零值，即沒有限制。FLUSH USER_RESOURCES和FLUSH PRIVILEGES可對資源限制計數器清零。
* REVOKE語法說明：
```
mysql>REVOKE privileges (columns) ON what FROM account;
```
示例：
```
mysql>REVOKE SELECT ON db.* FROM 'test'@'localhost';
刪除test帳號從本機查詢db數據庫的權限
```
REVOKE可刪除權限，但不能刪除帳號，即使帳號已沒有任何權限。所以user數據表里還會有該帳號的記錄，要徹底刪除帳號，需用DELETE命令刪除user數據表的記錄，如：
```
% mysql -u root -p
mysql>use mysql
mysql>DELETE FROM user where User='test' and Host='localhost';
mysql fulsh privileges;
```
REVOKE不能刪除REQUIRE和資源占用的配置。他們是要用GRANT來刪除的，如：
```
GRANT USAGE ON *.* TO account REQUIRE NONE; #刪除account帳號的SSL連接選項
GRANT USAGE ON *.* TO account WITH MAX_CONNECTIONS_PER_HOUR 0 MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0; #刪除account帳號的資源限制
```
## 4.5\. 日志文件管理
有關MySQL的日志文件前面章節已簡要討論過了，主要有四種日志文件，分別是常規查詢日志、慢查詢日志、變更查詢日志和二進制變更日志。這些日志文件的創建需在啟動服務器時用選項指定。
**Table 4.3\. 日志啟動選項**
| 啟動選項 | 激活日志 |
| --- | --- |
| --log[=file_name] | 常規日志文件 |
| --log-bin[=file_name] | 二進制變更日志文件 |
| --log-bin-index=file_name | 二進制變更日志文件索引文件 |
| --log-update[=file_name] | 變更日志文件 |
| --log-slow-queries[=file_name] | 慢查詢日志文件 |
| --log-isam[=file_name] | ISAM/MyISAM日志文件 |
| --log-long-format | 設置慢查詢日志和變更日志的格式 |
BDB和InnoDB數據表的日志文件會自動創建不用指定選項。但可用以下選項指時日志文件的存放路徑。
**Table 4.4\. BDB和InnoDB數據表日志選項**
| 啟動選項 | 用途 |
| --- | --- |
| --bdb-logdir=dir_name | 存放BDB日志文件的位置 |
| --innodb-log_arch_dir=dir_name | 存放InnoDB日志文件的歸檔目錄 |
| --innodb_log_group_home_dir=dir_name | 存放InnoDB日志文件的位置 |
MySQL日志文件選項可在mysqld和mysqld_safe腳本中使用，也可在選項文件my.cnf的[mysqld]中使用。推薦在選項文件中使用，因為每次啟動服務器的日志選項基本上都是一致的。
日志的刷新可用mysqladmin flush-logs命令或flush logs語句實現。另外，對MySQL服務器發送一條SIGHUP信號也會刷新日志。錯誤日志和DBD/InnoDB日志不能用以上方法刷新。
錯誤日志記錄MySQL數據庫系統的論斷和出錯信息，由mysqld_safe腳本創建，文件名默認為hostname.err，也可通過--err-log或選項文件的err-log語句指定另外的名字。如果直接用mysqld程序啟動服務器，錯誤信息會直接輸出到輸出設備，也就是屏幕。但我們可用重定向方法把錯誤信息輸出到其它地方，如把錯誤信息輸出到/var/log/mysql.err文件中，可以執行以下語句：
```
% mysqld > /var/log/mysql.err 2>&1 &
```
在windows平臺下，MySQL服務器默認把診斷信息寫到數據目錄的mysql.err文件中，并且不允許另外指定錯誤日志文件名。如在啟動MySQL服務器時給出了--console選項，則MySQL會把診斷信息輸出到控制臺窗口而不創建錯誤日志。但如MySQL是作為一個服務運行，則--console選項不起作用。
### 4.5.1\. 日志失效處理
在服務器正常運行中，會產生大量的日志文件。我們要對這些日志文件進行失效管理，以節省磁盤空間和方便查詢。進行日志失效處理的方式主要有以下幾種：
* 日志輪轉。該方法適用于常規查詢日志和慢查詢日志這些文件名固定的日志文件，在日志輪轉時，應進行日志刷新操作(mysqladmin flush-logs命令或flush logs語句)，以確保緩存在內存中的日志信息寫入磁盤；
日志輪轉的操作過程是這樣的：第一次輪轉時，把log更名為log.1，然后服務器再創建一個新的log文件，在第二輪轉時，再把log.1更名為log.2，把log更名為log.1，然后服務器再創建一個新的log文件。如此循環，創建一系列的日志文件。當到達日志輪轉失效位置時，下次輪轉就不再對它進行更名，直接把最后一個日志文件覆蓋掉。例如：如果每天進行一次日志輪轉并想保留最后7天的日志文件，就需要保留log.1--log.7共七個日志文件，等下次輪轉時，用log.6覆蓋原來的log.7成新的log.7，原來的log.7就自然失效。下面是一個失效處理的shell腳本，以供參考：
```
#!/bin/sh
# shell script --- rotate_log.sh
if [ $# -ne 1 ]; then
echo "Usage: $0 logname" 1>&2
exit 1
if
logfile=$1
mv $logfile.6 $logfile.7
mv $logfile.5 $logfile.6
mv $logfile.4 $logfile.5
mv $logfile.3 $logfile.4
mv $logfile.2 $logfile.3
mv $logfile.1 $logfile.2
mv $logfile $logfile.1
mysqladmin -u flush -pflushpass flush-logs #執行mysqladmin flush-logs會打開一個日志文件----重新生成一個新的日志文件
```
該腳本以日志文件名為參數，執行方法如下：
```
% rotate_log.sh /usr/local/mysql/data/log
```
注意，腳本中的mysqladmin命令是帶有-u和-p參數的，因為我們進行日志刷新時需連接服務器。為確保安全，我們建立一個flush用戶，密碼為flushpass。該用戶只有日志刷新的權限(reload權限)。創建該用戶的語句如下：
```
GRANT RELOAD ON *.* TO 'flush'@'localhost' IDENTIFIED BY 'fulshpass';
```
設置好后，我們就可利用系統的自動處理機制定期運行該腳本以生成輪轉日志。在Linux系統上的MySQL發行版中帶有一個用來安裝mysql-log-rotate日志輪轉腳本的logrotate工具，如用RPM安裝,則在/usr/share/mysql目錄，如用二進制方式安裝,則在MySQL安裝目錄的support-files目錄，如用源碼安裝，則在安裝目錄的share/mysql目錄中。
在windows平臺下，不能在線更名，需停掉服務器，再進行。下面是一個進行日志更名的批處理文件：
```
@echo off
REM script name : rotate_log.bat
if not "%1" == "" goto ROTATE
@echo Usage: rotate_log logname
goto DONE
:ROTATE
set logfile=%1
erase %logfile%.7
rename %logfile%.6 %logfile%.7
rename %logfile%.5 %logfile%.6
rename %logfile%.4 %logfile%.5
rename %logfile%.3 %logfile%.4
rename %logfile%.2 %logfile%.3
rename %logfile%.1 %logfile%.2
rename %logfile% %logfile%.1
:DONE
```
該腳本的執行方法如下：
```
c:\rotate_log c:\mysql\data\log
```
* 以時間為依據對日志進行失效處理。該方法將定期刪除超過給定時間的日志文件，適用于變更日志和二進制日志等文件名用數字編號標識的日志文件。下面是一個用Perl寫成的處理腳本：
```
#!/usr/bin/perl -w
# script name: expire_log.pl
# Usage: expire_log.pl logfile ...
use strict
die "Usage: $0 logfile ...\n" if @ARGV == 0;
my $max_allowed_age = 7; #max allowed age in days
foreach my $file (@ARGV) #chack each argument
{
unlink ($file) if -e $file && -M $file >= $max_allowed_age;
}
exit(0);
```
該腳本需提供一個將被輪轉的日志文件名作為參數，如：
```
% expire_log.pl /usr/local/mysql/data/log.[0-9]*
```
> 在給腳本參數時請小心，如給出*為參數，則會刪除目錄中所有更新時間大于7天的文件。
* 鏡像機制。把日志文件鏡像到所有的從服務器上。要使用鏡像機制，你必須知道主服務器有多少個從服務器，哪些正在運行，并需依次連接每一個從服務器并發出show slave status語句以確定它正處理主服務器的哪個二進制日志文件(語句輸出列表的Master_Log_File項)，只有所有的從服務器都不會用到的日志文件才能刪除。刪除方法是在主服務器上發出以下語句：
```
mysql> PURGE MASTER LOGS TO 'last_log.xx';
```
上面語句中的last_log.xx是所有從服務器已處理的最小編號日志文件。
## 4.6\. MySQL服務器的一些優化配置
* 服務器的監聽端口設置
* TCP/IP端口3306是MySQL服務器默認的網絡監聽端口，如用--skip-networking選項啟動服務器，則不監聽TCP/IP端口。可用--port端口另行指定一個監聽端口。如服務器主機有多個IP，還可用--bind-address選項對服務器在監聽客戶連接時使用的IP地址進行設定。
* 在UNIX系統上，MySQL可在一個UNIX域套接字文件上監聽有無本地客戶在試圖以localhost為主機名進行連接。默認的套接字文件是/tmp/mysql.sock，可用--socket選項指定另外一個套接字文件。
* 在基于NT的Windows平臺上，有-nt的MySQL服務器都支持命名管道。默認的命名管道是MySql，可用--socket選項另行指定。
* 啟用或禁用LOAD DATA語句的LOCAL能力
* 可在MySQL服務器編譯時，用configure腳本的--enable-local-infile或--disable-local-infile選項把LOAD DATA語句的LOCAL能力設置為啟用或禁用；
* 在MySQL服務器啟動是，可以用--local-infile或--disable-local-infile選項來啟用或禁用服務器的LOCAL能力(在MySQL 4.0.2之前的版本里，要用--local-infile=0來禁用它)。
如果在服務器端禁用了LOCAL的能力，則客戶端就不能使用該功能；如服務器啟用了LOCAL的能力，客戶端默認也是禁止使用的，但可用mysql程序的--local-infile選項啟用它。
* 國際化和本地化，國際化是指軟件能夠在世界多個國家地區使用，而本地化則是指可從國際化軟件中選擇一套適合本地區的語言和習慣的設置來使用。在MySQL中的國際化和本地化設置有以下幾方面內容：
* 時區，如果時區設置不對，則服務器顯示的時間將會和當地時間有沖突。設置方法可通過mysqld_safe腳本的--timezone選項來設置，但最好還是在選項文件里設置，如:
```
[mysqld_safe]
timezone=US/Central
```
* 配置顯示信息的語言，MySQL能用多種語言來顯示診斷信息與出錯信息，默認是英語。查看share/mysql目錄下有幾個以語言名稱作為目錄名的目錄就可知道有哪些語言可供選擇。可用--language啟動選項來指定語言，如--language=/usr/local/mysql/share/mysql/french。
* 配置服務器的字符集，MySQL支持多種字符集，可在share/mysql/charsets目錄下查詢支持的字符集，也可用show variables like 'character_sets'來顯示支持的字符集清單。MySQL把latin1作為默認的字符集。可在編譯時用--with-charset指定另外一個字符集為默認字符集。如要增加另外的字符集支持，可用--with-extra-charasets選項進行添加。如：
```
% ./configure --with-extra-charsets=latin1,gb2312,big5
```
--with-extra-charsets有兩個特殊的選項，一個是all，代表所有可用字符集；一個是complex，代表所有的復雜字符集(包括多字節字符集和有特殊排序規則的字符集)。
服務器啟動時，使用默認字符集，如需指定另外的字符集，需用--default-character-set選項指明。
在MySQL 4.1以前，如果在創建好數據表后改變服務器的默認字符集，就需對索引重新排序才能保證索引鍵值能夠正確反映出數據表記錄在新字符集下的排列順序。重新排序的操作命令如下：
```
% myisamchk --recover --quick --set-character-set=gb2312 #在執行該語句需關閉服務器，適用于MyISAM數據表
也可用：
% mysqlcheck --repair --quick #不需關閉服務器，適用于各種數據表
或者用：
mysql> REPLACE TABLE ... QUICK；
```
在客戶端，可用--default-character-set選項指定客戶程序使用的字符集。--character-sets-dir選項可指出字符集文件的安裝目錄。
* 升級數據表到4.1，支持多字符集數據表。步驟如下：
1. 用mysqldump程序備份數據庫：
```
% mysqldump -p -u root --all-databases --opt > dumpfile.sql
--all-databases選項的作用是轉儲所有數據庫；
--opt選項的作用是對轉儲文件進行優化。
```
2. 關閉服務器，升級MySQL服務器軟件到4.1版。
3. 用備份文件重新加載數據表：
```
% mysql -p -u root < dumpfile.sql
```
這樣，字符集信息就被分配到每一個數據列中，此后，即使服務器改變了默認的字符集，各數據列的字符集也不會改變。當以后修改某個數據列的字符集時，服務器會自動重索引，以反映最新變化。
* 配置InnoDB表空間。InnoDB表空間在邏輯上是一個連接的存儲區域，但實際上是由一個或多個磁盤文件組成。這些文件可以是普通的文件，也可以是一個未格式化的原始硬盤分區。InnoDB表空間通過一系列的配置選項來設置，其中最重要的有以下兩個：
為確保服務器每次啟動時都能調用同樣的選項，InnoDB的選項最好存放到選文件中。下面是一個例子：
```
innodb_data_home_dir =
innodb_data_file_path=/usr/loca/mysql/data/idbdata1:10M:autoextend:max:100M
說明：
InnoDB表空間文件默認存放到了MySQL的數據目錄中，名字叫idbdata1；
文件長度為10M；
可自動擴展，以8M為步長擴展，如有多個數據文件，只允許最后一個文件可自動擴展；
規定了最大的可擴展尺寸為100M。
```
* innodb_data_home_dir，設置InnoDB表空間各組成文件的父目錄，如果沒有指出，則默認是MySQL的數據目錄。
* innodb_data_file_path，描述InnoDB主目錄中各有關文件，包括文件名，文件長度和一些選項。各文件以分號分隔，各組成文件長度至少為10M。
把選項寫入選項文件后，啟動服務器就可自動創建和初始化InnoDB表空間。
利用原始磁盤分區作為InnoDB表空間可創建一個非常大的表空間，不受操作系統單文件最大容量的限制。并且能有效減少磁盤碎片的產生。要使用原始磁盤分區，需作如下配置：
* 首先，要進行初始化，在選項文件的[mysqld]中配置：
```
innodb_data_home_dir=
innodb_data_file_path=/dev/hda1:10Gnewraw #初始化/dev/hda1這個10G容量的分區
```
啟動服務器，服務器會對這個10G的分區進行初始化。
* 接著，關閉服務器，修改配置文件，把newraw改為raw，如：
```
innodb_data_home_dir=
innodb_data_file_path=/dev/hda1:10Graw
```
重新啟動服務器，MySQL就會以讀/寫方式使用該表空間了。在windows平臺上配置InnoDB表空間時，windows路徑名中的反斜杠可以寫成單個的斜線字符(/)。也可寫成兩個反斜杠(\\)。如：
```
innodb_data_home_dir=
innodb_data_file_path=c:/mysql/data/ibdata1:10M;d:/ibdata2:20M
```
默認情況下，InnoDB的日志文件會存儲在MySQL的數據目錄，文件名以ib開頭。一旦完成InnoDB表空間的初始化，就不能改變組成文件的大小，但可通過添加數據文件或設置自動擴展來增加表空間容量。如需通過增加文件的方法擴大表空間的容量，可按以下步驟進行：
1. 關閉正在運行的MySQL服務器
2. 如果InnoDB表空間的最后一個組成文件是可自擴展的，就要先把它改變成一個固定長度文件才能把另一個文件添加到它后面。方法是先計算出該文件的近似大小，重新設置，如：
```
innodb_data_file_path=ibdata1:100M:autoextend
改成：
innodb_data_file_path=ibdata1:150M
```
3. 把新的組成文件添加到文件清單的末尾，該文件可以是普通文件，也可以是一個原始硬盤分區。
4. 重啟服務器。
還有一種方法重新配置InnoDB表空間，就是先備份，再重新配置，最后重新加載備份。具體步驟如下：
1. 使用mysqldump備份整個InnoDB數據庫；
2. 關閉服務器，刪除所有InnoDB表空間、InnoDB日志文件 及InnoDB數據表的.frm文件；
3. 重新配置InnoDB表空間；
4. 配置完成后，用備份文件重載數據，生成新的InnoDB數據表。
## 4.7\. 優化服務器
MySQL服務器為我們提供了豐富的參數，以調整服務器滿足不同環境的要求。下面分別討論一下這些參數：
* 服務器參數變量的設置。MySQL服務器參數可在服務器啟動時設置，在MySQL4.0.3及以后的版本中，有些參數也允許在線設置。在MySQL4.0.2及以后的版本里，可以把一個變量名視為一個選項名來設置。如數據表緩沖區的尺寸由服務器參數talbe_cache來設置。如果需把它設置為128,則可以在命令行里增加
```
--table_cache=128
```
也可在選項文件中設置：
```
[mysqld]
table_cache=128
```
在命令行選項中'_'可寫'-'，變成：
```
--table-cache=128 #這種寫法更像一個標準選項
```
還有一種是使用--set-variable或-O選項，如：
```
--set-variable=table_cache=128
or
-O table_cache=128
在選項文件中可寫成：
[mysqld]
set-variable=table_cache=128
```
服務器參數分為全局級和會話級兩個級別。全局級參數將影響整個服務器，會話級參數則只影響某給定客戶連接上的工作。如果某個變量同時存在于兩個級別，則服務器在客戶建立連接時用全局變量的值去初始化相應的會話級參數，一旦客戶連接建立起來后，對全局參數所作的修改不會影響到相應的會話級參數當前值。設置全局參數和會話級參數的語句：
```
全局級：
mysql> SET GLOBAL variable = value;
mysql> SET @@GLOBAL.variable = value;
會話級：
mysql> SET SESSION variable = value;
mysql> SET @@SESSION.variable = value;
默認不帶級別限定符的SET語句修改的參數屬會話級，如：
mysql> SET variable = value;
mysql> SET @@variable = value;
可用一條SET語句設置多個參數，參數間用逗號分隔，如：
SET SESSION variable = value1,value2,value3;
```
SESSION和LOCAL是同義語，可用LOCAL代替SESSION，如：@@LOCAL
具備SUPER權限才能設置全局參數，新設置值的效力將持續到該參數被再次修改或服務器退出。設置會話級參數不用特殊的權限，新設置值的效力將持續到該值被再次修改或連接斷開。顯示參數的語句如下：
```
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'TEST';
SHOW SESSION VARIABLES;
SHOW SESSION VARIABLES LIKE 'TEST';
如不帶限定符，則返回會話級參數，如會話級參數不存在則返回全局級參數。
也可用命令行方式顯示服務器參數變量，如：
% mysqladmin variables
```
* 下面介紹一些MySQL服務器通用的參數變量：
* back_log，當多個客戶同時連接服務器時，客戶處理過程需進入一個隊列排隊等待服務器處理。該值定義服務器等待處理隊列長度的最大值，如果站點訪問量大，需加大該值。
* delayed_queue_size，在實際插入數據表前，來自insert delayed語句的數據行會進入一個隊列等待服務器處理。該值定義該隊列能容納的數據行的最大個數。當隊列滿時，會阻塞后續的語句。加大該值能提高insert delayed語句的執行速度。
* flush_time，自動存盤間隔。如果系統經常死機或重啟，把這個變量設置為一個適當的非零值，使MySQL服務器每隔flush_time稱去刷新一次數據表緩沖區，將其中的信息寫入磁盤。這將導致系統性能下降，但可減少數據表被破壞或丟失數據的概率。在命令行上用--flush選項啟動服務器可使數據表在每次修改后被自動存盤。
* key_buffer_size，用來容納索引塊的緩沖區的長度。加大該值可加快索引創建和修改操作的速度，該索引緩沖區越大，在內存中找到鍵值的可能性就越大，讀盤次數就越少。MySQL3.23前的版本里，該參數叫key_buffer。3.23版本之后，兩種叫法都可以。
* max_allowed_packet，服務器與客戶程序之間通信時使用的緩沖區在最大值。MySQL 4版本之前，該最大值可取16MB，MySQL 4版本以后，該值的最大值是1GB。如果客戶端與服務器需傳送大容量的數據，如BLOB或TEXT值，就要加大該值。客戶端也有一個同名的變量，默認是16MB，該值也要加大。客戶端的啟動命令為：
```
% mysql --set-variable=max_allowed_packet=64M
```
* max_connections，允許同時打開的連接數，如果站點繁忙，需加大該值。
* table_cache，數據表緩存區的尺寸。加大該值可使服務器能夠同時打開更多的數據表，從而減少文件打開/關閉操作的次數。
注意：加大max_connections和table_cache參數的值，會使服務器占用更多的文件描述符。運行多個服務器可繞過該限制。對一些分配給每個客戶的資源變量，設置時不能過大，因為當連接數快速增長時會很快耗盡服務器的資源，造成服務器性能下降。
* InnoDB處理程序變量：
* innodb_buffer_pool_size，InnoDB數據庫緩沖池的大小，如果有足夠的內存，可把該值設置得大些以減少讀盤操作。
* innodb_log_file_size和innodb_log_files_in_group，前者設置日志文件的長度，后者設置日志文件的個數。InnoDB日志文件的總長度是兩者的乘積，它的總長度不得超過4GB。
## 4.8\. 運行多個MySQL服務器
需運行多個服務器的原因有很多，比如上面提到的可繞過最大文件描述符的限制，還有是進行版本測試和提供專用服務等。運行多個服務器比運行單個服務器復雜很多，需注意以下問題：
* 在安裝不同版本的程序時，需分開目錄存放程序和數據目錄。如果同一版本的服務器軟件，則程序目錄可一樣，但數據目錄則要不同。可用--basedir=dir_name和--datadir=dir_name兩個啟動選項指時這兩個目錄。
* 要為不同的服務器指定不時的--port=port_name(TCP/IP監聽端口)，--socket=file_name(套接字文件名)和--pid-file=file_name(進程ID文件)值。
* 如果激活了日志功能，就要為不同的服務器指定不同的日志文件名。
* 在Windows平臺上，被安裝為服務的多個MySQL服務器必須有不同的服務名。
多服務器環境下選項文件的配置方法：
* 使用--defaults-file選項指定每個選項文件，這樣，每個服務器就不會去讀/etc/my.cnf這些配置文件，而會使用你所指定的配置文件。
* 可把一些公共的選項放到/etc/my.cnf文件里，再用--defaults-extra-file選項指出特定服務器的特定選項文件。這樣就不用在所有的配置文件時重復公共的選項。
* 用mysql_multi腳本啟動服務器，它允許我們把所有的選項放到同一個選項文件里。每一個服務器對應該文件中的一個選項組。
下面介紹用mysql_multi腳本啟動多服務器的方法。
1. 為每個服務器編一個編號xxx，對應選項文件的[mysqldxxx]選項組。mysql_multi本身要用到的選項可放到[mysqld_multi]里。這樣/etc/my.cnf選項配置文件看起來就象下面這樣：
```
[mysqld001]
basedir=/usr/local/mysql/001
datadir=/usr/local/mysql/001/data
mysqld=/usr/local/mysql/001/bin/mysqld_safe
socket=/usr/local/mysql/001/mysql.sock
port=3306
local-infile=1
user=mysqladm
log=log
log-update=update-log
innodb_data_file_path=ibdata1:10M
[mysqld002]
basedir=/usr/local/mysql/002
datadir=/usr/local/mysql/002/data
mysqld=/usr/local/mysql/002/bin/mysqld_safe
socket=/usr/local/mysql/002/mysql.sock
port=3307
local-infile=1
user=mysqladm
log=log
log-update=update-log
innodb_data_file_path=ibdata1:10M
...
```
2. 配置好選項文件后，就可用以下命令啟動服務器：
```
% mysqld_multi --no-log start 001,002
#啟動001和002兩個服務器，并把啟動信息發送到控制臺，也可用區間的形式給出服務器編號
```
用以下命令可查看服務器狀態：
```
% mysqld_multi --no-log --user=root --password=password report 001
```
可用以下命令停止MySQL服務器：
```
% mysqld_multi --no-log --user=root --password=password stop 001
```
* 在windows平臺下運行多個MySQL服務器的方式有兩種，一種是運行同一個MySQL程序的兩個實例，一種是運行多個windows服務，下面分別介紹：
* 第一種情況需設置兩個選項文件，指定不同的數據目錄，如：
```
c:\mysql\my.cnf1
[mysqld]
basedir=c:/mysql
datedir=c:/mysql/data1
port=3306
c:\mysql\my.cnf2
[mysqld]
basedir=c:/mysql
datadir=c:/mysql/data2
port=3307
```
在啟動服務器時，用--defaults-file選項指出選項文件即可。如：
```
c:\> mysqld --defaults-file=c:\mysql\my.cnf1
c:\> mysqld --defaults-file=c:\mysql\my.cnf2
```
* 在MySQL 4.0.2版本開始，可以把MySQL安裝為一個服務，并可指定一個服務名，如：
```
c:\> mysql-nt --install service_name
```
在MySQL 4.0.3開始，安裝服務還支持--defaults-file=file_name選項
這樣，我們就可把MySQL安裝為一系列不同的服務，如果不指定service_name，則安裝的服務名默認為MySql，如果指定service_name，則安裝的服務名為指定的service_name，并對應選項文件中的[service_name]選項組。以默認服務名運行的服務器還支持一個名為MySql的命名管道，而明確給出服務名的服務器將只監聽TCP/IP連接而不支持命名管道--除非還用socket選項明確指定一個套接字文件。
移除服務需先用mysqladmin shutdown命令停掉服務器，再執行以下命令：
```
c:\> mysql-nt --remove #移除默認的服務
c:\> mysql-nt --remove service_name #移除指定服務
```
## 4.9\. MySQL服務器鏡像配置
通過鏡像機制可把數據從一個地方復制到另一個地方，并能實現同步兩個或多個地方的數據。MySQL服務器也支持鏡像，大提高數據的安全性和穩定性。下面介紹一下MySQL數據中的鏡像機制：
* 在鏡像關系中，一個MySQL服務器扮演主服務器角色，另外一個或多個服務器扮演從服務器角色，從服務器中的數據和主服務器中的數據完全一樣。
* 在鏡像建立之前，主服務器和從服務器必須進行一次完全同步。同步之后，在主服務器上所做的操作將會在從服務器上再實現，主服務器上的操作不是直接作用于從服務器上的。
* 負責在主、從服務器上傳輸各種修改動作的媒介是主服務器上的二進制變更日志，該日志記錄著主服務器上所有的操作動作。因此，主服務器必須激活二進制日志功能。
* 從服務器必須有足夠的權限從主服務器上接收二進制日志文件。鏡像協調信息記錄從服務器的進展情況，包括，從服務器正在讀取的二進制變更日志文件名和它在該文件里的當前讀寫位置。
* 每個主服務器可以有多個從服務器，但每個從服務器只能有一個主服務器。但MySQL服務器允許把一個從服務器作為另一個從服務器的主服務器，這樣就可創建一個鏡像服務器鏈。
鏡像機制在MySQL中還是一個新生事物，最早實現于3.23.15版。各版本間的鏡像能力有差異，一般來說，建議大家盡量使用最新的版本，下面列舉了不同版本的MySQL服務器在鏡像機制方面的兼容規則：
* 3.23.x系統版本的從服務器不能與4.x系統版本的主服務器通信。
* 4.0.0版本的從服務器只能與4.0.0版本的主服務器通信。
* 4.0.1或更高版本的從服務器既能與3.23.x系統版本的主服務器通信，也能與4.x系統版本的主服務器通信。但后一種情況要求主服務器的版本號等于或大于從服務器的版本號。
一般來說，建議遵循以下原則：
* 要盡可能地讓主服務器和從服務器都使用同一版本系統。
* 在選定系統后，盡量使用該系統的最新版本。
建立主從鏡像服務器的步驟：
* 確定主從服務器的鏡像ID號，主從服務器的ID號不能相同。在啟動主從服務器時，用--server_id啟動選項給出其ID。
* 從服務器必須在主服務器上有一個具備足夠的權限的帳戶，從服務器將使用該帳戶去連接主服務器并請求主服務器把二進制變更日志發送給它。可用以下命令創建這個帳戶：
```
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_host' IDENTIFIED BY 'slave_pass';
#REPLICATION權限只MySQL4.0.2后版本，之前的版本請用FILE權限。
```
* 把主服務器上的數據庫文件拷貝到從服務器上完成最初同步工作。也可用備份后再加載的方法。在MySQL 4.0.0及以后版本里，還可用在主服務器上運行LOAD DATA FROM MASTER語句來建立從服務器。但有約束條件：
* 數據表要全部是MyISAM表
* 為發出這條指令而在連接從服務器時使用的帳戶必須有SUPER權限。
* 從服務器用來連接主服務器的帳戶必須具備RELOAD和SUPER權限。注意，這是一個主服務器上的帳戶，而用來發出LOAD DATA FROM MASTER語句的帳戶是一個從服務器上的帳戶。
* LOAD DATA FROM MASTER語句在執行時需申請一個全局性的讀操作鎖，這個鎖在語句執行期間阻塞主服務器上一切的寫操作。
無論用哪種方法同步數據，都要確保在開始制作備份到給主服務器重新配置好二進制日志功能這段時間，不能在主服務器上發生修改操作。
* 關閉服務器。
* 對主服務器的配置進行修改--把它的鏡像ID告訴它并激活其二進制日志功能。在主服務器要讀取的選項文件內增加以下內容：
```
[mysqld]
server-id=master_server_id
log-bin=binlog_name
```
* 重新啟動主服務器，從現在開始，它將把客戶對數據庫的修改操作記錄到二進制變更日志里。如果在此之前已經激活了二進制日志功能，則要在重啟前把二進制變更日志備份下來，在重啟后再發一條RESET MASTER語句去清除現有的二進制變更日志。
* 關閉從服務器。
* 對從服務器進行配置，使它知道自已的鏡像ID，到哪里去找主服務器以及如何去連接主服務器。配置內容如下：
```
[mysqld]
server-id=slave_server_id
master-host=master_host
master-user=slave_user #在主服務器上為從服務器建立的帳戶
msater-password=slave_pass #在主服務器上為從服務器建立的帳戶的密碼
master-connet-retry=30 #設置連接重試間隔，默認為60秒
master-retry-count=100000 #設置重試次數，默認為86400次
注：最后兩個選項在網絡連接不可靠時設置
```
* 重新啟動從服務器。從服務器用兩個信息源來確定它自已在鏡像工作中的進度位置：一個是數據目錄中的master.info文件，另一個是啟動選項所給定的配置信息。第一次啟動從服務器時，master.info文件不存在，從服務器會根據選項文件中給出的各種master-xxx選項值去連接主服務器。一旦連接成功，從服務器會創建一個master.info文件以保存各種連接參數和它自已的鏡像工作狀態。如果以后再重啟從服務器，從服務器會優先讀取該文件，而不是選項文件。所以如果你修改了選項文件的內容，想該選項生效就要刪除master.info文件并重啟從服務器。
以上步驟是鏡像所有數據庫的操作過程，如果我們想把mysql權限數據保留在主服務器上，排除在鏡像機制外的話，可用在選項文件的[mysqld]中加入--binlog-ignore-db=mysql選項，這樣，mysql數據庫上的操作就不會記錄在二進制變更日志里。如要排除其它數據庫，只要增加多幾個該選項即可。
通過以下幾個命令可監控和管理主從服務器：
* SLAVE STOP，SLAVE START用于掛起來恢復從服務器上鏡像，如當備份時，可用該語句讓從服務器暫時停止鏡像活動。
* SHOW SLAVE STATUS，在從服務器上查看其鏡像協調信息，這些信息可以用來判斷哪些二進制變更日志已經不再使用。
* PURGE MASTER，在主服務器上對二進制變更日志進行失效處理。刪除所有從服務器都不再使用的二進制變更日志。
* CHANGE MASTER，在從服務器上修改鏡像參數。如正在讀取主服務器上哪個二進制變更日志，正在寫哪個中繼日志文件等。
在MySQL4.0.2之后版本中，鏡像機制中的從服務器由兩個內部線程組成：
* 一個叫“I/O線程”，負責與主服務器通信，請求主服務器發送二進制變更日志，并把接收到的數據修改命令寫入某個中繼日志文件；用SLAVE STOP IO_THREAD或SLAVE START IO_THREAD可掛起或恢復該線程。
* 另一個叫“SQL線程”，負責從中繼日志中讀取數據修改命令并執行。同理，用SLAVE STOP SQL_THREAD或SLAVE START SQL_THREAD可掛起或恢復該線程。
中繼日志文件默認的文件為hostname-relay-bin.nnn和hostname-relay-bin.index。可用從服務器的--relay-log和--relay-log-index選項修改。在從服務器中還有一個relay-log.info中繼信息文件，可用--relay-log-info-file啟動選項修改文件名。
## Chapter 5\. 數據庫安全
安全是一個過程，而不是一個方法，它貫穿在我們使用和維護MySQL數據庫的過程中。這不單是系統管理員工作，用戶也要有安全的意識，使安全問題得到有效控制。MySQL服務器的安全問題可分為內部安全和外部安全兩部份。內部安全問題大都與系統文件有關，我們需確保MySQL程序文件和數據文件的安全。外部安全是指通過網絡連接到服務器的安全問題，應該只允許合法用戶訪問數據庫，在一些情況下還可用SSL加密信息傳輸通道。下分別介紹內部安全和外部安全的防范措施。
## 5.1\. 保護MySQL安裝程序文件
* 在重設置文件權限時，請先關閉數據庫服務器。
* 用以下命令把MySQL安裝程序目錄的屬主和所屬組設置為MySQL管理員帳號的用戶名和用戶組名。
```
% chown -R mysql.mysql /usr/local/mysql
```
另外一種方法是把除數據目錄外的所有目錄屬主設置為root所有，如：
```
% chown -R root.mysql /usr/local/mysql
% chown -R mysql.mysql /usr/local/mysql/data
```
* 設置安裝目錄及各有關子目錄的權限，允許管理員進行所有操作，只允許其他人進行讀和執行訪問，設置命令如下：
```
#設置mysql目錄
% chmod 755 /usr/local/mysql
or
% chmod u=rwx,go=rx /usr/local/mysql
#設置mysql/bin目錄
% chmod 755 /usr/local/mysql/bin
or
% chmod u=rwx,go=rx /usr/local/mysql/bin
#設置mysql/libexec目錄
% chmod 700 /usr/local/mysql/libexec
or
% chmod u=rwx,go-rwx /usr/local/mysql/libexec
```
* 把數據目錄及目錄中的所有子目錄和文件設置為只允許MySQL管理員訪問。
```
% chmod -R go-rwx /usr/local/mysql/data
```
如果數據目錄下有選項文件或套接字文件，并一些客戶需訪問這些文件，則可用以下的權限設置，使客戶在沒有讀權限的前提下使用這些文件：
```
% chmod go+x /usr/local/mysql/data
```
* mysql.sock套接字文件一般放以/tmp目錄下，要確保該目錄設置了粘著位，使自戶只能刪除自已創建的文件，不能刪除其他用戶創建的文件。/etc/my.cnf中公共選項文件，是對所有用戶可讀的，所以不應把一些敏感信息保存在里面。.my.cnf是用戶專用選項文件，要確保只有該用戶有權訪問。
* 這樣設置以后，只有MySQL管理員才能啟動服務器。
## 5.2\. 權限表
MySQL服務器通過權限表來控制用戶對數據庫的訪問，權限表存放在mysql數據庫里，由mysql_install_db腳本初始化。這些權限表分別user，db，table_priv，columns_priv和host。下面分別介紹一下這些表的結構和內容：
* user權限表：記錄允許連接到服務器的用戶帳號信息，里面的權限是全局級的。
* db權限表：記錄各個帳號在各個數據庫上的操作權限。
* table_priv權限表：記錄數據表級的操作權限。
* columns_priv權限表：記錄數據列級的操作權限。
* host權限表：配合db權限表對給定主機上數據庫級操作權限作更細致的控制。這個權限表不受GRANT和REVOKE語句的影響。
大家注意到，以上權限沒有限制到數據行級的設置。在MySQL只要實現數據行級控制就要通過編寫程序(使用GET-LOCK()函數)來實現。
MySQL的版本很多，所以權限表的結構在不同版本間會有不同。如果出現這種情況，可用mysql_fix_privilege_tables腳本來修正。運行方式如下：
```
% mysql_fix_privilege_tables rootpassword #這里要給出MySQL的root用戶密碼
```
最好一下子升級到MySQL 4.0.4版本，因為4.0.2和4.0.3的db表沒有Create_tmp_table_priv和Lock_tables_priv權限。
MySQL的權限表定義了兩部份內容，一個部份定義權限的范圍，即誰(帳戶)可以從哪里(客戶端主機)訪問什么(數據庫、數據表、數據列)；另一部份定義權限，即控制用戶可以進行的操作。下面是一些常用的權限介紹，可直接在GRANT語句中使用。
* CREATE TEMPORARY TABLES，允許創建臨時表的權限。
* EXECUTE，允許執行存儲過程的權限，存儲過程在MySQL的當前版本中還沒實現。
* FILE，允許你通過MySQL服務器去讀寫服務器主機上的文件。但有一定限制，只能訪問對任何用戶可讀的文件，通過服務器寫的文件必須是尚未存在的，以防止服務器寫的文件覆蓋重要的系統文件。盡管有這些限制，但為了安全，盡量不要把該權限授予普通用戶。并且不要以root用戶來運行MySQL服務器，因為root用戶可在系統任何地方創建文件。
* GRANT OPTION，允許把你自已所擁有的權限再轉授給其他用戶。
* LOCK TABLES，可以使用LOCK TABLES語句來鎖定數據表
* PROCESS，允許你查看和終止任何客戶線程。SHOW PROCESSLIST語句或mysqladmin processlist命令可查看線程，KILL語句或mysqladmin kill命令可終止線程。在4.0.2版及以后的版本中，PROCESS權限只剩下查看線程的能力，終止線程的能力由SUPER權限控制。
* RELOAD，允許你進行一些數據庫管理操作，如FLUSH，RESET等。它還允許你執行mysqladmin命令：reload，refresh，flush-hosts，flush-logs，flush-privileges，flush-status，flush-tables和flush-threads。
* REPLICATION CLIENT，允許查詢鏡像機制中主服務器和從服務器的位置。
* REPLICATION SLAVE，允許某個客戶連接到鏡像機制中的主服務器并請求發送二進制變更日志。該權限應授予從服務器用來連接主服務器的帳號。在4.0.2版這前，從服務器是用FILE權限來連接的。
* SHOW DATABASES，控制用戶執行SHOW DATABASES語句的權限。
* SUPER，允許終止線程，使用mysqladmin debug命令，使用CHANGE MASTER，PURGE MASTER LOGS以及修改全局級變量的SET語句。SUPER還允許你根據存放在DES密鑰文件里的密鑰進行DES解密的工作。
user權限表中有一個ssl_type數據列，用來說明連接是否使用加密連接以及使用哪種類型的連接，它是一個ENUM類型的數據列，可能的取值有：
* NONE，默認值，表示不需加密連接。
* ANY，表示需要加密連接，可以是任何一種加密連接。由GRANT的REQUIRE SSL子句設置。
* X509，表示需要加密連接，并要求客戶提供一份有效的X509證書。由GRANT的REQUIRE X509子句設置。
* SPECIFIED，表示加密連接需滿足一定要求，由REQUIRE子句的ISSUER，SUBJECT或CIPHER的值進行設置。只要ssl_type列的值為SPECIFIED，則MySQL會去檢查ssl_cipher(加密算法)、x509_issuer(證書簽發者)和x509_subject(證書主題)列的值。這幾列的列類型是BLOB類型的。
user權限表里還有幾列是設置帳戶資源使用情況的，如果以下數據列中的數全為零，則表示沒有限制：
* max_connections，每小時可連接服務器的次數。
* max_questions，每小時可發出查詢命令數。
* max_updates，每小時可以發出的數據修改類查詢命令數。
設置權限表應注意的事項：
* 刪除所有匿名用戶。
* 查出所有沒有口令用戶，重新設置口令。可用以下命令查詢空口令用戶：
```
mysql> SELECT host,user FROM user WHERE password = '';
```
* 盡量不要在host中使用通配符。
* 最好不要用user權限表進行授權，因為該表的權限都是全局級的。
* 不要把mysql數據庫的權限授予他人，因為該數據庫包含權限表。
* 使用GRANT OPTION權限時不要濫用。
* FILE權限可訪問文件系統中的文件，所以授權時也要注意。一個具有FILE權限的用戶執行以下語句就可查看服務器上全體可讀的文件：
```
mysql> CREATE TABLE etc_passwd(pwd_entry TEXT);
mysql> LOAD DATA INFILE '/etc/passwd' INTO TABLE etc_passwd;
mysql> SELECT * FROM etc_passwd；
```
如果MySQL服務器數據目錄上的訪問權限設置得不好，就會留下讓具有FILE權限的用戶進入別人數據庫的安全漏洞。所以建議把數據目錄設置成只能由MySQL服務器讀取。下面演示一個利用具有FILE權限的用戶讀取數據目錄中文件權限設置不嚴密的數據庫數據的過程：
```
mysql> use test;
mysql> create table temp(b longblob);
mysql> show databases #顯示數據庫名清單，--skip-show-database可禁止該功能
mysql> load data infile './db/xxx.frm' into table temp fields escaped by '' lines terminated by '';
mysql> select * from temp into outfile 'xxx.frm' fields escaped by '' lines terminated by '';
mysql> delete from temp;
mysql> load data infile './db/xxx.MYD' into table temp fields escaped by '' lines terminated by '';
mysql> select * from temp into outfile 'xxx.MYD' fields escaped by '' lines terminated by '';
mysql> delete from temp;
mysql> load data infile './db/xxx.MYI' into table temp fields escaped by '' lines terminated by '';
mysql> select * from temp into outfile 'xxx.MYI' fields escaped by '' lines terminated by '';
mysql> delete from temp;
```
這樣，你的數據庫就給人拷貝到本地了。如果服務器是運行在root用戶下，那危害就更大了，因為root可在服務器上做任何的操作。所以盡量不要用root用戶來運行服務器。
* 只把PROCESS權限授予可信用戶，該用戶可查詢其他用戶的線程信息。
* 不要把RELOAD權限授予無關用戶，因為該權限可發出FLUSH或RESET語句，這些是數據庫管理工具，如果用戶不當使用會使數據庫管理出現問題。
* ALTER權限也不要授予一般用戶，因為該權限可更改數據表。
GRANT語句對權限表的修改過程：
* 當你發送一條GRANT語句時，服務器會在user權限表里創建一個記錄項并把你用戶名、主機名和口令記錄在User、Host和Password列中。如果設置了全局權限，由把該設置記錄在相在的權限列中。
* 如果在GRANT里設置了數據庫級權限，你給出的用戶名和主機名就會記錄到db權限表的User和Host列中，數據庫名記錄在Db列中，權限記錄到相關的權限列中。
* 接著是到數據表和數據列級的權限設置，設置方法和上面的一樣。服務器會把用戶名、主機名、數據庫名以及相應的數據表名和數據列名記錄到數據表中。
刪除用戶權限其實就是把這些權限表中相應的帳號記錄全部刪除即可。
## 5.3\. 建立加密連接
加密連接可提高數據的安全性，但會降低性能。要進行加密連接，必須滿足以下要求：
* user權限表里要有相關的SSL數據列。如果安裝的MySQL服務器是4.0.0版的，user權限表已包含相關的SSL數據列，否則，我們也可用mysql_fix_privilege_tables腳本升級權限表。
* 服務器和客戶程序都已經編譯有OpenSSL支持。首先要安裝openssl，在編譯時MySQL服務器時加--with-vio和--with-openssl選項加上openssl支持。可用以下語句查詢服務器是否支持SSL：
```
mysql> show variables like 'have_openssl';
```
* 在啟動服務器時用有關選項指明證書文件和密鑰文件的位置。在建立加密連接前，要準備三個文件，一個CA證書，是由可信賴第三方出具的證書，用來驗證客戶端和服務器端提供的證書。CA證書可向商業機構購買，也可自行生成。第二個文件是證書文件，用于在連接時向對方證明自已身份的文件。第三個文件是密鑰文件，用來對在加密連接上傳輸數據的加密和解密。MySQL服務器端的證書文件和密鑰文件必須首先安裝，在sampdb發行版本的ssl目錄里有幾個供參考的樣本文件：ca-cert.pem(CA證書)，server-cert.pem(服務器證書)，server-key.pem(服務器公共密鑰)。把這幾個文件拷貝到服務器的數據目錄中，再在選項文件里加上以下內容：
```
[mysqld]
ssl-ca=/usr/local/mysql/data/ca-cert.pem
ssl-cert=/usr/local/mysql/data/server-cert.pem
ssl-key=/usr/local/mysql/data/server-key.pem
```
重啟服務器，使配置生效。
* 要想讓某個客戶程序建立加密連接，必須在調用這個客戶程序時用有關選項告訴它在哪里能找到其證書文件和密鑰文件。在sampdb發行版的ssl目錄中提供了client-cert.pem(客戶證書文件)，client-key.pem(客戶密鑰文件)，CA證書與服務器使用同樣的ca-cert.pem。把他們拷貝到個人目錄下，并在.my.cnf選項文件中指出文件位置，如：
```
[mysql]
ssl-ca=/home/mysql/ca-cert.pem
ssl-cert=/home/mysql/client-cert.pem
ssl-key=/home/mysql/client-key.pem
```
配置完成后，調用mysql程序運行\s或SHOW STATUS LIKE 'SSL%'命令，如果看到SSL:的信息行就說明是加密連接了。如果把SSL相關的配置寫進選項文件，則默認是加密連接的。也可用mysql程序的--skip-ssl選項取消加密連接。如果用命令行方式啟用加密連接可以這樣寫：
```
% mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem
```
可用GRANT語句的REQUIRE SSL選項來強制用戶使用加密連接。
使用sampdb發行版的證書可以建立一個加密連接，但由于該文件已公開，所以安全性不好，我們可以在測試成功后自行建立證書或購買商業證書，以提高安全性。如何自行建立SSL證書的文檔在sampdb發行版的ssl/README文件里有說明。
## Chapter 6\. 數據庫的備份、維護和修復
數據庫在運行中，會因為人為因素或一些不可抗力因素造成數據損壞。所以為了保護數據的安全和最小停機時間，我們需制定詳細的備份/恢復計劃，并定期對計劃的有效性進行測試。本章結合MySQL服務器的運行機制和所提供的工具，介紹如何進行數據庫的備份、維護和修復。
以下是幾點防范的措施：
* 制定一份數據庫備份/恢復計劃，并對計劃進行仔細測試。
* 啟動數據庫服務器的二進制變更日志，該功能的系統開銷很小(約為1%)，我們沒有理由不這樣做。
* 定期檢查數據表，防范于未燃。
* 定期對備份文件進行備份，以防備份文件失效。
* 把MySQL的數據目錄和備份文件分別放到兩個不同的驅動器中，以平衡磁盤I/O和增加數據的安全。
## 6.1\. 檢查/修復數據表
對數據表進行維護最好通過發出CHECK TABLE(檢查數據表)或REPAIRE TABLE(修復數據表)命令來做，這樣MySQL服務器自動進行表鎖定以協調數據表中數據的讀寫一致性問題。也可用myisamchk工具來做數據表的維護，但它直接訪問有關的數據表文件，不通過服務器，所以需人為地協調數據表數據的讀寫一致性問題。使用myisamchk檢查數據表的具體操作步驟如下：
* 以mysql客戶端程序連接服務器，并發出LOCK TABLE命令，以只讀方式鎖住數據表。
```
% mysql
mysql> use db
mysql> LOCK TABLE table_name READ; #以只讀方式鎖定表
mysql> FLUSH TABLE table_name; #關閉數據表文件，并把內存中的信息寫入磁盤
```
* 保持上面的狀態不退出，另開一個shell窗口，用以下命令維護(檢查)數據表。
```
% myisamchk table_name
```
> 如果不保持上面狀態，退出mysql會話，則表鎖定會自動取消。
* 維護完成，切換回mysql狀態的shell窗口，發出以下命令解除表鎖定。
```
mysql> UNLOCK TABLES;
```
使用myisamchk修復數據表的具體操作步驟如下：
* 進行修復操作需以讀/寫方式鎖定數據表，命令如下：
```
% mysql
mysql> use db
mysql> LOCK TABLE table_name WRITE; #以讀/寫方式鎖定數據表
mysql> FLUSH TABLE table_name;
```
* 保持mysql客戶端連接狀態，切換到第二個shell窗口，運行修復命令：
```
% myisamchk --recover table_name
```
> 運行修復命令前最好先備份一下數據文件。
* 修復完成后，切換回mysql客戶端連接窗口，運行以下命令解除數據表鎖定：
```
mysql> FLUSH TABLE table_name; #使服務器覺察新產生的索引文件
mysql> UNLOCK TABLE;
```
還可用以下命令鎖定所有表，鎖定后，所有用戶就只能讀不能寫數據，這樣就可使我們能安全地拷貝數據文件。
```
mysql> FLUSH TABLES WITH READ LOCK;
```
下面是解除鎖語句：
```
mysql> UNLOCK TABLES;
```
## 6.2\. 備份數據庫
定期的備份可使我們數據庫崩潰造成的損失大大降低。在MySQL中進行數據備份的方法有兩種，一種是使用mysqldump程序，一種是使用mysqlhotcopy、cp、tar或cpio等打包程序直接拷貝數據庫文件。mysqldump程序備份數據庫較慢，但它生成的文本文件便于移植。使用mysqlhotcopy等程序備份速度快，因為它直接對系統文件進行操作，需人為協調數據庫數據的備份前后一致性。
* 使用mysqldump備份數據庫其實就是把數據庫轉儲成一系列CREATE TABLE和INSERT語句，通過這些語句我們就可重新生成數據庫。使用mysqldump的方法如下：
```
% mysqldump --opt testdb | gzip > /data/backup/testdb.bak
#--opt選項會對轉儲過程進行優化，生成的備份文件會小一點，后的管道操作會進行數據壓縮
% mysqldump --opt testdb mytable1,mytable2 | gzip > /data/backup/testdb_mytable.bak
#可在數據庫后接數據表名，只導出指定的數據表，多個數據表可用逗號分隔
```
> --opt選項還可激活--add-drop-table選項，它將會在備份文件的每條CREATE TABLE前加上一條DROP TABLE IF EXISTS語句。這可方便進行數據表的更新，而不會發生“數據表已存在”的錯誤。
用mysqldump命令還可直接把數據庫轉移到另外一臺服務器上，不用生成備份文件。重復執行可定期更新遠程數據庫。
```
% mysqladmin -h remote_host create testdb
% mysqldump --opt testdb | mysql -h remote_host testdb
另外還可通過ssh遠程調用服務器上的程序，如：
% ssh remote_host mysqladmin create testdb
% mysqldump --opt testdb | ssh remote_host mysql testdb
```
* 通過直接拷貝系統文件的方式備份數據庫，在備份時，要確保沒有人對數據庫進行修改操作。要做到這點，最好關閉服務器。如果不能關閉的，要以只讀方試鎖定有關數據表。下面是一些示例：
```
% cp -r db /backup/db #備份db數據庫到/backup/db目錄
% cp table_name.* /backup/db #只備份table_name數據表
% scp -r db remotehot:/usr/local/mysql/data #用scp把數據庫直接拷貝到遠程服務器
```
> 在把數據庫直接拷貝到遠程主機時，應注意兩臺機器必須有同樣的硬件結構，或者將拷貝的數據表全部是可移植數據表類型。
* 使用mysqlhotcopy工具，它是一個Perl DBI腳本，可在不關閉服務器的情況下備份數據庫，它主要的優點是：
* 它直接拷貝文件，所以它比mysqldump快。
* 可自動完成數據鎖定工作，備份時不用關閉服務器。
* 能刷新日志，使備份文件和日志文件的檢查點能保持同步。
下面是該工具的使用示例：
```
% mysqlhotcopy db /bakcup/ #把db數據庫備份到backup/db目錄里，會自動創建一個db目錄
```
* 使用BACKUP TABLE語句進行備份，該語句最早出現在MySQL 3.23.25版本中，僅適用于MyISAM數據表。用法如下：
```
mysql> BACKUP TABLE mytable TO '/backup/db'; #把mytable數據表備份到/backup/db目錄下
```
為了執行該語句，你必須擁有那些表的FILE權限和SELECT權限，備份目錄還必須是服務器可寫的。該語句執行時，會先把內存中的數據寫入磁盤，再把各個數據表的.frm(表結構定義文件)、.MYD(數據)文件從數據目錄拷貝到備份目錄。它不拷貝.MYI(索引)文件，因為它能用另外兩個文件重建。BACKUP TABLE語句備份時，依次鎖定數據表，當同時備份多個數據表時，數據表可能會被修改，所以備份0完成時，備份文件中的數據和現時數據表中的數據可能會有差異，為了消除該差異，我們可用只讀方式鎖定數據表，在備份完成后再解鎖。如：
```
mysql> LOCK TABLES tb1 READ,tb2 READ;
mysql> BACKUP TABLE tb1,tb2 TO 'backup/db';
mysql> UNLOCK TABLES;
```
使用BACKUP TABLE語句備份的數據表可用RESTORE TABLE重新加載到服務器。
* InnoDB和BDB數據庫也可用mysqldump和直接拷貝法進行備份。使用直接拷貝法時應注意需把組成InnoDB和BDB數據庫的所有文件都拷貝下來，如InnoDB的.frm文件、日志文件和表空間配置文件；BDB的數據文件、日志文件等。
* 使用鏡像機制進行備份，我們可用SLAVE STOP語句掛起從服務器的鏡像，在從服務器上通過直接拷貝法或其它工具制作備份。備份完成，用SLAVE START重新啟動鏡像，從服務器重新與主服務器同步，接收備份時主服務器所做的修改。
在MySQL中沒有為數據庫重命名的命令，但我們可用mysqldump轉儲數據庫，再創建一個新的空數據庫，把轉儲文件加載到該新數據庫，這樣就完成數據庫重命名的工作。如：
```
% mysqldump old_db >db.sql #轉儲db數據庫數據
% mysqladmin create new_db #新建一個空的數據庫
% mysql new_db < db.sql #把db數據庫的數據加載到新的數據庫中
% mysqladmin drop old_db #刪除舊的數據庫
```
一個更簡單的重命名數據庫的方法是直接修改數據庫目錄名，但該方法不適用于InnoDB和BDB數據庫。注意，在更名后，需在權限表中更新相關數據表信息，需執行以下語句：
```
mysql> UPDATE db SET db='new_db' WHERE db='old_db';
mysql> UPDATE tables_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE columns_priv SET db='new_db' WHERE db='old_db';
mysql> UPDATE host SET db='new_db' WHERE db='old_db';
```
## 6.3\. 使用備份恢復數據
恢復過程包括兩個信息源---備份文件和二進制日志，備份文件可使用數據恢復到執行備份時的狀態，而二進制日志可恢復到發生故障時的狀態。下面分別介紹如何利用這兩個文件恢復一個數據庫或恢復單個數據表。
恢復整個數據庫的步驟：
1. 把需恢復的數據庫的整個目錄的內容拷貝到其它地方，以備用。
2. 使用最近的備份文件重載數據庫。如果使用mysqldump生成的備份，則可使用它們作為mysql的輸入重載；如果是通過拷貝數據庫目錄來備份的，則要關閉數據庫服務器，再把備份重新拷貝到數據目錄，再重啟數據庫服務器。
3. 通過二進制日志重做事務，恢復到出錯點的數據。具體操作是這樣的，用mysqlbinlog把日志轉換成ASCII格式，再把它作為mysql的輸入，并指定--one-database選項，以便mysql只執行你指定的數據庫恢復。如：
```
% ls -t -r -l binlog.[0-9]* | xargs mysqlbinlog | mysql --one-database db_name
```
但上面命令只適合所有日志文件名具有相同長度的情況。否則需用下面的Perl代碼來處理：
```
#!/usr/bin/perl -w
# sort_num.pl
use strict;
my @files = <>; #read all input file
@files = sort { my $anum = $1 if $a =~/\.(\d+)$/; #sort them by numeric extension
my $bnum = $1 if $b =~/\.(\d+)$/;
$anum <=> $bnum;
} @files;
print @files; #print them
exit(0);
如下使用該腳本：
% ls -l binlog.[0-9]* | sort_num.pl | xargs mysqlbinlog | mysql --one-database db_name
```
上面討論的是需所有日志文件的情況，但多數情況下我們只需備份后生成的幾個日志文件，這時，可用以下命令來重做。
```
% mysqlbinlog binlog.1234 | mysql --one-database db_name
% mysqlbinlog binlog.1235 | mysql --one_database db_name
...
```
如果我們需恢復因執行DROP DATABASE，DROP TABLE或DELETE語句而損壞的數據庫，就需在日志文件中刪除該語句，否則重做后結果還是一樣。所以需把日志轉換成ASCII格式并保存起來，再用編輯器打開該文件，刪除這些語句后再重做。
> 如果使用文本變更日志，則不需使用mysqlbinlog程序，因為該日志文件本身就是ASCII格式。
恢復使用BACKUP TABLE命令備份的數據表可用RESTORE TABLE語句：
```
備份語句：
mysql> BACKUP TABLE table_name1,table_name2 TO '/backup/table_backup';
恢復語句：
mysql> RESTORE TABLE table_name1,table_name2 FROM '/backup/table_backup';
```
恢復有外鍵的數據表，可用SET FOREIGN_KEY_CHECK = 0;語句先關閉鍵字檢查，導入表后再重啟它，賦值為1表示檢查有效。
恢復InnoDB表空間，當服務器重啟時，InnoDB處理程序會執行自動恢復工作，通過選項文件[mysqld]段中的set-variable=innodb_force_recovery=level設置自動恢復的級別，推薦典型的啟動值為4。如果需從備份文件恢復，則和上面介紹的方法一樣。BDB數據表的恢復也一樣，啟動服務器時它會進行自動恢復。
## Chapter 7\. MySQL程序介紹
安裝完MySQL后，在MySQL的安裝目錄下會生成很多有用的程序，下面對這些程序進行一一介紹。
* libmysqld----嵌入式MySQL服務器，它不能獨立運行，它是一個函數庫，可嵌入到其它程序中，使程序具有MySQL服務器的功能。
* myisamchk和isamchk----檢查和修復數據表、分析鍵值的分布情況、禁止或啟用數據表索引的工具。
* myisampack和pack_isam----壓縮數據表并生成只讀數據表。
* mysql----最常用的一個與服務器交互的命令行客戶端程序。
* mysqlaccess----一個用來測試數據庫訪問權限的腳本。
* mysqladmin----一個用來執行各種系統維護和管理工作的工具。
* mysqlbinlog----一個以ASCII格式顯示二進制變更日志內容的工具。
* mysqlbug----一個用來生成程序漏洞報告的腳本。
* mysql_config----當準備編譯基于MySQL的程序時，可以利用這個工具程序來確定該程序的編譯選項和標志。
* mysqld----MySQL服務器程序，MySQL數據的核心。
* mysqld_multi----一個用來同時啟動和關閉多個MySQL服務器的腳本。
* mysql_safe----一個用來啟動和監控MySQL服務器的腳本。
* mysqldump----一個用來導出數據表內容的工具。
* mysqlhotcopy----數據庫備份工具。
* mysqlimport----一個對數據表批量加載數據的工具。
* mysql_install_db----一個初始化系統權限表和數據目錄的腳本。
* mysql.server----一個用來啟動和關閉MySQL服務器的腳本。
* mysqlshow----一個用來顯示數據庫中數據表的工具。
## Chapter 8\. MySQL How-To
## 8.1\. 連接數據庫服務器
```
$ ./mysql -h host_name -u user_name -p
```
* `-h host_name(--host=host_name)`，連接的數據庫主機名，如果在本地主機上則可省略。
* `-u user_name(--user=user_name)`，數據庫用戶名，在unix系統上，如果系統的登錄名與數據用戶名一樣，則可省略。在windows系統中，可通過設置環境變量USER來給出數據庫用戶名，如set USER=username。
* `-p(--password)`，提供數據庫用戶密碼，有該選項mysql就會提示你輸入密碼。輸入的密碼以星號顯示，以確保安全。也可直接在-p后寫上密碼(-p和密碼間不能有空格)，但這不安全，不推薦。
連接成功后，mysql數據庫服務器會顯示一些歡迎信息。接著就可用mysql>use database_name命令打開指定的數據庫。
> `$ ./mysql -h host_name -u user_name -p database_name`命令可直接打開指定數據庫。
## 8.2\. 更新用戶密碼
```
mysql>update user set password=passowrd('your passowrd') where host='%';
```
刷新權限設置：`mysql>flush privileges;`
## 8.3\. MySQL讀取配置文件的順序
my.cnf是MySQL數據庫的配置文件，它存在多個地方，在/etc目錄，數據目錄和用戶主目錄都有。放在不同位置，里面的選項有不同的作用范圍，下面是MySQL讀取配置文件的順序和作用。
```
mysql 讀取配置文件的順序:
/etc/my.cnf Global options.
DATADIR/my.cnf Server-specific options.
defaults-extra-file The file specified with the --defaults-extra-file option.
~/.my.cnf User-specific options.
```
## 8.4\. 重設置MySQL管理員密碼的方法
有時我們會因為設置原因或時間長了忘記了數據庫管理員的密碼，使得我們被關在MySQL服務器外。MySQL服務器提供了一種方法可使我們在服務器上重設密碼。在windows和linux/unix平臺上操作稍有不同，下面分別介紹：
* Linux/Unix平臺下：
1. 用 % kill -TERM PID關閉服務器，用-TERM信息可使服務器在關閉前把內存中的數據寫入磁盤。如果服務器沒有響應，我們可用% kill -9 PID來強制刪除進程，但不建議這樣做。這時內存中的數據不會寫入磁盤，造成數據不完整。如果你是用mysql_safe腳本啟動MySQL服務器的，這個腳本會監控服務器的運行情況并在它被終止時重啟服務器，所以如需關閉服務器，要先終止該進程，然后再真正終止mysqld進程。
2. 使用--skip-grant-tables參數啟動MySQL Server，這時MySQL服務器將不使用權限表對連接操作進行驗證。你就可在不提供root密碼的情況下連接上服務器，并獲得root的權限。
```
% mysqld_safe --skip-grant-tables &
```
3. 用以下命令登錄服務器，并重設密碼：
```
% mysql -u root #不用密碼就可連接到服務器
mysql> use mysql
mysql> set password for 'root'@'localhost' = password('password');
```
> 修改MySQL服務器帳戶密碼有三種方式，你可參考本筆記中數據庫日常管理一章中的相關內容。在這種環境下，使用mysaladmin修改密碼好象不行，還是提示要輸入密碼。
4. 關閉服務器，再用正常方式啟動服務器。
* windows平臺下：
1. 用管理員帳號登錄服務器，關閉MySQL數據庫服務器。
2. 使用--skip-grant-tables參數啟動服務器：
```
c:\mysql\bin>mysqld-nt --skip-grant-tables
```
3. 重新打開一個console窗口，用mysql命令登錄服務器設置root的新密碼：
```
c:\mysql\bin>mysql
mysql> use mysql
mysql> set password for 'root'@'localhost' = password('password');
```
4. 關閉服務器，再用正常方式啟動服務器。
## 8.5\. NULL值
NULL是空值，代表什么也沒有。它不能與值進行比較操作和算術操作，也不能和NULL進行比較，因為兩個空值比較是沒有意義的。我們可用“is NULL” 或 “is not NULL”來判斷是不空值。如：
```
mysql> select * from test where mytest is NULL;
mysql> select * from test where mytest is not NULL;
```
在MySQL3.23以后的版本有一個新的比較操作符“<=>”，它可對NULL值進行相等比較。如：
```
mysql> select * from test where mytest <=> UNLL;
mysql> select * from test where not (mytest <=>);
```
如果查詢后排序中的數據中包含NULL，則從MySQL4.0.2開始，有NULL值的數據行總是出現在查詢結果的開頭，即使設置的desc參數。4.0.2以前版本，如果設置了asc，則出在查詢結果的開頭，如果設置了desc，則出現在查詢結果的結尾。
## 8.6\. 使用SQL變量
MySQL3.23.6以后的版本允許通過查詢結果來設置變量，設置好的變量可在以后使用。變量用@name定義，賦值方式用@name:=value。下面是一個在查詢語句中進行賦值和使用變量的例子：
```
mysql> select @name:=id from test where mytest="test";
mysql> select * from test where mytest=@name
```
## 8.7\. 改變默認提示符
用mysql登錄進數據庫后，MySQL數據的默認提示符是“mysql”，我們可設置它根據用戶打開的數據庫名而變化，如：
```
mysql>prompt \d>\_
none>use test
test>use mysql
mysql>
```
prompt為設置命令，\d代表當前數據庫，\_代表一個空格。
## 8.8\. 非優化的全數據表DELETE操作
為了清空數據表，又需知道刪除的行數和保持AUTO_INCREMENT序列的值，需用以下的刪除語句：
```
# delete from table_name where 1;
```
## 8.9\. MySQL事務處理示例
MYSQL高級特性 -- 事務處理下面以兩個銀行賬戶之間的轉賬為例子進行演示。要使用MySQL中的事務處理，首先需要創建使用事務表類型(如BDB = Berkeley DB或InnoDB)的表。
```
CREATE TABLE account (
account_id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
balance DOUBLE
) TYPE = InnoDB;
```
要在事務表上使用事務處理，必須要首先關閉自動提交：
```
SET AUTOCOMMIT = 0;
```
事務處理以BEGIN命令開始：
```
BEGIN;
```
現在mysql客戶處在于服務器相關的事物上下文中。任何對事務表所做的改變在提交之前不會成為永久性的改變。
```
UPDATE ACCOUNT SET balance = 50.25 WHERE account_id = 1;
UPDATE ACCOUNT SET balance = 100.25 WHERE account_id = 2;
```
在做出所有的改變之后，使用COMMIT命令完成事務處理：
```
COMMIT;
```
當然，事務處理的真正優點是在執行第二條語句發生錯誤時體現出來的，若在提交前終止整個事務，可以進行回滾操作：
```
ROLLBACK;
```
下面是另一個例子，通過MYSQL直接進行數學運算：
```
SELECT @first := balance FROM account WHERE account_id = 1;
SELECT @second := balance FROM account WHERE account_id = 2;
UPDATE account SET balance = @first - 25.00 WHERE account_id = 1;
UPDATE account SET balance = @second + 25.00 WHERE account_id = 2;
```
除了COMMIT命令外，下列命令也會自動結束當前事務：
```
ALTER TABLE
BEGIN
CREATE INDEX
DROP DATABASE
DROP TABLE
LOCK TABLES
RENAME TABLE
TRUNCATE
UNLOCK TABLES
```
