使用MYSQL服務的一些經驗
主要從以下幾個方面考慮的MYSQL服務規劃設計
MYSQL服務的安裝/配置的通用性
系統的升級和數據遷移方便性
備份和系統快速恢復
MYSQL服務器的規劃 =================
為了以後維護
升級備份的方便和數據的安全性
最好將MYSQL程序文件和數據分別安裝在
不同的硬件
上
/
/usr <== 操作系統 }==> 硬盤
/home/mysql <== mysql應用程序
/data/app_
/ <== 應用數據和腳本 }==> 硬盤
/data/app_
/
/data/app_
/
mysql服務的安裝和服務的啟動
MYSQL一般使用當前STABLE的版本
盡量不使用
with
charset=選項
我感覺with
charset只在按字母排序的時候才有用
這些選項會對數據的遷移帶來很多麻煩
configure
prefix=/home/mysql
make
make install
服務的啟動和停止 ================
復制缺省的mysql/var/mysql到 /data/app_
/目錄下
MYSQLD的啟動腳本
start_mysql
sh
#!/bin/sh
rundir=`dirname
$
`
echo
$rundir
/home/mysql/bin/safe_mysqld
user=mysql
pid
file=
$rundir
/mysql
pid
datadir=
$rundir
/var
$@
O max_connections=
O wait_timeout=
O key_buffer=
M
port=
socket=
$rundir
/mysql
sock &
注釋
pid
file=
$rundir
/mysql
pid
socket=
$rundir
/mysql
sock
datadir=
$rundir
/var
目的都是將相應數據和應用臨時文件放在一起
O 後面一般是服務器啟動全局變量優化參數
有時候需要根據具體應用調整
port: 不同的應用使用PORT參數分布到不同的服務上去
一個服務可以提供的連接數一般是MYSQL服務的主要瓶頸
修改不同的服務到不同的端口後
在rc
local文件中加入
/data/app_
/start_mysql
sh
/data/app_
/start_mysql
sh
/data/app_
/start_mysql
sh
注意
必須寫全路徑
MYSQLD的停止腳本
stop_mysql
sh
#!/bin/sh
rundir=`dirname
$
`
echo
$rundir
/home/mysql/bin/mysqladmin
u mysql
S
$rundir
/mysql
sock shutdown
使用這個腳本的好處在於
多個服務啟動
只需要修改腳本中的
port=參數
單個目錄下的數據和服務腳本都是可以獨立打包的
所有服務相應文件都位於/data/app_
/目錄下
比如
mysql
pid mysql
sock
當一台服務器上啟動多個服務時
多個服務不會互相影響
但都放到缺省的/tmp/下則有可能被其他應用誤刪
當硬盤
出問題以後
直接將硬盤
放到一台裝好MYSQL的服務器上就可以立刻恢復服務（如果放到f裡則還需要備份相應的配置文件）
服務啟動後/data/app_
/下相應的文件和目錄分布如下
/data/app_
/
start_mysql
sh 服務啟動腳本
stop_mysql
sh 服務停止腳本
mysql
pid 服務的進程ID
mysql
sock 服務的SOCK
var/ 數據區
mysql/ 用戶庫
app_
_db_
/ 應用庫
app_
_db_
/
/data/app_
/
查看所有的應用進程ID
cat /data/*/mysql
pid
查看所有數據庫的錯誤日志
cat /data/*/var/*
err
個人建議
MYSQL的主要瓶頸在PORT的連接數上
因此
將表結構優化好以後
相應單個MYSQL服務的CPU占用仍然在
％以上
就要考慮將服務拆分到多個PORT上運行了
服務的備份 ==========
盡量使用MYSQL DUMP而不是直接備份數據文件
以下是一個按weekday將數據輪循備份的腳本
備份的間隔和周期可以根據備份的需求確定
/home/mysql/bin/mysqldump
S/data/app_
/mysql
sock
umysql db_name | gzip
f>/path/to/backup/db_name
`data +%w`
dump
gz
因此寫在CRONTAB中一般是
*
* * * /home/mysql/bin/mysqldump
S/data/app_
/mysql
sock
umysql db_name | gzip
f>/path/to/backup/db_name
`data +\%w`
dump
gz
注意
在crontab中
%
需要轉義成
\%
根據日志統計
應用負載最低的時候一般是在早上
點
先備份在本地然後傳到遠程的備份服務器上
或者直接建立一個數據庫備份帳號
直接在遠程的服務器上備份
遠程備份只需要將以上腳本中的
S /path/to/msyql
sock改成
h IP
ADDRESS即可
數據的恢復和系統的升級 ======================
日常維護和數據遷移
在數據盤沒有被破壞的情況下
硬盤一般是系統中壽命最低的硬件
而系統（包括操作系統和MYSQL應用）的升級和硬件升級
都會遇到數據遷移的問題
只要數據不變
先裝好服務器
然後直接將數據盤（硬盤
）安裝上
只需要將啟動腳本重新加入到rc
local文件中
系統就算是很好的恢復了
災難恢復
數據本身被破壞的情況下
確定破壞的時間點
然後從備份數據中恢復
應用的設計要點 ==============
非用數據庫不可嗎？
數據庫的確可以簡化很多應用的結構設計
但本身也是一個系統資源消耗比較大的應用
所以很多應用如果沒有很高的實時統計需求的話
完全可以先記錄到文件日志中
定期的導入到數據庫中做後續統計分析
如果還是需要記錄
維表結構
結構足夠簡單的話可以使用DBM結構
即使需要使用數據庫的
應用如果沒有太復雜的數據完整性需求的化
完全可以不使用那些支持外鍵的商業數據庫
數據庫服務的主要瓶頸
單個服務的連接數
對於一個應用來說
如果數據庫表結構的設計能夠按照數據庫原理的范式來設計的話
並且已經使用了最新版本的MYSQL
並且按照比較優化的方式運行了
那麼最後的主要瓶頸一般在於單個服務的連接數
即使一個數據庫可以支持並發
個連接
最好也不要把應用用到這個地步
因為並發連接數過多數據庫服務本身用於調度的線程的開銷也會非常大了
所以如果應用允許的話
讓一台機器多跑幾個MYSQL服務分擔
將服務均衡的規劃到多個MYSQL服務端口上
比如app_
==>
app_
==>
app_
==>
一個
G內存的機器跑上
個MYSQL是很正常的
讓
個MYSQLD承擔
個並發連接效率要比讓
個MYSQLD承擔
個效率高的多
當然
這樣也會帶來一些應用編程上的復雜度
使用單獨的數據庫服務器（不要和前台WEB服務搶內存）
MYSQL擁有更多的內存就可能能有效的進行結果集的緩存
應用盡量使用PCONNECT和polling機制
用於節省MYSQL服務建立連接的開銷
表的橫向拆分
讓最常被訪問的
%的數據放在一個小表裡
%的歷史數據放在一個歸檔表裡
數據中間通過定期
搬家
和定期刪除無效數據來節省
這樣對於應用來說總是在
%數據中進行選擇
比較有利於數據的緩存
不要指望MYSQL中對單表記錄數在
萬級以上還有比較高的效率
表的縱向拆分（過渡范化）
將所有的定長字段（char
int等）放在一個表裡
所有的變長字段（varchar
text
blob等）放在另外一個表裡
個表之間通過主鍵關聯
這樣
定長字段表可以得到很大的優化（甚至可以使用HEAP表類型
數據完全在內存中存取）
這裡也說明另外一個原則
對於我們來說
盡量使用定長字段可以通過空間的損失換取訪問效率的提高
MYSQL之所以支持多種表類型
實際上是針對不同應用提供了不同的優化方式
仔細的檢查應用的索引設計
甚至在服務啟動中加入
log
slow
queries[=file]用於跟蹤分析應用瓶頸
From:http://tw.wingwit.com/Article/program/MySQL/201311/29443.html
