欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

部署mysql的读写分离

程序员文章站 2022-05-02 10:04:43
...

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

部署mysql的读写分离

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

启动后查看进程和端口:

部署mysql的读写分离


5、创建用于读写分离的数据库连接用户
登陆主数据库服务器192.168.2.201,通过命令行登录管理MySQL服务器

[aaa@qq.com 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的读写分离

当输入如下指令进入数据库明令行时,可以对数据库增删改查操作。

mysql -uwyl -p1234  -h 192.168.2.203 -P4040

执行多次查询和插入询操作,如果出现下面图中的state都是up状态,则表示读写分离功能实现。

部署mysql的读写分离部署mysql的读写分离