ProxySQL has an advanced multi-core architecture. It’s built from the ground up to support hundreds of thousands of concurrent connections, multiplexed to potentially hundreds of backend servers. The largest ProxySQL deployment spans several hundred proxies.
Query Caching
Forget applications that generate load by inefficiently accessing the same results over and over again. ProxySQL quickly jumps in with its advanced rule engine. Results can also be cached for a configurable timespan, in native MySQL packets format.
Query Routing
Take an advanced case, where different classes of queries need to be routed to different MySQL clusters, with different configurations. Solve it with ProxySQL’s hostgroup concept. Based on an advanced matching engine, it is able to route queries transparently towards the destination cluster that can execute them most efficiently.
Supports failover
While ProxySQL does not offer support failover as a feature, it collaborates smoothly with the existing tools that enable it. It monitors the health of the backends it communicates with and is able to temporarily shun them based on configurable error rates.
Advanced configuration with 0 downtime
ProxySQL’s configuration system is inspired from routers. You can dynamically configure everything, persist the configuration and modify it. All with 0 downtime.
Application layer proxy
ProxySQL does not forward traffic blindly. It understands the MySQL protocol and acts accordingly. That’s why it can easily serve advanced use-cases such as sticky transactions or real-time, in-depth statistics generation about the workload.
Cross platform
ProxySQL works out of the box on multiple flavors of Linux and FreeBSD. Moreover, it should be easily portable to other platforms where a gcc-compatible toolchain is available.
Advanced topology support
Cascading proxies for extra availability and flexibility. Complex MySQL topologies support, involving replication and failover or query mirroring. All of it performed effortlessly by ProxySQL.
Firewall
In case of offending queries that cause problems to the DB (SQL injection or inefficient retrieval of information via SELECT * without WHERE, for example), ProxySQL acts as a gatekeeper between the application and the DB, allowing DBAs to react quickly.
Lets Install
First follow the guide and install MariaDB Cluster. (https://linuxglobe.com/2020/01/17/creating-mariadb-galera-clustering/)
Install ProxySQL
yum install "perl(DBD::mysql)" -y
rpm -ivh https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm
systemctl start proxysqlCheck Status of ProxySQL
systemctl status proxysql
Enter into ProxySQL admin
mysql -u admin -padmin -P6032 -h 127.0.0.1We have a cluster to MariaDB server with named IP’s 192.168.1.1, 192.168.1.2, 192.168.1.3 Respectively. Now we will create read and write host group for all the following above IP’s. hostgroup_id 10 is for write group and hostgroup_id 20 is for read group.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, comment) VALUES (10, '192.168.1.1', 3306, 'write'), (10, '192.168.1.2', 3306, 'write'), (10, '192.168.1.3', 3306, 'write');
In the above syntax hostgroup_id is 10 which is write group. Also I added a comment as write group. I added all 3 of my MariaDB servers as write group.
INSERT INTO mysql_servers (hostgroup_id, hostname, port, comment) VALUES (20, '192.168.1.1', 3306, 'read'), (20, '192.168.1.2', 3306, 'read'), (20, '192.168.1.3', 3306, 'read');
In the above syntax hostgroup_id is 20 which is read group. Also I added a comment as read group. I added all 3 of my MariaDB servers as read group.
Create default hostgroup for mysql users
UPDATE mysql_users SET default_hostgroup=10;
Create users that will connect to application
INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('test', 'test', 1, 10);Create rule which will be sent to write group. In the below example regex is used and select update query is sent to hostgroup 10 which is write group.
INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 10, 1);Create rule which will be sent to read group. In the below example select queries are sent to hostgroup 20 which is read group.
INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 20, 1);Set retries on failure
SET mysql-query_retries_on_failure=10;Setup scheduler for log processing or in case of node failure to take another node in use.
INSERT INTO scheduler (id, active, interval_ms, filename, arg1, arg2, arg3, arg4, arg5) VALUES (1, 1, 1000, '/usr/share/proxysql/tools/proxysql_galera_checker.sh', 10, 20, 1, 1, '/var/lib/proxysql/proxysql.log');In above we can see /usr/share/proxysql/tools/proxysql_galera_checker.sh 10, 20, 1, 1 is a 10 is write hostgroup, 20 is read hostgroup, 1 is as how many writers are used and 1 means if writers are readers.
Lets apply the changes
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;Set correct permission
chown proxysql:proxysql /var/lib/proxysql/reloadOn one of the MariaDB node create a user as below. The monitor user is default user added by proxySQL to monitor the node
CREATE USER 'monitor'@'YOURPROXYSQLIP' IDENTIFIED BY 'monitor';On ProxySQL server.
mysql -u admin -padmin -P6032 -h 127.0.0.1SELECT hostgroup_id, hostname, comment, status FROM mysql_servers;
As you can see in image write group only 1 server is online and other 2 is soft offline that means if online write node goes offline then second write node will take over and for read groups all servers are online.