Read/Write-Split und Health-Checks für Replicas mit ProxySQL

Befehl

-- On MySQL (source): monitor user
CREATE USER 'proxysql_mon'@'%' IDENTIFIED BY 'M0n#itor2024';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql_mon'@'%';

-- ProxySQL admin: mysql -u admin -p -h 127.0.0.1 -P 6032
UPDATE global_variables SET variable_value = 'proxysql_mon' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'M0n#itor2024' WHERE variable_name = 'mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, check_type, comment) VALUES (10, 20, 'read_only', 'shop');
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag) VALUES (10, 'db1', 3306, 0), (20, 'db2', 3306, 30), (20, 'db3', 3306, 30);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) VALUES ('app', 'App#Pass2024', 10, 1);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (100, 1, '^SELECT .* FOR (UPDATE|SHARE)', 10, 1), (200, 1, '^SELECT ', 20, 1);
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;

-- Verify
SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;
SELECT hostgroup, srv_host, status, ConnUsed, Queries, Latency_us FROM stats_mysql_connection_pool;
SELECT hostgroup, substr(digest_text, 1, 60) AS digest, count_star, sum_time / 1000 AS sum_ms FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10;

Was es tut

Verwaltet Writer- (10) und Reader-Hostgroups (20) automatisch anhand des Flags read_only; leitet SELECTs an Replicas und sperrende Lesezugriffe sowie Schreibvorgänge an die Quelle; nimmt eine Replica, die max_replication_lag überschreitet, vorübergehend heraus (SHUNNED).

Wann einsetzen

Um in einer asynchronen Replikationstopologie Leselast zu verteilen, ohne die Anwendung anzufassen; für Connection Pooling und Routing / Caching / Firewalling je Abfrage.

Vorsicht

Die Konfiguration hat 3 Ebenen (MEMORY → RUNTIME → DISK): Ohne LOAD ... TO RUNTIME ändert sich nichts, ohne SAVE ... TO DISK geht sie beim Neustart verloren. Die read_only-Prüfung erfordert super_read_only=ON auf Replicas; sonst werden Replicas in die Writer-Hostgroup verschoben. Jedes SELECT an Replicas zu senden, verursacht wegen des Lags Read-after-Write-Fehler; Regeln nur für schwere / Reporting-Abfragen auf Basis von stats_mysql_query_digest zu schreiben ist sicherer. Die Admin-Schnittstelle ist SQLite: MySQL-Funktionen wie LEFT() gibt es nicht, substr() verwenden. Ändere das Standardkonto admin:admin (admin-admin_credentials).