简单mysql读写分离操作

主从配置：

主:

log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 1

binlog-do-db    = cacti
binlog-ignore-db        = mysql  

添加两行，默认日志不变，要同步的库为cacti，忽略mysql

从:

log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id       = 2

master-host     = 192.168.1.174
master-user     = root
master-pass     = google

master-port     = 3306
master-connect-retry    = 30
replicate-do-db = cacti
replicate-ignore-db     = mysql

 

安装mysql-proxy

 

wget
tar -zxvf mysql-proxy-0.8.0-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.0-linux-rhel5-x86-32bit mysql-proxy
mv -f mysql-proxy /usr/local

 

cat run.sh
/usr/local/mysql-proxy/bin/mysql-proxy --proxy-read-only-backend-addresses=192.168.1.246:3306 --proxy-backend-addresses=192.168.1.174  --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua >/root/mysql-proxy.log&

 

运行脚本，就可以测试了

/usr/local/mysql/bin/mysql -h 127.0.0.1 -P 4040 -u root -p

 

测试是否读写分离，我这里正好用的是cacti的库，正好可以测试，先切到从，sotp slave

然后在cacti上修改admin的密码，，然后再退出，这时候你用新的密码登录，是登录不上来的，证明写已经可以了，然后start slave,,等数据同步完之后，就可以用新密码登录了，读也是OK的