2011 年 6 月 – Nekobe's Lair

Update on 2011/07/17
如果碰到

2011-07-17 02:56:25: (critical) proxy-plugin.c:263: read_query_result() in /etc/mysql-proxy/proxy.lua tries to modify the resultset, but hasn’t asked to buffer it in proxy.query:append(…, { resultset_is_needed = true }). We ignore the change to the result-set.

這樣的問題的話, 修改 /etc/mysql-proxy/proxy.lua 的第 201 行, 把

proxy.queries:append(1, packet)

改成

proxy.queries:append(1, packet, {resultset_is_needed = true})

就可以了

一樣是個Run down, 中間碰到問題請找Google大神
基本準備三台機器, 兩台互為MySQL Server Master/Slave, 請參考MySQL Replication (Master-Slave) for CentOS Installation Guide(後文各稱為Master/Slave), 再參考MySQL-Proxy for CentOS 5 Installation Guide安裝好一台MySQL-Proxy(後文稱為Proxy), 然後進入設定程序.

首先相關目錄

[Proxy] # mkdir /etc/mysql-proxy/

後續的lua檔案會放至於此
接著設定成服務, 建立一個檔案/etc/init.d/mysql-proxy, 內容如下

#!/bin/sh
#
# mysql-proxy This script starts and stops the mysql-proxy daemon
#
# chkconfig: – 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql

# Source function library.
. /etc/rc.d/init.d/functions

PROXY_PATH=/usr/local/bin

prog=”mysql-proxy”

# Source networking configuration.
. /etc/sysconfig/network

# Check that networking is up.
[ ${NETWORKING} = “no” ] && exit 0

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ] ; then
. /etc/sysconfig/mysql-proxy
fi

# Set default mysql-proxy configuration.
PROXY_PID=/var/run/mysql-proxy.pid

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH

# By default it’s all good
RETVAL=0

# See how we were called.
case “$1″ in
start)
# Start daemon.
echo -n $”Starting $prog: ”
daemon $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS $ADMIN_PLUGIN_OPTIONS $PROXY_PLUGIN_OPTIONS
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
;;
stop)
# Stop daemons.
echo -n $”Stopping $prog: ”
killproc $prog
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID
fi
;;
restart)
$0 stop
sleep 3
$0 start
;;
condrestart)
[ -e /var/lock/subsys/mysql-proxy ] && $0 restart
;;
status)
status mysql-proxy
RETVAL=$?
;;
*)
echo “Usage: $0 {start|stop|restart|status|condrestart}”
RETVAL=1
;;
esac

exit $RETVAL

並且設定成可執行

[Proxy] # chmod a+x /etc/init.d/mysql-proxy
接著在/etc/sysconfig下建立服務啟動設定檔 /etc/sysconfig/mysql-proxy, 內容如下

# Options to mysql-proxy
# do not remove –daemon
LD_LIBRARY_PATH=”/usr/local/lib:$LD_LIBRARY_PATH”
LUA_PATH=”/usr/local/lib/mysql-proxy/lua/?.lua;$LUA_PATH”
LUA_CPATH=”/usr/local/lib/mysql-proxy/lua/?.so;$LUA_CPATH”
export LD_LIBRARY_PATH LUA_PATH LUA_CPATH

PROXY_OPTIONS=”–daemon”

ADMIN_PLUGIN_OPTIONS=”–admin-address=PROXY_IP:4401 –admin-username=admin –admin-password=admin_password –admin-lua-script=/etc/mysql-proxy/admin.lua”
PROXY_PLUGIN_OPTIONS=”–proxy-address=PROXY_IP:3306 –proxy-read-only-backend-addresses=SLAVE_IP:3306 –proxy-backend-addresses=MASTER_IP:3306 –proxy-lua-script=/etc/mysql-proxy/proxy.lua”

請把其中的 PROXY_IP, MASTER_IP, SLAVE_IP 改成各自的ip address, 並且變更admin使用者名稱及密碼為你想要的.
建立成系統服務

[Proxy] # /sbin/chkconfig –add mysql-proxy
[Proxy] # /sbin/chkconfig mysql-proxy on
到MASTER及SLAVE下建立一個用來連線使用的帳號密碼, 請注意為了Replication的完整性, 請考慮在SLAVE上將該帳號的權限鎖死為僅SELECT.
設定/etc/mysql-proxy/admin.lua, 內容如下

–[[ $%BEGINLICENSE%$
Copyright (c) 2008, 2009, Oracle and/or its affiliates. All rights reserved.

This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License as
published by the Free Software Foundation; version 2 of the
License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
02110-1301 USA

$%ENDLICENSE%$ –]]

function set_error(errmsg)
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = errmsg or “error”
}
end

function read_query(packet)
if packet:byte() ~= proxy.COM_QUERY then
set_error(“[admin] we only handle text-based queries (COM_QUERY)”)
return proxy.PROXY_SEND_RESULT
end

local query = packet:sub(2)

local rows = { }
local fields = { }

if query:lower() == “select * from backends” then
fields = {
{ name = “backend_ndx”,
type = proxy.MYSQL_TYPE_LONG },

{ name = “address”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “state”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “type”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “uuid”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “connected_clients”,
type = proxy.MYSQL_TYPE_LONG },
}

for i = 1, #proxy.global.backends do
local states = {
“unknown”,
“up”,
“down”
}
local types = {
“unknown”,
“rw”,
“ro”
}
local b = proxy.global.backends[i]

rows[#rows + 1] = {
i,
b.dst.name, — configured backend address
states[b.state + 1], — the C-id is pushed down starting at 0
types[b.type + 1], — the C-id is pushed down starting at 0
b.uuid, — the MySQL Server’s UUID if it is managed
b.connected_clients — currently connected clients
}
end
elseif query:lower() == “select * from help” then
fields = {
{ name = “command”,
type = proxy.MYSQL_TYPE_STRING },
{ name = “description”,
type = proxy.MYSQL_TYPE_STRING },
}
rows[#rows + 1] = { “SELECT * FROM help”, “shows this help” }
rows[#rows + 1] = { “SELECT * FROM backends”, “lists the backends and their state” }
else
set_error(“use ‘SELECT * FROM help’ to see the supported commands”)
return proxy.PROXY_SEND_RESULT
end

proxy.response = {
type = proxy.MYSQLD_PACKET_OK,
resultset = {
fields = fields,
rows = rows
}
}
return proxy.PROXY_SEND_RESULT
end

這個檔案可以在/usr/local/lib/mysql-proxy/lua找到, 複製過去也行, 或者可以自己寫個lua script, 這篇不是LUA語言教學, 就略過不提.
設定/etc/mysql-proxy/proxy.lua, 內容如下

–[[ $%BEGINLICENSE%$
Copyright (C) 2007-2008 MySQL AB, 2008 Sun Microsystems, Inc

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 of the License.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

$%ENDLICENSE%$ –]]

—
— a flexible statement based load balancer with connection pooling
—
— * build a connection pool of min_idle_connections for each backend and maintain
— its size
— *
—
—

local commands = require(“proxy.commands”)
local tokenizer = require(“proxy.tokenizer”)
local lb = require(“proxy.balance”)
local auto_config = require(“proxy.auto-config”)

— config
—
— connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 4,
max_idle_connections = 8,

is_debug = false
}
end

—
— read/write splitting sends all non-transactional SELECTs to the slaves
—
— is_in_transaction tracks the state of the transactions
local is_in_transaction = false

— if this was a SELECT SQL_CALC_FOUND_ROWS … stay on the same connections
local is_in_select_calc_found_rows = false

—
— get a connection to a backend
—
— as long as we don’t have enough connections in the pool, create new connections
—
function connect_server()
local is_debug = proxy.global.config.rwsplit.is_debug
— make sure that we connect to each backend at least ones to
— keep the connections to the servers alive
—
— on read_query we can switch the backends again to another backend

if is_debug then
print()
print(“[connect_server] ” .. proxy.connection.client.address)
end

local rw_ndx = 0

— init all backends
for i = 1, #proxy.global.backends do
local s = proxy.global.backends[i]
local pool = s.pool — we don’t have a username yet, try to find a connections which is idling
local cur_idle = pool.users[“”].cur_idle_connections

pool.min_idle_connections = proxy.global.config.rwsplit.min_idle_connections
pool.max_idle_connections = proxy.global.config.rwsplit.max_idle_connections

if is_debug then
print(” [“.. i ..”].connected_clients = ” .. s.connected_clients)
print(” [“.. i ..”].pool.cur_idle = ” .. cur_idle)
print(” [“.. i ..”].pool.max_idle = ” .. pool.max_idle_connections)
print(” [“.. i ..”].pool.min_idle = ” .. pool.min_idle_connections)
print(” [“.. i ..”].type = ” .. s.type)
print(” [“.. i ..”].state = ” .. s.state)
end

— prefer connections to the master
if s.type == proxy.BACKEND_TYPE_RW and
s.state ~= proxy.BACKEND_STATE_DOWN and
cur_idle < pool.min_idle_connections then proxy.connection.backend_ndx = i break elseif s.type == proxy.BACKEND_TYPE_RO and s.state ~= proxy.BACKEND_STATE_DOWN and cur_idle < pool.min_idle_connections then proxy.connection.backend_ndx = i break elseif s.type == proxy.BACKEND_TYPE_RW and s.state ~= proxy.BACKEND_STATE_DOWN and rw_ndx == 0 then rw_ndx = i end end if proxy.connection.backend_ndx == 0 then if is_debug then print(" [" .. rw_ndx .. "] taking master as default") end proxy.connection.backend_ndx = rw_ndx end -- pick a random backend -- -- we someone have to skip DOWN backends -- ok, did we got a backend ? if proxy.connection.server then if is_debug then print(" using pooled connection from: " .. proxy.connection.backend_ndx) end -- stay with it return proxy.PROXY_IGNORE_RESULT end if is_debug then print(" [" .. proxy.connection.backend_ndx .. "] idle-conns below min-idle") end -- open a new connection end --- -- put the successfully authed connection into the connection pool -- -- @param auth the context information for the auth -- -- auth.packet is the packet function read_auth_result( auth ) if is_debug then print("[read_auth_result] " .. proxy.connection.client.address) end if auth.packet:byte() == proxy.MYSQLD_PACKET_OK then -- auth was fine, disconnect from the server proxy.connection.backend_ndx = 0 elseif auth.packet:byte() == proxy.MYSQLD_PACKET_EOF then -- we received either a -- -- * MYSQLD_PACKET_ERR and the auth failed or -- * MYSQLD_PACKET_EOF which means a OLD PASSWORD (4.0) was sent print("(read_auth_result) ... not ok yet"); elseif auth.packet:byte() == proxy.MYSQLD_PACKET_ERR then -- auth failed end end --- -- read/write splitting function read_query( packet ) local is_debug = proxy.global.config.rwsplit.is_debug local cmd = commands.parse(packet) local c = proxy.connection.client local r = auto_config.handle(cmd) if r then return r end local tokens local norm_query -- looks like we have to forward this statement to a backend if is_debug then print("[read_query] " .. proxy.connection.client.address) print(" current backend = " .. proxy.connection.backend_ndx) print(" client default db = " .. c.default_db) print(" client username = " .. c.username) if cmd.type == proxy.COM_QUERY then print(" query = " .. cmd.query) end end if cmd.type == proxy.COM_QUIT then -- don't send COM_QUIT to the backend. We manage the connection -- in all aspects. proxy.response = { type = proxy.MYSQLD_PACKET_OK, } if is_debug then print(" (QUIT) current backend = " .. proxy.connection.backend_ndx) end return proxy.PROXY_SEND_RESULT end proxy.queries:append(1, packet) -- read/write splitting -- -- send all non-transactional SELECTs to a slave if not is_in_transaction and cmd.type == proxy.COM_QUERY then tokens = tokens or assert(tokenizer.tokenize(cmd.query)) local stmt = tokenizer.first_stmt_token(tokens) if stmt.token_name == "TK_SQL_SELECT" then is_in_select_calc_found_rows = false local is_insert_id = false for i = 1, #tokens do local token = tokens[i] -- SQL_CALC_FOUND_ROWS + FOUND_ROWS() have to be executed -- on the same connection -- print("token: " .. token.token_name) -- print(" val: " .. token.text) if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then is_in_select_calc_found_rows = true elseif not is_insert_id and token.token_name == "TK_LITERAL" then local utext = token.text:upper() if utext == "LAST_INSERT_ID" or utext == "@@INSERT_ID" then is_insert_id = true end end -- we found the two special token, we can't find more if is_insert_id and is_in_select_calc_found_rows then break end end -- if we ask for the last-insert-id we have to ask it on the original -- connection if not is_insert_id then local backend_ndx = lb.idle_ro() if backend_ndx > 0 then
proxy.connection.backend_ndx = backend_ndx
end
else
print(” found a SELECT LAST_INSERT_ID(), staying on the same backend”)
end
end
end

— no backend selected yet, pick a master
if proxy.connection.backend_ndx == 0 then
— we don’t have a backend right now
—
— let’s pick a master as a good default
—
proxy.connection.backend_ndx = lb.idle_failsafe_rw()
end

— by now we should have a backend
—
— in case the master is down, we have to close the client connections
— otherwise we can go on
if proxy.connection.backend_ndx == 0 then
return proxy.PROXY_SEND_QUERY
end

local s = proxy.connection.server

— if client and server db don’t match, adjust the server-side
—
— skip it if we send a INIT_DB anyway
if cmd.type ~= proxy.COM_INIT_DB and
c.default_db and c.default_db ~= s.default_db then
print(” server default db: ” .. s.default_db)
print(” client default db: ” .. c.default_db)
print(” syncronizing”)
proxy.queries:prepend(2, string.char(proxy.COM_INIT_DB) .. c.default_db)
end

— send to master
if is_debug then
if proxy.connection.backend_ndx > 0 then
local b = proxy.global.backends[proxy.connection.backend_ndx]
print(” sending to backend : ” .. b.address);
print(” is_slave : ” .. tostring(b.type == proxy.BACKEND_TYPE_RO));
print(” server default db: ” .. s.default_db)
print(” server username : ” .. s.username)
end
print(” in_trans : ” .. tostring(is_in_transaction))
print(” in_calc_found : ” .. tostring(is_in_select_calc_found_rows))
print(” COM_QUERY : ” .. tostring(cmd.type == proxy.COM_QUERY))
end

return proxy.PROXY_SEND_QUERY
end

—
— as long as we are in a transaction keep the connection
— otherwise release it so another client can use it
function read_query_result( inj )
local is_debug = proxy.global.config.rwsplit.is_debug
local res = assert(inj.resultset)
local flags = res.flags

if inj.id ~= 1 then
— ignore the result of the USE
— the DB might not exist on the backend, what do do ?
—
if inj.id == 2 then
— the injected INIT_DB failed as the slave doesn’t have this DB
— or doesn’t have permissions to read from it
if res.query_status == proxy.MYSQLD_PACKET_ERR then
proxy.queries:reset()
proxy.response = {
type = proxy.MYSQLD_PACKET_ERR,
errmsg = “can’t change DB “.. proxy.connection.client.default_db ..
” to on slave ” .. proxy.global.backends[proxy.connection.backend_ndx].address
}

return proxy.PROXY_SEND_RESULT
end
end
return proxy.PROXY_IGNORE_RESULT
end

is_in_transaction = flags.in_trans
local have_last_insert_id = (res.insert_id and (res.insert_id > 0))

if not is_in_transaction and
not is_in_select_calc_found_rows and
not have_last_insert_id then
— release the backend
proxy.connection.backend_ndx = 0
elseif is_debug then
print(“(read_query_result) staying on the same backend”)
print(” in_trans : ” .. tostring(is_in_transaction))
print(” in_calc_found : ” .. tostring(is_in_select_calc_found_rows))
print(” have_insert_id : ” .. tostring(have_last_insert_id))
end
end

—
— close the connections if we have enough connections in the pool
—
— @return nil – close connection
— IGNORE_RESULT – store connection in the pool
function disconnect_client()
local is_debug = proxy.global.config.rwsplit.is_debug
if is_debug then
print(“[disconnect_client] ” .. proxy.connection.client.address)
end

— make sure we are disconnection from the connection
— to move the connection into the pool
proxy.connection.backend_ndx = 0
end

這個檔案在MySQL-Proxy的repository找的到, 是標準的Read/Write分離, 請自行Google
接著啟動MySQL-Proxy

[Proxy] # /sbin/service mysql-proxy start

連接上Admin Console

[Proxy] # mysql -u admin -padmin_password -h PROXY_IP -P 4401
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> SELECT * FROM backends;
+————-+——————–+———+——+——+——————-+
| backend_ndx | address | state | type | uuid | connected_clients |
+————-+——————–+———+——+——+——————-+
| 1 | MASTER_IP:3306 | unknown | rw | NULL | 0 |
| 2 | SLAVE_IP:3306 | unknown | ro | NULL | 0 |
+————-+——————–+———+——+——+——————-+
2 rows in set (0.00 sec)

或者是直接用設定在MASTER與SLAVE上相同的mysql使用者帳密透過proxy連接

[Proxy] # mysql -u USERNAME -pPASWORD -h PROXY_IP -P 3306
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 162
Server version: 5.0.77-log Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

這樣就可以正常使用了

一樣, 這只是個clean install/configure的run down, 碰到問題請詢問Google大人. 雖然那堆config很吃篇幅, 不過我相信有人用的到, 避免每個人Google到不同的東西, 所以寫個清楚.

這個是承上一篇, 裝完MySQL-Proxy後, 後續怎麼從零建置起, 第一步要先把後端的MySQL Replication建立起來.

準備好兩台機器(目前稱為Master/Slave), 首先安裝好CentOS 5之後, 一樣先把Repositories都裝好, 也都update/upgrade好, 以下是兩台共通的部份.

安裝一套CentOS5, 我實驗的VM是使用CentOS 5.6, 只裝了基本的Base跟Develop Tools
先做一次yum -y update, 更新一些套件到最新
安裝RPMForge & EPEL Repositories
再做一次yum -y update, 更新一些套件到最新
安裝MySQL Server, 設定好services

# yum -y install mysql-server
# /sbin/chkconfig mysqld on
# /sbin/service mysqld start
設定iptables, Master/Slave互相開放對方的TCP 3306 port, 其中eth0改成自己的網卡代號.

# /sbin/iptables -A INPUT -i eth0 -p tcp -m tcp –dport 3306 -j ACCEPT

接著是兩台不同的部份, 請注意前面的Hostname代表是在哪一台上

到Master上先設定my.cnf, 這個是可以選擇不同設定檔, 在/usr/share/mysql 下有很多個, 這裡挑選medium的, 細部設定請參考mysql網站上的文件, 這裡直接用medium的標準設定.

[Master] # /sbin/services mysqld stop
[Master] # cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
設定 /etc/my.cnf, 務必打開log-bin

server-id = 1
log-bin = mysql-bin

其中server-id必須各自不同, 用於Replication關係中識別各Server.
設定Replication使用的連線帳號

[Master] # /sbin/service mysqld start
[Master] # mysql -u root
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@SLAVE-IP IDENTIFIED BY ‘repl_password’;
mysql> FLUSH PRIVILEGES;

其中 repl 是Slave用來連接Master使用的帳號, SLAVE-IP是Slave的ip address, 增加一個slave時必須重加, repl_password則是Slave連接Master時的密碼, 請更換成自己的.
接著把Master的資料都Lock, 避免任何新資料寫入, 並且取得Master status

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 759 | | |
+——————+———-+————–+——————+

上頭的File跟Position就是等等Slave設定時使用的參數
到Slave上, 一樣先複製一份my.cnf

[Slave] # /sbin/service mysqld stop
[Slave] # cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
設定my.cnf, 唯一的不同是server-id一定要跟Master不同, 而且不能跟其他Slave重複

server-id = 2
log-bin = mysql-bin

server-id務必與Master不同, 也不能與其他Slave(假如有的話)相同
啟動Slave並設定Master相關資訊

[Slave] # /sbin/services mysqld start
[Slave] # mysql -u root
mysql> CHANGE MASTER TO
-> MASTER_HOST=Master-IP,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl_password’,
-> MASTER_LOG_FILE=’mysql-bin.000001′,
-> MASTER_LOG_POS=’759′;

其中Master-IP則為Master的IP, repl是剛剛設定在Master上的帳號, MASTER_LOG_FILE則是剛剛在Master上SHOW Master Status出現的Binary Log檔名, MASTER_LOG_POS則是現在在Binary Log檔案中的位置, 請依照前面看到的填入.
在Slave上啟動Slave 機制

mysql> Start Slave;
Query OK, 0 rows affected, 0 warning (0.00 sec)

這樣就表示成功了, 也可以在Slave上SHOW SLAVE STATUS來看狀態

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xxx.xxx.xxx.xxx
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 759
Relay_Log_File: mysqld-relay-bin.000042
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 759
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)

主要要看到Slave_SQL_Running是Yes就大致ok了.
最後回到Master上, 把剛剛Lock住的解開

[Master] # mysql -u root
mysql> UNLOCK TABLES;

接下來就可以實驗新增Db/Tables/Rows看看有沒有被Replication到Slave去.

大致這樣就乾淨的把兩台機器之間的MySQL Replication設定好了, 但是還有一些注意事項

絕對不要在Slave上寫入或更新任何資料, 必要時甚至只開啟Select的權限
必須定期檢查Slave上的Replication狀態, 是否有錯誤
由於開啟Binary Log後, 忙碌的MySQL Server會不停的佔用空間, 也請在確認好Replication狀態運行中的時候, 定期PURGE binary log, 相關文件請參考這裡
若Replication狀態損壞時, 需要重建關係, 在Master Lock之後, 把mysql data file複製一份至Slave後, 程序相同

這裡只有簡單的run down, 中間碰到任何問題請Google解決吧.

月份: 2011 年 6 月

MySQL Proxy for CentOS Configuration Guide

MySQL Replication (Master-Slave) for CentOS Installation Guide

MySQL-Proxy for CentOS 5 Installation Guide