mysql資料庫維護和效能提高

標籤：

為了有效防止資料丟失,並將損失降到最低,應對資料庫伺服器做維護。資料庫維護,包括資料備份,還原,匯出和匯入操作。

1. MySQLDatabase Backup

所謂資料庫維護,主要包含備份資料,還原資料和資料庫遷移,對於MySQL來說,還包括資料庫物件表的匯出和匯入。

1.1 通過複製資料檔案實現資料備份

造成資料損失的原因很多,主要包含如下幾個方面:

a. 儲存介質故障:儲存資料庫檔案的磁碟裝置損壞,使用者沒有Database Backup導致資料徹底丟失。

b. 使用者的錯誤操作:如誤刪了某些重要資料,甚至整個資料庫。

c. 伺服器的徹底癱瘓,系統需要重建。

由於MySQL伺服器中的資料檔案是基於磁碟的文字檔,所以最簡單,最直接的備份操作就是資料庫檔案直接複製出來。該種方式對InnoDB儲存引擎的表不合適,只適合儲存引擎為MyISAM的表。

1.2 通過命令mysqldump實現資料備份

有三種形式:備份一個資料庫,備份多個資料庫,備份所有資料庫。

1.2.1 備份一個資料庫

mysqldump -u username -p dbnametable1 table2...tablen > backupname.sql

樣本如下：

備份資料庫company下的表t_dept;

mysqldump -u root -p company t_dept > d:\t_dept_back.sql

1.2.2 備份多個資料庫

mysqldump -u username -p --databases dbname1 dbname2 ... dbname3 > backupname.sql

樣本如下：

備份資料庫company資料庫和companynew資料庫:

mysqldump -u root -p --databases company companynew> d:\database_company_back.sql

1.2.3 備份所有資料庫

mysqldump -u username -p --all  > backupname.sql

樣本如下：

備份所有資料庫:

mysqldump -u root -p --all  > c:\all_database_back.sql

2. MySQL資料還原

2.1 通過複製資料檔案實現資料還原

在通過複製資料檔案這種方式實現資料還原時,必須保證兩個MySQL資料庫的主要版本號一致,因為只有MySQL資料庫主要版本號一致時，才能保證兩個MySQL資料庫的檔案類型是相同的。由於通過複製資料檔案實現資料備份時,對儲存引擎類型為I:nnoDB的表不可用,僅對儲存引擎為MyISAM類型的表有效。因此通過複製資料檔案實現資料還原時,也只對儲存類型為MyISAM類型的表有效。

注意:MySQL資料庫伺服器的版本號碼,第一個數字表示主要版本號。

2.2 通過命令mysql實現資料還原

mysqldump -u -username -p [dbname] < backname.sql

樣本如下:

還原資料庫company中的表t_dept:

mysqldump -u -root -p t_dept < t_dept_back.sql

3.MySQL資料庫表匯出到文字檔

3.1 執行SELECT ... INTO OUTFILE 實現匯出到文字檔

SELECT [file_name] FROM table_name [where condition] INTO OUTFILE ‘file_name‘ [OPTION]

樣本如下：

實現將表t_dept裡的所有資料匯出到檔案t_dept裡:

SELECT * FROM t_dept INTO OUTFILE ‘c:/t_dept_1.txt‘  FIELDS TERMINATED BY ‘\.‘ OPTIONALLY ENCLOSED BY ‘\"‘ LINES STARTING BY ‘\>‘  TERMINATED BY ‘\r\n‘;

上述實現了將表t_dept裡的所有資料匯出到檔案t_dept_1.txt檔案裡,而且還設定了相應的顯示格式。

3.2 執行命令mysqldump實現匯出到文字檔

mysqldump -u root -pPassword -T file_directory dbname table_name[option];

樣本如下:

將相應的資料匯出到文字檔t_dept.txt裡:

mysqldump -u root -proot -T c:\company t_dept;

上述實現將表t_dept裡的所有資料匯出到檔案t_dept裡。

3.3 執行命令mysql實現匯出到文字檔

mysql -u root -pPassword -e"SELECT [file_name] FROM table_name " dbname > file_name;

樣本如下:

mysql -u root -proot -e"SELECT * FROM t_dept"  company> c:/t_dept.txt;

4. MySQL實現文字檔匯入到資料庫表

4.1 執行"LOAD DATA INFILE"命令實現文字檔匯入到資料庫表

LOAD DATA[LOCAL] INFILE file_name INTO TABLE table_name [OPTION];

樣本如下：

LOAD DATA INFILE ‘c:/t_dept.txt‘ INTO TABLE t_dept FIELDS TERMINATED BY ‘\.‘ OPTIONALLY ENCLOSED BY ‘\"‘ TERMINATED BY ‘\r\n‘;

4.1 執行命令mysqlimport實現匯入文字檔

mysqlimport -u root -pPassword[--LOCAL] dbname file_name[OPTION]

樣本如下:

mysqlimport -u root -proot company c:t_dept.txt FIELDS TERMINATED BY ‘\.‘ OPTIONALLY ENCLOSED BY ‘\"‘ LINES STARTING BY ‘\>‘ TERMINATED BY ‘\r\n‘;

5. 資料庫遷移

5.1 相同版本的MySQL資料庫之間的遷移

該種資料庫遷移最容易實現。

5.2 不同版本的MySQL資料庫之間的遷移

兩種方式:低版本向高版本和高版本向低版本。

5.3 不同資料庫之間的遷移

由於不同資料庫之前的差異性，沒有普遍適用的辦法。

6. 資料效能最佳化

mysql資料庫維護和效能提高