MySQL管理員指南

MySQL管理員指南

Mysql資料庫介紹

MySQL是一個真正的多使用者、多線程SQL資料庫伺服器。SQL（結構化查詢語言 (SQL)）是世界上最流行的和標準化的資料庫語言。MySQL是以一個客戶機/伺服器結構的實現，它由一個伺服器精靈mysqld和很多不同的客戶程式和庫組成。

SQL是一種標準化的語言，它使得儲存、更新和存取資訊更容易。例如，你能用SQL語言為一個網站檢索產品資訊及儲存顧客資訊，同時MySQL也足夠快和靈活以允許你儲存記錄檔案和映像。

MySQL 主要目標是快速、健壯和易用。最初是因為我們需要這樣一個SQL伺服器，它能處理與任何可不昂貴硬體平台上提供資料庫的廠家在一個數量級上的大型資料庫，但速度更快，MySQL就開發出來。自1996年以來，我們一直都在使用MySQL，其環境有超過 40 個資料庫，包含 10,000個表，其中500多個表超過7百萬行，這大約有100 個吉位元組(GB)的關鍵應用資料。

Mysql資料庫特點

1. 使用核心線程的完全多線程。這意味著它能很容易地利用多CPU（如果有）。

2. 可運行在不同的平台上。

3. 多種列類型：1、 2、 3、4、和 8 位元組長度的有符號/不帶正負號的整數(INT)、FLOAT、DOUBLE、CHAR、VARCHAR、TEXT、BLOB、DATE、TIME、DATETIME、 TIMESTAMP、YEAR、SET和ENUM類型。

4. 利用一個最佳化的一遍掃描多重連接（one-sweep multi-join）非常快速地進行連接(join)。

5. 在查詢的SELECT和WHERE部分支援全部運算子和函數。

6. 通過一個高度最佳化的類庫實現SQL函數庫並且像他們能達到的一樣快速，通常在查詢初始化後不應該有任何記憶體配置。

7. 全面支援SQL的GROUP BY和ORDER BY子句，支援彙總函式( COUNT()、COUNT(DISTINCT)、AVG()、STD()、SUM()、 MAX()和MIN() )。

8. 支援ANSI SQL的LEFT OUTER JOIN和ODBC文法。

9. 你可以在同一查詢中混用來自不同資料庫的表。

10. 一個非常靈活且安全的許可權和口令系統，並且它允許基於主機的認證。口令是安全的，因為當與一個伺服器串連時，所有的口令傳送被加密。

11. ODBC for Windiws 95。

12. 具備索引壓縮的快速B樹磁碟表。

13. 每個表允許有16個索引。每個索引可以由1～16個列或列的一部分組成。最大索引長度是 256 個位元組(在編譯MySQL時，它可以改變)。一個索引可以使用一個CHAR或VARCHAR欄位的首碼。

14. 定長和變長記錄。

15. 用作暫存資料表的記憶體散列表。

16. 大資料庫處理。我們正在對某些包含 50,000,000 個記錄的資料庫使用MySQL。

17. 所有列都有預設值，你可以用INSERT插入一個表列的子集，那些沒用明確給定值的列設定為他們的預設值。

18. 一個非常快速的基於線程的記憶體配置系統。

19. 沒有記憶體漏洞。用一個商用記憶體漏洞監測程式測試過(purify)。

20. 包括myisamchk，一個檢查、最佳化和修複資料庫表的快速公用程式。

21. 全面支援ISO-8859-1 Latin1 字元集。

22. 所有資料以 ISO-8859-1 Latin1 格式儲存。所有正常的字串比較是忽略大小寫。

23. DELETE、INSERT、REPLACE和UPDATE 返回有多少行被改變(受影響)。

24. 函數名不會與表或列名衝突。例如ABS是一個有效列名字。對函數調用的唯一限制是函數名與隨後的"("不能有空格。

25. 所有MySQL程式可以用選項--help或-?獲得線上說明。

26. 伺服器能為客戶提供多種語言的出錯訊息。

27. 用戶端使用TCP/IP 串連或Unix通訊端（socket）或NT下的具名管道串連MySQL。

28. MySQL特有的SHOW命令可用來檢索資料庫、表和索引的資訊，EXPLAIN命令可用來確定最佳化器如何解決一個查詢。

MySQL伺服器的啟動與停止

一、啟動伺服器的方法

啟動伺服器由三種主要方法：

1、 直接調用mysqld。

#./mysqld&

這可能是最不常用的方法，建議不要多使用。

2、調用safe_mysqld指令碼，最好的方法。

#./safe_mysqld -O join_buffer=128M -O key_buffer=128M -O record_buffer=256M -O sort_buffer=128M -O table_cache=2048 -O tmp_table_size=16M -O max_connections=2048 &

3、調用mysql.server指令碼。

safe_mysqld指令碼安裝在MySQL安裝目錄的bin目錄下，或可在MySQL原始碼分發的scripts目錄下找到。

mysql.server指令碼安裝在MySQL安裝目錄下的share/mysqld目錄下或可以在MySQL原始碼分發的support_files

目錄下找到。如果你想使用它們，你需要將它們拷貝到適當的目錄下mysql/bin下。

#./mysql.server start

Sun Solariys開機自動啟mysql的方法

寫一個啟動和關閉的批次檔Web (在路徑/etc/init.d 下), 內容如下:

#!/bin/sh

OPT_=$1

case "$OPT_" in

start)

/bin/echo "$0 : (start)"

#

# Your service startup command goes here.

#

/usr/local/apache/bin/apachectl start

/home3/mysql/bin/safe_mysqld -O join_buffer=128M -O key_buffer=128M -O record_buffer=256M -O sort_buffer=128M -O table_cache=2048 -O tmp_table_size=16M -O max_connections=2048 &

# NOTE: Must exit with zero unless error is severe.

exit 0

;;

stop)

/bin/echo "$0 : (stop)"

#

# Your service shutdown command goes here.

#

/usr/local/apache/bin/apachectl stop

# NOTE: Must exit with zero unless error is severe.

exit 0

;;

*) /bin/echo ''

/bin/echo "Usage: $0 [start|stop]"

/bin/echo " Invalid argument ==> \"${OPT_}\""

/bin/echo ''

exit 0

;;

esac

確認此檔案有可執行檔權利

#chmod 500 web

#cd /etc/rc2.d

#ln -s ../init.d/web S99mysql

在系統啟動時，S99mysql指令碼將自動用一個start參數調用。注意頭字母必須大寫。

二、停止伺服器的方法

1、要手工停止伺服器，使用mysqladmin：

#mysqladmin -u 使用者名稱 -p'密碼' shutdown

2、 調用mysql.server指令碼，最好的方法。

#./mysql.server stop

3、 直接殺掉OS的進程號

#kill -9 進程號

這可能是最不常用的方法，建議不要多使用。

要自動停止伺服器，你不需做特別的事情。只需要加另外一個關閉程式。

#cd /etc/rc0.d

#ln -s ../init.d/web K01mysql

在系統啟動時，K01mysql指令碼將自動用一個stop參數調用。

MySQL目錄結構和常用命令

一、 資料目錄的位置

這是預設的mysql目錄結構

bin info libexec share var

include lib man sql-bench

一個預設資料目錄被編譯進了伺服器，如果你從一個原始碼分發安裝MySQL，典型的預設目錄為/usr/local/var，如果從RPM檔案安裝則為/var/lib/mysql，如果從一個二進位分發安裝則是/usr/local/mysql/data。

作為一名MySQL管理員，你應該知道你的資料目錄在哪裡。如果你運行多個伺服器，你應該是到所有資料目錄在哪裡，但是如果你不知道確切的位置，由多種方法找到它：

1、使用mysqladmin variables從你的伺服器直接獲得資料目錄路徑名。尋找datadir變數的值，在Unix上，其輸出類似於：

%mysqladmin -u username -p'*****' variables

+----------------------+----------------------+

| variable_name | Value |

+----------------------+----------------------+

| back_log | 5 |

| connect_timeout | 5 |

| basedir | /var/local/ |

| datadir | /usr/local/var/ |

....

2、尋找mysql啟動並執行路徑

%ps -ef | grep mysqld

二、資料目錄結構

每個資料庫對應於資料目錄下的一個目錄。

在一個資料庫中的表對應於資料目錄下的檔案。

資料目錄也包含由伺服器產生的幾個狀態檔案，如記錄檔。這些檔案提供了關於伺服器操作的重要訊息。

對管理特別在出了問題而試圖確定問題原因時很有價值。

1、資料庫表的表示

資料庫目錄中有3種檔案：一個樣式（描述檔案）、一個資料檔案和一個索引檔案。每個檔案的基本名是表名，檔案名稱副檔名代表檔案類型。副檔名如下表。資料和索引檔案的副檔名指出表使用老式IASM索引或新式MyISAM索引。

MySQL檔案類型

檔案類型 檔案名稱副檔名 檔案內容

樣式檔案 .frm 描述表的結構（它的列、列類型、索引等）

資料檔案 .ISD(ISAM)或.MYD(MyISAM) 包含表裡所有的資料

索引檔案 .ISM(ISAM)或.MYI(MyISAM) 包含資料檔案上的所有索引的索引樹

當你發出一條CREATE TABLE tbl_name時語句定義表的結構時，伺服器建立一個名為tbl_name.frm的檔案，它包括該結構的內部編碼，同時也建立一個空資料和索引檔案，初始化為包含指出無記錄和無索引的資訊（如果CREATE TABLE語句包括索引指定，索引檔案反映出這些索引）。對應於表的檔案的屬主和模式被設定為只允許MySQL伺服器使用者訪問。

下面介紹一下常用的mysql命令

進入mysql資料庫

#./mysql -u 使用者名稱 -p'密碼'

查看所有的資料庫

mysql> show databases;

進入一個特定的資料庫

mysql> use 資料庫名;

查看資料庫裡所有的表

mysql> show tables;

把表改名

mysql> alter table 表名1 rename 表名2;

例子：mysql>alter table dept rename dept2;

2、 建索引的注意事項：

先要把要加索引的欄位設為非空

mysql> alter table 表名 change 欄位名 欄位名 欄位描述 not null;

例子：

我們建立這樣一個表

mysql> create table employee

( id int(5) not null,

depno int(5),

name varchar(20) not null,

cardnumber bigint(15) not null);

mysql> alter table employee change depno depno int(5) not null;

加索引

mysql> alter table 表名 add index 索引名 (欄位名1[，欄位名2 ...]);

例子： mysql> alter table employee add index emp_name (name);

加主關鍵字的索引

mysql> alter table 表名 add primary key (欄位名);

例子： mysql> alter table employee add primary key(id);

加唯一限制條件的索引

mysql> alter table 表名 add unique 索引名 (欄位名);

例子： mysql> alter table employee add unique emp_name2(cardnumber);

查看某個表的索引

mysql> show index from 表名;

例子： mysql> show index from employee;

刪除某個索引

mysql> alter table 表名 drop index 索引名;

例子： mysql>alter table employee drop index emp_name;

MySQL使用者管理

MySQL管理員應該知道如何設定MySQL使用者帳號，指出哪個使用者可以串連伺服器，從哪裡串連，串連後能做什麼。MySQL 3.22.11開始引入兩條語句使得這項工作更容易做：GRANT語句建立MySQL使用者並指定其許可權，而REVOKE語句刪除許可權。兩條語句扮演了mysql資料庫的前端角色，並提供與直接操作這些表的內容不同的另一種方法。CREATE和REVOKE語句影響4個表：

授權表內容：

user 能串連伺服器的使用者以及他們擁有的任何全域許可權

db 資料庫級許可權

tables_priv 表級許可權

columns_priv 列級許可權

還有第5個授權表（host），但它不受GRANT和REVOKE的影響。

當你對一個使用者發出一條GRANT語句時，在user表中為該使用者建立一條記錄。如果語句指定任何全域許可權（系統管理權限或適用於所有資料庫的許可權），這些也記錄在user表中。如果你指定資料庫、表和列級許可權，他們被分別記錄在db、tables_priv和columns_priv表中。

在下面的章節中，我們將介紹如何設定MySQL使用者帳號並授權。我們也涉及如何撤權和從授權表中刪除使用者。

一、建立使用者並授權

GRANT語句的文法看上去像這樣：

GRANT privileges (columns)

ON what

TO user IDENTIFIED BY "password"

WITH GRANT OPTION　

要使用該語句，你需要填寫下列部分：

privileges

授予使用者的許可權，下表列出可用於GRANT語句的許可權指定符：

許可權指定符 許可權允許的操作：

ALTER 修改表和索引

CREATE 建立資料庫和表

DELETE 刪除表中已有的記錄

DROP 拋棄（刪除）資料庫和表

INDEX 建立或拋棄索引

INSERT 向表中插入新行

REFERENCE 未用

SELECT 檢索表中的記錄

UPDATE 修改現存表記錄

FILE 讀或寫伺服器上的檔案

PROCESS 查看伺服器中執行的線程資訊或殺死線程

RELOAD 重載授權表或清空日誌、主機緩衝或表緩衝。

SHUTDOWN 關閉伺服器

ALL 所有；ALL PRIVILEGES同義字

USAGE 特殊的"無許可權"許可權

columns

許可權運用的列，它是可選的，並且你只能設定列特定的許可權。如果命令有多於一個列，應該用逗號分開它們.

what

許可權運用的層級。許可權可以是全域的（適用於所有資料庫和所有表）、特定資料庫（適用於一個資料庫中的所有表）或特定表的。可以通過指定一個columns字句是許可權是列特定的。

user

許可權授予的使用者，它由一個使用者名稱和主機名稱組成。MySQL中的一個使用者名稱就是你串連伺服器時指定的使用者名稱，該名字不必與你的Unix登入名稱或Windows名聯絡起來。預設地，如果你不明確指定一個名字，客戶程式將使用你的登入名稱作為MySQL使用者名稱。這隻是一個約定。你可以在授權表中將該名字改為nobody，然後以nobody串連執行需要超級使用者權限的操作。

password

賦予使用者的口令，它是可選的。如果你對新使用者沒有指定IDENTIFIED BY子句，該使用者不賦給口令（不安全）。對現有使用者，任何你指定的口令將代替老口令。如果你不指定口令，老口令保持不變，當你用IDENTIFIED BY時，口令字串用改用口令的字面含義，GRANT將為你編碼口令，不要象你用SET PASSWORD 那樣使用password()函數。

WITH GRANT OPTION子句是可選的。如果你包含它，使用者可以授予許可權通過GRANT語句授權給其它使用者。你可以用該子句給與其它使用者授權的能力。

使用者名稱、口令、資料庫和表名在授權表記錄中是大小寫敏感的，主機名稱和列名不是。

舉例：建立一個超級使用者test1

mysql> grant all privilleges on *.* to test1@localhost identified by '123456' with grant option;

建立一個只能查詢的使用者 test2

mysql> grant select on *.* to test2@localhost identified by '9876543';

二、撤權並刪除使用者

要取消一個使用者的許可權，使用REVOKE語句。REVOKE的文法非常類似於GRANT語句，除了TO用FROM取代並且沒有INDETIFED BY和WITH GRANT OPTION子句：

REVOKE privileges (columns) ON what FROM user

user部分必須匹配原來GRANT語句的你想撤權的使用者的user部分。privileges部分不需匹配，你可以用GRANT語句授權，然後用REVOKE語句只撤銷部分許可權。REVOKE語句只刪除許可權，而不刪除使用者。即使你撤銷了所有許可權，在user表中的使用者記錄依然保留，這意味著使用者仍然可以串連伺服器。要完全刪除一個使用者，你必須用一條DELETE語句明確從user表中刪除使用者記錄：

#mysql -u root mysql

mysql>DELETE FROM user

->WHERE User="user_name" and Host="host_name";

mysql>FLUSH PRIVILEGES;　

DELETE語句刪除使用者記錄，而FLUSH語句告訴伺服器重載授權表。（當你使用GRANT和REVOKE語句時，表自動重載，而你直接修改授權表時不是。）

舉例：刪除使用者test1

mysql> revoke all on *.* from test2@localhost;

mysql> use mysql;

mysql> delete from user where user='test' and host='localhost';

mysql> flush privileges;

MySQLDatabase Backup

在資料庫表丟失或損壞的情況下，備份你的資料庫是很重要的。已經知道表被破壞，用諸如vi或Emacs等編輯器試圖直接編輯它們，這對錶絕對不是件好事！

備份資料庫兩個主要方法是用mysqldump程式或直接拷貝資料庫檔案（如用cp、cpio或tar等）。每種方法都有其優缺點：

mysqldump與MySQL伺服器協同操作。直接拷貝方法在伺服器外部進行，並且你必須採取措施保證沒有客戶正在修改你將拷貝的表，一般在資料庫關閉情況下做。mysqldump比直接拷貝要慢些。mysqldump產生能夠移植到其它機器的文字檔，甚至那些有不同硬體結構的機器上。直接拷貝檔案可以移植到同類機器上，但不能移植到其它機器上，除非你正在拷貝的表使用MyISAM儲存格式。

一、使用mysqldump備份和拷貝資料庫

當你使用mysqldump程式產生Database Backup檔案時，預設地，檔案內容包含建立正在傾倒的表的CREATE語句和包含表中行資料的INSERT語句。換句話說，mysqldump產生的輸出可在以後用作mysql的輸入來重建資料庫。

Mysqldump參數如下：

#mysqldump -u 使用者名稱-p'密碼' 資料庫名 [表名] > 作業系統下檔案名稱

舉例：#./mysqldump -u root -p'123456' samp_db>samp.db.txt

輸出檔案的開頭看起來象這樣：

# MySQL Dump 6.0

#

# Host: localhost Database: samp_db

#---------------------------------------

# Server version 3.23.2-alpha-log

#

# Table structure for table 'absence'

#

CREATE TABLE absence(

student_id int(10) unsigned DEFAULT '0' NOT NULL,

date date DEFAULT '0000-00-00' NOT NULL,

PRIMARY KEY (student_id,date)

);

#

# Dumping data for table 'absence'

#

INSERT INTO absence VALUES (3,'1999-09-03');

INSERT INTO absence VALUES (5,'1999-09-03');

INSERT INTO absence VALUES (10,'1999-09-08');

......　

檔案剩下的部分有更多的INSERT和CREATE TABLE語句組成。

輸出單個的表：

#mysqldump samp_db student score event absence >grapbook.sql

#mysqldump samp_db member president >hist-league.sql

預設地，mysqldump在寫入前將一個表的整個內容讀進記憶體。這通常確實不必要，並且實際上如果你有一個大表，幾乎是失敗的。你可用--quick選項告訴mysqldump只要它檢索出一行就寫出每一行。為了進一步最佳化傾倒過程，使用--opt而不是--quick。--opt選項開啟其它選項，加速資料的傾倒和把它們讀回。

二、使用直接拷貝資料庫的備份和拷貝方法

另一種不涉及mysqldump備份資料庫和表的方式是直接拷貝資料庫表檔案。典型地，這用諸如cp、tar或cpio公用程式。本文的例子使用cp。

%cd DATADIR

%cp -r samp_db /usr/archive/mysql

單個表可以如下備份：

%cd DATADIR/samp_db

%cp member.* /usr/archive/mysql/samp_db

%cp score.* /usr/archive/mysql/samp_db

....

當你完成了備份時，你可以重啟伺服器（如果關閉了它）或釋放加在表上的鎖定（如果你讓伺服器運行）。要用直接拷貝檔案把一個資料庫從一台機器拷貝到另一台機器上，只是將檔案拷貝到另一台伺服器主機的適當資料目錄下即可。要確保檔案是MyIASM格式或兩台機器有相同的硬體結構，否則你的資料庫在另一台主機上有奇怪的內容。你也應該保證在另一台機器上的伺服器在你正在安裝資料庫表時不訪問它們。

建一個sh檔案bakmysql.sh

#!/bin/sh

cd /usr/local/mysql/

tar cvf /mount2/mysqlvar.tar var

可以設到作業系統自動運行。

root使用者crontab檔案

/var/spool/cron/crontabs/root增加以下內容

0 2 1 * * /mount2/bakmysql.sh

#/etc/rc2.d/S75cron stop

#/etc/rc2.d/S75cron start

重新擊活Sun Solaris自動處理進程

三、用備份恢複資料

資料庫損壞的發生有很多原因，程度也不同。如果你走運，你可能僅損壞一兩個表（如掉電），如果你倒黴，你可能必須替換整個資料目錄（如磁碟損壞）。在某些情況下也需要恢複，比如使用者錯誤地刪除了資料庫或表。不管這些倒黴事件的原因，你將需要實施某種恢複。

如果表損壞但沒丟失，嘗試用myisamchk或isamchk修複它們，如果這樣的損壞可有修複程式修複。

1、 恢複整個資料庫

Mysqldump參數如下：

#mysqldump -u 使用者名稱-p'密碼' 資料庫名 < 作業系統下檔案名稱

舉例：

先在Mysql裡建立另一個資料庫

mysql> create database test;

然後將備份的資料匯入

#mysqldump -u root -p'123456' test2 < samp.db.txt