上一篇中提到 安裝LUA及MySQL Proxy後，接下來就是RW splitting(讀寫分流)的部份了

整體的概念圖跟上一篇MySQL Proxy安裝方式相同，丫忠再補上一個對應port的圖表：

針對上圖的架構，首先需要了解幾點基本的觀念：

• 所有寫入(ADD、UPDATE、DELETE)的動作都是針對MySQL Master
• 所有讀取(SELECT)的動作都是大部分都是在MySQL Slave(部份在MySQL Master，因為涉及到是否同步完成的關係，請見下面 MySQL Proxy如何解決同步延遲問題 的說明)
• MySQL Master與MySQL Slave已經建立起 同步(Replication)的機制
• MySQL Proxy也一個MySQL 的客戶端(Client)，也是一個MySQL的伺服器端(Server)

如果你對於上面的觀念都已經清楚後，對於上圖的流程我想應該很容易了解。丫忠就流程架構做個簡單說明：

1. 以往 所有應用程式(PHP)存取MySQL都是直接對應至MySQL Master，有了 MySQL Proxy當代理後，現在所有應用程式的連線都是連接至MySQL Proxy
2. 當MySQL Proxy接收到應用程式的SQL語法後，會自動判斷SQL語法是讀取還是寫入，再分別將寫入的SQL語法對應至MySQL Master、將讀取的SQL語法對應至MySQL Slave
3. 寫入MySQL Master的資料會透過Replication機制(binlog)同步至所有的MySQL Slave

看到這裡好像一切都很順利也沒有任何問題，如果你有意識到【當應用程式透過MySQL Proxy寫入資料到MySQL Master後， 此時應用程式又立即讀取剛寫入的資料；但是，MySQL Master還沒有同步至MySQL Slave，那麼應用程式讀取的資料不就是舊的資料嗎？】沒錯，當你有這樣的疑問時，表示你已經深入了解這個架構了，底下先針對MySQl Proxy如何解決同步延遲問題做說明

MySQL Proxy如何解決同步延遲問題

解決方式是在MySQL Master上新增一個自增表(count table)，當MySQL Master接收到更新資料的動作時便會觸發這個觸發器，這個觸發器會更新自增表(count table)中的記錄，如下圖所示：

(圖片來源：http://hi.baidu.com)

因為自增表(count table)也會一起同步(replication)至MySQL Slave，當應用程式透過MySQL Proxy讀取資料時，MySQL Proxy會先向MySQL Master和MySQL Slave的自增表(count table)發送查詢請求，當MySQL Master和MySQL Slave的資料相同時，MySQL Proxy就可以認定MySQL Master和MySQL Slave的資料是一致的，接著把應用程式的請求對應至MySQL Slave主機上，否則就將請求發送至MySQL Master上，如下圖所示：

(圖片來源：http://hi.baidu.com)

透過自增表(count table)的方式就可以解決同步延遲的問題，ok~了解整個架構及克服同步延遲的問題後，再來就是實做的部份啦，底下將說明如何啟動MySQL Proxy及設定相關參數

本章應用一個範例架構來說明RW splitting的應用，架構中架設了一台MySQL Porxy、一台MySQL Master以及二台MySQL Slave，詳細訊息如下：

MySQL Proxy：192.168.10.250
MySQL Master：192.168.10.2
MySQL Slave1：192.168.10.3
MySQL Slave2：192.168.10.4

MySQL Proxy開啟後，預設會有2個port(4040及4041)，port 4040為接受應用程式的port，port 4041為MySQL Proxy本身的管理port，詳細help的說明可以執行【/usr/local/mysql-proxy/bin/mysql-proxy –help-all】查看

如何啟動MySQL Proxy

$ /usr/local/mysql-proxy/bin/mysql-proxy \
> –daemon \
> –keepalive \
> –admin-username=root \
> –admin-password=123456 \
> –proxy-backend-addresses=192.168.10.2:3306 \
> –proxy-read-only-backend=192.168.10.3:3306 \
> –proxy-read-only-backend=192.168.10.4:3306 \
> –log-file=/var/log/mysql-proxy.log \
> –log-level=debug \
> –max-open-files=1024 \
> –proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua

要啟動MySQL Proxy就加了那麼多參數，代表的又是什麼意思呢？

• –daemon：指定mysql-proxy為一個daemon(@@)
• –keepalive：丫忠在測試時會有mysql-proxy自動停止服務的情況，加上此參數後就解決這個問題
• –admin-username：指定MySQL Proxy管理者端(port:4041)的登入帳號
• –admin-password：指定MySQL Proxy管理者端(port:4041)的登入密碼
• –proxy-backend-addresses：指定MySQL Master主機的位置及埠號(port)
• –proxy-read-only-backend：指定MySQL Slave主機的位置及埠號(port)，有多台Slave時， 一個 –proxy–read-only-backend 表示一台Slave
• –log-file：指定儲存MySQL Proxy log的檔案位置
• –log-level：指定要記錄log的等級
• –max-open-files：指定最大檔案開啟數為1024，否則為有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log訊息出現
• –proxy-lua-script：指定MySQL Proxy要套用那一個script

上面的啟動方式，你可以用手動方式輸入啟動；另外，官網也提供了一個init script的shell下載，詳細下載請至 MySQL Proxy init script下載

再來，啟動MySQL Proxy的參數中有一個 –proxy-lua-script用來指定lua的檔案位置，這個 rw-splitting.lua檔案是 MySQL Proxy重點中的重點，至於要怎麼產生此 rw-splitting.lua呢？rw-splitting.lua檔案在MySQL Proxy原始安裝檔中就已經包含在裡面了，詳細rw-splitting.lua檔案位置在【mysql-proxy-0.8.0/lib/rw-splitting.lua】，請將此檔案拷貝至–proxy-lua-script找的到的位置

rw-splitting.lua目前尚未有一個穩定的版本，至目前0.8.0為止還在持續修正中，詳細的 Bug或修正內容，有興趣的可以至作者的網站查看( rw-splitting.lua作者)，另外也有一個 bug修正的網址 以及針對 MySQL Proxy Lua Script 的主題

完成MySQL Proxy的啟動後，再來是測試是否MySQL Proxy可以真的達到讀寫分流的功能了，在測試前丫忠建議將 rw-splitting.lua的Debug功能打開，這樣才能清楚了解到 MySQL Proxy的流程及讀寫流程，請直接修改 rw-splitting.lua，將 is_debug = false 改成 is_debug = true，debug的log 會在應用程式執行時顯示出來，詳細修改後，如下圖所示：

MySQL Proxy測試寫入動作是否同步

這裡可以寫一個簡單的PHP程式，針對MySQL Proxy做寫入的動作，再至MySQL Master、MySQL Slave1及MySQL Slave2是否有同步更新即可驗證

$link = mysql_connect(‘192.168.10.250:4040′, ‘root’, ‘root_password’);
mysql_select_db(‘mytest’);
$sql=’UPDATE t1 SET t_name=’12345′ WHERE id=1;
$result = mysql_query( $sql);

MySQL Proxy測試讀取動作

丫忠剛開始的測試方式為直接在command下指令 做SELECT的動作；但是，這樣的方式是測試不出來的，錯誤的測試方式如下：

$ mysql -u root -p -P 4040 -h 192.168.10.250
mysql>SELECT * FROM t1;

丫忠再次強調，這樣的方式無法明顯測試出是否有從MySQL Slave讀取資料；而且，debug的記錄不會顯示出來

丫忠建議寫個簡單的PHP程式，跑個小迴圈，在debug log中就會顯示出 SELECT的資料是從那一台機器提供

$link = mysql_connect(‘192.168.10.250:4040′, ‘root’, ‘root_password’);
mysql_select_db(‘mytest’);
for($i=0;$i<20;$i++){
$sql=’SELECT * FROM t1′;
$result = mysql_query( $sql);
}

詳細 debug log的訊息就要請網友查看 rw-splitting debug訊息

另外，rw-splitting.lua 跟讀取較直接相關的參數有2個(min_idle_connections 及 max_idle_connections)，可以直接修改這2個參數的值，看看有什麼變化，此部份及詳細分流的部份就要查看 rw-splittinglua 這隻lua程式了

MySQL Proxy 讀取出來的資料是亂碼

丫忠在測試過程式有遇到此問題，最後是修改 /etc/my.cnf 設定檔中，加入底下設定，即可解決亂碼問題(當然您要依據您的環境做修改)

default-character-set = utf8
skip-character-set-client-handshake
character-set-server = utf8
collation-server = utf8_general_ci
init-connect = SET NAMES utf8

丫忠在上一篇文章中描述到 MySQL Replication 的架構，接著此篇將延伸那個架構再加入 MySQL Proxy來當mysql的代理，那麼要代理那些工作呢？主要是將讀寫分開，讓MySQL Proxy自動判斷來源的指令中是讀取(SELECT)還是寫入(ADD、UPDATE、DELETE)，然後再將寫入的動作移至Master主機，讀取的動作平均移至多台的Slave主機。

當然，如果你的程式中已經將讀取跟寫入的動作已經自動分配至各個不同的mysql，那麼你就可以忽略本篇文章。

MySQL Proxy整體架構的概念圖如下：

(圖片來源：http://kaj.arno.fi)

讓我們先來看一下 MySQL Proxy官網 對於MySQL Proxy的功能及特性的說法如下：

MySQL Proxy is a simple program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for unlimited uses; common ones include:

• load balancing
• failover (故障恢復)
• query analysis
• query filtering and modification
• RW Splitting (http://forge.mysql.com/wiki/MySQL_Proxy_RW_Splitting)
• … and many more

要使用MySQL Proxy除了先了解MySQL Proxy的功能外，再來就是MySQL Proxy的安裝囉

在安裝MySQL Proxy前，必須先安裝好 readline >= 5.1 ，詳細readline 的安裝方式丫忠就不再說明，尚未安裝的網友可以前往 ftp://ftp.gnu.org/gnu/readline/ 下載安裝；如果您都是用rpm安裝的話，可以用底下指令檢查看看 readline是否已經安裝：

$rpm -qa | grep readline

如果已經確定系統有readline 後，再來就是安裝LUA套件了，安裝到這邊網友可能會想說：甚麼是LUA？LUA能為MySQL Proxy做什麼事呢？

根據wiki的說法【Lua程式語言 是一個簡潔、輕量、可擴展的腳本語言】詳細請見 wiki-lua。是的，您沒有看錯，Lua是一種程式語言，看到這裡您是否有意識到當程式人員真的很歹命，什麼程式都要學，寫網站程式用PHP、Javascript，還會延伸出不同的框架(framework)像是JQuery…等，寫MySQL資料庫又有一些MySQL語法，接觸到Linux系統後，又要學一點都不人性的Shell script，現在架個MySQL Proxy又多了一個LUA程式語言，看到這裡丫忠真要給自己還有這些RD人員們拍拍手鼓勵一下了，加油加油!complain 發洩一下心情後，該做的還是要做 讓我們來看一下Lua的安裝方式吧!

LUA安裝方式

版本：5.1.4
官網下載點：http://www.lua.org/ftp/

$wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
$tar xzvf lua-5.1.4.tar.gz
$cd lua-5.1.4
$vi Makefile  #修改安裝路徑，本範例安裝路徑為 INSTALL_TOP= /usr/local/lua
$make linux install

依據上述指令即可安裝LUA，之後在/usr/local/lua就會發現LUA相關的檔案。

安裝完LUA後，再來就是安裝MySQL Proxy套件囉，在安裝MySQL Proxy前，請先確定您的系統上已經有安裝MySQL(廢話@@)

MySQL Proxy安裝方式

版本：0.8.0
官網下載點：http://dev.mysql.com/downloads/mysql-proxy/

請先至官網下載 0.8.0 版本的MySQL Proxy，本範例使用 Source code的安裝方式

$ tar xzvf mysql-proxy-0.8.0.tar.gz
$ cd mysql-proxy-0.8.0
$  env LUA_CFLAGS=’-I/usr/local/lua/include’ \
>  LUA_LIBS=’-L/usr/local/lua/lib -llua -ldl’ \
>  CFLAGS=’-I/usr/local/include’ \
>  LDFLAGS=’-L/usr/local/lib -lm’ \
>  PKG_CONFIG_PATH=/usr/local/lib/pkgconfig \
>  ./configure \
>  –prefix=/usr/local/mysql-proxy \
>  –with-mysql=/usr/local/mysql \
>  –with-lua
$ make;make install

看到上面的安裝方式或許會有一點亂，丫忠就針對這些參數說明一下，當然下指令【./configure –help】就會有英文說明了

• LUA_CFLAGS：指定LUA安裝位置的include目錄，請參照上面LUA安裝方式的INSTALL_TOP設定
• LUA_LIBS：指定LUA安裝位置的lib目錄，請參照上面LUA安裝方式的INSTALL_TOP設定
• CFLAGS：用來指定MySQL Proxy會用到的其他套件的 include目錄
• LDFLAGS：用來指定MySQL Proxy會用到的其他套件的 lib目錄
• PKG_CONFIG：有一些版本訊息會放在pkgconfig目錄中，設定pkgconfig目錄位置，告訴MySQL Proxy去參照各套件的版本及位置
• –prefix：mysql proxy的安裝位置  @@
• –with-mysql：指定mysql的安裝位置

在安裝過程中可能會有一些錯誤訊息出現，丫忠遇到的問題是版本不合的問題，不過上述套件的版本是可以相容的 ^^y

前陣子丫忠一直在尋找MySQL負載平衡的方式，搞了一陣子後終於暫告一個段落，趁著記憶猶新時趕快做個筆記，給有需要的人參考，自己日後也可以參考啦!

網路上有很多人提供MySQL的負載方式，例如：Master Slave架構、MySQL MMM架構、DRBD、Cluster、HeartBeat…等一堆MySQL負載平衡的架構，一直MySQL負載平衡的架構，到底要選擇那一個方式才是最好的？

丫忠認為符合自己個人(公司)需求的架構最好，再來是穩定及簡單好維護；如果只是單純的公司形象網站或者是一般個人的Blog，總不能想著要架構Master + Slave，外加MySQL Proxy做讀寫分流，再替Master做個HeartBeat的備援，再後再加幾台memcached來加快搜尋速度….等，如果只是為了測試架構倒是可以玩一玩，如果是要用在運作中的主機的話，那丫忠勸您要3思 再3思了，不要搞到後來出問題時找不到問題，或者資料不一致時，到時候拜再多的神也沒用囉…

講了一堆哩哩扣扣都還沒進入主題，MySQL 的M/S架構(Master Slave)是負載平衡中最基礎的架構，也就是很多的架構都是從此延伸出去，這也是丫忠認為最簡單的架構之一了。如果可以閱讀英文的網友可以直接參考MySQL官網的資料 http://dev.mysql.com/doc/refman/5.0/en/replication.html

MySQL M/S架構的原理大致如下：
1.  新增(Add)、刪除(delete)、修改(update)的動作必須移至Master
2. Master會針對這些動作的語法自動產生binlog 檔案
3. Master會將binlog檔案同步(Replication)至所有的Slave
4. Slave會執行binlog檔案中的SQL語法

原理的圖形概念如下：

(圖形來源：http://baoz.net)

看到這個架構的原理，或者有些網友就想到了，程式中針對MySQL的動作(SELECT、UPDATE、DELETE、ADD…)都是固定在同一台MySQL中運作，除非是修改程式，否則要怎樣將讀寫分開呢？這個部份就要加入MySQL Proxy的概念了，MySQL Proxy可以自動將讀寫分開，不用修改程式碼，不過本章的重點不是在MySQL Proxy，有興趣的網友可以先參考 http://forge.mysql.com/wiki/MySQL_Proxy

了解MySQL M/S的運作原理之後，再來就是MySQL的設定了，假設要完成的架構如下：

• Master：192.168.10.2
• Slave：192.168.10.3
• 只有同步(Replication) 資料庫mytest

MySQL Replication Master設定

Master要做的流程大概如下：
1. 設定 一個同步的帳號(如果是用root，可以忽略此步驟)
2. 設定my.cnf
3. 將資料庫變成唯讀(read only)
4. 將mytest資料匯出(mysqldump或tar)
5.  解除資料唯讀狀態
6. 重新啟動mysql讓剛設定的my.cnf 生效
7. 將匯出的資料拷貝(scp)至slave

下面針對Master流程的部份，做個詳細說明：
1. 設定一個同步的帳號repl，密碼為slavepass，讓repl具有REPLICATION的權限，相關指令如下：

$mysql -u root -p    #進入mysql
mysql>CREATE USER ‘repl’@'%’ IDENTIFIED BY ’slavepass’;
mysql>GRANT REPLICATION SLAVE ON *.* TO ‘repl’@'%’;

2. Master主機必須產生binlog檔案，並且指定只有同步 mytest 這個資料庫，/etc/my.cnf 的修改如下：

[mysqld]
server-id = 1   #此id不可以重覆
log-bin = mysql-bin  #指定產生binlog檔案的開頭檔名
binlog-do-db = mytest   #只針對mytest產生binlog

3. 在Replication完成前，Master跟Slave的資料必須一致，故在匯出資料前先Lock成唯讀

mysql > FLUSH TABLES WITH READ LOCK;

4. 將mytest資料庫整個匯出，此方式可以使用mysqldump或者是tar的方式把檔案壓縮起來，底下範例使用mysqldump的方式匯mytest的資料

$mysqldump -u root -p mytest > mytest.sql       #將mytest的資料匯出成mytest.sql

5. 將資料匯出後，解除資料唯讀狀態

mysql>UNLOCK TABLES;

6. 重新啟動mysql，讓剛設定的my.cnf生效；重新啟動後，在Master主機上的add、update、delete等動作都會記錄在binlog檔案中

$/etc/init.d/mysql restart

7. 最後將mytest.sql拷貝(scp)至slave主機，如果這時候你問丫忠怎麼沒有scp的詳細指令，那丫忠可能會暈倒躺給你看@@….

MySQL Replication Slave設定

Slave要做的流程大概如下：
1. 修改my.cnf
2. 匯入mytest資料庫的資料
3. 設定Master主機的相關訊息
4. 重新啟動mysql

下面針對Slave流程的部份，做個詳細說明：
1.  設定my.cnf相關訊息

[mysqld]
server-id = 2  #此id不可以重覆
log-slave-updates   #告訴slave讀取binlog，啟動slave的重要選項之一
log-bin = mysql-bin   #指定產生binlog檔案的開頭檔名
binlog_format = mixed    #設定binlog的儲存格式，(maxed為預設值)
relay-log = host_name-relay-bin    #記錄著binlog處理的過程，可以執行【FLUSH LOGS】讓mysql自動刪除較舊的檔案
replicate-do-db = mytest    #限制slave只同步mytest資料庫的資料
master-connect-retry = 60   #當slave無法連線至master時，間隔60秒嘗試連線(預設為60秒)

2. 將mytest的資料匯入

$mysql -u root -p mytest < mytest.sql

3. 設定Master主機的相關訊息，讓Slave知道Master的位置

以往的方式是直接在my.cnf中設定master的相關訊息；但是，這樣的方式已經改了，必須使用在 mysql下執行【CHANGE MASTER TO】的指令

底下在my.cnf中設定master是錯誤的

master-host            = 192.168.10.2
master-user            =  repl
master-password = slavepass
master-port            = 3306

請在mysql下執行底下指令

mysql>CHANGE MASTER TO
MASTER_HOST=’192.168.10.2′,
MASTER_USER=’repl’,
MASTER_PASSWORD=’slavepass’;

4. 重新啟動mysql

$/etc/init.d/mysql restart

設定完MySQL的M/S架構後，再來就是檢查及測試的動作了

要如何確定mysql slave是否正常運作？

因為丫忠習慣使用phpMyAdmin工具，只要登入phpMyAdmin後，在【狀態】的分頁中往下拉，會看到一個Slave status的狀態表
如果底下2行都出現Yes的話表示slave是在執行中
Slave_IO_Running Yes
Slave_SQL_Running Yes

當然您也可以在mysql下執行【show slave status;】但是，顯示出來的格式有點亂，看不清楚是否正在執行

要如何關閉、啟動slave？

mysql>slave stop;     #關閉slave服務
mysql>slave start;    #啟動slave服務

測試資料是否有同步？

在master上面更新mytest資料庫裡面的資料後，再至slave查看是否有更新，就可以確認了

接下來是在此架構上再加上一個MySQL Proxy，來達到Read Write分離的架構了^^Y

丫忠最近一直在搞mysql的東東，不得不參考大量的設定檔及說明，剛又看到一個關於mysql設定檔相關的說明，再次的轉貼到此給自己及大家參考看看囉!

MySQL資料庫5.0的my.cnf配置選項概述:

mysqld程序–目錄和文件
basedir = path 使用給定目錄作為根目錄(安裝目錄)。
character-sets-dir = path 給出存放著字符集的目錄。
datadir = path 從給定目錄讀取資料庫文件。
pid-file = filename 為mysqld程序指定一個存放進程ID的文件(僅適用於UNIX/Linux系統)
Init-V腳本需要使用這個文件裡的進程ID結束mysqld進程。
socket = filename 為MySQL客戶程序與服務器之間的本地通信指定一個套接字文件
(僅適用於UNIX/Linux系統; 默認設置一般是/var/lib/mysql/mysql.sock文件)。
在Windows環境下，如果MySQL客戶與服務器是通過命名管道進行通信的，–sock選項給出的將是該命名管道的名字(默認設置是MySQL)。
lower_case_table_name = 1/0 新目錄和資料表的名字是否只允許使用小寫字母; 這個選項在Windows環境下的默認設置是1(只允許使用小寫字母)。

mysqld程序–語言設置
character-sets-server = name 新資料庫或資料表的默認字符集。為了與MySQL的早期版本保持兼容，這個字符集也可以用–default-character-set選項給出; 但這個選項已經顯得有點過時了。
collation-server = name 新資料庫或資料表的默認排序方式。
lanuage = name 用指定的語言顯示出錯信息。

mysqld程序–通信、網絡、信息安全
enable-named-pipes 允許Windows 2000/XP環境下的客戶和服務器使用命名管道(named pipe)進行通信。這個命名管道的默認名字是MySQL，但可以用–socket選項來改變。
local-infile [=0] 允許/禁止使用LOAD DATA LOCAL語句來處理本地文件。
myisam-recover [=opt1, opt2, ...] 在啟動時自動修復所有受損的MyISAM資料表。這個選項的可取值有4種EFAULT、BACKUP、QUICK和FORCE; 它們與myisamchk程序的同名選項作用相同。
old-passwords 使用MySQL 3.23和4.0版本中的老算法來加密mysql資料庫裡的密碼(默認使用MySQL 4.1版本開始引入的新加密算法)。
port = n 為MySQL程序指定一個TCP/IP通信端口(通常是3306端口)。
safe-user-create 只有在mysql.user資料庫表上擁有INSERT權限的用戶才能使用GRANT命令; 這是一種雙保險機制(此用戶還必須具備GRANT權限才能執行GRANT命令)。
shared-memory 允許使用記憶體(shared memory)進行通信(僅適用於Windows)。
shared-memory-base-name = name 給共享記憶體塊起一個名字(默認的名字是MySQL)。
skip-grant-tables 不使用mysql資料庫裡的信息來進行訪問控制(警告:這將允許用戶任何用戶去修改任何資料庫)。
skip-host-cache 不使用高速緩存區來存放主機名和IP地址的對應關係。
skip-name-resovle 不把IP地址解析為主機名; 與訪問控制(mysql.user資料表)有關的檢查全部通過IP地址行進。
skip-networking 只允許通過一個套接字文件(Unix/Linux系統)或通過命名管道(Windows系統)進行本地連接，不允許ICP/IP連接; 這提高了安全性，但阻斷了來自網絡的外部連接和所有的Java客戶程序(Java客戶即使在本地連接裡也使用TCP/IP)。
user = name mysqld程序在啟動後將在給定UNIX/Linux賬戶下執行; mysqld必須從root賬戶啟動才能在啟動後切換到另一個賬戶下執行; mysqld_safe腳本將默認使用–user=mysql選項來啟動mysqld程序。

mysqld程序–記憶體管理、優化、查詢緩存區
bulk_insert_buffer_size = n 為一次插入多條新記錄的INSERT命令分配的緩存區長度(默認設置是8M)。
key_buffer_size = n 用來存放索引區塊的RMA值(默認設置是8M)。
join_buffer_size = n 在參加JOIN操作的數據列沒有索引時為JOIN操作分配的緩存區長度(默認設置是128K)。
max_heap_table_size = n HEAP資料表的最大長度(默認設置是16M); 超過這個長度的HEAP資料表將被存入一個臨時文件而不是駐留在記憶體裡。
max_connections = n MySQL服務器同時處理的資料庫連接的最大數量(默認設置是100)。
query_cache_limit = n 允許臨時存放在查詢緩存區裡的查詢結果的最大長度(默認設置是1M)。
query_cache_size = n 查詢緩存區的最大長度(默認設置是0，不開闢查詢緩存區)。
query_cache_type = 0/1/2 查詢緩存區的工作模式:0, 禁用查詢緩存區; 1，啟用查詢緩存區(默認設置); 2，’按需分配’模式，只響應SELECT SQL_CACHE命令。
read_buffer_size = n 為從資料表順序讀取數據的讀操作保留的緩存區的長度(默認設置是128KB); 這個選項的設置值在必要時可以用SQL命令SET SESSION read_buffer_size = n命令加以改變。
read_rnd_buffer_size = n 類似於read_buffer_size選項，但針對的是按某種特定順序(比如使用了ORDER BY子句的查詢)輸出的查詢結果(默認設置是256K)。
sore_buffer = n 為排序操作分配的緩存區的長度(默認設置是2M); 如果這個緩存區太小，則必須創建一個臨時文件來進行排序。
table_cache = n 同時打開的資料表的數量(默認設置是64)。
tmp_table_size = n 臨時HEAP資料表的最大長度(默認設置是32M); 超過這個長度的臨時資料表將被轉換為MyISAM資料表並存入一個臨時文件。

mysqld程序–日誌
log [= file] 把所有的連接以及所有的SQL命令記入日誌(通用查詢日誌); 如果沒有給出file參數，MySQL將在資料庫目錄裡創建一個hostname.log文件作為這種日誌文件(hostname是服務器的主機名)。
log-slow-queries [= file] 把執行用時超過long_query_time變量值的查詢命令記入日誌(慢查詢日誌); 如果沒有給出file參數，MySQL將在資料庫目錄裡創建一個hostname-slow.log文件作為這種日誌文件(hostname是服務器主機 名)。
long_query_time = n 慢查詢的執行用時上限(默認設置是10s)。
long_queries_not_using_indexs 把慢查詢以及執行時沒有使用索引的查詢命令全都記入日誌(其餘同–log-slow-queries選項)。
log-bin [= filename] 把對數據進行修改的所有SQL命令(也就是INSERT、UPDATE和DELETE命令)以二進制格式記入日誌(二進制變更日誌，binary update log)。這種日誌的文件名是filename.n或默認的hostname.n，其中n是一個6位數字的整數(日誌文件按順序編號)。
log-bin-index = filename 二進制日誌功能的索引文件名。在默認情況下，這個索引文件與二進制日誌文件的名字相同，但後綴名是.index而不是.nnnnnn。
max_binlog_size = n 二進制日誌文件的最大長度(默認設置是1GB)。在前一個二進制日誌文件裡的信息量超過這個最大長度之前，MySQL服務器會自動提供一個新的二進制日誌文件接續上。
binlog-do-db = dbname 只把給定資料庫裡的變化情況記入二進制日誌文件，其他資料庫裡的變化情況不記載。如果需要記載多個資料庫裡的變化情況，就必須在配置文件使用多個本選項來設置，每個資料庫一行。
binlog-ignore-db = dbname 不把給定資料庫裡的變化情況記入二進制日誌文件。
sync_binlog = n 每經過n次日誌寫操作就把日誌文件寫入硬盤一次(對日誌信息進行一次同步)。n=1是最安全的做法，但效率最低。默認設置是n=0，意思是由操作系統來負責二進制日誌文件的同步工作。
log-update [= file] 記載出錯情況的日誌文件名(出錯日誌)。這種日誌功能無法禁用。如果沒有給出file參數，MySQL會使用hostname.err作為種日誌文件的名字。

mysqld程序–鏡像(主控鏡像服務器)
server-id = n 給服務器分配一個獨一無二的ID編號; n的取值範圍是1~2的32次方啟用二進制日誌功能。
log-bin = name 啟用二進制日誌功能。這種日誌的文件名是filename.n或默認的hostname.n，其中的n是一個6位數字的整數(日誌文件順序編號)。
binlog-do/ignore-db = dbname 只把給定資料庫裡的變化情況記入二進制日誌文件/不把給定的資料庫裡的變化記入二進制日誌文件。

mysqld程序–鏡像(從屬鏡像服務器)
server-id = n 給服務器分配一個唯一的ID編號
log-slave-updates 啟用從屬服務器上的日誌功能，使這台計算機可以用來構成一個鏡像鏈(A->B->C)。
master-host = hostname 主控服務器的主機名或IP地址。如果從屬服務器上存在mater.info文件(鏡像關係定義文件)，它將忽略此選項。
master-user = replicusername 從屬服務器用來連接主控服務器的用戶名。如果從屬服務器上存在mater.info文件，它將忽略此選項。
master-password = passwd 從屬服務器用來連接主控服務器的密碼。如果從屬服務器上存在mater.info文件，它將忽略此選項。
master-port = n 從屬服務器用來連接主控服務器的TCP/IP端口(默認設置是3306端口)。
master-connect-retry = n 如果與主控服務器的連接沒有成功，則等待n秒(s)後再進行管理方式(默認設置是60s)。如果從屬服務器存在mater.info文件，
它將忽略此選項。
master-ssl-xxx = xxx 對主、從服務器之間的SSL通信進行配置。
read-only = 0/1 0: 允許從屬服務器獨立地執行SQL命令(默認設置); 1: 從屬服務器只能執行來自主控服務器的SQL命令。
read-log-purge = 0/1 1: 把處理完的SQL命令立刻從中繼日誌文件裡刪除(默認設置); 0: 不把處理完的SQL命令立刻從中繼日誌文件裡刪除。
replicate-do-table = dbname.tablename 與–replicate-do-table選項的含義和用法相同，但資料庫和資料表名字裡允許出現通配符’%’

(例如: test%.%–對名字以’test’開頭的所有資料庫裡的所以資料庫表進行鏡像處理)。
replicate-do-db = name 只對這個資料庫進行鏡像處理。

replicate-ignore-table = dbname.tablename 不對這個資料表進行鏡像處理。
replicate-wild-ignore-table = dbn.tablen 不對這些資料表進行鏡像處理。
replicate-ignore-db = dbname 不對這個資料庫進行鏡像處理。
replicate-rewrite-db = db1name > db2name 把主控資料庫上的db1name資料庫鏡像處理為從屬服務器上的db2name資料庫。
report-host = hostname 從屬服務器的主機名; 這項信息只與SHOW SLAVE HOSTS命令有關–主控服務器可以用這條命令生成一份從屬服務器的名單。
slave-compressed-protocol = 1 主、從服務器使用壓縮格式進行通信–如果它們都支持這麼做的話。
slave-skip-errors = n1, n2, …或all 即使發生出錯代碼為n1、n2等的錯誤，鏡像處理工作也繼續進行(即不管發生什麼錯誤，鏡像處理工作也繼續進行)。
如果配置得當，從屬服務器不應該在執行SQL命令時發生錯誤(在主控服務器上執行出錯的SQL命令不會被發送到從屬服務器上做鏡像處理); 如果不使用
slave-skip-errors選項，從屬服務器上的鏡像工作就可能人為發生錯誤而中斷，中斷後需要有人工參與才能繼續進行。

mysqld–InnoDB–基本設置、表空間文件
skip-innodb 不加載InnoDB資料表驅動程序–如果用不著InnoDB資料表，可以用這個選項節省一些記憶體。
innodb-file-per-table 為每一個新資料表創建一個表空間文件而不是把資料表都集中保存在中央表空間裡(後者是默認設置)。該選項始見於MySQL 4.1。
innodb-open-file = n InnoDB資料表驅動程序最多可以同時打開的文件數(默認設置是300)。如果使用了innodb-file-per-table選項並且需要同時打開很多
資料表的話，這個數字很可能需要加大。
innodb_data_home_dir = p InnoDB主目錄，所有與InnoDB資料表有關的目錄或文件路徑都相對於這個路徑。在默認的情況下，這個主目錄就是MySQL的數據目錄。
innodb_data_file_path = ts 用來容納InnoDB為資料表的表空間: 可能涉及一個以上的文件; 每一個表空間文件的最大長度都必須以字節(B)、兆字節(MB)或
千兆字節(GB)為單位給出; 表空間文件的名字必須以分號隔開; 最後一個表空間文件還可以帶一個autoextend屬性和一個最大長度(max:n)。
例如，ibdata1:1G; ibdata2:1G:autoextend:max:2G的意思是: 表空間文件ibdata1的最大長度是1GB，ibdata2的最大長度也是1G，但允許它擴充到2GB。
除文件名外，還可以用硬盤分區的設置名來定義表空間，此時必須給表空間的最大初始長度值加上newraw關鍵字做後綴，給表空間的最大擴充長度值加上
raw關鍵字做後綴(例如/dev/hdb1:20Gnewraw或/dev/hdb1:20Graw); MySQL 4.0及更高版本的默認設置是ibdata1:10M:autoextend。
innodb_autoextend_increment = n 帶有autoextend屬性的表空間文件每次加大多少兆字節(默認設置是8MB)。這個屬性不涉及具體的資料表文件，那些文件的
增大速度相對是比較小的。
innodb_lock_wait_timeout = n 如果某個事務在等待n秒(s)後還沒有獲得所需要的資源，就使用ROLLBACK命令放棄這個事務。這項設置對於發現和處理未能被
InnoDB資料表驅動程序識別出來的死鎖條件有著重要的意義。這個選項的默認設置是50s。
innodb_fast_shutdown 0/1 是否以最快的速度關閉InnoDB，默認設置是1，意思是不把緩存在INSERT緩存區的數據寫入資料表，那些數據將在MySQL服務器下次
啟動時再寫入(這麼做沒有什麼風險，因為INSERT緩存區是表空間的一個組成部分，數據不會丟失)。把這個選項設置為0反面危險，因為在計算機關閉時，
InnoDB驅動程序很可能沒有足夠的時間完成它的數據同步工作，操作系統也許會在它完成數據同步工作之前強行結束InnoDB，而這會導致數據不完整。

mysqld程序–InnoDB–日誌
innodb_log_group_home_dir = p 用來存放InnoDB日誌文件的目錄路徑(如ib_logfile0、ib_logfile1等)。在默認的情況下，InnoDB驅動程序將使用MySQL數據目
錄作為自己保存日誌文件的位置。
innodb_log_files_in_group = n 使用多少個日誌文件(默認設置是2)。InnoDB資料表驅動程序將以輪轉方式依次填寫這些文件; 當所有的日誌文件都寫滿以後，
之後的日誌信息將寫入第一個日誌文件的最大長度(默認設置是5MB)。這個長度必須以MB(兆字節)或GB(千兆字節)為單位進行設置。
innodb_flush_log_at_trx_commit = 0/1/2 這個選項決定著什麼時候把日誌信息寫入日誌文件以及什麼時候把這些文件物理地寫(術語稱為’同步’)到硬盤上。
設置值0的意思是每隔一秒寫一次日誌並進行同步，這可以減少硬盤寫操作次數，但可能造成數據丟失; 設置值1(設置設置)的意思是在每執行完一條COMMIT
命令就寫一次日誌並進行同步，這可以防止數據丟失，但硬盤寫操作可能會很頻繁; 設置值2是一般折衷的辦法，即每執行完一條COMMIT命令寫一次日誌，
每隔一秒進行一次同步。
innodb_flush_method = x InnoDB日誌文件的同步辦法(僅適用於UNIX/Linux系統)。這個選項的可取值有兩種: fdatasync，用fsync()函數進行同步; O_DSYNC，
用O_SYNC()函數進行同步。
innodb_log_archive = 1 啟用InnoDB驅動程序的archive(檔案)日誌功能，把日誌信息寫入ib_arch_log_n文件。啟用這種日誌功能在InnoDB與MySQL一起使用時沒有
多大意義(啟用MySQL服務器的二進制日誌功能就足夠用了)。

mysqld程序–InnoDB–緩存區的設置和優化
innodb_log_buffer_pool_size = n 為InnoDB資料表及其索引而保留的RAM記憶體量(默認設置是8MB)。這個參數對速度有著相當大的影響，如果計算機上只運行有
MySQL/InnoDB資料庫服務器，就應該把全部記憶體的80%用於這個用途。
innodb_log_buffer_size = n 事務日誌文件寫操作緩存區的最大長度(默認設置是1MB)。
innodb_additional_men_pool_size = n 為用於內部管理的各種數據結構分配的緩存區最大長度(默認設置是1MB)。
innodb_file_io_threads = n I/O操作(硬盤寫操作)的最大線程個數(默認設置是4)。
innodb_thread_concurrency = n InnoDB驅動程序能夠同時使用的最大線程個數(默認設置是8)。

mysqld程序–其它選項
bind-address = ipaddr MySQL服務器的IP地址。如果MySQL服務器所在的計算機有多個IP地址，這個選項將非常重要。
default-storage-engine = type 新資料表的默認資料表類型(默認設置是MyISAM)。這項設置還可以通過–default-table-type選項來設置。
default-timezone = name 為MySQL服務器設置一個地理時區(如果它與本地計算機的地理時區不一樣)。
ft_min_word_len = n 全文索引的最小單詞長度工。這個選項的默認設置是4，意思是在創建全文索引時不考慮那些由3個或更少的字符構建單詞。
Max-allowed-packet = n 客戶與服務器之間交換的數據包的最大長度，這個數字至少應該大於客戶程序將要處理的最大BLOB塊的長度。這個選項的默認設置是1MB。
Sql-mode = model1, mode2, … MySQL將運行在哪一種SQL模式下。這個選項的作用是讓MySQL與其他的資料庫系統保持最大程度的兼容。這個選項的可取值包括
ansi、db2、oracle、no_zero_date、pipes_as_concat。
注意：如果在配置文件裡給出的某個選項是mysqld無法識別的(如，因為犯了一個愚蠢的打字錯誤)，MySQL服務器將不啟動。

資料來源：http://blog.knick.tw/2009/02/mysql-50-mycnf.html

MySQL 5.1 中，在復制(Replication)方面的改進就是引進了新的複制技術：基於行的複制。 簡言之，這種新技術就是關注表中發生變化的記錄，而非以前的照抄 binlog 模式。 從 MySQL 5.1.12 開始，可以用以下三種模式來實現：基於SQL語句的複制(statement-based replication, SBR)，基於行的複制(row-based replication, RBR)，混合模式複制(mixed-based replication, MBR)。 相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED。MBR 模式中，SBR 模式是默認的。

在運行時可以動態低改變binlog的格式，除了以下幾種情況：

1. 存儲過程或者觸發器中間

2. 啟用了NDB

3. 當前會話試用 RBR 模式，並且已打開了臨時表

如果binlog採用了 MIXED 模式，那麼在以下幾種情況下會自動將binlog的模式由 SBR 模式改成 RBR 模式。

1. 當DML語句更新一個NDB表時

2. 當函數中包含 UUID() 時

3. 2個及以上包含 AUTO_INCREMENT 字段的表被更新時

4. 行任何 INSERT DELAYED 語句時

5. 用 UDF 時

6. 視圖中必須要求使用 RBR 時，例如創建視圖是使用了 UUID() 函數

設定主從復制(Replication)模式的方法非常簡單，只要在以前設定複製配置的基礎上，再加一個參數：

binlog_format=’STATEMENT’
#binlog_format=’ROW’
#binlog_format=’MIXED’

當然了，也可以在運行時動態修改binlog的格式。 例如

mysql> SET SESSION binlog_format = ‘STATEMENT’;
mysql> SET SESSION binlog_format = ‘ROW’;
mysql> SET SESSION binlog_format = ‘MIXED’;

mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;

現在來比較以下 SBR 和 RBR 2中模式各自的優缺點
SBR 的優點：

1. 歷史悠久，技術成熟

2. binlog文件較小

3. binlog中包含了所有資料庫更改信息，可以據此來審核資料庫的安全等情況

4. binlog可以用於實時的還原，而不僅僅用於復制(Replication)

5. 主從版本可以不一樣，從服務器版本可以比主服務器版本高

SBR 的缺點：

1. 不是所有的UPDATE語句都能被複製，尤其是包含不確定操作的時候。

2. 調用具有不確定因素的 UDF 時復制(Replication)也可能出問題

3. 使用以下函數的語句也無法被複製：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非啟動時啟用了 –sysdate-is-now 選項)

4. INSERT … SELECT 會產生比 RBR 更多的行級鎖

5. 複製需要進行全表掃描(WHERE 語句中沒有使用到索引)的 UPDATE 時，需要比 RBR 請求更多的行級鎖

6. 對於有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 語句會阻塞其他 INSERT 語句

7. 對於一些複雜的語句，在從服務器上的耗資源情況會更嚴重，而 RBR 模式下，只會對那個發生變化的記錄產生影響

8. 存儲函數(不是存儲過程)在被調用的同時也會執行一次 NOW() 函數，這個可以說是壞事也可能是好事

9. 確定了的 UDF 也需要在從服務器上執行

10. 數據表必須幾乎和主服務器保持一致才行，否則可能會導致複製出錯

11. 執行複雜語句如果出錯的話，會消耗更多資源

RBR 的優點：

1. 任何情況都可以被複製，這對複制來說是最安全可靠的

2. 和其他大多數資料庫系統的複制技術一樣

3. 多數情況下，從服務器上的表如果有主鍵的話，複製就會快了很多

4. 複製以下幾種語句時的行鎖更少：
* INSERT … SELECT
* 包含 AUTO_INCREMENT 字段的 INSERT
* 沒有附帶條件或者並沒有修改很多記錄的 UPDATE 或 DELETE 語句

5. 執行 INSERT，UPDATE，DELETE 語句時鎖更少

6. 從服務器上採用多線程來執行複製成為可能

RBR 的缺點：

1. binlog 大了很多

2. 複雜的回滾時 binlog 中會包含大量的數據

3. 主服務器上執行 UPDATE 語句時，所有發生變化的記錄都會寫到 binlog 中，而 SBR 只會寫一次，這會導致頻繁發生

binlog 的並發寫問題

4. UDF 產生的大 BLOB 值會導致複製變慢

5. 無法從 binlog 中看到都複製了寫什麼語句

6. 當在非事務表上執行一段堆積的SQL語句時，最好採用 SBR 模式，否則很容易導致主從服務器的數據不一致情況發生

另外，針對系統庫 mysql 裡面的表發生變化時的處理規則如下：

1. 如果是採用 INSERT，UPDATE，DELETE 直接操作表的情況，則日誌格式根據 binlog_format 的設定而記錄

2. 如果是採用 GRANT，REVOKE，SET PASSWORD 等管理語句來做的話，那麼無論如何都採用 SBR 模式記錄

注：採用 RBR 模式後，能解決很多原先出現的主鍵重複問題

丫忠最近要來實做mysql Replication的架構，在做之前當然是要先把參考資料都找出來，底下丫忠從網路上爬出來的文章，有針對mysql的設定檔(my.cnf)做中文的說明，剛好可以參考的到!

#BEGIN CONFIG INFO
#DESCR: 4GB RAM,只使用InnoDB, ACID,少量的連接,隊列負載大
#TYPE: SYSTEM
#END CONFIG INFO

#
#此mysql配置文件例子針對4G內存
#主要使用INNODB
#處理複雜隊列並且連接數量較少的mysql服務器
#
#將此文件複製到/etc/my.cnf 作為全局設置,
# mysql-data-dir/my.cnf 作為服務器指定設置
# (@localstatedir@ for this installation) 或者放入
# ~/.my.cnf 作為用戶設置.
#
# 在此配置文件中, 你可以使用所有程序支持的長選項.
# 如果想獲悉程序支持的所有選項
#請在程序後加上』–help』參數運行程序.
#
# 關於獨立選項更多的細節信息可以在手冊內找到
#

#
#以下選項會被MySQL客戶端應用讀取.
#注意只有MySQL附帶的客戶端應用程序保證可以讀取這段內容.
#如果你想你自己的MySQL應用程序獲取這些值
#需要在MySQL客戶端庫初始化的時候指定這些選項

#
[client]
#password = [your_password]
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@

# *** 應用定制選項***

#
# MySQL 服務端
#
[mysqld]

# 一般配置選項
port = @MYSQL_TCP_PORT@
socket = @MYSQL_UNIX_ADDR@

# back_log 是操作系統在監聽隊列中所能保持的連接數,
#隊列保存了在MySQL連接管理器線程處理之前的連接.
#如果你有非常高的連接率並且出現』connection refused』 報錯,
# 你就應該增加此處的值.
# 檢查你的操作系統文檔來獲取這個變量的最大值.
#如果將back_log設定到比你操作系統限制更高的值,將會沒有效果
back_log = 50

#不在TCP/IP端口上進行監聽.
#如果所有的進程都是在同一台服務器連接到本地的mysqld,
# 這樣設置將是增強安全的方法
#所有mysqld的連接都是通過Unix sockets 或者命名管道進行的.
#注意在windows下如果沒有打開命名管道選項而只是用此項
# (通過 『enable-named-pipe』 選項) 將會導致mysql服務沒有任何作用!
#skip-networking

# MySQL 服務所允許的同時會話數的上限
#其中一個連接將被SUPER權限保留作為管理員登錄.
# 即便已經達到了連接數的上限.
max_connections = 100

# 每個客戶端連接最大的錯誤允許數量,如果達到了此限制.
#這個客戶端將會被MySQL服務阻止直到執行了』FLUSH HOSTS』 或者服務重啟
# 非法的密碼以及其他在鏈接時的錯誤會增加此值.
#查看 『Aborted_connects』 狀態來獲取全局計數器.
max_connect_errors = 10

# 所有線程所打開表的數量.
#增加此值就增加了mysqld所需要的文件描述符的數量
#這樣你需要確認在[mysqld_safe]中 『open-files-limit』 變量設置打開文件數量允許至少4096
table_cache = 2048

# 允許外部文件級別的鎖. 打開文件鎖會對性能造成負面影響
#所以只有在你在同樣的文件上運行多個數據庫實例時才使用此選項(注意仍會有其他約束!)
#或者你在文件層面上使用了其他一些軟件依賴來鎖定MyISAM表
#external-locking

#服務所能處理的請求包的最大大小以及服務所能處理的最大的請求大小(當與大的BLOB字段一起工作時相當必要)
# 每個連接獨立的大小.大小動態增加
max_allowed_packet = 16M

#在一個事務中binlog為了記錄SQL狀態所持有的cache大小
# 如果你經常使用大的,多聲明的事務,你可以增加此值來獲取更大的性能.
#所有從事務來的狀態都將被緩沖在binlog緩衝中然後在提交後一次性寫入到binlog中
# 如果事務比此值大, 會使用磁盤上的臨時文件來替代.
# 此緩沖在每個連接的事務第一次更新狀態時被創建
binlog_cache_size = 1M

# 獨立的內存表所允許的最大容量.
# 此選項為了防止意外創建一個超大的內存表導致永盡所有的內存資源.
max_heap_table_size = 64M

#排序緩衝被用來處理類似ORDER BY以及GROUP BY隊列所引起的排序
# 如果排序後的數據無法放入排序緩衝,
# 一個用來替代的基於磁盤的合併分類會被使用
#查看 『Sort_merge_passes』 狀態變量.
# 在排序發生時由每個線程分配
sort_buffer_size = 8M

#此緩衝被使用來優化全聯合(full JOINs 不帶索引的聯合).
# 類似的聯合在極大多數情況下有非常糟糕的性能表現,
# 但是將此值設大能夠減輕性能影響.
#通過 『Select_full_join』 狀態變量查看全聯合的數量
# 當全聯合發生時,在每個線程中分配
join_buffer_size = 8M

#我們在cache中保留多少線程用於重用
#當一個客戶端斷開連接後,如果cache中的線程還少於thread_cache_size,
#則客戶端線程被放入cache中.
# 這可以在你需要大量新連接的時候極大的減少線程創建的開銷
# (一般來說如果你有好的線程模型的話,這不會有明顯的性能提升.)
thread_cache_size = 8

# 此允許應用程序給予線程系統一個提示在同一時間給予渴望被運行的線程的數量.
#此值只對於支持 thread_concurrency() 函數的系統有意義( 例如Sun Solaris).
#你可可以嘗試使用 [CPU數量]*(2..4) 來作為thread_concurrency的值
thread_concurrency = 8

#查詢緩衝常被用來緩衝 SELECT 的結果並且在下一次同樣查詢的時候不再執行直接返回結果.
# 打開查詢緩衝可以極大的提高服務器速度, 如果你有大量的相同的查詢並且很少修改表.
#查看 『Qcache_lowmem_prunes』 狀態變量來檢查是否當前值對於你的負載來說是否足夠高.
# 注意: 在你表經常變化的情況下或者如果你的查詢原文每次都不同,
# 查詢緩衝也許引起性能下降而不是性能提升.
query_cache_size = 64M

# 只有小於此設定值的結果才會被緩衝
# 此設置用來保護查詢緩衝,防止一個極大的結果集將其他所有的查詢結果都覆蓋.
query_cache_limit = 2M

# 被全文檢索索引的最小的字長.
# 你也許希望減少它,如果你需要搜索更短字的時候.
# 注意在你修改此值之後,
#你需要重建你的 FULLTEXT 索引
ft_min_word_len = 4

#如果你的系統支持 memlock() 函數,你也許希望打開此選項用以讓運行中的mysql在在內存高度緊張的時候,數據在內存中保持鎖定並且防止可能被swapping out
# 此選項對於性能有益
#memlock

# 當創建新表時作為默認使用的表類型,
# 如果在創建表示沒有特別執行表類型,將會使用此值
default_table_type = MYISAM

# 線程使用的堆大小. 此容量的內存在每次連接時被預留.
# MySQL 本身常不會需要超過64K的內存
#如果你使用你自己的需要大量堆的UDF函數
# 或者你的操作系統對於某些操作需要更多的堆,
# 你也許需要將其設置的更高一點.
thread_stack = 192K

# 設定默認的事務隔離級別.可用的級別如下:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
transaction_isolation = REPEATABLE-READ

#內部(內存中)臨時表的最大大小
# 如果一個表增長到比此值更大,將會自動轉換為基於磁盤的表.
# 此限制是針對單個表的,而不是總和.
tmp_table_size = 64M

# 打開二進制日誌功能.
#在復制(replication)配置中,作為MASTER主服務器必須打開此項
# 如果你需要從你最後的備份中做基於時間點的恢復,你也同樣需要二進制日誌.
log-bin=mysql-bin

#如果你在使用鍊式從服務器結構的複制模式 (A->B->C),
#你需要在服務器B上打開此項.
# 此選項打開在從線程上重做過的更新的日誌,
# 並將其寫入從服務器的二進制日誌.
#log_slave_updates

#打開全查詢日誌.所有的由服務器接收到的查詢 (甚至對於一個錯誤語法的查詢)
# 都會被記錄下來. 這對於調試非常有用, 在生產環境中常常關閉此項.
#log

#將警告打印輸出到錯誤log文件.如果你對於MySQL有任何問題
#你應該打開警告log並且仔細審查錯誤日誌,查出可能的原因.
#log_warnings

#記錄慢速查詢.慢速查詢是指消耗了比 『long_query_time』 定義的更多時間的查詢.
#如果 log_long_format 被打開,那些沒有使用索引的查詢也會被記錄.
# 如果你經常增加新查詢到已有的系統內的話. 一般來說這是一個好主意,
log_slow_queries

#所有的使用了比這個時間(以秒為單位)更多的查詢會被認為是慢速查詢.
#不要在這裡使用』1″,否則會導致所有的查詢,甚至非常快的查詢頁被記錄下來(由於MySQL 目前時間的精確度只能達到秒的級別).
long_query_time = 2

# 在慢速日誌中記錄更多的信息.
# 一般此項最好打開.
# 打開此項會記錄使得那些沒有使用索引的查詢也被作為到慢速查詢附加到慢速日誌裡
log_long_format

#此目錄被MySQL用來保存臨時文件.例如,
# 它被用來處理基於磁盤的大型排序,和內部排序一樣.
# 以及簡單的臨時表.
#如果你不創建非常大的臨時文件,將其放置到 swapfs/tmpfs 文件系統上也許比較好
# 另一種選擇是你也可以將其放置在獨立的磁盤上.
#你可以使用』;』來放置多個路徑
#他們會按照roud-robin方法被輪詢使用.
#tmpdir = /tmp

# *** 複製有關的設置

#唯一的服務辨識號,數值位於 1 到 2^32-1之間.
#此值在master和slave上都需要設置.
#如果 『master-host』 沒有被設置,則默認為1,但是如果忽略此選項,MySQL不會作為master生效.
server-id = 1

#複製的Slave (去掉master段的註釋來使其生效)
#
#為了配置此主機作為複制的slave服務器,你可以選擇兩種方法:
#
# 1)使用 CHANGE MASTER TO 命令 (在我們的手冊中有完整描述) -
# 語法如下:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT= ,
# MASTER_USER=, MASTER_PASSWORD= ;
#
#你需要替換掉, , 等被尖括號包圍的字段以及使用master的端口號替換 (默認3306).
#
# 例子:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret’;
#
# 或者
#
# 2)設置以下的變量.不論如何,在你選擇這種方法的情況下,然後第一次啟動複製(甚至不成功的情況下,
#例如如果你輸入錯密碼在master-password字段並且slave無法連接),
# slave會創建一個 master.info 文件,並且之後任何對於包含在此文件內的參數的變化都會被忽略
#並且由 master.info 文件內的內容覆蓋,除非你關閉slave服務,刪除 master.info 並且重啟slave 服務.
#由於這個原因,你也許不想碰一下的配置(註釋掉的) 並且使用 CHANGE MASTER TO (查看上面) 來代替
#
#所需要的唯一id號位於 2 和 2^32 – 1之間
# (並且和master不同)
#如果master-host被設置了.則默認值是2
# 但是如果省略,則不會生效
#server-id = 2
#
#複製結構中的master -必須
#master-host =
#
#當連接到master上時slave所用來認證的用戶名-必須
#master-user =
#
#當連接到master上時slave所用來認證的密碼-必須
#master-password = #
# master監聽的端口.
#可選-默認是3306
#master-port =

#使得slave只讀.只有用戶擁有SUPER權限和在上面的slave線程能夠修改數據.
#你可以使用此項去保證沒有應用程序會意外的修改slave而不是master上的數據
#read_only

#*** MyISAM 相關選項

#關鍵詞緩衝的大小,一般用來緩衝MyISAM表的索引塊.
#不要將其設置大於你可用內存的30%,
#因為一部分內存同樣被OS用來緩衝行數據
#甚至在你並不使用MyISAM 表的情況下,你也需要仍舊設置起 8-64M 內存由於它同樣會被內部臨時磁盤表使用.
key_buffer_size = 32M

#用來做MyISAM表全表掃描的緩衝大小.
# 當全表掃描需要時,在對應線程中分配.
read_buffer_size = 2M

# 當在排序之後,從一個已經排序好的序列中讀取行時,行數據將從這個緩衝中讀取來防止磁盤尋道.
#如果你增高此值,可以提高很多ORDER BY的性能.
# 當需要時由每個線程分配
read_rnd_buffer_size = 16M

# MyISAM 使用特殊的類似樹的cache來使得突發插入
# (這些插入是,INSERT … SELECT, INSERT … VALUES (…), (…), …,以及 LOAD DATA
# INFILE)更快.此變量限制每個進程中緩衝樹的字節數.
#設置為 0 會關閉此優化.
#為了最優化不要將此值設置大於 『key_buffer_size』.
# 當突發插入被檢測到時此緩衝將被分配.
bulk_insert_buffer_size = 64M

#此緩衝當MySQL需要在 REPAIR, OPTIMIZE, ALTER 以及 LOAD DATA INFILE 到一個空表中引起重建索引時被分配.
# 這在每個線程中被分配.所以在設置大值時需要小心.
myisam_sort_buffer_size = 128M

# MySQL重建索引時所允許的最大臨時文件的大小 (當 REPAIR, ALTER TABLE 或者 LOAD DATA INFILE).
#如果文件大小比此值更大,索引會通過鍵值緩衝創建(更慢)
myisam_max_sort_file_size = 10G

# 如果被用來更快的索引創建索引所使用臨時文件大於製定的值,那就使用鍵值緩衝方法.
# 這主要用來強制在大表中長字串鍵去使用慢速的鍵值緩衝方法來創建索引.
myisam_max_extra_sort_file_size = 10G

#如果一個表擁有超過一個索引, MyISAM 可以通過並行排序使用超過一個線程去修復他們.
#這對於擁有多個CPU以及大量內存情況的用戶,是一個很好的選擇.
myisam_repair_threads = 1

#自動檢查和修復沒有適當關閉的 MyISAM 表.
myisam_recover

#默認關閉 Federated
skip-federated

# *** BDB 相關選項***

#如果你運行的MySQL服務有BDB支持但是你不准備使用的時候使用此選項.這會節省內存並且可能加速一些事.
skip-bdb

# *** INNODB 相關選項***

#如果你的MySQL服務包含InnoDB支持但是並不打算使用的話,
# 使用此選項會節省內存以及磁盤空間,並且加速某些部分
#skip-innodb

#附加的內存池被InnoDB用來保存 metadata 信息
#如果InnoDB為此目的需要更多的內存,它會開始從OS這裡申請內存.
# 由於這個操作在大多數現代操作系統上已經足夠快, 你一般不需要修改此值.
# SHOW INNODB STATUS 命令會顯示當先使用的數量.
innodb_additional_mem_pool_size = 16M

# InnoDB使用一個緩衝池來保存索引和原始數據,不像 MyISAM.
#這裡你設置越大,你在存取表裡面數據時所需要的磁盤I/O越少.
#在一個獨立使用的數據庫服務器上,你可以設置這個變量到服務器物理內存大小的80%
# 不要設置過大,否則,由於物理內存的競爭可能導致操作系統的換頁顛簸.
#注意在32位系統上你每個進程可能被限制在 2-3.5G 用戶層面內存限制,
# 所以不要設置的太高.
innodb_buffer_pool_size = 2G

# InnoDB 將數據保存在一個或者多個數據文件中成為表空間.
# 如果你只有單個邏輯驅動保存你的數據,一個單個的自增文件就足夠好了.
# 其他情況下.每個設備一個文件一般都是個好的選擇.
#你也可以配置InnoDB來使用裸盤分區-請參考手冊來獲取更多相關內容
innodb_data_file_path = ibdata1:10M:autoextend

#設置此選項如果你希望InnoDB表空間文件被保存在其他分區.
#默認保存在MySQL的datadir中.
#innodb_data_home_dir =

#用來同步IO操作的IO線程的數量. This value is
#此值在Unix下被硬編碼為4,但是在Windows磁盤I/O可能在一個大數值下表現的更好.
innodb_file_io_threads = 4

#如果你發現InnoDB表空間損壞,設置此值為一個非零值可能幫助你導出你的表.
#從1開始並且增加此值知道你能夠成功的導出表.
#innodb_force_recovery=1

#在InnoDb核心內的允許線程數量.
# 最優值依賴於應用程序,硬件以及操作系統的調度方式.
# 過高的值可能導致線程的互斥顛簸.
innodb_thread_concurrency = 16

#如果設置為1 ,InnoDB會在每次提交後刷新(fsync)事務日誌到磁盤上,
#這提供了完整的ACID行為.
#如果你願意對事務安全折衷,並且你正在運行一個小的食物,你可以設置此值到0或者2來減少由事務日誌引起的磁盤I/O
# 0代表日誌隻大約每秒寫入日誌文件並且日誌文件刷新到磁盤.
# 2代表日誌寫入日誌文件在每次提交後,但是日誌文件只有大約每秒才會刷新到磁盤上.
innodb_flush_log_at_trx_commit = 1

#加速InnoDB的關閉.這會阻止InnoDB在關閉時做全清除以及插入緩衝合併.
#這可能極大增加關機時間,但是取而代之的是InnoDB可能在下次啟動時做這些操作.
#innodb_fast_shutdown

# 用來緩沖日志數據的緩衝區的大小.
#當此值快滿時, InnoDB將必須刷新數據到磁盤上.
#由於基本上每秒都會刷新一次,所以沒有必要將此值設置的太大(甚至對於長事務而言)

innodb_log_buffer_size = 8M

# 在日誌組中每個日誌文件的大小.
#你應該設置日誌文件總合大小到你緩衝池大小的25%~100%
# 來避免在日誌文件覆寫上不必要的緩衝池刷新行為.
# 不論如何, 請注意一個大的日誌文件大小會增加恢復進程所需要的時間.
innodb_log_file_size = 256M

# 在日誌組中的文件總數.
#通常來說2~3是比較好的.
innodb_log_files_in_group = 3

# InnoDB的日誌文件所在位置.默認是MySQL的datadir.
#你可以將其指定到一個獨立的硬盤上或者一個RAID1卷上來提高其性能
#innodb_log_group_home_dir

#在InnoDB緩衝池中最大允許的髒頁面的比例.
#如果達到限額, InnoDB會開始刷新他們防止他們妨礙到干淨數據頁面.
# 這是一個軟限制,不被保證絕對執行.
innodb_max_dirty_pages_pct = 90

# InnoDB用來刷新日誌的方法.
# 表空間總是使用雙重寫入刷新方法
#默認值是 『fdatasync』,另一個是 『O_DSYNC』.
#innodb_flush_method=O_DSYNC

#在被回滾前,一個InnoDB的事務應該等待一個鎖被批准多久.
# InnoDB在其擁有的鎖表中自動檢測事務死鎖並且回滾事務.
#如果你使用 LOCK TABLES 指令,或者在同樣事務中使用除了InnoDB以外的其他事務安全的存儲引擎
#那麼一個死鎖可能發生而InnoDB無法注意到.
#這種情況下這個timeout值對於解決這種問題就非常有幫助.
innodb_lock_wait_timeout = 120

[mysqldump]
# 不要在將內存中的整個結果寫入磁盤之前緩存. 在導出非常巨大的表時需要此項
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

#僅僅允許使用鍵值的 UPDATEs 和 DELETEs .
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# 增加每個進程的可打開文件數量.
# 警告: 確認你已經將全系統限制設定的足夠高!
# 打開大量表需要將此值設高
open-files-limit = 8192

資料來源：http://www.hiadmin.com/?cat=4

丫忠 在上一篇【cacti 安裝教學】中提到如何安裝 cacti此套流量監控系統，當安裝完成後 cacti套件就會附上一些templates了，例如：ucd/net – Load Average、ucd/net – CPU Usage…等，如果你只是要監控系統的CPU、記憶體的效能，丫忠認為用原先附上的templates就已經足夠了。

不過，如果你需要監控更多類型的伺服器，例如：Apache以及MySQL等伺服器的效能，那就必須額外安裝templates 。

底下 丫忠 列出這2個套件的下載點，有興趣的朋友可以自行下載安裝：
cacti apache的templates：ApacheStats 0.8.2
cacti mysql的templates：mysql-cacti-templates

底下為 cacti 透過snmp監控系統狀態的實圖：
監控的項目：
Traffic-eth0
Memory Usage
CPU Usage
當然還有很多監控的項目沒有加上

底下為丫忠安裝 ApacheStats 後的實圖：
監控的項目：
Apache Stats CPU Usage
Apache Stats Bytes / Hit
Apache Stats Thread Details
Apache Stats Thread scroeboard
當然還有很多監控的項目沒有加上

底下為安裝 cacti-mysql-templates後的實圖：
監控的項目：
MySQL Select Types
MySQL ProcessList
MySQL Network Traffic
MySQL MyISAM Indexes
MySQL Handlers
MySQL connections
MySQL Command Counters
當然還有很多監控的項目沒有加上，你可以依自行需求加上graph

上一篇丫忠介紹了 MySQL的查詢時間記錄 後，除了從記錄檔中去分析查詢時間外；另外，還有一個非常棒的指令可以用來分析SELECT指令在MySQL中的執行情況，那就是 EXPLAIN。

EXPLAIN 顯示的訊息可以用來幫助索引和查出最佳化的查詢語法。

EXPLAIN 使用的方式：
只要在SELECT語法前面加上 EXPLAIN 指令即可。

例如：EXPLAIN SELECT * FROM website WHERE url=’http://homeserver.com.tw’;
執行結果畫面如下：

其中針對EXPLAIN的欄位說明如下：
table：關連到的資料表(Table)會顯示在此。

type：顯示使用了何種類型。從最優至最差的類型為const、eq_reg、ref、range、indexhe、ALL。

possible_keys：顯示可能使用到的索引。此為從WHERE語法中選擇一個適合的欄位名稱。

key：實際使用到的索引。如果為NULL，則是沒有使用索引。

key_len：使用索引的長度。長度越短 準確性越高。

ref：顯示那一列的索引被使用。一般是一個常數(const)。

rows：MySQL用來返回資料的筆數。

Extra：MySQL用來解析額外的查詢訊息。如果此欄位的值為：Using temporary和Using filesort，表示MySQL無法使用索引。

Extra為MySQL用來解析額外的查詢訊息，其中欄位值所代表的意義如下：

Distinct：當MySQL找到相關連的資料時，就不再搜尋。

Not exists：MySQL優化 LEFT JOIN，一旦找到符合的LEFT JOIN資料後，就不再搜尋。

Range checked for each Record(index map:#)：無法找到理想的索引。此為最慢的使用索引。

Using filesort：當出現這個值時，表示此SELECT語法需要優化。因為MySQL必須進行額外的步驟來進行查詢。

Using index：返回的資料是從索引中資料，而不是從實際的資料中返回，當返回的資料都出現在索引中的資料時就會發生此情況。

Using temporary：同Using filesort，表示此SELECT語法需要進行優化。此為MySQL必須建立一個暫時的資料表(Table)來儲存結果，此情況會發生在針對不同的資料進行ORDER BY，而不是GROUP BY。

Using where：使用WHERE語法中的欄位來返回結果。

System：system資料表，此為const連接類型的特殊情況。

Const：資料表中的一個記錄的最大值能夠符合這個查詢。因為只有一行，這個值就是常數，因為MySQL會先讀這個值然後把它當做常數。

eq_ref：MySQL在連接查詢時，會從最前面的資料表，對每一個記錄的聯合，從資料表中讀取一個記錄，在查詢時會使用索引為主鍵或唯一鍵的全部。

ref：只有在查詢使用了非唯一鍵或主鍵時才會發生。

range：使用索引返回一個範圍的結果。例如：使用大於>或小於<查詢時發生。

index：此為針對索引中的資料進行查詢。

ALL：針對每一筆記錄進行完全掃描，此為最壞的情況，應該盡量避免。

MySQL資料庫的query查詢時間往往取決於【是否建立索引】、【資料量多寡】、【query查詢的方法】…等，然而【是否建立索引】的因素卻是影響query查詢時間最重要的因素之一。
舉個例子：如果你的Table(User)中有一個name的欄位，當你要查詢姓氏為’林’的使用者，通常你會這樣下SQL語法
SELECT * FROM User WHERE name like ‘%林%’;
如此，一般而言你會將name這個欄位加入index索引中，否則當資料量大時，query時間的快慢就會明顯出現差異了。

當完成了一個網站後，程式中針對MySQL查詢(query)的次數那麼多，那要如何找出query查詢時間較長的語法呢？其實在my.cnf設定檔中(一般在/etc/my.cnf)，已經提供了參數可以直接產生log記錄檔案，讓管理者可以很清楚了解到那些查詢(query)的語法花費較多的時間。

請在my.cnf設定檔中，找到[mysqld]標籤，在此標籤下面加上：
log-slow-queries = /path/slow-query.log #slow query記錄檔的路儲存路徑
long_query_time = 2 #query超過2秒時，則會記錄
log-queries-not-using-indexes #沒有索引的記錄，則會記錄

設定完成my.cnf後，請記得要重新啟動MySQL

其中，log-slow-queries記錄檔的儲存路徑，這個目錄一定要是mysql使用者可以寫入權限的位置，因為slow-query.log記錄檔是由mysql使用者去執行寫入；否則，會造成記錄檔案無法產生的情況，這個問題是最常發生，請切記!

mysql安裝完成後，預設是只有telnet localhost 3306會有回應；但是，如果telnet 本機 ip 3306是不會有回應的。

此時就必須要藉由phpMyAdmin將 本機ip 加到mysql資料庫的user資料表中，詳細如下：

1.選擇【權限】→【新增使用者】

2. 在新增使用者時
使用者名稱：指的是要允許那些使用者可以連到mysql
主機：這裡記得要輸入本機的IP(內部或外部IP)，此例子代表 telnet 192.168.1.253 3306  這樣才會有回應

另外，如果要限制某一個IP才能連到mysql，那要如何做呢？

mysql 限制連線IP

修改/etc/my.conf(請依自行環境尋找適當路徑),在 [mysqld] 區域中新增設定，如下：
[mysqld]
bind-address=192.168.1.253

但是，以丫忠的測試結果，當bind-address設定為192.168.1.253時，telnet localhost 3306就會鎖起來，也就是不能連線。反之當bind-address設定為localhost(127.0.0.1)時，telnet 192.168.1.253也是不能連線。

所以，當你設定bind-address後，mysql只能對外開放一個IP,包含localhost也是在限制的範圍內