Linux安全网 - Linux操作系统_Linux 命令_Linux教程_Linux黑客

会员投稿 投稿指南 本期推荐: 强烈推荐:Ylmf OS 3.0 正式版发布附迅雷高 XP系统下硬盘安装Fedora 14图文教程
搜索:
您的位置: Linux安全网 > Linux入门 > » 正文

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践

来源: wjpinrain 分享至:

 

  实验环境: 10.10.4.81    mysql-proxy 10.10.4.82    mysql master ---write 10.10.4.83    mysql slave   ---read 三台服务器上均搭建mysql服务 82、83主从同步详细过程略 81mysql-proxy   一、编译安装lua mysql-proxy的读写分离主要是通过rw-splitting.lua脚本实现的,因此需要安装lua. wget http://www.lua.org/ftp/lua-5.1.4.tar.gz #tar zxvf 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 install #cp etc/lua.pc /usr/lib/pkgconfig/ #export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig   二、安装配置mysql-proxy 测试平台为centos 32位,因此选择32位的软件包 #wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz #tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz #mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy 创建mysql-proxy服务管理脚本 #mkdir /opt/mysql-proxy/init.d/ #vi /opt/mysql-proxy/init.d/mysql-proxy vim mysql-proxy #!/bin/sh # # mysql-proxy This script starts and stops the mysql-proxy daemon # # chkconfig: - 78 30 # processname: mysql-proxy # description: mysql-proxy is a proxy daemon to mysql   # Source function library. . /etc/rc.d/init.d/functions   #PROXY_PATH=/usr/local/bin PROXY_PATH=/opt/mysql-proxy/bin   prog="mysql-proxy"   # Source networking configuration. . /etc/sysconfig/network   # Check that networking is up. [ ${NETWORKING} = "no" ] && exit 0   # Set default mysql-proxy configuration. #PROXY_OPTIONS="--daemon" PROXY_OPTIONS="--admin-username=root --admin-password=password --proxy-read-only-backend-addresses=10.10.4.83:3306 --proxy- backend-addresses=10.10.4.82:3306 --admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua --proxy-lua- script=/opt/mysql-proxy/scripts/rw-splitting.lua" PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid   # Source mysql-proxy configuration. if [ -f /etc/sysconfig/mysql-proxy ]; then         . /etc/sysconfig/mysql-proxy fi   PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH   # By default it's all good RETVAL=0   # See how we were called. case "$1" in  start)         # Start daemon.         echo -n $"Starting $prog: "         $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=debug -- log-file=/opt/mysql-proxy/log/mysql-proxy.log         RETVAL=$?         echo         if [ $RETVAL = 0 ]; then                 touch /var/lock/subsys/mysql-proxy         fi        ;;  stop)         # Stop daemons.         echo -n $"Stopping $prog: "         killproc $prog         RETVAL=$?         echo         if [ $RETVAL = 0 ]; then                 rm -f /var/lock/subsys/mysql-proxy                 rm -f $PROXY_PID         fi        ;;  restart)         $0 stop         sleep 3         $0 start        ;;  condrestart)        [ -e /var/lock/subsys/mysql-proxy ] && $0 restart       ;;  status)         status mysql-proxy         RETVAL=$?        ;;  *)         echo "Usage: $0 {start|stop|restart|status|condrestart}"         RETVAL=1        ;; esac   exit $RETVAL   脚本详解注释:/opt/mysql-proxy/libexec/mysql-proxy --admin-username=root --admin-password=password --proxy-read-only-backend -addresses=10.10.4.83:3306 --proxy-backend-addresses=10.10.4.82:3306 --admin-lua-script=/opt/mysql-proxy/lib/mysql- proxy/lua/admin.lua --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua --daemon --pid-file=/opt/mysql- proxy/run/mysql-proxy.pid --user=mysql --log-level=debug --log-file=/opt/mysql-proxy/log/mysql-proxy.log //--admin-username=root 指定管理员用户 //--admin-password=password 指定管理员密码 //--proxy-read-only-backend-address=10.10.4.83:3306只读数据库 //--proxy-backend-address=10.10.4.82:3306 写的数据库 //--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua lua管理脚本路径 //--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua 指定lua脚本,在这里,使用的是rw-splitting脚本,用于读写分离 //--daemon 采用daemon方式启动 //--pid-file=/opt/mysql-proxy/run/mysql-proxy.pid 定义mysql-proxyPID文件路径 //--user=mysql 以mysql用户身份启动服务 //--log-level=debug //定义log日志级别,由高到低分别有(error|warning|info|message|debug) //--log-file=/opt/mysql-proxy/log/mysql-proxy.log 定义log日志文件路径   #chmod +x /opt/mysql-proxy/init.d/mysql-proxy #mkdir /opt/mysql-proxy/run #mkdir /opt/mysql-proxy/log #mkdir /opt/mysql-proxy/scripts   三、配置并使用rw-splitting.lua读写分离脚本. 最新的脚本我们可以从最新的mysql-proxy源码包中获取 #wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz #tar xzvf mysql-proxy-0.8.1.tar.gz #cd mysql-proxy-0.8.1 #cp lib/rw-splitting.lua /opt/mysql-proxy/scripts 修改读写分离脚本 rw-splitting.lua 修改默认连接,进行快速测试,不修改的话达到连接数为4时才启动读写分离 #vi /opt/mysql-proxy/scripts/rw-splitting.lua -- connection pool if not proxy.global.config.rwsplit then proxy.global.config.rwsplit = { min_idle_connections = 1, //默认为4 max_idle_connections = 1, //默认为8 is_debug = false } end 修改完成后,启动mysql-proxy #/opt/mysql-proxy/init.d/mysql-proxy start   四、测试读写分离效果 创建用户读写分离的数据库连接用户 登录主数据库服务器10.10.4.82,通过命令行登录管理mysql服务器 为了清晰的看到读写分离的效果,需要暂时关闭mysql主从复制的功能。 #mysql -uroot -ppassword mysql>grant all privileges on test.* to 'root'@'%' identified by 'password';(82/83都授权) 在主mysql数据库中的test数据库常见表,并插入数据 mysql>use test; Database changed mysql>create table first_tb(id int(3),name char(10)); Query Ok, 1 row affected (0.00 sec) mysql>insert into first_tb values (1,'myself'); Query Ok, 1 row affected (0.00 sec) mysql>select * from first_tb; +------+-------+ | id   | name | +------+-------+ |    1 | myself | +------+-------+ 1 row in set (0.00 sec) 在从10.10.4.83mysql数据库中的test数据库创建表,并插入数据 mysql>use test; Database changed mysql>create table first_tb(id int(3),name char(10)); Query Ok, 1 row affected (0.00 sec) mysql>insert into first_tb values (2,'yours'); Query Ok, 1 row affected (0.00 sec) mysql>select * from first_tb; +------+-------+ | id   | name | +------+-------+ |    2 | yours | +------+-------+ 1 row in set (0.00 sec) 两个环境均已搭建OK 然后在10.10.4.81mysql-proxy服务器上登录 #mysql -uroot -ppassword -h10.10.4.81 -P4040 test mysql> use test; Database changed mysql> select * from first_tb; +------+--------+ | id   | name   | +------+--------+ |    1 | myself | +------+--------+ 4 rows in set (0.00 sec) 显示读取的数据为10.10.4.82主数据库中的数据 再重新打开一个mysql-proxy终端同样select一下 #mysql -uroot -ppassword -h10.10.4.81 -P4040 test mysql> use test; Database changed mysql> select * from first_tb; +------+--------+ | id   | name   | +------+--------+ |    2 | yours | +------+--------+ 4 rows in set (0.00 sec) 再之后的连接请求过来,select到的全部为10.10.4.83从库上的数据; 然后在10.10.4.81mysql-proxy上插入一条数据: #mysql -uroot -ppassword -h10.10.4.81 -P4040 test mysql> use test; mysql>insert into first_tb values (110,'second'); 然后分别去主从服务器上查看信息 mysql> select * from first_tb; (主10.10.4.82) +------+--------+ | id   | name   | +------+--------+ |    1 | myself |110 | second | +------+--------+ 4 rows in set (0.00 sec) mysql> select * from first_tb; (从10.10.4.83) +------+--------+ | id   | name   | +------+--------+ |    2 | yours | +------+--------+ 4 rows in set (0.00 sec) 可见写到主数据库(10.10.4.82)中了,读写分离完成! 由此,我们已经实现了mysql读写分离,目前所有的写操作都全部在master主服务器上,用来避免数据的不同步; 另外,所有的读操作都分摊给了slave从服务器,分担数据库的压力     后注: [root@kdq81 ~]# mysql -uroot -ppassword -h127.0.0.1 -P4041 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.99-agent-admin   Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> select * from help; +------------------------+------------------------------------+ | command                | description                        | +------------------------+------------------------------------+ | SELECT * FROM help     | shows this help                    | | SELECT * FROM backends | lists the backends and their state | +------------------------+------------------------------------+ 2 rows in set (0.00 sec)   mysql> select * from backends; +-------------+-----------------+-------+------+------+-------------------+ | backend_ndx | address         | state | type | uuid | connected_clients | +-------------+-----------------+-------+------+------+-------------------+ |           1 | 10.10.4.82:3306 | up    | rw   | NULL |                 0 | |           2 | 10.10.4.83:3306 | up    | ro   | NULL |                 0 | +-------------+-----------------+-------+------+------+-------------------+ 2 rows in set (0.00 sec) backend_ndx:1表示该数据库是主库,2表示该数据库是从库 state:提示DB处于一个什么状态 在/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua中有定义 state有三种状态:up /down/unkown type有三种状态:rw /ro /unknow

Tags:
分享至:
最新图文资讯
1 2 3 4 5 6
验证码:点击我更换图片 理智评论文明上网,拒绝恶意谩骂 用户名:
关于我们 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 发展历史