ProxySQL Features
ProxySQL은 MySQL을 위한 오픈 소스 고성능 고가용성 데이터베이스 프로토콜 인식 프록시입니다.
ProxySQL의 주요 기능에 대해 살펴보도록 하겠습니다.
Application Layer Proxy
- Connection Multiplexing
- Query Caching
- Limiting the number of connections to a backend
- Prioritizing traffic by changing the weight of a backend
- Disabling a backend server (Gracefully / Immediately / Re-enabling an offline)
- Query Logging
- ProxySQL Statistics
Advanced Query Rules
- Query Rules in MySQL
- Read/Write Split (using different ports, regex, regex and digest)
- Query Rewrite
Data Sharding & Transformation
- Sharding in ProxySQL (User / Schema / Data based sharding)
Failover Detection
- Backend’s health check
- Monitor Module
- Automatically shunning slaves with replication lag
Zero-downtime Changes
Database Firewall
ProxySQL 설치는 이전 게시글을 참고 합니다.
Getting Started
Configuring ProxySQL
ProxySQL 환결설정은 config file 또는 database(=sqlite) 에 저장됩니다.
Configuring ProxySQL through the admin interface
$ mysql -u radmin -pradmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin>Configuring ProxySQL through the config file
mysql_servers =
(
{
address="127.0.0.1"
port=3306
hostgroup=0
max_connections=200
}
)Exporting configuration into file
# /etc/proxysql.cnf
SELECT CONFIG INTO OUTFILE /tmp/f1
mysql -padmin -uadmin -h127.0.0.1 -P6032 -e 'select config file'
Application Layer Proxy
Connection Multiplexing
Connection Multiplexing이란, 출발지에서 요청한 수 많은 커넥션이 있을 때, 그 커넥션의 성격이 비슷한 경우 하나의 커넥션으로 인식하여, 효율적으로 사용할 수 있도록 해주는 기능을 의미한다. Application과 DBMS 사이에서 Middle Ware역할을 해주면서, Connection Multiplexing을 제공해준다. 출처 : hakurei.tistory
같이 보면 좋은 레퍼런스 영상 : NHN, 200만 동접 게임을 위한 MySQL 샤딩
Query Caching
mysql_query_rules의 rule_id별 cache_ttl를 설정 하여 Query Cache를 설정할수 있습니다.
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'msandbox','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'msandbox','DISTINCT c FROM sbtest1',2,1);Admin> UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;Define traffic that needs to be cached
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply)
VALUES (5,1,'0xE8930CB2CC9E68D7',2000,1);
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;Limiting the number of connections to a backend
mysql_servers의 hostgroup_id별 max_connections을 설정하여 제어할수 있습니다.
Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
+--------------+------------+-----------------+
| hostgroup_id | hostname | max_connections |
+--------------+------------+-----------------+
| 0 | 172.16.0.1 | 1000 |
| 1 | 172.16.0.2 | 1000 |
| 1 | 172.16.0.3 | 1000 |
+--------------+------------+-----------------+
3 rows in set (0.00 sec)
Admin> UPDATE mysql_servers SET max_connections=10 WHERE hostname='172.16.0.2';
Admin> SELECT hostgroup_id,hostname,max_connections FROM mysql_servers;
+--------------+------------+-----------------+
| hostgroup_id | hostname | max_connections |
+--------------+------------+-----------------+
| 0 | 172.16.0.1 | 1000 |
| 1 | 172.16.0.2 | 10 |
| 1 | 172.16.0.3 | 1000 |
+--------------+------------+-----------------+
3 rows in set (0.00 sec)Prioritizing traffic by changing the weight of a backend
mysql_servers의 hostgroup_id별 weight을 설정하여 가중치를 제어할수 있습니다.
Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname | weight |
+--------------+------------+--------+
| 0 | 172.16.0.1 | 1 |
| 1 | 172.16.0.2 | 1 |
| 1 | 172.16.0.3 | 1 |
+--------------+------------+--------+
3 rows in set (0.00 sec)
Admin> UPDATE mysql_servers SET weight=1000 WHERE hostname NOT IN ('172.16.0.2', '172.16.0.1') AND hostgroup_id=1;
Admin> SELECT hostgroup_id,hostname,weight FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname | weight |
+--------------+------------+--------+
| 0 | 172.16.0.1 | 1 |
| 1 | 172.16.0.2 | 1 |
| 1 | 172.16.0.3 | 1000 |
+--------------+------------+--------+
3 rows in set (0.00 sec)Disabling a backend server
mysql_servers > status (ONLINE | OFFLINE_SOFT | OFFLINE_HARD)
Gracefully disabling a backend server
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname | status |
+--------------+------------+--------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | ONLINE |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)
Admin> UPDATE mysql_servers SET status='OFFLINE_SOFT' WHERE hostname='172.16.0.2';
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname | status |
+--------------+------------+--------------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | OFFLINE_SOFT |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | ONLINE |
+--------------+------------+--------------+
4 rows in set (0.00 sec)Immediately disabling a backend server
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname | status |
+--------------+------------+--------------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | OFFLINE_SOFT |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | ONLINE |
+--------------+------------+--------------+
4 rows in set (0.00 sec)
Admin> UPDATE mysql_servers SET status='OFFLINE_HARD' WHERE hostname='172.16.0.1' AND hostgroup_id=1;
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname | status |
+--------------+------------+--------------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | OFFLINE_SOFT |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | OFFLINE_HARD |
+--------------+------------+--------------+
4 rows in set (0.00 sec)Re-enabling an offline / disabled backend server
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------------+
| hostgroup_id | hostname | status |
+--------------+------------+--------------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | OFFLINE_SOFT |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | OFFLINE_HARD |
+--------------+------------+--------------+
4 rows in set (0.00 sec)
Admin> UPDATE mysql_servers SET status='ONLINE' WHERE status NOT IN ('ONLINE');
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname | status |
+--------------+------------+--------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | ONLINE |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)Query Logging
To enable logging in JSON format it is required to set mysql-eventslog_format=2.
SET mysql-eventslog_format=2;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;ProxySQL Statistics
- stats.stats_mysql_connection_pool
- stats_mysql_commands_counters
- stats_mysql_query_digest
Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+----------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+----------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 10 | mysql-01 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 379 |
| 20 | mysql-01 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 379 |
| 20 | mysql-02 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 715 |
| 20 | mysql-03 | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 375 |
+-----------+----------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
4 rows in set (0.01 sec)Admin> SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN | 1921940 | 7249 | 4214 | 2106 | 570 | 340 | 14 | 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| COMMIT | 5986400 | 7249 | 119 | 3301 | 1912 | 1864 | 44 | 8 | 1 | 0 | 0 | 0 | 0 | 0 |
| DELETE | 2428829 | 7249 | 325 | 5856 | 585 | 475 | 5 | 3 | 0 | 0 | 0 | 0 | 0 | 0 |
| INSERT | 2260129 | 7249 | 356 | 5948 | 529 | 408 | 6 | 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| SELECT | 40461204 | 101490 | 12667 | 69530 | 11919 | 6943 | 268 | 149 | 13 | 1 | 0 | 0 | 0 | 0 |
| UPDATE | 6635032 | 14498 | 333 | 11149 | 1597 | 1361 | 42 | 16 | 0 | 0 | 0 | 0 | 0 | 0 |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.00 sec)Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| hostgroup | schemaname | username | client_address | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time | sum_rows_affected | sum_rows_sent |
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
| 10 | information_schema | appuser | | 0xE1880433C2B1F950 | insert into testdb.insert_test select @@hostname,now() | 5 | 1615910122 | 1615910126 | 7449 | 941 | 3483 | 0 | 0 |
| 10 | information_schema | appuser | | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 5 | 1615910122 | 1615910126 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------------+----------+----------------+--------------------+--------------------------------------------------------+------------+------------+------------+----------+----------+----------+-------------------+---------------+
2 rows in set (0.02 sec)Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+--------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+--------------------------------------------------------+
| 10 | 7449 | 5 | insert into testdb.insert_test select @@hostname,now() |
| 10 | 0 | 5 | select @@version_comment limit ? |
+----+----------+------------+--------------------------------------------------------+
2 rows in set (0.01 sec)Advanced Query Rules
Query Rules in MySQL
Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;
+-------------------------------------+-----------------------+
| match_digest | destination_hostgroup |
+-------------------------------------+-----------------------+
| ^SELECT c FROM sbtest1 WHERE id=\?$ | 2 |
| DISTINCT c FROM sbtest1 | 2 |
+-------------------------------------+-----------------------+
2 rows in set (0.00 sec)Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------------------------------------------+
| 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? |
| 2 | 3203582 | 5001 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 3142041 | 5001 | COMMIT |
| 1 | 2270931 | 5001 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 2021320 | 5003 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 1768748 | 5001 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 1697175 | 5003 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 1346791 | 5001 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 1263259 | 5001 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 1191760 | 5001 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 875343 | 5005 | BEGIN |
+----+----------+------------+----------------------------------------------------------------------+
11 rows in set (0.00 sec)Read/Write Split
Read/write split using different ports
INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,10,1), (2,1,6402,20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanentBasic read/write split using regex
DO NOT USE THE ABOVE EXAMPLE IN PRODUCTION
UPDATE mysql_users SET default_hostgroup=10; # by default, all goes to HG10
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK; # if you want this change to be permanent
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),
(2,1,'^SELECT',20,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanentRead/write split using regex and digest
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+----------+--------+
| digest | SUBSTR(digest_text,0,25) | count_star | sum_time | avg_time | pct |
+--------------------+--------------------------+------------+---------------+----------+--------+
| 0x38BE36BDFFDBE638 | SELECT instance.name as | 59360371 | 1096562204931 | 18472 | 13.006 |
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390 | 185951894994 | 1270249 | 2.205 |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid | 592281 | 40215136635 | 67898 | 0.477 |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130 | 24842335265 | 562935 | 0.295 |
| 0x9EED412C6E63E477 | SELECT a.id as accountid | 961768 | 24116011513 | 25074 | 0.286 |
+--------------------+--------------------------+------------+---------------+----------+--------+
5 rows in set (0.00 sec)using digest
INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)
VALUES
(1,1,'0x38BE36BDFFDBE638',20,1);using regex
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT COUNT\(\*\)',20,1);Query Rewrite
Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------------------------------------------+
| 1 | 8150528 | 5307 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 7341765 | 5304 | COMMIT |
| 2 | 5717866 | 7860 | SELECT c FROM sbtest1 WHERE id=? |
| 1 | 4807609 | 5307 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 4164131 | 5308 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3731299 | 5307 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3156638 | 5305 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 3074430 | 5306 | UPDATE sbtest1 SET c=? WHERE id=? |
| 2 | 2857863 | 1705 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1 | 2732332 | 5304 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2165367 | 5310 | BEGIN |
| -1 | 0 | 3602 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 45235 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | match_digest | match_pattern | replace_pattern | cache_ttl | apply |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10 | ^SELECT c FROM sbtest1 WHERE id=\? | NULL | NULL | 5000 | 1 |
| 4856 | 20 | DISTINCT c FROM sbtest1 | NULL | NULL | 5000 | 0 |
| 4856 | 30 | NULL | DISTINCT(.*)ORDER BY c | DISTINCT\1 | NULL | 1 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+-------------------------------------------------------------+
| 1 | 7240757 | 4856 | COMMIT |
| 1 | 6127168 | 4856 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2 | 4264263 | 7359 | SELECT c FROM sbtest1 WHERE id=? |
| 1 | 4081063 | 4856 | UPDATE sbtest1 SET k=k+? WHERE id=? |
| 1 | 3497644 | 4856 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3270527 | 4856 | DELETE FROM sbtest1 WHERE id=? |
| 1 | 3193123 | 4856 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| 1 | 3124698 | 4856 | UPDATE sbtest1 SET c=? WHERE id=? |
| 1 | 2866474 | 4856 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) |
| 1 | 2538840 | 4856 | BEGIN |
| 2 | 1889996 | 1633 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| -1 | 0 | 3223 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? |
| -1 | 0 | 41201 | SELECT c FROM sbtest1 WHERE id=? |
+----+----------+------------+-------------------------------------------------------------+
13 rows in set (0.00 sec)Data Sharding & Transformation
Sharding in ProxySQL
User based sharding
INSERT INTO mysql_users
(username, password, active, default_hostgroup, comment)
VALUES
('accounts', 'shard0_pass', 1, 0, 'Routed to the accounts shard'),
('transactions', 'shard1_pass', 1, 1, 'Routed to the transactions shard'),
('logging', 'shard2_pass', 1, 2, 'Routed to the logging shard');
LOAD MYSQL USERS RULES TO RUNTIME;
SAVE MYSQL USERS RULES TO DISK;Schema based sharding
INSERT INTO mysql_query_rules (rule_id, active, schemaname,
destination_hostgroup, apply)
VALUES
(1, 1, 'shard_0', 0, 1),
(2, 1, 'shard_1', 1, 1),
(3, 1, 'shard_2', 2, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;Data based sharding
Data based sharding is the most complex type of sharding.
Failover Detection
Backend’s health check
mysql_servers은 RUNTIME에로드되기 전에도 테이블의 내용을 기반으로 연결 및 핑 모니터링이 수행 된다는 것입니다. 이 접근 방식은 의도적입니다. 이렇게하면 프로덕션에 노드를 추가하기 전에 기본 상태 확인을 수행 할 수 있습니다.
admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+----------+------+------------------+-------------------------+---------------+
| mysql-02 | 3306 | 1615909440358636 | 2963 | NULL |
| mysql-01 | 3306 | 1615909439688804 | 3129 | NULL |
| mysql-03 | 3306 | 1615909439018691 | 2516 | NULL |
| mysql-02 | 3306 | 1615909380144622 | 3530 | NULL |
| mysql-03 | 3306 | 1615909379616115 | 2158 | NULL |
| mysql-01 | 3306 | 1615909379087750 | 2622 | NULL |
+----------+------+------------------+-------------------------+---------------+
6 rows in set (0.01 sec)Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+----------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+----------+------+------------------+----------------------+------------+
| mysql-02 | 3306 | 1615909499134205 | 320 | NULL |
| mysql-03 | 3306 | 1615909499048568 | 400 | NULL |
| mysql-01 | 3306 | 1615909498961603 | 217 | NULL |
| mysql-02 | 3306 | 1615909489209406 | 873 | NULL |
| mysql-01 | 3306 | 1615909489103038 | 947 | NULL |
| mysql-03 | 3306 | 1615909488996409 | 484 | NULL |
| mysql-01 | 3306 | 1615909479218781 | 354 | NULL |
| mysql-02 | 3306 | 1615909479107152 | 318 | NULL |
| mysql-03 | 3306 | 1615909478995644 | 238 | NULL |
| mysql-03 | 3306 | 1615909469260573 | 574 | NULL |
+----------+------+------------------+----------------------+------------+
10 rows in set (0.00 sec)LOAD MYSQL SERVERS TO RUNTIME;
Admin> 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 | mysql-01 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | mysql-01 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | mysql-02 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | mysql-03 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)Monitor Module
The Monitor Module is responsible for a series of checks against the backends.
It currently supports 4 types of checks:
- connect : it connects to all the backends, and success/failure is logged in table mysql_server_connect_log;
- ping : it pings to all the backends, and success/failure is logged in table mysql_server_ping_log . In the case of mysql-monitor_ping_max_failures missing a heartbeat, it sends a signal to MySQL_Hostgroups_Manager to kill all connections;
- replication lag : it checks Seconds_Behind_Master to all backends configured with max_replication_lag greater than 0, and the check is logged in table mysql_server_replication_lag_log. If Seconds_Behind_Master > max_replication_lag the server is shunned until Seconds_Behind_Master < max_replication_lag ;
- read only : it checks read_only for all hosts in the hostgroups in table mysql_replication_hostgroups, and the check is logged in table mysql_server_read_only_log . If read_only=1 the host is copied/moved to the reader_hostgroup, while if read_only=0 the host is copied/moved to the writer_hostgroup .
Automatically shunning slaves with replication lag
Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
+--------------+------------+---------------------+
| hostgroup_id | hostname | max_replication_lag |
+--------------+------------+---------------------+
| 0 | 172.16.0.1 | 0 |
| 1 | 172.16.0.2 | 0 |
| 1 | 172.16.0.3 | 0 |
+--------------+------------+---------------------+
3 rows in set (0.00 sec)
Admin> UPDATE mysql_servers SET max_replication_lag=30 WHERE hostname='172.16.0.3';
Admin> SELECT hostgroup_id,hostname,max_replication_lag FROM mysql_servers;
+--------------+------------+---------------------+
| hostgroup_id | hostname | max_replication_lag |
+--------------+------------+---------------------+
| 0 | 172.16.0.1 | 0 |
| 1 | 172.16.0.2 | 0 |
| 1 | 172.16.0.3 | 30 |
+--------------+------------+---------------------+
3 rows in set (0.00 sec)
## Removing a backend server
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname | status |
+--------------+------------+--------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.2 | ONLINE |
| 1 | 172.16.0.3 | ONLINE |
| 1 | 172.16.0.1 | ONLINE |
+--------------+------------+--------+
4 rows in set (0.00 sec)
Admin> DELETE FROM mysql_servers WHERE hostgroup_id=1 AND hostname IN ('172.16.0.1','172.16.0.2');
Query OK, 2 rows affected (0.00 sec)
Admin> SELECT hostgroup_id,hostname,status FROM mysql_servers;
+--------------+------------+--------+
| hostgroup_id | hostname | status |
+--------------+------------+--------+
| 0 | 172.16.0.1 | ONLINE |
| 1 | 172.16.0.3 | ONLINE |
+--------------+------------+--------+
2 rows in set (0.00 sec)
> 자세한 정보는 [공식문서](https://proxysql.com/documentation/) 또는 [github](https://github.com/sysown/proxysql)을 참고 부탁드립니다.
> 이미지 출처 : https://www.percona.com/blog/2018/06/11/proxysql-experimental-feature-native-clustering/
Data Engineer