《MYSQL數(shù)據(jù)庫MySQL服務維護筆記第1/2頁》要點：
本文介紹了MYSQL數(shù)據(jù)庫MySQL服務維護筆記第1/2頁，希望對您有用。如果有疑問，可以聯(lián)系我們。

內(nèi)容摘要：使用MySQL服務的一些經(jīng)驗,主要從以下幾個方面考慮的MySQL服務規(guī)劃設計.對于高負載站點來說PHP和MySQL運行在一起(或者說任何應用和數(shù)據(jù)庫運行在一起的規(guī)劃)都是性能最大的瓶頸,這樣的設計有如讓人一手畫圓一手畫方,這樣2個人的工作效率肯定不如讓一個人專門畫圓一個人專門畫方效率高,讓應用和數(shù)據(jù)庫都跑在一臺高性能服務器上說不定還不如跑在2臺普通服務器上快.


以下就是針對MySQL作為專門的數(shù)據(jù)庫服務器的優(yōu)化建議：

MySQL服務的安裝/配置的通用性；?
系統(tǒng)的升級和數(shù)據(jù)遷移方便性；?
備份和系統(tǒng)快速恢復；?
數(shù)據(jù)庫應用的設計要點；?
一次應用優(yōu)化實戰(zhàn)；?
MySQL服務器的規(guī)劃
=================
為了以后維護,升級備份的方便和數(shù)據(jù)的安全性,最好將MySQL程序文件和數(shù)據(jù)分別安裝在“不同的硬件”上.


?????????/???/?????????|????/usr?????????????????????<==?操作系統(tǒng)?????????????????|????/home/mysql??????????????<==?mysql主目錄,為了方便升級,這只是一個最新版本目錄的鏈接?硬盤1==>|????/home/mysql-3.23.54/?????<==?最新版本的mysql?/home/mysql鏈接到這里????????????/home/mysql-old/?????????<==?以前運行的舊版本的mysql?????????/???/data/app_1/?????????????<==?應用數(shù)據(jù)和啟動腳本等硬盤2==>|????/data/app_2/????????????/data/app_3/
MySQL服務的安裝和服務的啟動：
MySQL一般使用當前STABLE的版本：
盡量不使用--with-charset=選項,我感覺with-charset只在按字母排序的時候才有用,這些選項會對數(shù)據(jù)的遷移帶來很多麻煩.
盡量不使用innodb,innodb主要用于需要外鍵,事務等企業(yè)級支持,代價是速度比MYISAM有數(shù)量級的下降.
./configure?--prefix=/home/mysql?--without-innodb
make?
make?install

服務的啟動和停止
================
1?復制缺省的mysql/var/mysql到?/data/app_1/目錄下,
2?MySQLD的啟動腳本：start_mysql.sh
#!/bin/sh
rundir=`dirname?"$0"`
echo?"$rundir"
/home/mysql/bin/safe_mysqld?--user=mysql?--pid-file="$rundir"/mysql.pid?--datadir="$rundir"/var?"$@"
-O?max_connections=500?-O?wait_timeout=600?-O?key_buffer=32M?--port=3402?--socket="$rundir"/mysql.sock?&?
注釋：
--pid-file="$rundir"/mysql.pid?--socket="$rundir"/mysql.sock?--datadir="$rundir"/var?
目的都是將相應數(shù)據(jù)和應用臨時文件放在一起；
-O?后面一般是服務器啟動全局變量優(yōu)化參數(shù),有時候需要根據(jù)具體應用調(diào)整；
--port:?不同的應用使用PORT參數(shù)分布到不同的服務上去,一個服務可以提供的連接數(shù)一般是MySQL服務的主要瓶頸；?

修改不同的服務到不同的端口后,在rc.local文件中加入：
/data/app_1/start_mysql.sh
/data/app_2/start_mysql.sh
/data/app_3/start_mysql.sh
注意：必須寫全路徑

3?MySQLD的停止腳本：stop_mysql.sh
#!/bin/sh
rundir=`dirname?"$0"`
echo?"$rundir"
/home/mysql/bin/mysqladmin?-u?mysql?-S"$rundir"/mysql.sock?shutdown?

使用這個腳本的好處在于：
1?多個服務啟動：對于不同服務只需要修改腳本中的--port[=端口號]參數(shù).單個目錄下的數(shù)據(jù)和服務腳本都是可以獨立打包的.
2?所有服務相應文件都位于/data/app_1/目錄下：比如：mysql.pid?mysql.sock,當一臺服務器上啟動多個服務時,多個服務不會互相影響.但都放到缺省的/tmp/下則有可能被其他應用誤刪.
3?當硬盤1出問題以后,直接將硬盤2放到一臺裝好MySQL的服務器上就可以立刻恢復服務(如果放到my.cnf里則還需要備份相應的配置文件).?

服務啟動后/data/app_1/下相應的文件和目錄分布如下：
/data/app_1/
????start_mysql.sh?服務啟動腳本
????stop_mysql.sh?服務停止腳本
????mysql.pid?服務的進程ID
????mysql.sock?服務的SOCK
????var/?數(shù)據(jù)區(qū)
???????mysql/?用戶庫
???????app_1_db_1/?應用庫
???????app_1_db_2/
...
/data/app_2/
...?

查看所有的應用進程ID：
cat?/data/*/mysql.pid?

查看所有數(shù)據(jù)庫的錯誤日志：
cat?/data/*/var/*.err?

個人建議：MySQL的主要瓶頸在PORT的連接數(shù)上,因此,將表結構優(yōu)化好以后,相應單個MySQL服務的CPU占用仍然在10％以上,就要考慮將服務拆分到多個PORT上運行了.

服務的備份
==========
盡量使用MySQL?DUMP而不是直接備份數(shù)據(jù)文件,以下是一個按weekday將數(shù)據(jù)輪循備份的腳本：備份的間隔和周期可以根據(jù)備份的需求確定
/home/mysql/bin/mysqldump?-S/data/app_1/mysql.sock?-umysql?db_name?|?gzip?-f>/path/to/backup/db_name.`data?+%w`.dump.gz
因此寫在CRONTAB中一般是：
15?4?*?*?*?/home/mysql/bin/mysqldump?-S/data/app_1/mysql.sock?-umysql?db_name?|?gzip?-f>/path/to/backup/db_name.`data?+%w`.dump.gz
注意：
1?在crontab中'%'需要轉(zhuǎn)義成'%'
2?根據(jù)日志統(tǒng)計,應用負載最低的時候一般是在早上4-6點

先備份在本地然后傳到遠程的備份服務器上,或者直接建立一個數(shù)據(jù)庫備份帳號,直接在遠程的服務器上備份,遠程備份只需要將以上腳本中的-S?/path/to/msyql.sock改成-h?IP.ADDRESS即可.
_baidu_page_break_tag_
數(shù)據(jù)的恢復和系統(tǒng)的升級
======================
日常維護和數(shù)據(jù)遷移：在數(shù)據(jù)盤沒有被破壞的情況下
硬盤一般是系統(tǒng)中壽命最低的硬件.而系統(tǒng)(包括操作系統(tǒng)和MySQL應用)的升級和硬件升級,都會遇到數(shù)據(jù)遷移的問題.
只要數(shù)據(jù)不變,先裝好服務器,然后直接將數(shù)據(jù)盤(硬盤2)安裝上,只需要將啟動腳本重新加入到rc.local文件中,系統(tǒng)就算是很好的恢復了.

災難恢復：數(shù)據(jù)庫數(shù)據(jù)本身被破壞的情況下
確定破壞的時間點,然后從備份數(shù)據(jù)中恢復.

應用的設計要點
==============
如果MySQL應用占用的CPU超過10%就應該考慮優(yōu)化了.


如果這個服務可以被其他非數(shù)據(jù)庫應用代替(比如很多基于數(shù)據(jù)庫的計數(shù)器完全可以用WEB日志統(tǒng)計代替)最好將其禁用：
非用數(shù)據(jù)庫不可嗎?雖然數(shù)據(jù)庫的確可以簡化很多應用的結構設計,但本身也是一個系統(tǒng)資源消耗比較大的應用.在某些情況下文本,DBM比數(shù)據(jù)庫是更好的選擇,比如：很多應用如果沒有很高的實時統(tǒng)計需求的話,完全可以先記錄到文件日志中,定期的導入到數(shù)據(jù)庫中做后續(xù)統(tǒng)計分析.如果還是需要記錄簡單的2維鍵－值對應結構的話可以使用類似于DBM的HEAP類型表.因為HEAP表全部在內(nèi)存中存取,效率非常高,但服務器突然斷電時有可能出現(xiàn)數(shù)據(jù)丟失,所以非常適合存儲在線用戶信息,日志等臨時數(shù)據(jù).即使需要使用數(shù)據(jù)庫的,應用如果沒有太復雜的數(shù)據(jù)完整性需求的化,完全可以不使用那些支持外鍵的商業(yè)數(shù)據(jù)庫,比如MySQL.只有非常需要完整的商業(yè)邏輯和事務完整性的時候才需要Oracle這樣的大型數(shù)據(jù)庫.對于高負載應用來說完全可以把日志文件,DBM,MySQL等輕量級方式做前端數(shù)據(jù)采集格式,然后用Oracle?MSSQL?DB2?Sybase等做數(shù)據(jù)庫倉庫以完成復雜的數(shù)據(jù)庫挖掘分析工作.
有朋友和我說用標準的MyISAM表代替了InnoDB表以后,數(shù)據(jù)庫性能提高了20倍.


數(shù)據(jù)庫服務的主要瓶頸：單個服務的連接數(shù)
對于一個應用來說,如果數(shù)據(jù)庫表結構的設計能夠按照數(shù)據(jù)庫原理的范式來設計的話,并且已經(jīng)使用了最新版本的MySQL,并且按照比較優(yōu)化的方式運行了,那么最后的主要瓶頸一般在于單個服務的連接數(shù),即使一個數(shù)據(jù)庫可以支持并發(fā)500個連接,最好也不要把應用用到這個地步,因為并發(fā)連接數(shù)過多數(shù)據(jù)庫服務本身用于調(diào)度的線程的開銷也會非常大了.所以如果應用允許的話：讓一臺機器多跑幾個MySQL服務分擔.將服務均衡的規(guī)劃到多個MySQL服務端口上：比如app_1?==>?3301?app_2?==>?3302...app_9?==>?3309.一個1G內(nèi)存的機器跑上10個MySQL是很正常的.讓10個MySQLD承擔1000個并發(fā)連接效率要比讓2個MySQLD承擔1000個效率高的多.當然,這樣也會帶來一些應用編程上的復雜度；


使用單獨的數(shù)據(jù)庫服務器(不要讓數(shù)據(jù)庫和前臺WEB服務搶內(nèi)存),MySQL擁有更多的內(nèi)存就可能能有效的進行結果集的緩存；在前面的啟動腳本中有一個-O?key_buffer=32M參數(shù)就是用于將缺省的8M索引緩存增加到32M(當然對于)


應用盡量使用PCONNECT和polling機制,用于節(jié)省MySQL服務建立連接的開銷,但也會造成MySQL并發(fā)鏈接數(shù)過多(每個HTTPD都會對應一個MySQL線程)；


表的橫向拆分：讓最常被訪問的10%的數(shù)據(jù)放在一個小表里,90%的歷史數(shù)據(jù)放在一個歸檔表里(所謂：快慢表),數(shù)據(jù)中間通過定期“搬家”和定期刪除無效數(shù)據(jù)來節(jié)省,畢竟大部分應用(比如論壇)訪問2個月前數(shù)據(jù)的幾率會非常少,而且價值也不是很高.這樣對于應用來說總是在一個比較小的結果級中進行數(shù)據(jù)選擇,比較有利于數(shù)據(jù)的緩存,不要指望MySQL中對單表記錄條數(shù)在10萬級以上還有比較高的效率.而且有時候數(shù)據(jù)沒有必要做那么精確,比如一個快表中查到了某個人發(fā)表的文章有60條結果,快表和慢表的比例是1:20,那么就可以簡單的估計這個人一共發(fā)表了1200篇.Google的搜索結果數(shù)也是一樣：對于很多上十萬的結果數(shù),后面很多的數(shù)字都是通過一定的算法估計出來的.


數(shù)據(jù)庫字段設計：表的縱向拆分(過渡范化)：將所有的定長字段(char,?int等)放在一個表里,所有的變長字段(varchar,text,blob等)放在另外一個表里,2個表之間通過主鍵關聯(lián),這樣,定長字段表可以得到很大的優(yōu)化(這樣可以使用HEAP表類型,數(shù)據(jù)完全在內(nèi)存中存取),這里也說明另外一個原則,對于我們來說,盡量使用定長字段可以通過空間的損失換取訪問效率的提高.在MySQL4中也出現(xiàn)了支持外鍵和事務的InnoDB類型表,標準的MyISAM格式表和基于HASH結構的HEAP內(nèi)存表,MySQL之所以支持多種表類型,實際上是針對不同應用提供了不同的優(yōu)化方式；


仔細的檢查應用的索引設計：可以在服務啟動參數(shù)中加入?--log-slow-queries[=file]用于跟蹤分析應用瓶頸,對于跟蹤服務瓶頸最簡單的方法就是用MySQL的status查看MySQL服務的運行統(tǒng)計和show?processlist來查看當前服務中正在運行的SQL,如果某個SQL經(jīng)常出現(xiàn)在PROCESS?LIST中,一.有可能被查詢的此時非常多,二,里面有影響查詢的字段沒有索引,三,返回的結果數(shù)過多數(shù)據(jù)庫正在排序(SORTING)；所以做一個腳本：比如每2秒運行以下show?processlist;把結果輸出到文件中,看到底是什么查詢在吃CPU.


全文檢索：如果相應字段沒有做全文索引的話,全文檢索將是一個非常消耗CPU的功能,因為全文檢索是用不上一般數(shù)據(jù)庫的索引的,所以要進行相應字段記錄遍歷.關于全文索引可以參考一下基于Java的全文索引引擎lucene的介紹.


前臺應用的記錄緩存：比如一個經(jīng)常使用數(shù)據(jù)庫認證,如果需要有更新用戶最后登陸時間的操作,最好記錄更新后就把用戶放到一個緩存中(設置2個小時后過期),這樣如果用戶在2個小時內(nèi)再次使用到登陸,就直接從緩存里認證,避免了過于頻繁的數(shù)據(jù)庫操作.


查詢優(yōu)先的表應該盡可能為where和order?by字句中的字段加上索引,數(shù)據(jù)庫更新插入優(yōu)先的應用索引越少越好.

總之：對于任何數(shù)據(jù)庫單表記錄超過100萬條優(yōu)化都是比較困難的,關鍵是要把應用能夠轉(zhuǎn)化成數(shù)據(jù)庫比較擅長的數(shù)據(jù)上限內(nèi).也就是把復雜需求簡化成比較成熟的解決方案內(nèi).

一次優(yōu)化實戰(zhàn)
============
以下例子是對一個論壇應用進行的優(yōu)化：


用Webalizer代替了原來的通過數(shù)據(jù)庫的統(tǒng)計.

首先通過TOP命令查看MySQL服務的CPU占用左右80%和內(nèi)存占用：10M,說明數(shù)據(jù)庫的索引緩存已經(jīng)用完了,修改啟動參數(shù),增加了-O?key_buffer=32M,過一段時間等數(shù)據(jù)庫穩(wěn)定后看的內(nèi)存占用是否達到上限.最后將緩存一直增加到64M,數(shù)據(jù)庫緩存才基本能充分使用.對于一個數(shù)據(jù)庫應用來說,把內(nèi)存給數(shù)據(jù)庫比給WEB服務實用的多,因為MySQL查詢速度的提高能加快web應用從而節(jié)省并發(fā)的WEB服務所占用的內(nèi)存資源.

用show?processlist;統(tǒng)計經(jīng)常出現(xiàn)的SQL：

每分鐘運行一次show?processlist并記錄日志：
*?*?*?*?*?(/home/mysql/bin/mysql?-uuser?-ppassword?<?/home/chedong/show_processlist.sql?>>??/home/chedong/mysql_processlist.log)

show_processlist.sql里就一句：
show?processlist;

比如可以從日志中將包含where的字句過濾出來：
grep?where?mysql_processlist.log
如果發(fā)現(xiàn)有死鎖,一定要重新審視一下數(shù)據(jù)庫設計了,對于一般情況：查詢速度很慢,就將SQL?where字句中沒有索引的字段加上索引,如果是排序慢就將order?by字句中沒有索引的字段加上.對于有%like%的查詢,考慮以后禁用和使用全文索引加速.


還是根據(jù)show?processlist;看經(jīng)常有那些數(shù)據(jù)庫被頻繁使用,考慮將數(shù)據(jù)庫拆分到其他服務端口上.?
MSSQL到MySQL的數(shù)據(jù)遷移：ACCESS＋MySQL?ODBC?Driver

在以前的幾次數(shù)據(jù)遷移實踐過程中,我發(fā)現(xiàn)最簡便的數(shù)據(jù)遷移過程并不是通過專業(yè)的數(shù)據(jù)庫遷移工具,也不是MSSQL自身的DTS進行數(shù)據(jù)遷移(遷移過程中間會有很多表出錯誤警告),但通過將MSSQL數(shù)據(jù)庫通過ACCESS獲取外部數(shù)據(jù)導入到數(shù)據(jù)庫中,然后用ACCESS的表==>右鍵==>導出,制定ODBC,通過MySQL的DSN將數(shù)據(jù)導出.這樣遷移大部分數(shù)據(jù)都會非常順利,如果導出的表有索引問題,還會出添加索引提示(DTS就不行),然后剩余的工作就是在MySQL中設計字段對應的SQL腳本了.

參考文檔：

MySQL的參考：
http://www.mysql.com/documentation/mysql/bychapter/

轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/1579.html