mysql讀寫分離配置

環境：centos7.2 mysql5.7

場景描述：
資料庫Master主伺服器：192.168.206.100
資料庫Slave從伺服器：192.168.206.200
MySQL-Proxy調度伺服器：192.168.206.210

以下操作，均是在192.168.206.210即MySQL-Proxy調度伺服器 上進行的。

1.檢查系統所需軟體包

安裝之前需要配置EPEL YUM源

wget https://mirrors.ustc.edu.cn/epel//7/x86_64/Packages/e/epel-release-7-11.noarch.rpm
rpm -ivh epel-release-7-11.noarch.rpm
yum clean all
yum update

yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib*

2.編譯安裝lua

MySQL-Proxy的讀寫分離主要是通過rw-splitting.lua腳本實現的，因此需要安裝lua

lua可通過以下方式獲得
從http://www.lua.org/download.html下載源碼包

從rpm.pbone.net搜索相關的rpm包
download.fedora.redhat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm
download.fedora.redhat.com/pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm

這裡我們建議採用源碼包進行安裝

cd /opt/install
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zvfx lua-5.1.4.tar.gz
cd lua-5.1.4
make linux
make install
mkdir /usr/lib/pkgconfig/
cp /opt/install/lua-5.1.4/etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

註意的問題

編譯的時候，遇到的問題是，缺少依賴包** readline**, 然後readline又依賴ncurses,所以要先安裝著兩個軟體

yum install -y readline-devel ncurses-devel

3.安裝配置MySQL-Proxy

下載mysql-proxy

下載：http://dev.mysql.com/downloads/mysql-proxy/

wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy

配置mysql-proxy，創建主配置文件

cd /usr/local/mysql-proxy
mkdir lua #創建腳本存放目錄
mkdir logs #創建日誌目錄
cp share/doc/mysql-proxy/rw-splitting.lua  ./lua #複製讀寫分離配置文件
vi /etc/mysql-proxy.cnf   #創建配置文件
[mysql-proxy]
user=root #運行mysql-proxy用戶
admin-username=proxyuser #主從mysql共有的用戶
admin-password=123456 #用戶的密碼
proxy-address=192.168.206.210:4040 #mysql-proxy運行ip和埠，不加埠，預設4040
proxy-read-only-backend-addresses=192.168.206.200 #指定後端從slave讀取數據
proxy-backend-addresses=192.168.206.100 #指定後端主master寫入數據
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定讀寫分離配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua #指定管理腳本
log-file=/var/log/mysql-proxy.log #日誌位置
log-level=info #定義log日誌級別
daemon=true#以守護進程方式運行
keepalive=true #mysql-proxy崩潰時，嘗試重啟

這裡就有一個坑了

配置文件裡面的註釋要全部刪掉，不然可能會引起一些字元不能識別的錯誤。
這還不是最坑的，最坑的是：即使刪掉註釋，去除多餘的空白字元，仍然可能會報如下錯誤：

2018-09-21 06:39:40: (critical) Key file contains key “daemon” which has a value that cannot be interpreted.

或者：

2018-09-21 06:52:22: (critical) Key file contains key “keepalive” which has a value that cannot be interpreted.

出現以上問題的原因是daemon=true，keepalive=true現在不這樣寫了，要改為：

daemon=1
keepalive=1

參考鏈接 ： http://anothermysqldba.blogspot.com/2013/05/setup-mysql-proxy.html

給配置文件執行許可權
chmod 660 /etc/mysql-porxy.cnf

配置admin.lua 文件

在/etc/mysql-proxy.cnf 配置文件中，還差/usr/local/mysql-proxy/lua/admin.lua 的管理文件，實際現在還沒有創建的。所以，現在需要編輯創建admin.lua文件。mysql-proxy-0.8.5的這個版本，我找到了下麵的admin.lua腳本，對這個版本才是有效的：

vim  /usr/local/mysql-proxy/lua/admin.lua



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

修改讀寫分離配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit 
 proxy.global.config.rwsplit = {
  min_idle_connections = 1, #預設超過4個連接數時，才開始讀寫分離，改為1
  max_idle_connections = 1, #預設8，改為1
  is_debug = false
 }
end

啟動mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf

netstat -tupln | grep 4000 #已經啟動killall -9 mysql-proxy #關閉mysql-proxy