ProxySQL快速入门

最新推荐文章于 2024-05-23 19:15:47 发布
最新推荐文章于 2024-05-23 19:15:47 发布
阅读量557 收藏 2
点赞数 1
分类专栏： 数据库 文章标签： mysql centos 数据库
原文链接：https://proxysql.com/documentation/
版权

ProxySQL官网

准备

以CentOS 7.x下载为例：ProxySQL v2.4.5 x86_64 for RedHat & CentOS 7

  • yum install -y proxysql-2.4.5-1-centos7.x86_64.rpm 安装
  • systemctl start proxysql 启动

两种配置方式：

  1. Admin interface
  2. /etc/proxysql.cnf

Admin interface

进入控制台：mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
初始化用户名密码均为 admin

note: If your MySQL client version is version 8.04 or higher add --default-auth=mysql_native_password to the above command to connect to the admin interface.

/etc/proxysql.cnf

  • admin_variables: contains global variables that control the functionality of the admin interface.
  • mysql_variables: contains global variables that control the functionality for handling the incoming MySQL traffic.
  • mysql_servers: contains rows for the mysql_servers table from the admin interface. Basically, these define the backend servers towards which the incoming MySQL traffic is routed. Rows are encoded as per the .cfg file format, here is an example:
mysql_servers =
(
  {
    address="127.0.0.1"
    port=3306
    hostgroup=0
    max_connections=200
  }
)
  • mysql_users: contains rows for the mysql_users table from the admin interface. Basically, these define the users which can connect to the proxy, and the users with which the proxy can connect to the backend servers. Rows are encoded as per the .cfg file format, here is an example:
mysql_users:
(
  {
    username = "root"
    password = "root"
    default_hostgroup = 0
    max_connections=1000
    default_schema="information_schema"
    active = 1
  }
)
  • mysql_query_rules: contains rows for the mysql_query_rules table from the admin interface. Basically, these define the rules used to classify and route the incoming MySQL traffic, according to various criteria (patterns matched, user used to run the query, etc.). Rows are encoded as per the .cfg file format, here is an example (Note: the example is a very generic query routing rule and it is recommended to create specific rules for queries rather than using a generic rule such as this):
mysql_query_rules:
(
  {
    rule_id=1
    active=1
    match_pattern="^SELECT .* FOR UPDATE$"
    destination_hostgroup=0
    apply=1
  },
  {
    rule_id=2
    active=1
    match_pattern="^SELECT"
    destination_hostgroup=1
    apply=1
  }
)
  • top-level configuration item: datadir, as a string, to point to the data dir.

基本配置

添加mysql服务器

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+

note: proxysql通过MySQL的read_only = 1配置判断是否是读写，primary数据库读写，slave只读，则 set global read_only = 1

监控

  1. To create the user in MySQL connect to the PRIMARY and execute:
mysql> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)
  1. add the credentials of the monitor user to ProxySQL
ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)
  1. configure the various monitoring intervals
ProxySQL Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)

ProxySQL Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------+---------------------------------------------------+
| variable_name                          | variable_value                                    |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history                  | 600000                                            |
| mysql-monitor_connect_interval         | 2000                                              |
| mysql-monitor_connect_timeout          | 200                                               |
| mysql-monitor_ping_interval            | 2000                                              |
| mysql-monitor_ping_timeout             | 100                                               |
| mysql-monitor_read_only_interval       | 2000                                              |
| mysql-monitor_read_only_timeout        | 100                                               |
| mysql-monitor_replication_lag_interval | 10000                                             |
| mysql-monitor_replication_lag_timeout  | 1000                                              |
| mysql-monitor_username                 | monitor                                           |
| mysql-monitor_password                 | monitor                                           |
| mysql-monitor_query_variables          | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status             | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS    |
| mysql-monitor_query_interval           | 60000                                             |
| mysql-monitor_query_timeout            | 100                                               |
| mysql-monitor_timer_cached             | true                                              |
| mysql-monitor_writer_is_also_reader    | true                                              |
+----------------------------------------+---------------------------------------------------+
17 rows in set (0.00 sec)
  1. Apply changes: LOAD MYSQL VARIABLES TO RUNTIME . Persist the configuration changes SAVE MYSQL VARIABLES TO DISK .
Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.02 sec)

健康检查

健康检查的库monitor

ProxySQL Admin> SHOW TABLES FROM monitor;
+----------------------------------+
| tables                           |
+----------------------------------+
| mysql_server_connect             |
| mysql_server_connect_log         |
| mysql_server_ping                |
| mysql_server_ping_log            |
| mysql_server_read_only_log       |
| mysql_server_replication_lag_log |
+----------------------------------+
6 rows in set (0.00 sec)

ProxySQL Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+----------------------+---------------+
| hostname  | port | time_start_us    | connect_success_time | connect_error |
+-----------+------+------------------+----------------------+---------------+
| 10.10.0.1 | 3306 | 1456968814253432 | 562                  | NULL          |
| 10.10.0.2 | 3306 | 1456968814253432 | 309                  | NULL          |
| 10.10.0.3 | 3306 | 1456968814253432 | 154                  | NULL          |
+-----------+------+------------------+----------------------+---------------+
3 rows in set (0.00 sec)

ProxySQL Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+-------------------+------------+
| hostname  | port | time_start_us    | ping_success_time | ping_error |
+-----------+------+------------------+-------------------+------------+
| 10.10.0.1 | 3306 | 1456968828686787 | 124               | NULL       |
| 10.10.0.2 | 3306 | 1456968828686787 | 62                | NULL       |
| 10.10.0.3 | 3306 | 1456968828686787 | 57                | NULL       |
+-----------+------+------------------+-------------------+------------+
3 rows in set (0.01 sec)

复制hostgroups

ProxySQL通过监控mysql server的read_only配置来观察 replication topology. hostgroups可在表mysql_replication_hostgroups中定义，插入时必须定义a pair of READER and WRITER hostgroups

Admin> SHOW CREATE TABLE mysql_replication_hostgroupsG
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
Query OK, 1 row affected (0.00 sec)
// 生效
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

The read_only check results are logged to the mysql_servers_read_only_log table in the monitor database:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+-------+------------------+--------------+-----------+-------+
| hostname  | port  | time_start_us    | success_time | read_only | error |
+-----------+-------+------------------+--------------+-----------+-------+
| 10.10.0.1 | 3306  | 1456969634783579 | 762          | 0         | NULL  |
| 10.10.0.2 | 3306  | 1456969634783579 | 378          | 1         | NULL  |
| 10.10.0.3 | 3306  | 1456969634783579 | 317          | 1         | NULL  |
+-----------+-------+------------------+--------------+-----------+-------+
3 rows in set (0.01 sec)

持久化配置：

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 54 rows affected (0.00 sec)

MySQL Users

配置完 mysql_servers之后要配置mysql_users

Admin> SHOW CREATE TABLE mysql_usersG
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 0,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('stnduser','stnduser',1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_users;                                                                                                                      
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root     |          | 1      | 0       | 1                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
| stnduser | stnduser | 1      | 0       | 1                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)

ProxySQL Admin> LOAD MYSQL USERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
ProxySQL Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

测试

sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run

[ output omitted ]

Note: For older versions of sysbench, report-interval should be removed and --db-ps-mode=disable added.

ProxySQL Statistics, the foundation for Query Rules

ProxySQL 在库stats 中提供了实时的数据。

ProxySQL Admin> SHOW TABLES FROM stats;
+--------------------------------+
| tables                         |
+--------------------------------+
| stats_mysql_query_rules        |
| stats_mysql_commands_counters  |
| stats_mysql_processlist        |
| stats_mysql_connection_pool    |
| stats_mysql_query_digest       |
| stats_mysql_query_digest_reset |
| stats_mysql_global             |
+--------------------------------+
7 rows in set (0.00 sec)

stats_mysql_connection_pool

ProxySQL Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| hostgroup | srv_host  | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| 1         | 10.10.0.1 | 3306     | ONLINE       | 0        | 4        | 5      | 0       | 144982  | 7865186         | 278734683       |
| 2         | 10.10.0.1 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
| 2         | 10.10.0.2 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
| 2         | 10.10.0.3 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
4 rows in set (0.00 sec)

stats_mysql_commands_counters

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

stats_mysql_query_digest

ProxySQL Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | digest             | digest_text                                                          | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | sbtest             | stnduser | 0x13781C1DBF001A0C | SELECT c FROM sbtest1 WHERE id=?                                     | 72490      | 1456971810 | 1456971830 | 17732590 | 23       | 58935    |
| 1         | sbtest             | stnduser | 0x704822A0F7D3CD60 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249       | 1456971810 | 1456971830 | 9629225  | 20       | 121604   |
| 1         | sbtest             | stnduser | 0xADF3DDF2877EEAAF | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          | 7249       | 1456971810 | 1456971830 | 6650716  | 26       | 76159    |
| 1         | sbtest             | stnduser | 0x5DBEB0DD695FBF25 | COMMIT                                                               | 7249       | 1456971810 | 1456971830 | 5986400  | 64       | 59229    |
| 1         | sbtest             | stnduser | 0xCCB481C7C198E52B | UPDATE sbtest1 SET k=k+? WHERE id=?                                  | 7249       | 1456971810 | 1456971830 | 3948930  | 44       | 47860    |
| 1         | sbtest             | stnduser | 0x7DD56217AF7A5197 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     | 7249       | 1456971810 | 1456971830 | 3235986  | 22       | 24624    |
| 1         | sbtest             | stnduser | 0xE75DB8313E268CF3 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                | 7249       | 1456971810 | 1456971830 | 3211197  | 51       | 29569    |
| 1         | sbtest             | stnduser | 0x5A23CA36FB239BC9 | UPDATE sbtest1 SET c=? WHERE id=?                                    | 7249       | 1456971810 | 1456971830 | 2686102  | 23       | 27779    |
| 1         | sbtest             | stnduser | 0x55319B9EE365BEB5 | DELETE FROM sbtest1 WHERE id=?                                       | 7249       | 1456971810 | 1456971830 | 2428829  | 29       | 11676    |
| 1         | sbtest             | stnduser | 0x10634DACE52A0A02 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              | 7249       | 1456971810 | 1456971830 | 2260129  | 61       | 13711    |
| 1         | sbtest             | stnduser | 0x4760CBDEFAD1519E | BEGIN                                                                | 7249       | 1456971810 | 1456971830 | 1921940  | 30       | 39871    |
| 1         | information_schema | stnduser | 0x9DD5A40E1C46AE52 | SELECT ?                                                             | 1          | 1456970758 | 1456970758 | 1217     | 1217     | 1217     |
| 1         | information_schema | stnduser | 0xA90D80E5831B091B | SELECT @@port                                                        | 1          | 1456970769 | 1456970769 | 273      | 273      | 273      |
| 1         | information_schema | stnduser | 0x52A2BA0B226CD90D | select @@version_comment limit ?                                     | 2          | 1456970758 | 1456970769 | 0        | 0        | 0        |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
14 rows in set (0.00 sec)

MySQL Query Rules

表mysql_query_rules记录了路由规则

Admin> SHOW CREATE TABLE mysql_query_rules\G
*************************** 1. row ***************************
       table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT,
    digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0),
    replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED,
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED,
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
    comment VARCHAR)
1 row in set (0.00 sec)

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=?,2,1);
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'stnduser','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)
// 生效
ProxySQL Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

注意：

  • 路由规则由 rule_id
  • 只有active=1的规则会被应用
  • 支持正则匹配 ^,$ mark the beginning and the end of a pattern,in this case match_digestormatch_pattern should completely match the query
  • apply=1 表示当前规则匹配上，后续规则不再匹配。

Query Caching

默认不缓存，可在表mysql_query_rules中设定cache_ttl字段（单位ms）

Query Rewrite

提供两种匹配机制：

  • match_digest：match the regular expression against the digest of the query which strips SQL query data (e.g. SELECT c FROM sbtest1 WHERE id=? as represented in stats_mysql_query_digest.query_digest
  • match_pattern：match the regular expression against the actual text of the query (e.g. SELECT c FROM sbtest1 WHERE id=2

ProxySQL读写分离

2个端口区分读写流量

例如：通过ProxySQL Admin来配置路由端口，假设已经配置好primary and replicas configured in the right hostgroups: MySQL writer in hostgroup 1, and MySQL readers in hostgroup 2.

  1. 在mysql-interfaces配置两个端口并重启
SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';
## save it on disk and restart proxysql
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;
  1. 添加路由for incoming port
INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply)
VALUES (1,1,6401,1,1), (2,1,6402,2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

正则区分读写流量（生产环境不适用）

在mysql_query_rules表中插入时加入匹配规则

UPDATE mysql_users SET default_hostgroup=1; # by default, all goes to HG1
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$',1,1),
(2,1,'^SELECT',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

以上的路由规则如下：

  • all SELECT FOR UPDATE statements will be sent to HG1
  • all other SELECT statements will be sent to HG2
  • everything else will be sent to HG1 (the default)

regex和digest智能区分读写流量

  • 配置所有流量至MySQL主节点
  • 分析stats_mysql_query_digest去定位最耗时的SELECT语句
  • 决定哪些语句可以读分离（可以忍受复制延迟，复制主机硬件支持）
  • 配置mysql_query_rules将这些语句路由到副本

定位最耗时的语句

  • 基于total execution time
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time 
FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT%' 
ORDER BY sum_time DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030026798 | 1479082636017 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025902778 | 1206116187539 |
| 0x38BE36BDFFDBE638 | SELECT instance.name as  | 59343662   | 1096236803754 |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362897166 | 488971769571  |
| 0x4A131A16DCFFD6C6 | SELECT i.id as id, i.sta | 934402293  | 475253770301  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.01 sec)
  • 基于 query count
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time 
FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT%' 
ORDER BY count_star DESC LIMIT 5;
+--------------------+--------------------------+------------+---------------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |
+--------------------+--------------------------+------------+---------------+
| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 |
| 0xB081A85245DEA5B7 | SELECT a.ip_id as ip_id, | 2025916528 | 1206123010791 |
| 0x22E0A5C585C53EAD | SELECT id as instanceid, | 1551361254 | 426419508609  |
| 0x3DB4B9FA4B2CB36F | SELECT i.id as instancei | 1465274289 | 415565419867  |
| 0xB4233552504E43B8 | SELECT ir.type as type,  | 1362906755 | 488974931108  |
+--------------------+--------------------------+------------+---------------+
5 rows in set (0.00 sec)
  • 基于max execution time
Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time 
FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT%' 
ORDER BY max_time DESC LIMIT 5;
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | min_time | max_time  |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 445      | 237344243 |
| 0xDA8C56B5644C0822 | SELECT COUNT(*) as total | 44130      | 24842335265  | 562935   | 494      | 231395575 |
| 0x8C1B0405E1AAB9DB | SELECT COUNT(*) as total | 1194       | 1356742749   | 1136300  | 624      | 216677507 |
| 0x6C03197B4A2C34BE | Select *, DateDiff(Date_ | 4796       | 748804483    | 156131   | 607      | 197881845 |
| 0x1DEFCE9DEF3BDF87 | SELECT DISTINCT i.extid  | 592196     | 40209254260  | 67898    | 416      | 118055372 |
+--------------------+--------------------------+------------+--------------+----------+----------+-----------+
5 rows in set (0.01 sec)
  • 其他
Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time 
FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 
ORDER BY sum_time DESC LIMIT 5;

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),3) pct 
FROM stats_mysql_query_digest 
WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 
ORDER BY sum_time DESC LIMIT 5;

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;

Sharding

  • User based sharding
  • Schema based sharding
  • Data based sharding

User based sharding

在表mysql_users创建用户时，指定default_hostgroup即可，这种不需要指定query rules

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;

建议在应用中保存应用到用户的映射：


+--------------+--------------+-------------+
| app_module   | shard_user   | shard_pass  |
+--------------+--------------+-------------+
| CRM          | accounts     | shard0_pass |
| OLTP         | transactions | shard1_pass |
| Log Manager  | logging      | shard1_pass |
+--------------+--------------+-------------+

Schema based sharding

在mysql_query_rules中指定destination_hostgroup：

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

要点：

  • 从一开始就要设计好应用，严格遵循query rules
  • 基本上通过表mysql_query_rules中的match_digest和match_pattern实现
  • sharding一般都是针对表去sharding而不是库
# 针对INSERT分片
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup,
apply)
VALUES
(1, 1, 'loc_account_data \(loc_id, user, acc_id\) VALUES
\(20086020554955909836090724037181646035', 0, 1),
(2, 1, 'loc_account_data \(loc_id, user, acc_id\) VALUES
\(20086020554955909836090724037181646035’, 1, 1),
(3, 1, 'loc_account_data \(loc_id, user, acc_id\) VALUES
\(20086020554955909836090724037181646035’, 2, 1);

# 针对UPDATES / DELETES / SELECTS分片
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup,
apply)
VALUES
(4, 1, 'loc_id = 20086020554955909836090724037181646035', 0, 1),
(5, 1, 'loc_id = 21503957780049285539986052866765125704', 1, 1),
(6, 1, 'loc_id = 75863560943999160082133817802533222835', 2, 1);

sharding considerations

  • user 和 schema 分片简单好维护
  • 针对每个分片都要考虑各自的规则，如读写分离
  • 分片不知三种，以上只是最常见的
T.Y.Bao
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ProxySQL技术实践分享
360技术
04-19 1473
一、ProxySQL基本介绍1.1 前言ProxySQL是 MySQL 的高性能、高可用性、协议感知代理。以下为结合主从复制对ProxySQL读写分离、黑白名单、路由规则等做些基本测试。1.2 基本介绍先简单介绍下ProxySQL及其功能和配置，主要包括：最基本的读/写分离，且方式有多种；可定制基于用户、基于schema、基于语句的规则对SQL语句进行路由，规则很灵活；动态加载配置，即绝大部分的...
【MySql】ProxySQL指南
指错||纠正||建议||水评+点赞&&评论&&关注&&转发，在这里你大概率会有所收获
06-04 2283
ProxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后，还是觉得ProxySQL是一款性能不谙，靠谱稳定的MySQL 中间件产品.它有官方版和 percona 版两种.percona版是在官方版的基础上修改的，添加了几个比较实用的工
海山数据库（He3DB）代理ProxySQL使用详解：（一）架构说明与安装
最新发布
weixin_42744640的博客
05-23 799
后端的MySQL实例可根据用途配置到不同的hostgroup中，由ProxySQL基于7层网络协议,将来源于APP的流量路由到对应的hostgroup内，从而实现负载均衡或者读写分离。通常用于黑名单功能，即如果匹配该规则后，如果其error_msg不为空，则客户端的操作将会接受到error_msg设置的错误信息；transaction_persistent：表示是否持久化事务，设置为1表示开启一个事务后，该事务的所有操作均在同一个hostgroup中操作完成，其会忽略设置的任何查询规则；
高级DBA带你学习Mysql代理Proxysql主从读写分离安装配置例子全网最详细
nasen512的博客
12-09 1871
MySql数据库代理代理技术ProxySql技术详细介绍
搭建proxysql运行配置详解
ziye2630的专栏
07-13 1432
ProxySQL是一款强大的MySQL代理，它可以在你的数据库应用程序和MySQL服务器之间添加一层中间层，以提高查询的性能，提供高可用性和提高查询路由的灵活性。本篇会逐步讲解怎样配置基础信息
mysql进阶1——proxysql中间件
百慕卿君博客
07-25 2107
mysql读写分离方案之proxysql中间件介绍。
ProxySQL快速上手
cug_jiang126com的专栏
06-13 6943
安装并登陆管理界面 1 wgethttps://github.com/sysown/proxysql/releases/download/v1.3.6/proxysql-1.3.6-1-centos67.x86_64.rpm 2 yum install perl-DBD-MySQL 3 rpm -ivh proxysql-1.3.6-1-centos67.x86_64.rpm 4 ser
ProxySQL快速上手.docx
05-17
本文将引导你快速上手ProxySQL的安装和配置，让你掌握如何利用它来优化数据库服务。 首先，要安装ProxySQL，你可以按照以下步骤进行： 1. 从GitHub上下载最新版本的ProxySQL RPM包，例如`wget ...
proxysql 的安装配置
01-26
proxysql 的安装配置 proxysql 是一个高性能的 MySQL 代理服务器，能够提供高可用性、负载均衡、查询分析和缓存等功能。下面是 proxysql 的安装配置知识点： 一、下载和安装 1. 下载 proxysql 的 rpm 包从官网 ...
proxysql 监控详解
01-26
ProxySQL 是一款高性能的 MySQL 代理，用于管理与后端 MySQL 服务器的连接。它具有强大的负载均衡和故障切换功能，同时提供了详细的监控机制来确保服务的稳定性和可靠性。本篇文章将深入探讨 ProxySQL 的监控模块，...
ProxySQL离线安装包CENTOS6.5
12-07
ProxySQL是一款高性能、高可用的MySQL中间件，它主要用于负载均衡、读写分离以及数据库管理。在本场景中，我们关注的是在CENTOS 6.5操作系统上进行离线安装的过程。由于是离线安装，这意味着我们需要手动下载并准备...
ProxySQL最新官方文档
11-23
ProxySQL最新官方文档，带详细目录，全英文，很好用。
proxysql介绍与配置
yeqixian123456的博客
08-14 3977
proxysql实现mysql读写分离1. mysql实现读写分离的方式2. ProxySQL简介3. ProxySQL安装4. ProxySQL的Admin管理接口5. 和admin管理接口相关的变量5.1 admin-admin_credentials5.2 admin-stats_credentials5.3 admin-mysql_ifaces6. 多层配置系统6.1 proxysql中的库6.2 ProxySQL多层配置系统 1. mysql实现读写分离的方式 mysql 实现读写分离的方式有以下
【ProxySql】Mysql如何实现读写分离？看这一篇就够了
编码人生
09-11 747
ProxySQL是一个高性能的开源数据库代理，专门用于在数据库系统和客户端之间进行流量路由和负载均衡。它能够管理和优化数据库连接，提高应用程序的性能和可用性。ProxySQL支持多种数据库系统，包括MySQL、MariaDB、Percona等，使其成为了许多企业和开发者在构建大规模、高性能数据库架构时的首选工具。ProxySQL能够将客户端请求均匀地分配到多个后端数据库服务器上，从而实现负载均衡，提高数据库的吞吐量和响应能力。
ProxySQL 基础篇
山有山的高度，海有海的胸怀。
02-10 616
1、ProxySQL 介绍 ProxySQL 是基于 MySQL 的一款开源的中间件的产品，是一个灵活的 MySQL 代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载（无需重启 ProxySQL 服务），故障切换和一些 SQL 的过滤功能。介绍：相关 ProxySQL 的网站 https://www.proxysql.com/ ht...
ProxySQL--灵活强大的MySQL代理层
热门推荐
奋进的K
09-25 3万+
本文是我在学习和验证ProxySQL的过程中，从初识（对其机制猜想或者仅凭几次命令的结果臆断其原理），到逐渐深入（模拟各种场景测试、抓包分析、与作者交流）过程中的思路和方法和结论的记录。ProxySQL项目网址亮点： 几乎所有的配置均可在线更改（其配置数据基于SQLite存储），无需重启proxysql 基于正则和client_addr的可完全自主控制的路由规则 详细的状态统计，统计结果和pt-qu
mysql替代mha中间件_MYSQL 中间件 为什么选择 PROXYSQL VS MHA
weixin_28683391的博客
02-05 373
MYSQL 的中间件其实也不少，但实际上用的比较广的(非分库分表)的选择点基本上会落到 PROXYSQL 和 MyRouter 两个中间件中，1使用的人数多，2丰富的文档和相当多的案例实际上proxysql 可以算是一个支持广泛的中间件，下面是其支持的产品线 本着没有使用就没有发言权的原则，以下内容仅仅是针对proxysql 中间件的使用的一些特点和优点来阐述 从官方网址 https://pr...
【ProxySQL 介绍】
06-03 365
ProxySQL  ProxySQL 是一个高性能，高可用性，的数据中间层项目。   高性能 ProxySQL 具有先进的多核架构。 它从根本上构建，支持数十万个并发连接，复用到可能数百个后端服务器。 最大的 ProxySQL 部署跨越了几百个代理。     高效的工作负载管理 ProxySQL 可帮助您从 MySQL 集群中挤出最后一滴性能，而无需控制生成查询的应用程序。  ...
proxy SQL实现MySQL主从读写分离
小啊宇的博客
09-23 1244
proxy SQL实现MySQL主从读写分离proxy SQL简介同类型产品proxy SQL功能安装环境MySQL主从搭建proxy SQL安装下载安装 ProxySQL启动 ProxySQL查看 ProxySQL 的版本管理员登录 ProxySQL配置 ProxySQL 所需账户ProxySQL 的多层配置系统在不同层次间移动配置管理配置小结 proxy SQL简介 ProxySQL 是一款可以实际用于生产环境的 MySQL 中间件，它有官方版和 percona 版两种。percona版是在官方版的基础
快速指南：Proxysql的下载、安装与高级配置
Proxysql是一款用于MySQL数据库的高性能、高可用性和可扩展性解决方案，它提供了代理和管理功能，以优化数据库连接性能和安全性。本篇文章主要介绍了Proxysql的安装与配置过程。 首先，我们来了解Proxysql的下载...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值