HA and clustering solution

Galera和Group复制都为集群解决方案提供了虚拟同步复制。这两种产品所使用的不同复制技术确保了数据的复制、一致性和安全性,并且可以处理多个故障场景以保持高可用性。虽然数据库层是高度可用的,但是应用程序很少知道集群中各个节点的状态,因此将流量发送到降级系统中的错误节点可能导致较高的错误率。
proxysql能够定期检查集群中各个节点的状态,并确定哪些节点是健康的,在哪里发送流量。
本课程将介绍如何将proxysql配置为应用程序和集群解决方案之间的中间层,使用galera或组复制,使用哪些算法将流量路由到正确的节点,并确保即使在集群降级的情况下也能实现高可用性。条件。

展开查看详情

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