1、讀寫(xiě)分離原理：

讀寫(xiě)分離就是在主服務(wù)器上修改，數(shù)據(jù)會(huì)同步到從服務(wù)器，從服務(wù)器只能提供讀取數(shù)據(jù)，不能寫(xiě)入，實(shí)現(xiàn)備份的同時(shí)也實(shí)現(xiàn)了數(shù)據(jù)庫(kù)性能的優(yōu)化，以及提升了服務(wù)器安全。

安裝環(huán)境：

linux環(huán)境	centos7.3
192.168.2.201	master主數(shù)據(jù)庫(kù)
192.168.2.202	slave從數(shù)據(jù)庫(kù)
192.168.2.203	mysql-proxy中間件

2、mysql的讀寫(xiě)分離是在mysql主從復(fù)制的基礎(chǔ)上的，所以我們先要搭建mysql的主從復(fù)制環(huán)境，之前的博客已經(jīng)寫(xiě)到，這里不再多贅述。mysql的主從復(fù)制

下面我們?cè)?92.162.2.203機(jī)器上操作：

安裝依賴(lài)：

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

MySQL-Proxy的讀寫(xiě)分離主要是通過(guò)rw-splitting.lua腳本實(shí)現(xiàn)的，因此需要安裝lua。

  wget http://www.lua.org/ftp/lua-5.3.4.tar.gz    tar xf lua-5.3.4.tar.gz    cd lua-5.3.4    vi src/Makefile    #替換成下面的配置內(nèi)容，刪除原有的  CFLAGS= -O2 -Wall -fPIC -Wextra -DLUA_COMPAT_5_2 $(SYSCFLAGS) $(MYCFLAGS)    make linux    make install

3、下載myqsl-proxy中間件安裝包，解壓設(shè)置屬組權(quán)限。

  wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz    tar xf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local    cd /usr/local/    mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit /usr/local/mysql-proxy    useradd mysql-proxy    chown -R mysql-proxy.mysql-proxy mysql-proxy*    cd /usr/local/mysql-proxy    #創(chuàng)建存放lua的腳本和日志的兩個(gè)目錄  mkdir lua   mkdir logs       #復(fù)制讀寫(xiě)分離配置文件  cd /usr/local/mysql-proxy/lua  cp share/doc/mysql-proxy/rw-splitting.lua .  #修改rw-splitting.lua配置內(nèi)容  proxy.global.config.rwsplit = {  min_idle_connections = 1, //默認(rèn)為4  max_idle_connections = 1, //默認(rèn)為8  is_debug = false  }      #創(chuàng)建admin.lua腳本，不使用自帶的admin-sql.lua腳本  vim admin.lua    #添加一下內(nèi)容    --[[ $%BEGINLICENSE%$   Copyright (c) 2007, 2012, 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  

4、創(chuàng)建mysql-proxy啟動(dòng)時(shí)所需要的配置文件

  vi /etc/mysql-proxy.cnf   #創(chuàng)建配置文件    [mysql-proxy]  user=root  admin-username=wyl  admin-password=1234  proxy-address=192.168.2.203:4040  proxy-read-only-backend-addresses=192.168.2.202:3306  proxy-backend-addresses=192.168.2.201:3306  proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua  admin-lua-script=/usr/local/mysql-proxy/lua/admin.lua  log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log  log-level=info  daemon=true  keepalive=true  ~              

這里注意上述的配置在復(fù)制的時(shí)候注意每行后面一定不要有空格，否則會(huì)報(bào)如下的錯(cuò)誤，尤其在復(fù)制他人博客的時(shí)候每行后面都有一個(gè)注釋?zhuān)褪沁@個(gè)地方會(huì)有空格出現(xiàn)。

   Key file contains key 'daemon' which has value that cannot be interpreted.

啟動(dòng)mysql-proxy

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

啟動(dòng)后查看進(jìn)程和端口：

5、創(chuàng)建用于讀寫(xiě)分離的數(shù)據(jù)庫(kù)連接用戶(hù)
登陸主數(shù)據(jù)庫(kù)服務(wù)器192.168.2.201，通過(guò)命令行登錄管理MySQL服務(wù)器

  [root@sqldb01 opt]# mysql -uroot -p1234    mysql> GRANT ALL ON *.* TO 'wyl'@'192.168.%.%' IDENTIFIED BY '1234';    mysql>flush privileges;    

由于我們配置了主從復(fù)制功能，因此從數(shù)據(jù)庫(kù)服務(wù)器192.168.2.202上已經(jīng)同步了此操作。

6、在mysql-proxy的機(jī)器上進(jìn)入后發(fā)現(xiàn)已經(jīng)變成代理的mysql，version:5.0.99-agent-admin。

當(dāng)輸入如下指令進(jìn)入數(shù)據(jù)庫(kù)明令行時(shí)，可以對(duì)數(shù)據(jù)庫(kù)增刪改查操作。

  mysql -uwyl -p1234 -h 192.168.2.203 -P4040

執(zhí)行多次查詢(xún)和插入詢(xún)操作，如果出現(xiàn)下面圖中的state都是up狀態(tài)，則表示讀寫(xiě)分離功能實(shí)現(xiàn)。