HA and clustering solution: ProxySQL as an intelligent router for Galera and Group Replication Rene Cannao ProxySQL

Introduction

3 A bit about me… Rene Cannao • Founder of ProxySQL • MySQL DBA

4 A bit about ProxySQL LLC We provide services to help build, support as well as improve the performance & reliability of your Cloud-Based or On-Premise MySQL infrastructure: • ProxySQL Development • Remote Consulting • ProxySQL Support Services • ProxySQL Training

5 Companies using ProxySQL

What is ProxySQL?

What is ProxySQL? • A "Layer 7" database proxy • MySQL / ClickHouse protocol aware • High Performance • High Availability • Feature Rich

Architecture Overview • Clients connect to ProxySQL • Requests are evaluated • Actions are performed (e.g. RW Split / Sharding / etc.)

What is Galera Cluster and Oracle Group Replication?

What is Galera Cluster? • Multi-master / Active-Active Clustered MySQL Solution • Synchronous Replication (certification based) • Multi-threaded Replication • InnoDB Compliant • Suitable for LAN, WAN and Cloud Solutions • IST (incremental) & SST (full) for state transfer • Auto reconnect mechanism for rejected nodes

What is Galera Cluster? • Multi-master / Active-Active Clustered MySQL Solution • Synchronous Replication (certification based) • Multi-threaded Replication • InnoDB Compliant • Suitable for LAN, WAN and Cloud Solutions • IST (incremental) & SST (full) for state transfer • Auto reconnect mechanism for rejected nodes

What is Oracle Group Replication? • Multi-master / Active-Active Clustered MySQL Solution • Single master by default (group_replication_single_primary_mode) • Synchronous Replication (certification based) • Multi-threaded Replication • InnoDB Compliant • Suitable for LAN and low latency networks • State transfer is based on GTID matching

ProxySQL for Galera

Galera Support in ProxySQL • Historically in ProxySQL v1.x support for Galera is based on “external” scripts • An “external” script is configured in the ProxySQL scheduler: • proxysql_galera_checker.sh is provided as a “ready to use script” • ProxySQL 2.x provides native support for Galera

A quick overview of pre-2.x support Typically the script is added to the “scheduler” table and loaded to runtime Admin> select * from scheduler\G *************************** 1. row *************************** id: 1 interval_ms: 10000 filename: /var/lib/proxysql/proxysql_galera_checker.sh arg1: 127.0.0.1 arg2: 6032 arg3: 0 arg4: /var/lib/proxysql/proxysql_galera_checker.log arg5: NULL

A quick overview of pre-2.x support Sample log file during an outage: ### /var/lib/proxysql/proxysql_galera_checker.log Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , wsrep_local_state 4 ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.12:3306 , status ONLINE , wsrep_local_state Sat Jul 16 18:42:14 UTC 2016 Changing server 10.10.10.12:3306 to status OFFLINE_SOFT Sat Jul 16 18:42:14 UTC 2016 Check server 10.10.10.13:3306 , status ONLINE , wsrep_local_state 4 Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , wsrep_local_state 4 ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.12:3306 , status OFFLINE_SOFT , wsrep_local_state Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.13:3306 , status ONLINE , wsrep_local_state 4

A quick overview of pre-2.x support Changes are reflected in the “mysql_servers” and “runtime_mysql_servers” tables: Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+-------------+------+--------------+ | hostgroup_id | hostname | port | status | +--------------+-------------+------+--------------+ | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.13 | 3306 | ONLINE | +--------------+-------------+------+--------------+

A quick overview of pre-2.x support Changes will be reflected in the “mysql_servers” table: Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+-------------+------+--------------+ | hostgroup_id | hostname | port | status | +--------------+-------------+------+--------------+ | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.13 | 3306 | ONLINE | +--------------+-------------+------+--------------+

ProxySQL 2.0 Galera Support • In ProxySQL 2.0 the concept of the regular “mysql_replication_hostgroup” is extended • In addition to “reader_hostgroup” and “writer_hostgroup” we also have the following additional concepts and hostgroup types: • max_writers • writer_is_also_reader • max_transactions_behind • backup_writer_hostgroup • offline_hostgroup

Galera Related Tables The key Galera tables in ProxySQL Admin are: +--------------------------------------------+ | galera related tables | +--------------------------------------------+ | mysql_galera_hostgroups | | runtime_mysql_galera_hostgroups | | mysql_server_galera_log | +--------------------------------------------+

Galera Configuration Table The key configuration table is “mysql_galera_hostgroups”: CREATE TABLE mysql_galera_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0), offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR

Galera Configuration Table • writer_hostgroup • the hostgroup id that will contain writer nodes (read_only=0) • backup_writer_hostgroup • when multiplie writers are active (read_only=0 on more than 1x node) but max_writers is defined • all nodes that exceed this value are moved to the backup writer group (standby nodes) • reader_hostgroup • the hostgroup id that will contain reader nodes (read_only=1) • offline_hostgroup • when ProxySQL's monitoring determines a node is offline it will be moved to the offline_hostgroup

Galera Configuration Table • active • ProxySQL monitors the active hostgroups and makes use only of active hostgroups • Facilitates standby configurations e.g. switch configuration in case of DC failover • max_writers • limits the number of nodes allocated to the writer hostgroup • controls behaviour of backup_writer_hostgroup • writer_is_also_reader • determines if a node in the writer hostgroup will also belong to the reader hostgroup • max_transactions_behind • determines the maximum number of writesets behind the node can fall before shunning the node to prevent stale reads (this is determined by querying the `wsrep_local_recv_queue` galera variable).

Galera Related Global Variables The check timeouts are controlled in the “global_variables” table’s “xxx_galera_healthcheck_xxx” variables: ProxySQL Admin> select * from global_variables where variable_name like '%monitor_galera%'; +-------------------------------------------+----------------+ | variable_name | variable_value | +-------------------------------------------+----------------+ | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | +-------------------------------------------+----------------+

How to configure ProxySQL 2.x for Galera

Define the Galera Hostgroups In this example, we define the following: writer_hostgroup = 2 reader_hostgroup = 3 offline_hostgroup = 1 backup_writer_hostgroup = 4 max_writers = 1 writer_is_also_reader = 0 active = 1 max_transactions_behind = 100 ProxySQL Admin> INSERT INTO mysql_galera_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (2, 4, 3, 1, 1, 1, 0, 100);

Define the Galera Servers • Add two servers to “writer_hostgroup = 2” • 172.16.1.112 (read_only=0) • 172.16.1.113 (read_only=0) • Add one server to “reader_hostgroup = 3” • 172.16.1.114 (read_only=1) ProxySQL Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight) VALUES (2,'172.16.1.112',3306,100), (2,'172.16.1.113',3306,10), (3,'172.16.1.114',3306,100);

Verify mysql_servers ProxySQL Admin> select hostgroup_id, hostname, port, status, weight, max_connections from mysql_servers; +--------------+--------------+------+--------+--------+-----------------+ | hostgroup_id | hostname | port | status | weight | max_connections | +--------------+--------------+------+--------+--------+-----------------+ | 2 | 172.16.1.112 | 3306 | ONLINE | 100 | 1000 | | 2 | 172.16.1.113 | 3306 | ONLINE | 10 | 1000 | | 3 | 172.16.1.114 | 3306 | ONLINE | 100 | 1000 | +--------------+--------------+------+--------+--------+-----------------+

Verify mysql_galera_hostgroups ProxySQL Admin> select * from mysql_galera_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 2 backup_writer_hostgroup: 4 reader_hostgroup: 3 offline_hostgroup: 1 active: 1 max_writers: 1 writer_is_also_reader: 0 max_transactions_behind: 100 comment: NULL

Load / Save Configuration • We must not forget to “load the configuration to runtime” and additionally “save the configuration to disk”: # The following command will LOAD both mysql_servers and mysql_galera_hostgroups to RUNTIME making this the active ProxySQL configuration for runtime only (does not persist across restarts): LOAD MYSQL SERVERS TO RUNTIME; # The following command will SAVE both mysql_servers and mysql_galera_hostgroups to DISK making this the “persisted” configuration in case ProxySQL is restarted: SAVE MYSQL SERVERS TO DISK;

Dynamic Reconfiguration • After loading the configuration to runtime ProxySQL has reconfigured the “mysql_servers” based on the configuration of “mysql_galera_hostgroups” (specifically due to the configuration “max_writers = 1”) • The host “172.16.1.113” was moved to the backup_writer_hostgroup ProxySQL Admin> select hostgroup, srv_host, status, ConnUsed, MaxConnUsed, Queries from stats.stats_mysql_connection_pool order by srv_host; +-----------+--------------+--------+----------+-------------+-----------+ | hostgroup | srv_host | status | ConnUsed | MaxConnUsed | Queries | +-----------+--------------+--------+----------+-------------+-----------+ | 2 | 172.16.1.112 | ONLINE | 3 | 4 | 930742390 | | 4 | 172.16.1.113 | ONLINE | 0 | 0 | 0 | | 3 | 172.16.1.114 | ONLINE | 1 | 1 | 233130345 | +-----------+--------------+--------+----------+-------------+-----------+

Galera Monitoring Statistics The statistics related to the Galera cluster can be viewed in the “monitor.mysql_server_galera_log” table: ProxySQL Admin> select * from mysql_server_galera_log order by time_start_us desc limit 3; *************************** 1. row *************************** hostname: 172.16.1.112 port: 3306 time_start_us: 1529510693289001 success_time_us: 1234 primary_partition: YES read_only: NO wsrep_local_recv_queue: 0 wsrep_local_state: 4 wsrep_desync: NO wsrep_reject_queries: NO wsrep_sst_donor_rejects_queries: NO error: NULL *************************** 2. row *************************** hostname: 172.16.1.113 ...

ProxySQL for InnoDB Cluster / Oracle Group Replication (OGR)

OGR Support in ProxySQL • ProxySQL has had support for OGR since version 1.3.x • Native support has been available since version 1.4.x • Configuration of ProxySQL for OGR is very similar to the configuration for Galera • The differences are highlighted in the next slides: • The “max_transactions_behind” variable • The OGR healthcheck global variables • A set of stored procedures required for OGR to support ProxySQL

OGR Related Tables The key OGR tables in ProxySQL Admin are: +---------------------------------------------+ | ogr related tables. | +---------------------------------------------+ | mysql_group_replication_hostgroups | | runtime_mysql_group_replication_hostgroups | | mysql_server_group_replication_log | +---------------------------------------------+

OGR Configuration Table The key configuration table is “mysql_group_replication_hostgroups”: CREATE TABLE mysql_group_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0), offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0), active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1, writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR, UNIQUE (reader_hostgroup), UNIQUE (offline_hostgroup), UNIQUE (backup_writer_hostgroup))

OGR Configuration Table • The only difference when compared to the Galera Configuration Table is the concept of “max_transactions_behind”: • max_transactions_behind • determines the maximum number of binlog events behind the node can fall before shunning the node to prevent stale reads • this is determined by querying the sys.gr_member_routing_candidate_status view • When dealing with OGR this relates to the number of GTID events rather than Galera writesets • Apart from this difference the definition of the other fields remains the same as for the configuration of Galera

OGR Configuration Table • The only difference when compared to the Galera Configuration Table is the concept of “max_transactions_behind”: • max_transactions_behind: determines the maximum number of binlog events behind the node can fall before shunning the node to prevent stale reads (this is determined by querying the `wsrep_local_recv_queue` galera variable). • When dealing with OGR this relates to the number of GTID events rather than Galera writesets • Apart from this difference the definition of the other fields remains the same as for the configuration of Galera

OGR Related Global Variables The check timeouts are controlled in the “global_variables” table’s “xxx_groupreplication_healthcheck_xxx” variables: ProxySQL Admin> select * from global_variables where variable_name like '%monitor_groupreplication%'; +-----------------------------------------------------+----------------+ | variable_name | variable_value | +-----------------------------------------------------+----------------+ | mysql-monitor_groupreplication_healthcheck_interval | 5000 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | +-----------------------------------------------------+----------------+

OGR Required Stored Procs The following view and helper functions also need to be added to MySQL in order for OGR to be compatible with ProxySQL’s monitoring: • CREATE VIEW gr_member_routing_candidate_status • CREATE FUNCTION gr_member_in_primary_partition • CREATE FUNCTION gr_applier_queue_length • CREATE FUNCTION GTID_COUNT • CREATE FUNCTION GTID_NORMALIZE • CREATE FUNCTION LOCATE2 • CREATE FUNCTION IFZERO *** Available here - https://gist.github.com/lefred/77ddbde301c72535381ae7af9f968322

OGR Required Stored Procs Once deployed ProxySQL will query the view in order to retrieve the status of each of the OGR nodes (the view can also be used for troubleshooting purposes): ## Status of the primary node (mysql_node1) mysql> SELECT * FROM sys.gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | YES | NO | 0 | 0 | +------------------+-----------+---------------------+----------------------+ ## Status of a secondary node (mysql_node2) mysql> SELECT * FROM sys.gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | YES | YES | 0 | 0 | +------------------+-----------+---------------------+----------------------+

OGR Monitoring Statistics The statistics related to the OGR cluster can be viewed in the “monitor.mysql_server_group_replication_log” table: ProxySQL Admin> select * from mysql_server_group_replication_log order by time_start_us desc limit 3\G *************************** 1. row *************************** hostname: mysql_node1 port: 3306 time_start_us: 1515079109821971 success_time_us: 1582 viable_candidate: YES read_only: NO transactions_behind: 0 error: NULL *************************** 2. row *************************** hostname: mysql_node2 port: 3306 time_start_us: 1515079109822292 success_time_us: 1845 viable_candidate: YES read_only: YES transactions_behind: 0 error: NULL ...

OGR Live Demonstration

OGR Live Demo Configuration • 1x ProxySQL 2.0 instance • Proxysql1 • 3x MySQL 5.7 Docker Instances • Mysql1: Read / Write Master • Mysql2: Read Only Slave • Mysql3: Read Only Slave

Thank you! • Please remember to report feature requests and bug reports: https://github.com/sysown/proxysql/ • Community support can be found on our forum: https://groups.google.com/forum/#!forum/proxysql • Useful blog articles are available at our site: http://proxysql.com/blog • Visit us at http://proxysql.com/support for subscription and support options

46 Rate My Session