atlas的升级版本mysql,Mysql-Atlas调整节点的尝试

Atlas的配置文件如下：

[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

从这个employees.cnf里我们可知写库是10.3.254.110:22585，而读库是10.3.254.110:22586@1和10.3.254.110:22587@2，同时有两个账号密码，而且atlas监听的工作端口是1234，atlas监听的管理端口是2345。

管理接口

[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，并不及时，而且易误报);

[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)

参考资料：https://highdb.com/360-atlas%E4%B8%AD%E9%97%B4%E4%BB%B6-%E6%B5%8B%E8%AF%95%E5%8F%8A%E4%BD%BF%E7%94%A8%E8%AF%B4%E6%98%8E/#comment-3064