站長資訊網1、讀寫分離原理：
讀寫分離就是在主服務器上修改，數據會同步到從服務器，從服務器只能提供讀取數據，不能寫入，實現備份的同時也實現了數據庫性能的優化，以及提升了服務器安全。
安裝環境：
| linux環境 | centos7.3 |
| 192.168.2.201 | master主數據庫 |
| 192.168.2.202 | slave從數據庫 |
| 192.168.2.203 | mysql-proxy中間件 |
2、mysql的讀寫分離是在mysql主從復制的基礎上的，所以我們先要搭建mysql的主從復制環境，之前的博客已經寫到，這里不再多贅述。mysql的主從復制
下面我們在192.162.2.203機器上操作：
安裝依賴：
yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib* readline-devel*
MySQL-Proxy的讀寫分離主要是通過rw-splitting.lua腳本實現的，因此需要安裝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 #替換成下面的配置內容，刪除原有的 CFLAGS= -O2 -Wall -fPIC -Wextra -DLUA_COMPAT_5_2 $(SYSCFLAGS) $(MYCFLAGS) make linux make install
3、下載myqsl-proxy中間件安裝包，解壓設置屬組權限。
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 #創建存放lua的腳本和日志的兩個目錄 mkdir lua mkdir logs #復制讀寫分離配置文件 cd /usr/local/mysql-proxy/lua cp share/doc/mysql-proxy/rw-splitting.lua . #修改rw-splitting.lua配置內容 proxy.global.config.rwsplit = { min_idle_connections = 1, //默認為4 max_idle_connections = 1, //默認為8 is_debug = false } #創建admin.lua腳本，不使用自帶的admin-sql.lua腳本 vim admin.lua #添加一下內容 --[[ $%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、創建mysql-proxy啟動時所需要的配置文件
vi /etc/mysql-proxy.cnf #創建配置文件 [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 ~
這里注意上述的配置在復制的時候注意每行后面一定不要有空格，否則會報如下的錯誤，尤其在復制他人博客的時候每行后面都有一個注釋，就是這個地方會有空格出現。
Key file contains key 'daemon' which has value that cannot be interpreted.
啟動mysql-proxy
/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --plugins=proxy --plugins=admin
啟動后查看進程和端口：
5、創建用于讀寫分離的數據庫連接用戶
登陸主數據庫服務器192.168.2.201，通過命令行登錄管理MySQL服務器
[root@sqldb01 opt]# mysql -uroot -p1234 mysql> GRANT ALL ON *.* TO 'wyl'@'192.168.%.%' IDENTIFIED BY '1234'; mysql>flush privileges;
由于我們配置了主從復制功能，因此從數據庫服務器192.168.2.202上已經同步了此操作。
6、在mysql-proxy的機器上進入后發現已經變成代理的mysql，version:5.0.99-agent-admin。
當輸入如下指令進入數據庫明令行時，可以對數據庫增刪改查操作。
mysql -uwyl -p1234 -h 192.168.2.203 -P4040
執行多次查詢和插入詢操作，如果出現下面圖中的state都是up狀態，則表示讀寫分離功能實現。
