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重新建立即可