HA and clustering solution

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

2 .Introduction 

3 .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 3 

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

5 . Other sessions ProxySQL Hand-on Monday 5th November , 2018 @9:00AM ProxySQL Adaptive query routing based on GTID tracking Wednesday 7th November, 2018 @4:30PM 5 

6 .What is ProxySQL? 

7 .What is ProxySQL The MySQL data stargate 7 

8 .ProxySQL Architecture Overview Data gateway Clients connect to ProxySQL Requests are evaluated Actions are performed 8 

9 .Some of the most interesting features: ● on-the-fly rewrite of queries ● caching reads outside the database server ● connection pooling and multiplexing ● complex query routing and read/write split ● load balancing ● real time statistics ● monitoring ● data masking ● multiple instances on same ports 9 

10 .Some of the most interesting features: ● high availability and scalability ● seamless failover ● firewall ● query throttling ● query timeout ● query mirroring ● runtime reconfiguration ● scheduler ● support for Galera/PXC and Group Replication 10 

11 .Some of the most interesting features: ● support for millions of users ● support for tens of thousands of database servers ● native ProxySQL Clustering solution ● support for ClickHouse as a backend ● support for Aurora ● SSL support for frontend ● SSLv1.2 ● native Support for Galera ● causal reads using GTID 11 

12 .12 

13 .What is Galera Cluster and Oracle Group Replication? 

14 .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 

15 .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 

16 .Why ProxySQL? • Real time monitoring of backends • Transparent redirect of traffic • Automatic reconfiguration 

17 .ProxySQL for Galera 

18 .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 

19 .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 

20 .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 

21 .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 | +--------------+-------------+------+--------------+ 

22 .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 | +--------------+-------------+------+--------------+ 

23 .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 

24 .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 | +--------------------------------------------+ 

25 .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,2)) NOT NULL DEFAULT 0, max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0, comment VARCHAR 

26 .Galera Configuration Table • writer_hostgroup • the hostgroup id that will contain writer nodes (read_only=0) • backup_writer_hostgroup • when multiple 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 or not functional it will be moved to the offline_hostgroup 

27 .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). 

28 .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 | +-------------------------------------------+----------------+ 

29 .Galera Global Status/Variables read_only wsrep_desync wsrep_reject_queries wsrep_sst_donor_rejects_queries wsrep_local_state wsrep_local_recv_queue wsrep_cluster_status