Jakiś czas temu miałem przyjemność, zaprojektować i wdrożyć produkcyjnie system loadbalancingu zapytań sql-owych do serwera bazodanowego. Jego zadaniem było rozdzielanie ruchu pomiędzy:

• serwerem typu master, który miał obsługiwać operacje zapisu i niektóre odczytu

• serwerem typu slave, który miał otrzymać pozostałe - tylko do odczytu.

Sam proces instalacji oraz konfiguracji przedstawię poniżej:

Na początku instalujemy paczkę, po czym dodajemy aplikację do autostartu oraz uruchamiamy:

yum install -y https://github.com/sysown/proxysql/releases/download/v2.0.6/proxysql-2.0.6-1-centos7.x86_64.rpm
systemctl enable proxysql.service
systemctl start proxysql.service

By wszystko sieciowo działało, wymagane jest również zezwolenie na ruch, na firewallu.

firewall-cmd --permanent --add-port=6033/tcp
firewall-cmd --reload

Następnie przechodzimy do konsoli admina i zmieniamy hasło dla naszego użytkownika.

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='ProxySQL> '
UPDATE global_variables SET variable_value='admin:przykładowe_haslo_123' WHERE variable_name='admin-admin_credentials';
LOAD ADMIN VARIABLES TO RUNTIME;
SAVE ADMIN VARIABLES TO DISK;

Dodajemy serwery db1 i db2 w adminie (za X.X.X.21 i X.X.X.22 wpisujemy ip serwerów).

DELETE FROM mysql_servers;
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (0,'X.X.X.21',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'X.X.X.22',3306);
SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Logujemy się na standardowe bazy na db1 i db2 (z portu 3306).

mysql -u root -p123
mysql> CREATE USER 'monitor'@'%' IDENTIFIED BY ' przykładowe_haslo_123';
mysql> GRANT SELECT ON sys.* TO  'monitor'@'%';
mysql> FLUSH PRIVILEGES;
mysql> EXIT

Wracamy znów do proxysql monitora, gdzie ustawiamy odpowiednie dane do logowania.

ProxySQL>mysql -u admin -p -h 127.0.0.1 -P6032 --prompt='ProxySQL> '
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='przykładowe_haslo_123' WHERE variable_name='mysql-monitor_password';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Następnie sprawdzamy połączenie z serwerami.

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

Dodajemy użytkownika do logowania się przez monitor. Na niego będą się logować użytkownicy zewnętrzni.

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('mysql_user_1','przykladowe_haslo_123',0);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
SELECT username, default_hostgroup FROM mysql_users;

Wychodzimy z klienta i testujemy możliwość logowania na zwykłe konto użytkownika przez proxy, standardowym klientem mysql.

mysql -u mysql_user_1 -p -h 127.0.0.1 -P6033;
select @@hostname;
show databases;

Poniżej przedstawiam przykładowe dodanie reguły.

DELETE FROM mysql_query_rules;
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply,multiplex) VALUES(1,'^SELECT `auctions`.*',0,1,0);
INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply,multiplex) VALUES(1,'^SELECT `products`.*',1,1,0);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
SELECT rule_id, match_pattern,destination_hostgroup hg_id, apply FROM mysql_query_rules WHERE active=1;

*W przypadku gdy zapytanie nie trafi do żadnej reguły, zostanie wykonane na serwerze o najniższym hostgroup).

Pobieranie statystyk

Poniżej przedstawiam krótki spis najczęściej wykorzystywanych poleceń do odczytu statystyk:

SHOW TABLES FROM stats;
SELECT * FROM stats_mysql_connection_pool;
SELECT hostgroup_id,hostname,port,status FROM mysql_servers;
SELECT hostgroup, SUM(Queries), SUM(Bytes_data_sent), SUM(Bytes_data_recv) FROM stats_mysql_connection_pool GROUP BY hostgroup\G;
SELECT * FROM stats_mysql_query_digest;

Polecam samemu przetestować to rozwiązanie, gdyż bardzo intuicyjnie się je konfiguruje, a samo zagadnienie może przyśpieszyć działanie najwazniejszych komponentów aplikacji.