ProxySQL High Availability (Clustering)

Proxysql High Availability
Mydbops Meetup - 3
Presented by
S.Vignesh Prabhu
M.Aakash
www.mydbops.com info@mydbops.com 1

About Us
2
Aakash .M
Certified MySQL 5.7 DBA, Mydbops
Interested on MySQL Optimization and High Availability systems.
Active MySQL Blogger.
LinkedIn :
https://www.linkedin.com/in/aakash-muthuramalingam-520a77129
Vignesh Prabhu .S
Certified MySQL 5.7 DBA, Mydbops
Interested on MySQL performance and High Availability systems.
Active MySQL Blogger.
LinkedIn : https://www.linkedin.com/in/vignesh-prabhu-s-445827163

About Mydbops
● Founded in 2015, HQ in Bangalore India with 150+ customer base across the globe.
● Mydbops is on Database Consulting with core specialization on MySQL and MongoDB Administration and
Support.
● We have expert team with 20+ certified DBA’s providing full time support and currently managing 300+
servers on premises and cloud.
● We help organisations to architect and scale systems in MySQL/MongoDB by implementing the advanced
technologies in industry which are completely open source.
● We are a leading solution provider in the market for all sort of cloud based database deployments and
management.
3

Mydbops at a GlanceHappy Customers
4

Agenda
● Introduction
● Key Features
● Ensuring HA
● ProxySQL Clustering
● Concepts
● Implementation
5

Proxysql Introduction
● Proxysql is developed by René Cannaò, who is one of the MySQL Support Engineer.
● Initial release 1.1.0
● Released in the year December 2015
● Latest release 2.0.4
● Take over the failures of the Reverse proxies.
7

Replication Breakage Handling
9

Replication Breakage Handling
10

Proxysql - Features
● Runtime Configurable System
● Granular Query Logging
● Monitoring support
● PMM graphs - Proxysql Exporters
● Mirroring - Performance of Rewritten Query
● Clustering
● SQL intelligence
11

Proxysql Clustering
● Introduced in 1.4.2(Beta)
● Apply Changes from any node
● Easy to make configuration changes for multiple Servers
● Automatic Node Rejoining
● Easy to add new nodes
17

Prerequisite:
1) Minimum two nodes for proxysql installation.
2) Proxysql with version 1.4.2 or higher.
3) Port Opening - 6032
Pre-Requisites
18

1) Mysql_query_rules
2) Mysql_servers
3) Mysql_users
4) Proxysql_servers
Key Conﬁguration Tables
20

Runtime_checksums_values
How does it work ?
22

stats_proxysql_servers_checksums
How does it work ?
23

Making Entry in key tables
How does it work ?
24

Checksums_updated
How does it work ?
26

Flow from Log file:
● ProxySQL detected a change in the checksum for table mysql_users
● MySQL Users are fetched from 172.16.3.130:6032
● MySQL Users are loaded to runtime
● MySQL Users are also saved to disk because cluster_mysql_users_save_to_disk=true
● A new checksum is also detected from 172.16.3.132:6032 , but it matches the local one so there is no
need to sync
● A new checksum is also detected from 172.16.3.131:6032 (that is the same proxysql instance), but it
matches the local one so there is again no need to sync
How does it work ?
27

1) Define What to sync.
a) admin-checksum_mysql_query_rules
b) admin-checksum_mysql_servers
c) admin-checksum_mysql_users
2) Define Credentials
a) admin-cluster_username & admin-cluster_password
3) Define interval/frequency checks
a) admin-cluster_check_interval_ms
b) admin-cluster_check_status_frequency
Cluster Admin Variables
29

● Variables used for saving the changes to the disk
○ admin-cluster_mysql_query_rules_save_to_disk
○ admin-cluster_mysql_servers_save_to_disk
○ admin-cluster_mysql_users_save_to_disk
○ admin-cluster_proxysql_servers_save_to_disk
● Variables used to trigger the synchronization
○ admin-cluster_mysql_query_rules_diffs_before_sync
○ admin-cluster_mysql_servers_diffs_before_sync
○ admin-cluster_mysql_users_diffs_before_sync
○ admin-cluster_proxysql_server_diffs_before_sync
How to implement it ?
30

Proxysql Servers Table
31

Proxysql Servers Table
mysql> INSERT INTO proxysql_servers (hostname) VALUES ('172.16.3.130'), ('172.16.3.131'),
('172.16.3.132');
Query OK, 3 rows affected (0.00 sec)
mysql> LOAD PROXYSQL SERVERS TO RUNTIME;
mysql> SAVE PROXYSQL SERVERS TO DISK;
32

● SQL Aware Proxy VS Reverse Proxy
● Key Features of ProxySQL
● Basic Topology
● High Availabilities Topologies
● Clustering Concepts
● Clustering Implementation
Summary
33

ProxySQL High Availability (Clustering)

More Related Content

What's hot

Similar to ProxySQL High Availability (Clustering)

More from Mydbops

Recently uploaded

ProxySQL High Availability (Clustering)