HA and clustering solution: ProxySQL as an intelligent router for Galera and Group Replication Rene Cannao Rene Cannao ProxySQL ProxySQL
Introduction
A bit about ProxySQL LLC We provide services to help build, support We provide services to help build, support as well as improve the performance & as well as improve the performance & reliability of your Cloud-Based or On- reliability of your Cloud-Based or On- Premise MySQL infrastructure: Premise MySQL infrastructure: • ProxySQL Development • ProxySQL Development • Remote Consulting • Remote Consulting • ProxySQL Support Services • ProxySQL Support Services • ProxySQL Training • ProxySQL Training 3
A bit about me… Rene Cannao Rene Cannao • Founder of ProxySQL • Founder of ProxySQL • MySQL DBA • MySQL DBA 4
Other sessions ProxySQL Hand-on ProxySQL Hand-on Monday 5 th November , 2018 @9:00AM Monday 5 th November , 2018 @9:00AM ProxySQL Adaptive query routing based on GTID tracking ProxySQL Adaptive query routing based on GTID tracking Wednesday 7 th November, 2018 @4:30PM Wednesday 7 th November, 2018 @4:30PM 5
What is ProxySQL?
What is ProxySQL The MySQL data stargate The MySQL data stargate 7
ProxySQL Architecture Overview Data gateway Clients connect to ProxySQL Requests are evaluated Actions are performed 8
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
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
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
What is Galera Cluster and Oracle Group Replication?
What is Galera Cluster? • Multi-master / Active-Active Clustered MySQL Solution • Multi-master / Active-Active Clustered MySQL Solution • Synchronous Replication (certification based) • Synchronous Replication (certification based) • Multi-threaded Replication • Multi-threaded Replication • InnoDB Compliant • InnoDB Compliant • Suitable for LAN, WAN and Cloud Solutions • Suitable for LAN, WAN and Cloud Solutions • IST (incremental) & SST (full) for state transfer • IST (incremental) & SST (full) for state transfer • Auto reconnect mechanism for rejected nodes • Auto reconnect mechanism for rejected nodes
What is Oracle Group Replication? • Multi-master / Active-Active Clustered MySQL Solution • Multi-master / Active-Active Clustered MySQL Solution • • Single master by default (group_replication_single_primary_mode) Single master by default (group_replication_single_primary_mode) • Synchronous Replication (certification based) • Synchronous Replication (certification based) • Multi-threaded Replication • Multi-threaded Replication • InnoDB Compliant • InnoDB Compliant • Suitable for LAN and low latency networks • Suitable for LAN and low latency networks • State transfer is based on GTID matching • State transfer is based on GTID matching
Why ProxySQL? • Real time monitoring of backends • Real time monitoring of backends • Transparent redirect of traffic • Transparent redirect of traffic • Automatic reconfiguration • Automatic reconfiguration
ProxySQL for Galera
Galera Support in ProxySQL • Historically in ProxySQL v1.x support for Galera is based on “external” • Historically in ProxySQL v1.x support for Galera is based on “external” scripts scripts • An “external” script is configured in the ProxySQL scheduler: • An “external” script is configured in the ProxySQL scheduler: • • proxysql_galera_checker.sh is provided as a “ready to use script” proxysql_galera_checker.sh is provided as a “ready to use script” • ProxySQL 2.x provides native support for Galera • 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 Typically the script is added to the “scheduler” table and loaded to runtime Admin> select * from scheduler\G Admin> select * from scheduler\G *************************** 1. row *************************** *************************** 1. row *************************** id: 1 id: 1 interval_ms: 10000 interval_ms: 10000 filename: /var/lib/proxysql/proxysql_galera_checker.sh filename: /var/lib/proxysql/proxysql_galera_checker.sh arg1: 127.0.0.1 arg1: 127.0.0.1 arg2: 6032 arg2: 6032 arg3: 0 arg3: 0 arg4: /var/lib/proxysql/proxysql_galera_checker.log arg4: /var/lib/proxysql/proxysql_galera_checker.log arg5: NULL arg5: NULL
A quick overview of pre-2.x support Sample log file during an outage: Sample log file during an outage: ### /var/lib/proxysql/proxysql_galera_checker.log ### /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 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) 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 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 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: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 , Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.11:3306 , status ONLINE , wsrep_local_state 4 wsrep_local_state 4 ERROR 2003 (HY000): Can't connect to MySQL server on '10.10.10.12' (111) 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 , Sat Jul 16 18:42:25 UTC 2016 Check server 10.10.10.12:3306 , status OFFLINE_SOFT , wsrep_local_state wsrep_local_state Sat Jul 16 18:42:25 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.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: Changes are reflected in the “mysql_servers” and “runtime_mysql_servers” tables: Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | hostgroup_id | hostname | port | status | | hostgroup_id | hostname | port | status | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.13 | 3306 | ONLINE | | 0 | 10.10.10.13 | 3306 | ONLINE | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+
A quick overview of pre-2.x support Changes will be reflected in the “mysql_servers” table: Changes will be reflected in the “mysql_servers” table: Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; Admin> SELECT hostgroup_id,hostname,port,status FROM mysql_servers; +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | hostgroup_id | hostname | port | status | | hostgroup_id | hostname | port | status | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+ | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.11 | 3306 | ONLINE | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.12 | 3306 | OFFLINE_SOFT | | 0 | 10.10.10.13 | 3306 | ONLINE | | 0 | 10.10.10.13 | 3306 | ONLINE | +--------------+-------------+------+--------------+ +--------------+-------------+------+--------------+
Recommend
More recommend
