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