mysql-proxy
本文系统Centos6.0
MySQL代理是一个介于MySQL客户端和MySQL服务器之间的简单程序，可用来监视、分析或者传输他们之间的通讯。他的灵活性允许你最大限度的使用它，这些包括负载平衡、容错 、分析查询和修正等等。
因此，一般来说都是通过主从复制（Master-Slave）的方式来同步数据，在通过读写分离(MySQL-Proxy)来提升数据的并发能力这样的方案来进行实施与 部
署的。
master :172.16.1.88:13267
slave :172.16.1.88:13268
注意我在一台上面搭建了两个数据，
master登录mysql的方式mysql -u root -p123 -S /data/mysql_13267/mysql.sock
slave登录mysql的方式mysql -u root -S /data/mysql_13268/mysql.sock
一、mysql主从复制
1.1主数据库服务器上面进行操作
授权给从数据库服务器172.16.1.88
mysql> grant replication slave on *.* to cluster@'172.16.1.88' identified by 'cluster';
Query OK, 0 rows affected (0.00 sec)
查看主数据状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记录下FILE及Position的值，在后面进行从数据库操作的时候需要用到。
1.2配置从服务器
修改从服务器的配置文件/etc/13268/my.cnf
service-id=10
log-bin=mysql-bin
启动mysql
执行同步SQL语句
mysql> change master to master_host='172.16.1.88', master_user='cluster', master_password='cluster', master_log_file='mysql-bin.000001', master_log_pos=107,master_port=1327;
正确执行后启动Slave同步进程
mysql> start slave;
Query OK, 0 rows affected (2.68 sec)
主从同步检查
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.88
Master_User: cluster
Master_Port: 13267
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 262
Relay_Log_File: asdasda-relay-bin.000003
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 262
Relay_Log_Space: 566
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
1 row in set (0.00 sec)
其中Slave_IO_Running与Slave_SQL_Running的值必须都为YES，才表明状态正常.
1)如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理:
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
2)查看主数据库状态
mysql> show master status;
3)记录FILE及Position的值.
将主服务器的数据文件(数据存放的目录)复制到从服务器，建议通过tar归档压缩在传到从服务器解压.
4）取消主数据锁定
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
1.3验证主从复制效果
主服务器上的操作
在主服务器上创建数据库first_db
mysql> create database first_db;
Query OK, 1 row affected (0.00 sec)
在主服务器上创建表first_tb
mysql> use first_db;
Database changed
mysql> create table first_tb(id int(3),name char(10));
Query OK, 0 rows affected (2.95 sec)
在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,'myself');
Query OK, 1 row affected (0.00 sec)
在从服务器查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fightprototype |
| first_db |
| mysql |
| performance_schema |
| test |
| zhangfang |
+--------------------+
7 rows in set (0.00 sec)
mysql> use first_db;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_first_db |
+--------------------+
| first_tb |
+--------------------+
1 row in set (0.00 sec)
由此，整个mysql主从复制过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。
二，MySQL读写分离
master :172.16.1.88:13267
slave :172.16.1.88:13268
mysql_proxy:172.16.1.89
以下操作均在172.16.1.89即mysql_proxy调度服务器上进行的。
2.1Mysql的安装，自行安装
2.2检查系统需要软件包
gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib*
若确实相关软件包，可以通过yum -install 方式在线安装。
2.3编译安装lua
mysql-proxy的读写分离主要是通过rw-splitting.lua脚本实现的,因此需要安装lua
[root@mysql_proxy ~]# wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
[root@mysql_proxy ~]# tar zxf lua-5.1.4.tar.gz
[root@mysql_proxy ~]# cd lua-5.1.4
[root@mysql_proxy lua-5.1.4]# vim src/Makefile
在CFLAGS=-02 -Wall $(MYCFLAGS)这一行记录里面加上-fPIC，更改为CFLAGS=-02 -Wall $(MYCFLAGS)来避免编译过程中出现错误.
[root@mysql_proxy lua-5.1.4]# make linux
[root@mysql_proxy lua-5.1.4]# make install
[root@mysql_proxy lua-5.1.4]# cp etc/lua.pc /usr/lib64/pkgconfig/
[root@mysql_proxy lua-5.1.4]# export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib64/pkgconfig/
三、安装配置mysql-proxy
测试平台centos64，选择64位包，可以根据自己的环境来选择相应的版本,我在这里用的是08.3
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy
[root@mysql_proxy ~]# tar mysql-proxy-0.8.3-linux-sles10-x86-64bit.tar.gz
[root@mysql_proxy ~]# mv mysql-proxy-0.8.3-linux-sles10-x86-64bit /opt/mysql-proxy/
创建mysql-proxy服务器管理脚本
[root@mysql_proxy ~]# mkdir /opt/mysql-proxy/init.d/
[root@mysql_proxy ~]# cat /opt/mysql-proxy/init.d/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=" --proxy-read-only-backend-addresses=172.16.1.88:13268 --proxy-backend-addresses=172.16.1.88:13267 --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
