mysql proxy atlas_mysql-proxy Atlas

mysql上配置：

主：

上执行GRANT REPLICATION SLAVE ON *.* to 'rep1'@192.168.14.236

identified by 'mysql';

2.修改my.cnf，开启log-bin，设置server-id的值，这个值不能重复。

3.锁表，FLUSH TABLES WITH READ LOCK; 记录日志，show master

status;备份mysql的数据库。完成后解锁，UNLOCK TABLES;

3.将备份的数据库传到从库相应目录

从：

1.在stop slave && change master to

master_host='192.168.14.235', master_user='rep1',

master_password='mysql', master_log_file='mysql-bin.xx',

master_log_pos=xx; &&start slave

2.看状态show slave status\G;

同步成功后，安装mysql-proxy atlas

增加mysql-proxy用户：

grant all privileges on ceshi.* to proxy@'192.168.%.%'

identified by 'ceshi';

安装Atlas：

# wget

https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm

# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

Preparing...  ################################# [100%]

Updating / installing...

1:Atlas-2.2.1-1

################################# [100%]

编辑配置文件:

Atlas的安装目录是/usr/local/mysql-proxy,进入安装目录下的conf目录，可以看到已经有一个名为test.cnf的默认配置文件，里面有很详细配置项具解释

增加一个测试文件：

[root@localhost conf]# cat ceshi.conf

[mysql-proxy]

admin-username = admin

admin-password = zhaopin

proxy-backend-addresses = 192.168.14.235:3306

proxy-read-only-backend-addresses = 192.168.14.236:3306

pwds = proxy:O4k/oI7B92g=

daemon = true

keepalive = true

event-threads = 8

log-level = message

log-path = /usr/local/mysql-proxy/log

proxy-address = 0.0.0.0:1234

admin-address = 0.0.0.0:2345

charset = utf8

密码生成：

# /usr/local/mysql-proxy/bin/encrypt ceshi

O4k/oI7B92g=

启动：

/usr/local/mysql-proxy/bin/mysql-proxy

--defaults-file=/usr/local/mysql-proxy/conf/ceshi.conf

测试：

通过mysql-proxy插入数据和读取数据，这里只给出结果

insert全在主mysql：

[root@localhost ~]# grep insert

/usr/local/mysql-proxy/log/sql_ceshi.log |awk '{print $4}'|sort

|uniq -c

41

S:192.168.14.235:3306

select全在从msql：

[root@localhost ~]# grep select

/usr/local/mysql-proxy/log/sql_ceshi.log |awk

'{print $4}'|sort |uniq -c

114

S:192.168.14.236:3306

停掉mysql从库

select全部转移到主库：

[root@localhost ~]# grep select

/usr/local/mysql-proxy/log/sql_ceshi.log |awk '{print $4}'|sort

|uniq -c

22

S:192.168.14.235:3306

停掉主库：

select不受影响

insert报错：

mysql> insert into MyClass values('a','5');

ERROR 2013 (HY000): Lost connection to MySQL server during

query

安装过程中报错：

2016-12-16 15:40:56: (critical) proxy-plugin.c.1450: I have no

server backend, closing connection

2016-12-16 15:40:56: (critical) network-mysqld.c.1387:

plugin_call(CON_STATE_READ_QUERY) failed

2016-12-16 15:40:59: (critical) proxy-plugin.c.1450: I have no

server backend, closing connection

2016-12-16 15:40:59: (critical) network-mysqld.c.1387:

plugin_call(CON_STATE_READ_QUERY) failed

2016-12-16 15:41:04: (critical) proxy-plugin.c.1450: I have no

server backend, closing connection

2016-12-16 15:41:04: (critical) network-mysqld.c.1387:

plugin_call(CON_STATE_READ_QUERY) failed

解决：

grant 用户是出错，delete重新建立即可