部署mysql的读写分离

青旅半醒 2022-05-14 07:48 194阅读 0赞

1、读写分离原理：

读写分离就是在主服务器上修改，数据会同步到从服务器，从服务器只能提供读取数据，不能写入，实现备份的同时也实现了数据库性能的优化，以及提升了服务器安全。

安装环境：

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>linux环境</td> 
   <td>centos7.3</td> 
  </tr> 
  <tr> 
   <td>192.168.2.201</td> 
   <td>master主数据库</td> 
  </tr> 
  <tr> 
   <td>192.168.2.202</td> 
   <td>slave从数据库</td> 
  </tr> 
  <tr> 
   <td>192.168.2.203</td> 
   <td>mysql-proxy中间件</td> 
  </tr> 
 </tbody> 
</table>

2、mysql的读写分离是在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

![70][]

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

启动后查看进程和端口：

![70 1][]

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。

![70 2][]

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

mysql -uwyl -p1234  -h 192.168.2.203 -P4040

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

![70 3][]![70 4][]

[mysql]: https://blog.csdn.net/wyl9527/article/details/78829355
[70]: /images/20220514/49781fad4189492aabf5e988099745c3.png
[70 1]: /images/20220514/d221e3c57dfd47718f1c98dd4fb3477d.png
[70 2]: /images/20220514/a0d09ccd49ac4293ae597365c6fa283f.png
[70 3]: https://img-blog.csdn.net/20180905003549813?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3d5bDk1Mjc=/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 4]: /images/20220514/275512e59fec4cc9949aa4958779f6e7.png