-
Notifications
You must be signed in to change notification settings - Fork 30
功能使用介绍
Jesse edited this page Aug 31, 2020
·
7 revisions
安装3台 MySQL 作为测试机，一主俩从，Arkproxy 部署在 Master上，系统为 Centos7
| 角色 | 主机名 | IP |
|---|---|---|
| Master/Arkproxy | master1 | 10.0.0.134 |
| Slave | slave1 | 10.0.0.135 |
| Slave | slave2 | 10.0.0.136 |
创建一些测试数据，方便演示模拟：
- 登录Master, 创建库表和数据
create database arkproxy_test;
create table arkproxy_test.test(id int not null primary key AUTO_INCREMENT,name varchar(100), comment varchar(100));
insert into arkproxy_test.test values(1,'Master','Master');数据如下：
mysql> select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
+----+--------+---------+
1 row in set (0.00 sec)
- 登录 slave1, 插入如下数据
SET sql_log_bin =OFF;
insert into arkproxy_test.test values(2,'Slave1','Slave1');
SET sql_log_bin =ON;数据如下：
mysql> select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
| 2 | Slave1 | Slave1 |
+----+--------+---------+
2 rows in set (0.00 sec)
- 登录 slave2, 插入如下数据
SET sql_log_bin =OFF;
insert into arkproxy_test.test values(3,'Slave2','Slave2');
SET sql_log_bin =ON;数据如下：
mysql> select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
| 3 | Slave2 | Slave2 |
+----+--------+---------+
2 rows in set (0.00 sec)
操作步骤：
-
登录 Arkproxy 管理端口，通常 DBA/运维人员使用
mysql -uproxyshell -ppassword -P3335 -h127.0.0.1 -A -
查看支持的命令，详细完整的指令说明
mysql> config help; -
查看后端配置数据库的状态
mysql> show backend servers; -
查看后台server对应route的状态
mysql> show backend routes;
结果：
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
3 rows in set (0.00 sec)
mysql> show backend routes;
+----+---------+------------+------+------------+--------------------+
| Id | Name | Host | Port | Route_Type | Comments |
+----+---------+------------+------+------------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | Write | read_writer server |
| 2 | server1 | 10.0.0.134 | 3306 | Read | read_only server |
| 3 | server2 | 10.0.0.135 | 3306 | Read | read_only server |
| 4 | server3 | 10.0.0.136 | 3306 | Read | read_only server |
+----+---------+------------+------+------------+--------------------+
4 rows in set (0.00 sec)
操作步骤：
-
登录 Arkproxy 业务端口
mysql -h127.0.0.1 -uarkproxy -parkproxy -P3336 -
重复多次查询
select * from arkproxy_test.test;会被路由分发到 master1、slave1、slave2 三个设定的 readonly 节点 ( arkproxy.cnf 中 router_servers=server1,server2,server3)
mysql> select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master | /* readonly 类型的 router_servers 配置了 master1 节点导致了此行输出*/
+----+--------+---------+
1 row in set (0.00 sec)
mysql> select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
| 2 | Slave1 | Slave1 |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql> select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
| 3 | Slave2 | Slave2 |
+----+--------+---------+
2 rows in set (0.00 sec)
预期结果 按照hint分发到读写节点
操作步骤
-
hint 分配到读节点，多次测试，观察是否一致
/*!999999 route to read */ select * from arkproxy_test.test; -
hint 分配到写节点，多次测试
/*!999999 route to write */ select * from arkproxy_test.test;
mysql> /*!999999 route to read */ select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
| 2 | Slave1 | Slave1 |
+----+--------+---------+
2 rows in set (0.00 sec)
mysql> /*!999999 route to read */ select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
| 3 | Slave2 | Slave2 |
+----+--------+---------+
2 rows in set (0.01 sec)
mysql> /*!999999 route to read */ select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master | /* readonly 类型的 router_servers 配置了 master1 节点导致了此行输出*/
+----+--------+---------+
1 row in set (0.00 sec)
mysql> /*!999999 route to write */ select * from arkproxy_test.test;
+----+--------+---------+
| id | name | comment |
+----+--------+---------+
| 1 | Master | Master |
+----+--------+---------+
1 row in set (0.00 sec)
预期结果 超过设定的连接数限制将直接报错
操作步骤
- 登录arkproxy进行账号授权并测试:
GRANT ALL PRIVILEGES ON *.* TO 'test_conn'@'%' IDENTIFIED BY 'test_conn';
flush privileges;从slave2 节点测试登录 mysql -h10.0.0.134 -P 3336 -utest_conn -ptest_conn
-
登陆 Arkproxy 管理 shell ，设置最大连接数为0, 即不允许连接(10.0.0.136为 slave2 节点的 ip)
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
mysql> config set user 'test_conn'@'10.0.0.136' max_user_connections=0;
mysql> config flush;
-
再次尝试从 slave2 登陆 Arkproxy 的业务端口，预期将报错
mysql -h10.0.0.134 -P 3336 -utest_conn -ptest_conn
[root@slave2 ]# mysql -h10.0.0.134 -P 3336 -utest_conn -ptest_conn
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.20-19-log
Copyright (c) 2009-2017 Percona LLC and/or its affiliates
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
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> ^DBye
[root@slave2 ~]# mysql -h10.0.0.134 -P 3336 -utest_conn -ptest_conn
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1203 (42000): User test_conn already has more than 'max_user_connections' active connections
[root@slave2 ~]#
操作步骤
- 登陆 Arkproxy 管理 shell
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
- 查看集群情况
mysql> show backend servers;
- 下线 server3
mysql> config set server server3 offline;
mysql> config flush;
- 通过arkproxy业务端口多次查询数据 观察是否会有server3也就是slave2节点的数据 (此前环境准备中insert的数据中包含 “Slave2”字符)
mysql -h10.0.0.134 -uarkproxy -parkproxy -P3336
mysql> select * from arkproxy_test.test;
预期结果 修改配置文件的[server]和[router]组的参数后，可以热加载生效
操作步骤
- 登陆 Arkproxy 管理 shell
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
- 查看后端server和对应route的状态
mysql>show backend servers;
- 修改配置文件，将[server2]的
weight改为10
--- /etc/arkproxy.cnf.old 2020-08-28 21:11:32.378487375 +0800
+++ /etc/arkproxy.cnf.new 2020-08-28 21:11:52.112383695 +0800
@@ -60,7 +60,7 @@
proxy_type=server
backend_host=10.0.0.135
backend_port=3306
-weight=30
+weight=10
config_comment = day day up_server2
[server3]- 登陆 Arkproxy 管理 shell，执行
config reload重新加载配置文件 - 再次执行
show backend servers;查看server2的weight
注：如果为Arkproxy集群，则只会在该节点生效，不会同步到其他节点
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
mysql>
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | OFFLINE | day day up_server3 |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
3 rows in set (0.00 sec)
mysql> config reload;
Query OK, 0 rows affected (0.01 sec)
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
3 rows in set (0.00 sec)
操作步骤
- 登陆 Arkproxy 管理 shell
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
- 修改某个参数
set global proxy_flush_thread_num=20;
- 执行
config flush;使其生效 - 查看确认该参数的设置
mysql> config flush;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables;
+--------------------------------------+--------------------------------------+
| Variable_name | Value |
+--------------------------------------+--------------------------------------+
| proxy_async_connect_server | ON |
| proxy_backend_passwd | arkproxy |
| proxy_backend_user | arkproxy |
| proxy_check_consistend_period | 10 |
| proxy_check_slave_lag_period | 10 |
| proxy_config_file | /etc/arkproxy.cnf |
| proxy_config_host | |
| proxy_config_passwd | |
| proxy_config_port | 0 |
| proxy_config_user | |
| proxy_consistend_max_delay_time | 1000 |
| proxy_digest_trace | OFF |
| proxy_enable_consistend_read | 0 |
| proxy_flush_thread_num | 20 |
- 保存到配置文件中
config write outfile '/etc/arkproxy.cnf';
注：如果为Arkproxy集群，则只会在该节点生效，不会同步到其他节点
在线修改某个配置项之后，比如在线增加一个只读server，动态加载生效。
操作步骤
- 登陆 Arkproxy 管理 shell
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
- 修改某个配置项，例如在线增加server
config add read server server4 host='10.0.0.137',port=3307,max_lag=10000,weight=40;
- 此时配置在cache中，可通过
show config cache;查看确认；最终生效需要再执行config flush; - 若需要在 config flush 之前撤销 cache 中的未生效配置，请执行
config delete 2即可，2为 cache 中的配置 id - 查看确认新添加的节点的状态，
show backend servers;，默认为 OFFLINE
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
3 rows in set (0.00 sec)
mysql> config add read server server4 host='10.0.0.137',port=3307,max_lag=10000,weight=40 ;
Query OK, 0 rows affected (0.00 sec)
mysql> show config cache;
+----+--------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| Id | Config_Class | Config_Name | New_Value | Old_Value |
+----+--------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| 2 | ADD_SERVER | server4 | {"server_name":"server4","route_type":"Read","host_name":"10.0.0.137","port":"3307","max_lag":"10000","weight":"40"} = day day up_server3
[router1]
proxy_type = router
router_type = readwrite
| NULL |
+----+--------------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
1 row in set (0.00 sec)
mysql> config flush;
Query OK, 0 rows affected (0.00 sec)
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
| 4 | server4 | 10.0.0.137 | 3307 | 40 | 10000 | Yes | OFFLINE | NULL |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
4 rows in set (0.00 sec)
操作步骤
- 登陆 Arkproxy 管理 shell
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
- 上线 server，然后刷新缓存设置使其生效
config set server server4 online;
show config cache;
config flush;
show backend servers;
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
| 4 | server4 | 10.0.0.137 | 3307 | 40 | 10000 | Yes | OFFLINE | NULL |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
4 rows in set (0.00 sec)
mysql>
mysql> config set server server4 online;
Query OK, 0 rows affected (0.00 sec)
mysql> config flush;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
| 4 | server4 | 10.0.0.137 | 3307 | 40 | 10000 | Yes | ONLINE | NULL |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
4 rows in set (0.00 sec)
- 下线server也类似，需要刷新缓存设置使其生效
config set server server3 offline;
show config cache;
config flush;
show backend servers;
注：上线的节点必须要实际存在才可上线成功。
可将当前配置导出到外部文件
操作步骤
- 登陆 Arkproxy 管理 shell
mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
- 执行
config write outfile '/tmp/a.txt';即可导出。如果不指定绝对路径将导出到 arkproxy 的bin目录下
mysql> config write outfile '/tmp/a.txt';
Query OK, 0 rows affected (0.00 sec)
mysql> ^DBye
[root@master1 ~]# wc -l /tmp/a.txt
110 /tmp/a.txt
在一个 Arkproxy 节点所做的修改可以同步到另一个节点。执行 config flush、config write outfile、config reload 操作时会同步到其他的 Arkproxy 节点
操作步骤
- 修改配置文件参数 proxy_incoming_addresses，指定两边需要同步的IP及端口，格式为：IP:shell 管理端口，每组之间用 “,” 分隔，
config reload生效
# master1 的 /etc/arkproxy.cnf
proxy_general_log_file = /tmp/proxy.log
proxy_incoming_addresses = 10.0.0.135:3335
proxy_init_connect =
# slave1 的 /etc/arkproxy.cnf
proxy_general_log_file = /tmp/proxy.log
proxy_incoming_addresses = 10.0.0.134:3335
proxy_init_connect =
- 然后测试 online/offline 服务，这里在 master1 和 slave1 上各部署了一个 Arkproxy 节点，在 slave1 上将 server3 下线，然后登录到 master1 查看server已经同时下线，确认同步完成
slave1上操作：
[root@slave1 ~]# mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | ONLINE | day day up_server3 |
| 4 | server4 | 10.0.0.137 | 3307 | 40 | 10000 | Yes | ONLINE | NULL |
+----+---------+------------+------+--------+---------------+--------+--------+--------------------+
4 rows in set (0.00 sec)
mysql> config set server server3 offline;
Query OK, 0 rows affected (0.00 sec)
mysql> config flush;
Query OK, 0 rows affected (0.05 sec)
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | OFFLINE | day day up_server3 |
| 4 | server4 | 10.0.0.137 | 3307 | 40 | 10000 | Yes | ONLINE | NULL |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
4 rows in set (0.00 sec)
master1上观察：
[root@master1 ~]# mysql -h127.0.0.1 -uproxyshell -ppassword -P3335
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show backend servers;
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| Id | Name | Host | Port | Weight | Max_Slave_Lag | Routed | Status | Comments |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
| 1 | server1 | 10.0.0.134 | 3306 | 40 | 200 | Yes | ONLINE | day day up_server1 |
| 2 | server2 | 10.0.0.135 | 3306 | 10 | 1000 | Yes | ONLINE | day day up_server2 |
| 3 | server3 | 10.0.0.136 | 3306 | 30 | 1000 | Yes | OFFLINE | day day up_server3 |
| 4 | server4 | 10.0.0.137 | 3307 | 40 | 10000 | Yes | ONLINE | NULL |
+----+---------+------------+------+--------+---------------+--------+---------+--------------------+
4 rows in set (0.00 sec)