php atlas,使用Atlas实现读写分离

最新推荐文章于 2021-03-22 23:33:04 发布

鸳鸯蝴蝶派

最新推荐文章于 2021-03-22 23:33:04 发布

阅读量203

点赞数

文章标签： php atlas

Atlas实现读写分离

环境介绍：

试验机器4台：

192.168.0.41 Atlas

192.168.0.42 Master

192.168.0.43 Slave-1

192.168.0.44 Slave-2

4台机器均为RHEL 6.4 x86_64系统

[root@Nginx ~]# uname -a

Linux Nginx 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux

MySQL版本为5.5.21

防火墙及SElinux全部关闭；无VIP；

试验机的主从已搭建完毕，数据一致；

安装情况：

安装包使用的是RPM包

Atlas-2.2.el6.x86_64.rpm

使用RPM -hivAtlas-2.2.el6.x86_64.rpm

安装成功

配置文件：

[mysql-proxy]

#带#号的为非必需的配置项目

#管理接口的用户名

admin-username=admin

#管理接口的密码

admin-password=123

#Atlas后端连接的MySQL主库的IP和端口，可设置多项，用逗号分隔

proxy-backend-addresses=192.168.0.42:3306

#Atlas后端连接的MySQL从库的IP和端口，@后面的数字代表权重，用来作负载均衡，若省略则默认为1，可设置多项，用逗号分隔

proxy-read-only-backend-addresses=192.168.0.43:3306,192.168.0.44:3306

#用户名与其对应的加密过的MySQL密码，密码使用PREFIX/bin目录下的加密程序encrypt加密，下行的user1和user2为示例，将其替换为你的MySQL的用户名和加密密码！

pwds=atlas:3yb5jEku5h4=

#设置Atlas的运行方式，设为true时为守护进程方式，设为false时为前台方式，一般开发调试时设为false，线上运行时设为true,true后面不能有空格。

daemon=true

#设置Atlas的运行方式，设为true时Atlas会启动两个进程，一个为monitor，一个为worker，monitor在worker意外退出后会自动将其重启，设为false时只有worker，没有monitor，一般开发调试时设为false，线上运

行时设为true,true后面不能有空格。

keepalive=true

#工作线程数，对Atlas的性能有很大影响，可根据情况适当设置

event-threads=4

#日志级别，分为message、warning、critical、error、debug五个级别

log-level=message

#日志存放的路径

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

#SQL日志的开关，可设置为OFF、ON、REALTIME，OFF代表不记录SQL日志，ON代表记录SQL日志，REALTIME代表记录SQL日志且实时写入磁盘，默认为OFF

sql-log=ON

#慢日志输出设置。当设置了该参数时，则日志只输出执行时间超过sql-log-slow(单位：ms)的日志记录。不设置该参数则输出全部日志。

#sql-log-slow = 10

#实例名称，用于同一台机器上多个Atlas实例间的区分

instance=test

#Atlas监听的工作接口IP和端口

proxy-address=0.0.0.0:1234

#Atlas监听的管理接口IP和端口

admin-address=0.0.0.0:2345

#分表设置，此例中person为库名，mt为表名，id为分表字段，3为子表数量，可设置多项，以逗号分隔，若不分表则不需要设置该项

#tables = person.mt.id.3

#默认字符集，设置该项后客户端不再需要执行SET NAMES语句

charset = utf8

#允许连接Atlas的客户端的IP，可以是精确IP，也可以是IP段，以逗号分隔，若不设置该项则允许所有IP连接，否则只允许列表中的IP连接

client-ips=127.0.0.1,0.192.168.0

#Atlas前面挂接的LVS的物理网卡的IP(注意不是虚IP)，若有LVS且设置了client-ips则此项必须设置，否则可以不设置

#lvs-ips = 192.168.1.1

========================华丽的分割线============================

根据上面的配置文件，得到账户信息如下：

Atlas管理账号：admin

Atlas管理账号密码：123

Atlas管理账号端口:2345

Atlas工作账号：atlas

Atlas工作账号密码：atlas:3yb5jEku5h4=(123,用encrypt编译过的)

Atlas工作账号端口:1234

在主库及从库机器进行账号授权：

mysql>GRANT all privileges ON *.* TO 'atlas'@'192.168.0.%' IDENTIFIED BY '123';

3台机器全部如此授权，在Atlas服务器上远程登陆3台机器成功.

在Atlas服务器验证Atlas管理账号及工作账号：

[root@Nginx bin]#/usr/local/mysql-proxy/bin/mysql-proxyd test start

OK: MySQL-Proxy of test isstarted

[root@Nginx bin]# ps -ef | grep mysql | grep -v grep

root 6638 5610 0 02:00 pts/2 00:00:00 mysql -h127.0.0.1 -P1234 -uatlas -px x

root 6667 1919 0 02:01 pts/1 00:00:00 mysql -h127.0.0.1 -P2345 -uadmin -px x

root 6695 1 0 02:06 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf

root 6696 6695 0 02:06 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf

[root@Nginx ~]#mysql -h127.0.0.1 -P2345 -uadmin -p123

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.99-agent-admin

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>select * from help;

+-------------------+---------------------------------------+

| command | description |

+-------------------+---------------------------------------+

| SELECT * FROM help | shows this help |

| SELECT * FROM backends | lists the backends and their state |

| SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id |

| SET ONLINE $backend_id | online backend server, ... |

| ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... |

| ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... |

| REMOVE BACKEND $backend_id | example: "remove backend 1", ... |

| ADD CLIENT $client | example: "add client 192.168.1.2", ... |

| REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... |

| SAVE CONFIG | save the backends to config file |

+-------------------+---------------------------------------+

10 rows in set (0.00 sec)

mysql> select * from backends;

+-------------+-------------------+-------+------+

+-------------+-------------------+-------+------+

| 1| 192.168.0.42:3306|down| rw|

| 2| 192.168.0.44:3306|down| ro|

| 3| 192.168.0.43:3306|down| ro|

+-------------+-------------------+-------+------+

3 rows in set (0.00 sec)

噩梦就此开始

登陆atlas工作账号：

[root@Nginx ~]# mysql -h127.0.0.1 -P1234 -uatlas -p123

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.0.81-log

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 2

Current database: *** NONE ***

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

mysql>

无法连接到服务器

在管理账号中，将所有机器上线

mysql> SET ONLINE 1;

+-------------+-------------------+---------+------+

+-------------+-------------------+---------+------+

| 1| 192.168.0.42:3306|unknown| rw|

+-------------+-------------------+---------+------+

1 row in set (0.00 sec)

再查看backends状态：

mysql> select * from backends;

+-------------+-------------------+-------+------+

+-------------+-------------------+-------+------+

| 1| 192.168.0.42:3306|down| rw|

| 2| 192.168.0.44:3306|down| ro|

| 3| 192.168.0.43:3306|down| ro|

+-------------+-------------------+-------+------+

3 rows in set (0.00 sec)

依然如此

参照网上的方法，在3台主从机上的my.cnf中加入以下参数：

max_connect_errors=100000

max_allowed_packet=16777216 (16M)

依然不行

mysql> select * from backends;

+-------------+-------------------+-------+------+

+-------------+-------------------+-------+------+

| 1| 192.168.0.42:3306|down| rw|

| 2| 192.168.0.44:3306|down| ro|

| 3| 192.168.0.43:3306|down| ro|

+-------------+-------------------+-------+------+

3 rows in set (0.00 sec)

咨询开发人员，得知：

client-ips=127.0.0.1,0.192.168.0

此选项，如非必须，不要打开

故障排除后，进行读写分离测试

在atlas客户端，创建数据库

mysql> create database atlas；

Query OK, 1 row affected (10.02 sec)

mysql> create table atlas1

-> (

-> name varchar(15) not null,

-> sex enum('M','F')not null,

-> age int(2) not null,

-> primary key('name')

-> );

mysql> desc atlas.atlas1;

+-------+---------------+------+-----+---------+-------+

+-------+---------------+------+-----+---------+-------+

| name| varchar(15)| NO| PRI| NULL||

| sex| enum('M','F')| NO|| NULL||

| age| int(2)| NO|| NULL||

+-------+---------------+------+-----+---------+-------+

3 rows in set (0.01 sec)

插入数据：

mysql> insert into atlas.atlas1

-> VALUES

-> ('Zhangxuan','M','30'),

-> ('Yangyanjie','M','28'),

-> ('Liuchang','F','22');

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

在主库上进行一致性检查：

[root@MA ~]# pt-table-checksum --user=root --host=192.168.0.42 --nocheck-replication-filters --nocheck-binlog-format

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

10-28T04:36:06 0 0 3 1 0 0.028 atlas.atlas1

10-28T04:36:06 0 0 51 1 0 0.027 mhatest.mha

10-28T04:36:06 0 0 3012 1 0 0.282 mhatest.num1

10-28T04:36:06 0 0 5 1 0 0.029 mhatest.test

成功，然后在从库1(192.168.0.43)和从库2(192.168.0.43)上各增加一条数据

从库1(192.168.0.43)：

mysql> INSERT INTO atlas.atlas1 VALUES ('Yangzhenyu','M','30');

Query OK, 1 row affected (0.01 sec)

mysql> select * from atlas1;

+--------+---+--+

| name | sex | age |

+--------+---+--+

| Liuchang | F | 22 |

| Yangyanjie | M | 28 |

| Yangzhenyu | M | 30|

| Zhangxuan | M | 30 |

+--------+---+---+

4 rows in set (0.00 sec)

从库2(192.168.0.44)

mysql> INSERT INTO atlas.atlas1 VALUES ('Chenfei','M','14')；

mysql> select * from atlas.atlas1;

+--------+---+--+

| name | sex | age |

+--------+---+---+

|Chenfei | M | 14|

| Liuchang | F | 22 |

| Yangyanjie | M | 28 |

| Zhangxuan | M | 30 |

+--------+---+---+

4 rows in set (0.00 sec)

此时，主库(192.168.0.42)上：

mysql> select * from atlas.atlas1;

+--------+---+---+

| name | sex | age |

+--------+---+---+

| Liuchang | F | 22 |

| Yangyanjie | M | 28 |

| Zhangxuan | M | 30 |

+--------+---+---+

3 rows in set (0.00 sec)

开始验证读写分离：

在atlas服务器上执行：

mysql> select * from atlas.atlas1 where name='Yangzhenyu';

Empty set (0.00 sec)/*第一次执行居然无返回值*/

mysql> select * from atlas.atlas1 where name='Yangzhenyu';

+------------+-----+-----+

| name | sex | age |

+------------+-----+-----+

| Yangzhenyu | M | 30 |

+------------+-----+-----+

1 row in set (0.00 sec)/*第二次正确返回*/

查询日志：

[10/28/2014 03:10:07] C:127.0.0.1:39798 S:192.168.0.43:3306OK 0.719 "select * from atlas.atlas1 where name='Yangzhenyu'"

[10/28/2014 03:10:10] C:127.0.0.1:39798 S:192.168.0.44:3306OK 0.921 "select * from atlas.atlas1 where name='Yangzhenyu'"

第一次无返回值原因：两个read-only库数据不一致导致，将两个从库数据一致后，查询

返回值正常：

mysql> select * from atlas.atlas1 where name='Yangzhenyu';

+--------+---+---+

| name | sex | age |

+--------+---+---+

| Yangzhenyu | M | 30 |

+--------+---+---+

1 row in set (0.00 sec)/*第一次查询*/

mysql> select * from atlas.atlas1 where name='Yangzhenyu';

+--------+---+---+

| name | sex | age |

+--------+---+---+

| Yangzhenyu | M | 30 |

+--------+---+---+

1 row in set (0.00 sec)/*第二次查询*/

读写分离成功

时间有限，下一步准备用sysbench进行压力测试

鸳鸯蝴蝶派

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
php atlas,使用Atlas实现读写分离

Atlas实现读写分离环境介绍：试验机器4台：192.168.0.41Atlas192.168.0.42Master192.168.0.43Slave-1192.168.0.44Slave-24台机器均为RHEL6.4x86_64系统[root@Nginx~]#uname-aLinuxNginx2.6.32-358.el6.x86_64#1SMPTue...
复制链接

扫一扫