360-Atlas中間件-測試及使用說明
2014-02-10
概要說明
https://github.com/Qihoo360/Atlas
Atlas作為中間件存在於應用和DB之間，為應用提供統一的服務接口，中間件實現識讀和分離功能，避免開發人員的額外開發操作，就可以達到分離分離的目的，多奴隸可實現按權重均衡負載，如果應用端夠強壯，可以忽略掉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/chenzhe/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（每個代理代理實例實例庫，多個庫可啟動多個代理進程，每個conf文件單獨配置）：
/usr/local/mysql-proxy/bin/mysql-proxyd employees start
查詢連接測試
管理語句
[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 |
+---------------------+
選擇
[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 |
+--------+------------+------------+-----------+--------+------------+
加入
[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 |
+--------+------------+------------+-----------+--------+------------+--------+---------+------------+------------+
聯盟
[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 |
+--------+------------+------------+-----------+--------+------------+
更新
[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 |
+--------+------------+------------+-----------+--------+------------+
插入
[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 |
+---------+-----------+
刪除
[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時狀態為未知狀態，可以理解為準備狀態。
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)
＃中間件自動剔除除停機機的服務器，（DB停機機檢測是單向的，掛掉後，根據一條SQL失敗來設置為down，因為由檢測線程來設置down，並不及時，而且易誤報） ;
[chenzhe@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，不做更新服務
[chenzhe@z10 rsandbox_Percona-Server-5_5_30]$ cd master/
[chenzhe@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)
常問問題
- 為什麼使用中間件？提供統一的對外服務端口，避免開發人員在代碼中實現額外的讀寫分離工作； DB結構的變更不影響應用的運行；讀取負載均衡的實現，避免切換主從因為InnoDB預熱而帶來的嚴重影響；自動剔除停機計算機的服務器（按照SQL執行情況來判斷，另外沒有檢測複製狀態，後續關注官方的改善）；
- 架構問題中間件以vip對外提供服務，儘管Atlas有自身的keepalive機制，但為避免單例中斷問題，應避免部署架構，keepalive ip檢測及切換是個不錯的選擇。
- Atlas局限性包括1中提到的待改善問題，Atlas並沒有實現很好的水平擴展，更多的是負載了讀取性能，write性能並沒有改善；要擴展write特性，spock proxy或spider引擎是個很容易好的選擇，不過應該實現分庫是一個又大又重的架構； 4.沒有停機機，但是主從關係出錯怎麼處理？很遺憾，Atlas沒有做主從的檢測，不過可以通過管理接口來實現離線出問題的從屬，添加命令到監控項可實現自動下線。
[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 | +-------------+--------------------+---------+------+
5.採取以MariaDB做服務，需要注意什麼？MariaDB在5.3版本增加進度報告功能，詳見https://mariadb.com/kb/zh-cn/progress-reporting/ 此功能尚未在MySQL-Proxy中實現，Atlas可能也會觸發該問題，見https：/ /mariadb.com/kb/zh-CN/mariadb-versus-mysql-compatibility/，可通過以下方式替換報告功能：
set global progress_report_time = 0
為percona監視器插件 MHA masterha manager的mysql cacti模板啟用多端口， 檢測master及failover處理說明