Written by arstercz - 2014-02-10

360-Atlas中间件-测试及使用说明

概要说明

Atlas作为中间件存在于应用和DB之间，为应用提供统一的服务接口，中间件实现读写和分离功能，避免开发人员的额外开发操作，就可以达到读写分离的目的，多slave可实现按权重均衡负载，如果应用端够强壮，可以忽略掉Atlas的sql注入检测功能。管理接口以lua语言实现，易扩展，方便问题的排查和追踪。

Atlas为360公司在MySQl-Proxy 0.8.2版本所作的而次开发二开源出来的软件，有商业公司的支持，不必担心没有技术支持，异常问题得不到解决等因素。

架构说明

                              - | master |
                            /
   | app | --> |z6 (Atlas)|   - | slave1 |
                            \
                              - | slave2 |

安装环境说明

1.z10主机(DB)
# Percona Toolkit System Summary Report ######################
        Date | 2014-02-18 06:55:26 UTC (local TZ: CST +0800)
    Hostname | z10
      System | Dell Inc.; PowerEdge 1950; vNot Specified ()
    Platform | Linux
     Release | CentOS release 5.5 (Final)
      Kernel | 2.6.35.5.R610.CentOS5.5-x64.OpenBeta.KVM.MPT
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.5
    Compiler | GNU CC version 4.1.2 20080704 (Red Hat 4.1.2-48).
     SELinux | Disabled
 Virtualized | No virtualization detected
# Processor ##################################################
  Processors | physical = 2, cores = 4, virtual = 4, hyperthreading = no
      Speeds | 4x1595.883
      Models | 4xIntel(R) Xeon(R) CPU 5110 @ 1.60GHz
      Caches | 4x4096 KB
# Memory #####################################################
       Total | 3.9G
        Free | 45.8M
        Used | physical = 3.8G, swap allocated = 15.3G, swap used = 26.5M, virtual = 3.9G

MySQL Server安装说明

  The MySQL Sandbox,  version 3.0.43
  #make_replication_sandbox Percona-Server-5.5.30-rel30.2-500.Linux.i686.tar.gz 

    +  master 10.3.254.110 22585
    +- slave1 10.3.254.110 22586
    +- slave2 10.3.254.110 22587

测试集： z10:/home/arstercz/employees_db-full-1.0.6.tar.bz2

Atlas安装说明

z6主机  Atlas-2.0.5-1.x86_64

配置说明

[root@z6 ~]# cat /usr/local/mysql-proxy/conf/employees.cnf 
[mysql-proxy]

admin-username = admin
admin-password = xxxxxxx
admin-lua-script = /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
proxy-backend-addresses = 10.3.254.110:22585
proxy-read-only-backend-addresses = 10.3.254.110:22586@1, 10.3.254.110:22587@2
pwds = emp:1k5jfwi2K28=, test:1k5jfwi2K28=

daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log

sql-log = OFF

instance = employees
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
charset = utf8

#tables = person.mt.id.3
#client-ips = 127.0.0.1, 192.168.1
#lvs-ips = 192.168.1.1

启动mysql-proxy(每个proxy代理一个instance实例库，多个库可启动多个proxy进程，每个conf文件单独配置):

/usr/local/mysql-proxy/bin/mysql-proxyd employees start

query 连接测试

管理语句

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'show tables'
+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+

select

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees limit 2'
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
+--------+------------+------------+-----------+--------+------------+
[root@z6 ~]# 
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees limit 1000,2'
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  11001 | 1956-04-16 | Baziley    | Buchter   | F      | 1987-02-23 |
|  11002 | 1952-02-26 | Bluma      | Ulupinar  | M      | 1996-12-23 |
+--------+------------+------------+-----------+--------+------------+
[root@z6 ~]# 
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees limit 11000,2'
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  21001 | 1960-07-09 | Katsuyuki  | Penn      | M      | 1985-04-21 |
|  21002 | 1963-04-21 | Koldo      | Gustavson | M      | 1994-05-31 |
+--------+------------+------------+-----------+--------+------------+

join

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees join dept_emp on (employees.emp_no = dept_emp.emp_no) and 
employees.emp_no = 10001'
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | emp_no | dept_no | from_date  | to_date    |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  10001 | d005    | 1986-06-26 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees join dept_emp on (employees.emp_no = dept_emp.emp_no) and 
employees.emp_no = 10010'
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  | emp_no | dept_no | from_date  | to_date    |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |  10010 | d004    | 1996-11-24 | 2000-06-26 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |  10010 | d006    | 2000-06-26 | 9999-01-01 |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+

union

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees where emp_no = 10001 union select * from employees where emp_no = 20010'
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  20010 | 1961-01-26 | Saniya     | Veccia    | M      | 1997-06-16 |
+--------+------------+------------+-----------+--------+------------+

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees where emp_no = 100010 union select * from employees where emp_no = 30070' 
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 100010 | 1959-11-06 | Youpyo     | Aamodt    | M      | 1991-10-04 |
|  30070 | 1958-06-11 | Howell     | Berendt   | M      | 1986-05-26 |
+--------+------------+------------+-----------+--------+------------+

update

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'update employees set birth_date = "1987-02-16" where emp_no = 100010'
[root@z6 ~]# 
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'update employees set birth_date = "1987-02-16" where emp_no = 30070' 
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from employees where emp_no = 100010 union select * from employees where emp_no = 30070' 
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 100010 | 1987-02-16 | Youpyo     | Aamodt    | M      | 1991-10-04 |
|  30070 | 1987-02-16 | Howell     | Berendt   | M      | 1986-05-26 |
+--------+------------+------------+-----------+--------+------------+

insert

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'insert into departments values ("d010","IT")'     
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from departments where dept_no = "d010"'        
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d010    | IT        |
+---------+-----------+

delete

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'delete from departments where dept_no = "d010"'         
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from departments where dept_no = "d010"'                         
[root@z6 ~]#

管理接口

[root@z6 ~]# mysql -h 10.3.254.106 -P 2345 -u admin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

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", ...                        |
+----------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

后端服务节点

mysql> select * from backends;
+-------------+--------------------+-------+------+
| backend_ndx | address            | state | type |
+-------------+--------------------+-------+------+
|           1 | 10.3.254.110:22585 | up    | rw   |  --> master
|           2 | 10.3.254.110:22586 | up    | ro   |  --> slave
|           3 | 10.3.254.110:22587 | up    | ro   |  --> slave
+-------------+--------------------+-------+------+
3 rows in set (0.00 sec)

中间件中离线后端节点以使不对外服务，但不会影响MySQL的主从服务

mysql> set offline 3;
+-------------+--------------------+---------+------+
| backend_ndx | address            | state   | type |
+-------------+--------------------+---------+------+
|           3 | 10.3.254.110:22587 | offline | ro   |
+-------------+--------------------+---------+------+
1 row in set (0.00 sec)

mysql> select * from backends;
+-------------+--------------------+---------+------+
| backend_ndx | address            | state   | type |
+-------------+--------------------+---------+------+
|           1 | 10.3.254.110:22585 | up      | rw   |
|           2 | 10.3.254.110:22586 | up      | ro   |
|           3 | 10.3.254.110:22587 | offline | ro   |
+-------------+--------------------+---------+------+
3 rows in set (0.00 sec)

slave2 [localhost] {root} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 22585
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 168399291
               Relay_Log_File: mysql_sandbox22587-relay-bin.000005
                Relay_Log_Pos: 168399437
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#中间件上线一个后端节点，set时state为unknown状态，可以理解为prepare状态。

mysql> set online 3;
+-------------+--------------------+---------+------+
| backend_ndx | address            | state   | type |
+-------------+--------------------+---------+------+
|           3 | 10.3.254.110:22587 | unknown | ro   |
+-------------+--------------------+---------+------+
1 row in set (0.01 sec)

mysql> select * from backends;
+-------------+--------------------+-------+------+
| backend_ndx | address            | state | type |
+-------------+--------------------+-------+------+
|           1 | 10.3.254.110:22585 | up    | rw   |
|           2 | 10.3.254.110:22586 | up    | ro   |
|           3 | 10.3.254.110:22587 | up    | ro   |
+-------------+--------------------+-------+------+
3 rows in set (0.00 sec)

#中间件移除一个后端节点，节点不存在于对外服务中，不影响主从关系；

mysql> remove backend 3;
Empty set (0.00 sec)

mysql> select * from backends;
+-------------+--------------------+-------+------+
| backend_ndx | address            | state | type |
+-------------+--------------------+-------+------+
|           1 | 10.3.254.110:22585 | up    | rw   |
|           2 | 10.3.254.110:22586 | up    | ro   |
+-------------+--------------------+-------+------+
2 rows in set (0.00 sec)

slave2 [localhost] {root} ((none)) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rsandbox
                  Master_Port: 22585
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 168399291
               Relay_Log_File: mysql_sandbox22587-relay-bin.000005
                Relay_Log_Pos: 168399437
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

#中间件增加一个节点，不影响主从关系。

mysql> add slave 10.3.254.110:22587;
Empty set (0.00 sec)

mysql> select * from backends;      
+-------------+--------------------+-------+------+
| backend_ndx | address            | state | type |
+-------------+--------------------+-------+------+
|           1 | 10.3.254.110:22585 | up    | rw   |
|           2 | 10.3.254.110:22586 | up    | ro   |
|           3 | 10.3.254.110:22587 | up    | ro   |
+-------------+--------------------+-------+------+
3 rows in set (0.00 sec)

#中间件自动剔除宕机的Server，(DB宕机检测是单向的,挂掉后，根据一条SQL失败来设置为down,因为由检测线程来设置down，并不及时，而且易误报);

[arstercz@z10 node2]$ ./stop 

mysql> select * from backends;
+-------------+--------------------+-------+------+
| backend_ndx | address            | state | type |
+-------------+--------------------+-------+------+
|           1 | 10.3.254.110:22585 | up    | rw   |
|           2 | 10.3.254.110:22586 | up    | ro   |
|           3 | 10.3.254.110:22587 | down  | ro   |
+-------------+--------------------+-------+------+
3 rows in set (0.00 sec)

#master宕机， Atlas剔除master,不做更新服务

[arstercz@z10 rsandbox_Percona-Server-5_5_30]$ cd master/
[arstercz@z10 master]$ ./stop 

[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from departments where dept_no = "d011"'
ERROR 2006 (HY000) at line 1: MySQL server has gone away
[root@z6 ~]# mysql -h 10.3.254.106 -P 1234 -u emp -pxxxxxx -D employees -e 'select * from departments where dept_no = "d011"'
+---------+-----------+
| dept_no | dept_name |
+---------+-----------+
| d011    | IT1       |
+---------+-----------+
[root@z6 ~]# 

mysql> select * from backends;
+-------------+--------------------+-------+------+
| backend_ndx | address            | state | type |
+-------------+--------------------+-------+------+
|           1 | 10.3.254.110:22585 | down  | rw   |
|           2 | 10.3.254.110:22586 | up    | ro   |
|           3 | 10.3.254.110:22587 | up    | ro   |
+-------------+--------------------+-------+------+
3 rows in set (0.00 sec)

FAQ

为什么使用中间件? 提供统一的对外服务端口，避免开发人员在代码中实现额外的读写分离工作； DB后端架构的变更不影响应用的运行； read负载均衡的实现，避免切换主从因为InnoDB预热而带来的恶劣影响；自动剔除宕机的Server(按照SQL执行情况来判断，另外没有检测replication状态，后期关注官方的改善); </hr>
架构问题中间件以vip对外提供服务，尽管Atlas有自身的keepalive机制，但为避免单节点失效问题，应该部署冗余架构，keepalive ip检测及切换是个不错的选择。</hr>
Atlas局限性包括1中提到的待改善问题， Atlas并没有实现很好的水平扩展，更多的是负载了read性能，write性能并没有改善；要扩展write特性，spock proxy或spider引擎是个很好的选择, 不过实现分库是一个又大又重的架构；</hr> 4.没有宕机，但是主从关系出错怎么处理? 很遗憾，Atlas没有做主从的检测,不过可以通过管理接口来实现offline出问题的slave，添加命令到监控项可实现自动下线。
```
[root@z6 ~]# mysql -h 10.3.254.106 -P 2345 -u admin -pxxxxxxxx -e 'SET OFFLINE 3'         
+-------------+--------------------+---------+------+
| backend_ndx | address            | state   | type |
+-------------+--------------------+---------+------+
|           3 | 10.3.254.110:22587 | offline | ro   |
+-------------+--------------------+---------+------+
```
详见: https://github.com/arstercz/Atlas_auto_setline

</hr> 5.后端以MariaDB做服务，需要注意什么? MariaDB在5.3版本增加progress report 功能，详见https://mariadb.com/kb/en/progress-reporting/ 此功能还未在MySQL-Proxy中实现，Atlas可能也会触发该问题，见 https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/，可通过以下方式禁用report功能:

set global progress_report_time = 0

← → Top