[笔记] mysql-proxy 实现主从读写分离

1. 实验环境：

1.1. mysql-proxy 调度器：

    Centos6.6  ip：192.168.38.151   

1.2. mysql 主服务器master：

    Centos6.6  ip：192.168.38.152

1.3. mysql 从服务器slave：

    Centos6.6  ip：192.168.38.153

2.搭建mysql主从复制的环境

使用的mysql版本：mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

2.1. Master上安装mysql数据库：

2.1. 1. 下载到 /usr/local/src 目录。

2.1. 2. 解压缩

tar  zxvf    mysql-5.1.40-linux-i686-icc-glibc23.tar.gz

2.1. 3. 移动并且从命名安装包

mv mysql-5.1.40-linux-i686-icc-glibc23   /usr/local/mysql

2.1. 4. 开始安装mysql，但是需要在安装之前，创建一个用户以及创建一个“datadir”即数据库路径。

useradd  -s  /sbin/nologin  -M  mysql

mkdir -p  /data/mysql

chown -R mysql  /data/mysql

安装mysql的文件在 ./scripts/

具体： ./scripts/mysql_install_db  --user=mysql  --datadir=/data/mysql

这一步安装完成后，可以用echo  $? 查看是否已经安装成功。

2.1. 5. 现在，将 support-files/my-large.cnf  覆盖到/etc/my.cnf文件。

cp support-files/my-large.cnf  /etc/my.cnf    将其覆盖。

2.1. 6. 添加mysql开机启动，即需要将 mysql.server 添加到/etc/init.d/目录下。

具体命令就是：  cp  mysql.server  /etc/init.d/mysqld    

然后，还需要将/etc/init.d/mysqld 文件里的参数进行配置。

vim /etc/init.d/mysqld 

主要就是配置 以下这两个参数：

basedir=/usr/local/mysql

datadir=/data/mysql

2.1. 7. 接着，用chkconfig --add mysqld  添加服务。

    用 chkconfig mysqld on  开启服务

2.1. 8.  /etc/init.d/mysqld start  启动了

2.1. 9. 最后，我们用 ps  aux | grep 'mysqld'

    以及   netstat  -lnp | grep 'mysqld'

    两条命令都可以查看mysqld 是否已经在某个端口监听运行了。

2.2 在slave上安装mysql数据库，方法和master上的一样。

2.3 配置主从上的mysql配置文件

主端：

vim   /etc/my.cnf   增加：

log-bin=mysql-bin         #这个选项是为了打开主的二进制日志记录

binlog_format=mixed

server-id  = 1         #整数，和从的区分

binlog-ignore-db = mysql    #这个选项是不同步的数据库，因为是实验，就不同步mysql库了

从端：

vim   /etc/my.cnf  修改：

server-id  =  2       #和主的不相同

2.4 主备需要同步的数据库

  在开始做主从复制的环境的时候，这一步是不能省略的。

  首先在主上： mysql  -e "create  database   db1";

  然后，mysqldump -S /tmp/mysql.sock mysql > 123.sql

      mysql -S /tmp/mysql.sock db1 < 123.sql    拷贝回去。

  这时的db1数据库和mysql数据库就是一样的了。我们现在用db1做实验。

    

  然后，将123.sql 拷贝到从机上，用 scp   123.sql    root@192.168.38.153:/tmp/

  然后在从机上操作：

  mysql   -e   "create  database   db1;"     #就是创建一个空的db1数据库

  mysql   -S   /tmp/mysql.sock   db1   <  /tmp/123.sql   #恢复备份，现在主从的db1都是相同的数据

2.5  在主从上进行用户授权：

主,登陆数据库：

mysql> grant   replication slave   on *.*  to   'repl'@'192.168.38.%'  identified  by '123456';

#这步给repl用户授权，它可以连接到master这里，下载二进制日志，然后同步数据库。

mysql>flush  privileges;

mysql>flush   tables  with  read lock;

mysql>show  master   status;

大概的样子：记录下File的名字，和Position的值，

从机，登陆mysql：

mysql>slave   stop;    #将slave 暂停

mysql>

change master to master_host='192.168.38.152', master_port=3306, master_user='repl', master_password='123456', master_log_file='mysql-bin.000039', master_log_pos=840;

#这里的意思，就是设定主服务器的ip端口，登陆的用户，权限，已经二进制日志的名字，同步的位置现在是840.

然后，

mysql>slave   start;

然后 slave start; 然后，show slave status\G; 

需要看到两个Yes 才代表成功。

记得刚刚修改了从机的server-id后，要重启一下数据库生效。

在“主”

由于之前都是mysql> flush tables with read lock;

所以，现在要unlock一下：unlock tables;

2.6  主从测试

在“主”操作一下：

例如，

use   db1;

show tables;

然后，我们把help_category删掉

然后，在从上，看看，发现这个表也不存在了。测试主从复制成功。

3.  部署mysql-proxy服务

现在开始在192.168.38.151这台机器上安装mysql-proxy。

3.1.1   安装依赖的东西

由于mysql-proxy需要的依赖包比较多，在开始前先用yum将下面的包都安装一下：

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

3.1.2  编译安装lua

mysql-proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。

这里采用源码包进行安装,需要自行下载lua的源码包,实验的时候，放在/usr/local/src 目录，，下载地址：http://www.lua.org/ftp/  选择一个版本进行下载。

cd /usr/local/src

tar zvfx lua-5.1.4.tar.gz

cd lua-5.1.4

vi  src/Makefile

在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。

 

make linux

出现系列错误提示解决办法

make linux

在包含自 lua.h：16 的文件中，

从 lua.c：15:

luaconf.h:275:31: 错误：readline/readline.h：没有那个文件或目录

luaconf.h:276:30: 错误：readline/history.h：没有那个文件或目录

lua.c: In function ‘pushline’:

lua.c:182: 警告：隐式声明函数 ‘readline’

lua.c:182: 警告：赋值时将整数赋给指针，未作类型转换

lua.c: In function ‘loadline’:

lua.c:210: 警告：隐式声明函数 ‘add_history’

make[2]: *** [lua.o] 错误 1

make[2]: Leaving directory `/data0/software/lua-5.1.4/src’

make[1]: *** [linux] 错误 2

make[1]: Leaving directory `/data0/software/lua-5.1.4/src’

make: *** [linux] 错误 2

解决方法： yum install libtermcap-devel ncurses-devel libevent-devel readline-devel

重新make linux 

make install

 

cp etc/lua.pc   /usr/lib/pkgconfig/

export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig

3.2 下载mysql-proxy

下载：http://dev.mysql.com/downloads/mysql-proxy/ 一定要下载对应的版本，由于实验用的系统是centos6.6  32位，所以就选择了以下的安装包：

tar zxvf mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-32bit  /usr/local/mysql-proxy

3.3 配置mysql-proxy，创建主配置文件

cd /usr/local/mysql-proxymkdir 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.38.151:4040 #mysql-proxy运行ip和端口，不加端口，默认4040proxy-read-only-backend-addresses=192.168.38.153 #指定后端从slave读取数据proxy-backend-addresses=192.168.38.152 #指定后端主master写入数据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 #定义log日志级别daemon=true    #以守护进程方式运行keepalive=true #mysql-proxy崩溃时，尝试重启

#保存退出！

chmod 660 /etc/mysql-porxy.cnf

3.4  配置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"        }endfunction 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_RESULTend

3.5 修改读写分离配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.luaif not proxy.global.config.rwsplit then proxy.global.config.rwsplit = {  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1  max_idle_connections = 1, #默认8，改为1  is_debug = false }end

4、启动mysql-proxy

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnfnetstat -tupln | grep 4000 #已经启动killall -9 mysql-proxy #关闭mysql-proxy使用

5. 测试读写分离

首先在从的mysql上停了slave  stop;

然后，再测试，否则因为主从备份了，看不出效果。

或者，不停掉slave，可以在主从的/etc/my.cnf配置文件中，增加日志:

log = /data/mysql/mysql.log 的配置。

然后，再重启主从mysql服务器。然后，在主从分别输入：tail  -F   /data/mysql/mysql.log

观察数据库的日志记录。

启动mysql-proxy 后，

登陆mysql-proxy:    mysql  -uproxyuser  -p123456  -h192.168.38.151   -P4040

然后，use   db1;

然后，

mysql> create table user_proxy (number INT(10),name VARCHAR(255));

mysql> insert into user_proxy values(01,'zhangsan');

mysql> insert into user_proxy values(02,'lisi');

mysql>select   *  from   user_proxy;

然后，查看主从的日志，发现只有在192.168.38.153这台从服务器上有select的记录，其他写入的记录在主服务器上有，但是由于主从还有一个复制的功能，所以，在从服务器上也有写入的记录。

到这里，基本上实验读写分离也是成功了。

本文出自 “dayAndNight” 博客，请务必保留此出处http://hellocjq.blog.51cto.com/11336969/1892094