ProxySQL平滑下线节点
因为MGR多主DDL和DML不能并发在不同节点执行, 而我们又存在即通过KO又通过ProxySQL访问的数据库的情况, 为了避免在发生故障, 我们决定将ProxySQL中配置的集群真实ip删除, 改为使用KO的vip.
本文介绍如何平滑下线ProxySQL节点, 从而做到对业务无感知.
KO我司自研php中间件
环境介绍
| 10.133.x.59 | 跑sysbench |
|---|---|
| 10.133.x.52:3307 | 测试集群节点 |
| 10.133.x.53:3307 | 测试集群节点 |
| 10.133.x.54:3307 | 测试集群节点 |
| VIP | 访问类型 | 所在服务器 |
|---|---|---|
| 10.133.x.202 | KO | 10.133.x.202 |
| 10.133.x.203 | ProxySQL | 10.133.x.203 |
ProxySQL版本1
2
3
4
5
6
7admin@127.0.0.1 23:12:02 [(none)]> select version();
+-------------------+
| version() |
+-------------------+
| 2.0.8-67-g877cab1 |
+-------------------+
1 row in set (0.00 sec)
MySQL版本1
2
3
4
5
6
7root@localhost 23:17:56 [(none)]> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.00 sec)
错误的下线方式
1.59跑压测1
2
3
4
5
6
7
8
9
10
11
12
13
14# cat sysbench_by_proxysql.sh
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-user=sysbench_user --mysql-password=superSecret --mysql-port=16034 \
--mysql-host=10.133.x.203 \
--mysql-db=sysbench --tables=10 --table-size=5000000 --threads=$1 \
--events=5000000 --report-interval=5 --db-driver=mysql --time=$2 run
# sh sysbench_by_proxysql.sh 16 1800
[ 5s ] thds: 16 tps: 1125.70 qps: 22546.06 (r/w/o: 15787.64/4503.82/2254.61) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 16 tps: 1125.31 qps: 22516.34 (r/w/o: 15762.30/4503.43/2250.61) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 16 tps: 1127.13 qps: 22542.62 (r/w/o: 15778.83/4509.72/2254.06) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 16 tps: 1130.82 qps: 22613.98 (r/w/o: 15829.67/4522.48/2261.84) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
...
查看ProxySQL vip所在节点的ProxySQL配置1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17admin 17:58:46 [(none)]> select * from runtime_mysql_servers;
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 10.133.x.53 | 3307 | 0 | ONLINE | 3 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 10.133.x.54 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 10.133.x.52 | 3307 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
admin 18:00:31 [(none)]> select * from mysql_servers;
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 10.133.x.53 | 3307 | 0 | ONLINE | 3 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 10.133.x.52 | 3307 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | 10.133.x.54 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
我们打算添加KO vip到mysql server中, 所以一开始我尝试删除mysql servers所有配置, 然后插入10.133.x.202:33071
2
3
4
5
6
7
8
9
10
11
12
13
14[2019-11-12 18:01:37]admin@127.0.0.1 18:00:49 [(none)]> delete from mysql_servers;
[2019-11-12 18:01:37]Query OK, 3 rows affected (0.00 sec)
[2019-11-12 18:01:37]
[2019-11-12 18:03:16]admin@127.0.0.1 18:01:37 [(none)]> insert into mysql_servers values(10,'10.133.x.202',3307,0,'ONLINE',10,0,1000,0,0,0,'');
[2019-11-12 18:03:16]Query OK, 1 row affected (0.00 sec)
[2019-11-12 18:03:16]
[2019-11-12 18:03:19]admin@127.0.0.1 18:03:16 [(none)]> select * from mysql_servers;
[2019-11-12 18:03:19]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:19]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 18:03:19]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:19]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 18:03:19]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:19]1 row in set (0.00 sec)
[2019-11-12 18:03:19]
加载配置到runtime, 使配置真正生效1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27[2019-11-12 18:03:43]admin@127.0.0.1 18:03:19 [(none)]> load mysql servers to runtime;
[2019-11-12 18:03:43]Query OK, 0 rows affected (0.00 sec)
[2019-11-12 18:03:43]
[2019-11-12 18:03:44]admin@127.0.0.1 18:03:43 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 18:03:44]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:44]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 18:03:44]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:44]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 18:03:44]| 11 | 10.133.x.54 | 3307 | 0 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 18:03:44]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:44]2 rows in set (0.00 sec)
[2019-11-12 18:03:44]
[2019-11-12 18:03:45]admin@127.0.0.1 18:03:44 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 18:03:45]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:45]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 18:03:45]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:45]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 18:03:45]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:45]1 row in set (0.00 sec)
[2019-11-12 18:03:45]
[2019-11-12 18:03:46]admin@127.0.0.1 18:03:45 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 18:03:46]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:46]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 18:03:46]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:46]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 18:03:46]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 18:03:46]1 row in set (0.01 sec)
此时查看sysbench, 发现在配置加载后, 连接立马报错.
显然, 如果以这种方式在生产环境下线节点, 业务肯定是不可以接受的1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31[2019-11-12 18:03:40][ 180s ] thds: 16 tps: 1134.87 qps: 22716.96 (r/w/o: 15904.95/4542.07/2269.94) lat (ms,95%): 15.55 err/s: 0.00 reconn/s: 0.00
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest7 WHERE id=?'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:419: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT'
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN ? AND ? ORDER BY c'
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest6 WHERE id BETWEEN ? AND ?'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:409: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest8 WHERE id=?'
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest6 WHERE id=?'
[2019-11-12 18:03:43](last message repeated 2 times)
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'UPDATE sbtest8 SET c=? WHERE id=?'
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest2 WHERE id=?'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:419: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:419: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43](last message repeated 3 times)
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:469: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'COMMIT'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:409: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT DISTINCT c FROM sbtest8 WHERE id BETWEEN ? AND ? ORDER BY c'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT c FROM sbtest8 WHERE id=?'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:419: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'DELETE FROM sbtest2 WHERE id=?'
[2019-11-12 18:03:43]FATAL: mysql_stmt_execute() returned error 2013 (Lost connection to MySQL server during query) for query 'SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c'
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:432: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:43]FATAL: `thread_run' function failed: /usr/share/sysbench/oltp_common.lua:487: SQL error, errno = 2013, state = 'HY000': Lost connection to MySQL server during query
[2019-11-12 18:03:48]Error in my_thread_global_end(): 16 threads didn't exit
正确的下线方式
1.59跑sysbench1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21[2019-11-12 21:33:03][root@node10-133-1-59 fanboshi]# sh sysbench_by_proxysql.sh 4 1800
[2019-11-12 21:33:03]sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)
[2019-11-12 21:33:03]
[2019-11-12 21:33:03]Running the test with following options:
[2019-11-12 21:33:03]Number of threads: 4
[2019-11-12 21:33:03]Report intermediate results every 5 second(s)
[2019-11-12 21:33:03]Initializing random number generator from current time
[2019-11-12 21:33:03]
[2019-11-12 21:33:03]
[2019-11-12 21:33:03]Initializing worker threads...
[2019-11-12 21:33:03]
[2019-11-12 21:33:03]Threads started!
[2019-11-12 21:33:03]
[2019-11-12 21:33:08][ 5s ] thds: 4 tps: 329.27 qps: 6592.58 (r/w/o: 4615.37/1317.88/659.34) lat (ms,95%): 13.95 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:33:13][ 10s ] thds: 4 tps: 339.83 qps: 6799.25 (r/w/o: 4760.65/1358.93/679.66) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:33:18][ 15s ] thds: 4 tps: 338.36 qps: 6768.12 (r/w/o: 4736.98/1354.42/676.71) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:33:23][ 20s ] thds: 4 tps: 335.65 qps: 6714.51 (r/w/o: 4700.84/1342.38/671.29) lat (ms,95%): 13.22 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:33:28][ 25s ] thds: 4 tps: 336.79 qps: 6734.32 (r/w/o: 4713.01/1347.94/673.37) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:33:33][ 30s ] thds: 4 tps: 340.61 qps: 6812.86 (r/w/o: 4770.58/1360.85/681.43) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:33:38][ 35s ] thds: 4 tps: 339.20 qps: 6779.83 (r/w/o: 4745.02/1356.41/678.40) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
...
1.52插入KO VIP, 修改其他节点状态为OFFLINE_SOFT1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47[2019-11-12 21:33:58]admin@127.0.0.1 21:33:28 [(none)]> insert into mysql_servers values(10,'10.133.x.202',3307,0,'ONLINE',100,0,1000,0,0,0,'');
[2019-11-12 21:33:58]Query OK, 1 row affected (0.00 sec)
[2019-11-12 21:33:58]
[2019-11-12 21:34:03]admin@127.0.0.1 21:33:58 [(none)]> select * from mysql_servers;
[2019-11-12 21:34:03]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:03]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:34:03]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:03]| 10 | 10.133.x.53 | 3307 | 0 | ONLINE | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:03]| 11 | 10.133.x.52 | 3307 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:03]| 11 | 10.133.x.54 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:03]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:03]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:03]4 rows in set (0.00 sec)
[2019-11-12 21:34:03]
[2019-11-12 21:34:08]admin@127.0.0.1 21:34:04 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 21:34:08]+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:08]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:34:08]+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:08]| 10 | 10.133.x.53 | 3307 | 0 | ONLINE | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:08]| 11 | 10.133.x.54 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:08]| 11 | 10.133.x.52 | 3307 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:08]+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:08]3 rows in set (0.00 sec)
[2019-11-12 21:34:08]
[2019-11-12 21:34:14]admin@127.0.0.1 21:34:08 [(none)]> update mysql_servers set status='OFFLINE_SOFT' where hostname!='10.133.x.202';
[2019-11-12 21:34:14]Query OK, 3 rows affected (0.00 sec)
[2019-11-12 21:34:14]
[2019-11-12 21:34:18]admin@127.0.0.1 21:34:14 [(none)]> select * from mysql_servers;
[2019-11-12 21:34:18]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:18]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:34:18]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:18]| 10 | 10.133.x.53 | 3307 | 0 | OFFLINE_SOFT | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:18]| 11 | 10.133.x.52 | 3307 | 0 | OFFLINE_SOFT | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:18]| 11 | 10.133.x.54 | 3307 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:18]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:18]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:18]4 rows in set (0.00 sec)
[2019-11-12 21:34:18]
[2019-11-12 21:34:22]admin@127.0.0.1 21:34:18 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 21:34:22]+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:22]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:34:22]+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:22]| 10 | 10.133.x.53 | 3307 | 0 | ONLINE | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:22]| 11 | 10.133.x.54 | 3307 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:22]| 11 | 10.133.x.52 | 3307 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:34:22]+--------------+-------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:34:22]3 rows in set (0.00 sec)
加载配置到runtime1
2
3
4
5
6
7
8
9
10
11
12
13
14[2019-11-12 21:34:47]admin@127.0.0.1 21:34:28 [(none)]> load mysql servers to runtime;
[2019-11-12 21:34:47]Query OK, 0 rows affected (0.00 sec)
[2019-11-12 21:34:47]
[2019-11-12 21:35:01]admin@127.0.0.1 21:34:47 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 21:35:01]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:35:01]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:35:01]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:35:01]| 10 | 10.133.x.53 | 3307 | 0 | OFFLINE_SOFT | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:35:01]| 11 | 10.133.x.54 | 3307 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:35:01]| 11 | 10.133.x.52 | 3307 | 0 | OFFLINE_SOFT | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:35:01]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:35:01]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:35:01]4 rows in set (0.00 sec)
[2019-11-12 21:35:01]
查看sysbench, 正常运行连接无异常1
2
3
4[2019-11-12 21:34:23][ 80s ] thds: 4 tps: 332.59 qps: 6646.37 (r/w/o: 4651.64/1329.75/664.98) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:34:28][ 85s ] thds: 4 tps: 339.42 qps: 6798.35 (r/w/o: 4759.64/1359.67/679.03) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:34:33][ 90s ] thds: 4 tps: 338.40 qps: 6763.76 (r/w/o: 4734.57/1352.39/676.80) lat (ms,95%): 12.98 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:34:38][ 95s ] thds: 4 tps: 337.20 qps: 6742.19 (r/w/o: 4719.79/1348.00/674.40) lat (ms,95%): 13.46 err/s: 0.00 reconn/s: 0.00
查看proxysql转台信息, 发现status为OFFLINE_SOFT, 连接仍然在10.133.x.53上1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17[2019-11-12 21:35:10]admin@127.0.0.1 21:35:01 [(none)]> SELECT hostgroup hg, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool WHERE ConnUsed+ConnFree > 0 ORDER BY hg, srv_host;
[2019-11-12 21:35:10]+----+-------------+--------------+----------+----------+--------+---------+
[2019-11-12 21:35:10]| hg | srv_host | status | ConnUsed | ConnFree | ConnOK | ConnERR |
[2019-11-12 21:35:10]+----+-------------+--------------+----------+----------+--------+---------+
[2019-11-12 21:35:10]| 10 | 10.133.x.53 | OFFLINE_SOFT | 4 | 0 | 26 | 0 |
[2019-11-12 21:35:10]+----+-------------+--------------+----------+----------+--------+---------+
[2019-11-12 21:35:10]1 row in set (0.01 sec)
[2019-11-12 21:35:10]
[2019-11-12 21:35:16]admin@127.0.0.1 21:35:10 [(none)]> select * from stats_mysql_processlist;
[2019-11-12 21:35:16]+----------+-----------+------------+----------+-------------+----------+-----------+-------------+------------+-------------+----------+---------+---------+---------------------------------------------------------------------+--------------+---------------+
[2019-11-12 21:35:16]| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags | extended_info |
[2019-11-12 21:35:16]+----------+-----------+------------+----------+-------------+----------+-----------+-------------+------------+-------------+----------+---------+---------+---------------------------------------------------------------------+--------------+---------------+
[2019-11-12 21:35:16]| 10 | 15664 | sysbench_user | sysbench | 10.133.x.59 | 60008 | 10 | 10.133.x.52 | 60207 | 10.133.x.53 | 3307 | Execute | 0 | COMMIT | 0 | NULL |
[2019-11-12 21:35:16]| 5 | 15665 | sysbench_user | sysbench | 10.133.x.59 | 60012 | 10 | 10.133.x.52 | 28221 | 10.133.x.53 | 3307 | Execute | 0 | SELECT c FROM sbtest8 WHERE id=? | 0 | NULL |
[2019-11-12 21:35:16]| 19 | 15666 | sysbench_user | sysbench | 10.133.x.59 | 60014 | 10 | 10.133.x.52 | 25673 | 10.133.x.53 | 3307 | Execute | 0 | SELECT DISTINCT c FROM sbtest10 WHERE id BETWEEN ? AND ? ORDER BY c | 0 | NULL |
[2019-11-12 21:35:16]| 4 | 15667 | sysbench_user | sysbench | 10.133.x.59 | 60010 | 10 | 10.133.x.52 | 28223 | 10.133.x.53 | 3307 | Execute | 0 | SELECT c FROM sbtest3 WHERE id BETWEEN ? AND ? | 0 | NULL |
[2019-11-12 21:35:16]+----------+-----------+------------+----------+-------------+----------+-----------+-------------+------------+-------------+----------+---------+---------+---------------------------------------------------------------------+--------------+---------------+
我们手动停止sysbench, 重启sysbench1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
182019-11-12 21:35:37][root@node10-133-1-59 fanboshi]# sh sysbench_by_proxysql.sh 4 1800
[2019-11-12 21:35:37]sysbench 1.0.18 (using bundled LuaJIT 2.1.0-beta2)
[2019-11-12 21:35:37]
[2019-11-12 21:35:37]Running the test with following options:
[2019-11-12 21:35:37]Number of threads: 4
[2019-11-12 21:35:37]Report intermediate results every 5 second(s)
[2019-11-12 21:35:37]Initializing random number generator from current time
[2019-11-12 21:35:37]
[2019-11-12 21:35:37]
[2019-11-12 21:35:37]Initializing worker threads...
[2019-11-12 21:35:37]
[2019-11-12 21:35:37]Threads started!
[2019-11-12 21:35:37]
[2019-11-12 21:35:42][ 5s ] thds: 4 tps: 432.41 qps: 8659.34 (r/w/o: 6062.50/1731.23/865.61) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:35:47][ 10s ] thds: 4 tps: 433.20 qps: 8661.86 (r/w/o: 6064.24/1731.21/866.41) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:35:52][ 15s ] thds: 4 tps: 433.79 qps: 8676.81 (r/w/o: 6074.06/1735.16/867.58) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:35:57][ 20s ] thds: 4 tps: 436.80 qps: 8736.88 (r/w/o: 6114.46/1748.82/873.61) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:36:02][ 25s ] thds: 4 tps: 434.99 qps: 8701.31 (r/w/o: 6092.20/1739.14/869.97) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
再次查看连接状况1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18[2019-11-12 21:35:41]admin@127.0.0.1 21:35:25 [(none)]> SELECT hostgroup hg, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR FROM stats_mysql_connection_pool WHERE ConnUsed+ConnFree > 0 ORDER BY hg, srv_host;
[2019-11-12 21:35:41]+----+--------------+--------+----------+----------+--------+---------+
[2019-11-12 21:35:41]| hg | srv_host | status | ConnUsed | ConnFree | ConnOK | ConnERR |
[2019-11-12 21:35:41]+----+--------------+--------+----------+----------+--------+---------+
[2019-11-12 21:35:41]| 10 | 10.133.x.202 | ONLINE | 4 | 0 | 4 | 0 |
[2019-11-12 21:35:41]+----+--------------+--------+----------+----------+--------+---------+
[2019-11-12 21:35:41]1 row in set (0.00 sec)
[2019-11-12 21:35:41]
[2019-11-12 21:35:50]admin@127.0.0.1 21:35:42 [(none)]> select * from stats_mysql_processlist;
[2019-11-12 21:35:50]+----------+-----------+------------+----------+-------------+----------+-----------+--------------+------------+--------------+----------+---------+---------+--------------------------------------------------------------------+--------------+---------------+
[2019-11-12 21:35:50]| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags | extended_info |
[2019-11-12 21:35:50]+----------+-----------+------------+----------+-------------+----------+-----------+--------------+------------+--------------+----------+---------+---------+--------------------------------------------------------------------+--------------+---------------+
[2019-11-12 21:35:50]| 2 | 15731 | sysbench_user | sysbench | 10.133.x.59 | 60330 | 10 | 10.133.x.202 | 15300 | 10.133.x.202 | 3307 | Sleep | 0 | NULL | 0 | NULL |
[2019-11-12 21:35:50]| 4 | 15732 | sysbench_user | sysbench | 10.133.x.59 | 60334 | 10 | 10.133.x.202 | 15304 | 10.133.x.202 | 3307 | Sleep | 0 | NULL | 0 | NULL |
[2019-11-12 21:35:50]| 7 | 15733 | sysbench_user | sysbench | 10.133.x.59 | 60336 | 10 | 10.133.x.202 | 15302 | 10.133.x.202 | 3307 | Execute | 0 | COMMIT | 0 | NULL |
[2019-11-12 21:35:50]| 6 | 15734 | sysbench_user | sysbench | 10.133.x.59 | 60332 | 10 | 10.133.x.202 | 15306 | 10.133.x.202 | 3307 | Execute | 0 | SELECT DISTINCT c FROM sbtest9 WHERE id BETWEEN ? AND ? ORDER BY c | 0 | NULL |
[2019-11-12 21:35:50]+----------+-----------+------------+----------+-------------+----------+-----------+--------------+------------+--------------+----------+---------+---------+--------------------------------------------------------------------+--------------+---------------+
[2019-11-12 21:35:50]4 rows in set (0.01 sec)
可以看到连接已经转发到了10.133.x.202上
删除无用配置1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52[2019-11-12 21:36:18]admin@127.0.0.1 21:35:50 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 21:36:18]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:18]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:36:18]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:18]| 10 | 10.133.x.53 | 3307 | 0 | OFFLINE_SOFT | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:18]| 11 | 10.133.x.54 | 3307 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:18]| 11 | 10.133.x.52 | 3307 | 0 | OFFLINE_SOFT | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:18]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:18]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:18]4 rows in set (0.01 sec)
[2019-11-12 21:36:18]
[2019-11-12 21:36:23]admin@127.0.0.1 21:36:18 [(none)]> select * from mysql_servers;
[2019-11-12 21:36:23]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:23]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:36:23]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:23]| 10 | 10.133.x.53 | 3307 | 0 | OFFLINE_SOFT | 3 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:23]| 11 | 10.133.x.52 | 3307 | 0 | OFFLINE_SOFT | 2 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:23]| 11 | 10.133.x.54 | 3307 | 0 | OFFLINE_SOFT | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:23]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:23]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:23]4 rows in set (0.00 sec)
[2019-11-12 21:36:23]
[2019-11-12 21:36:29]admin@127.0.0.1 21:36:23 [(none)]> delete from mysql_servers where hostname!='10.133.x.202';
[2019-11-12 21:36:29]Query OK, 3 rows affected (0.00 sec)
[2019-11-12 21:36:29]
[2019-11-12 21:36:31]admin@127.0.0.1 21:36:29 [(none)]> select * from mysql_servers;
[2019-11-12 21:36:31]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:31]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:36:31]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:31]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:31]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:31]1 row in set (0.00 sec)
[2019-11-12 21:36:31]
[2019-11-12 21:36:42]admin@127.0.0.1 21:36:31 [(none)]> load mysql servers to runtime;
[2019-11-12 21:36:42]Query OK, 0 rows affected (0.00 sec)
[2019-11-12 21:36:42]
[2019-11-12 21:36:49]admin@127.0.0.1 21:36:42 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 21:36:49]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:49]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:36:49]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:49]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:49]| 11 | 10.133.x.54 | 3307 | 0 | OFFLINE_HARD | 1 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:49]+--------------+--------------+------+-----------+--------------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:49]2 rows in set (0.00 sec)
[2019-11-12 21:36:49]
[2019-11-12 21:36:55]admin@127.0.0.1 21:36:49 [(none)]> select * from runtime_mysql_servers;
[2019-11-12 21:36:55]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:55]| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
[2019-11-12 21:36:55]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:55]| 10 | 10.133.x.202 | 3307 | 0 | ONLINE | 100 | 0 | 1000 | 0 | 0 | 0 | |
[2019-11-12 21:36:55]+--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
[2019-11-12 21:36:55]1 row in set (0.00 sec)
连接无异常1
2
3
4[2019-11-12 21:36:37][ 60s ] thds: 4 tps: 418.79 qps: 8379.26 (r/w/o: 5866.30/1675.37/837.59) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:36:42][ 65s ] thds: 4 tps: 421.60 qps: 8433.50 (r/w/o: 5904.67/1685.62/843.21) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:36:47][ 70s ] thds: 4 tps: 416.80 qps: 8334.11 (r/w/o: 5832.93/1667.58/833.59) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[2019-11-12 21:36:52][ 75s ] thds: 4 tps: 413.81 qps: 8273.84 (r/w/o: 5790.77/1655.45/827.62) lat (ms,95%): 11.65 err/s: 0.00 reconn/s: 0.00
关于OFFLINE_SOFT
详见https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_servers
status:- ONLINE - backend server is fully operational
- SHUNNED - backend sever is temporarily taken out of use because of either too many connection errors in a time that was too short, or replication lag exceeded the allowed threshold
- OFFLINE_SOFT - when a server is put into OFFLINE_SOFT mode, new incoming connections aren’t accepted anymore, while the existing connections are kept until they became inactive. In other words, connections are kept in use until the current transaction is completed. This allows to gracefully detach a backend
- OFFLINE_HARD - when a server is put into OFFLINE_HARD mode, the existing connections are dropped, while new incoming connections aren’t accepted either. This is equivalent to deleting the server from a hostgroup, or temporarily taking it out of the hostgroup for maintenance work