High-availability With Galera Cluster For MySQL
www.fromdual.comHigh-availability withGalera Clusterfor MySQLLinuxTag 201410. Mai 2014, Berlinby oli.sennhauser@fromdual.comwww.fromdual.com1 / 22
About FromDual GmbH www.fromdual.comFromDual provides neutral and independent: Consulting for MySQL, Galera Cluster, MariaDB andPercona Server Support for all MySQL and Galera Cluster Remote-DBA Services for all MySQL MySQL Training Open Source Business Alliance (OSBA) Member of SOUG, DOAG, /ch/openwww.fromdual.com2 / 22
High-Availability :-(www.fromdual.com Its also about maintenance. Who loves night-shifts? Who loves weekend-work? Who does regular upgrade (DB, kernel, etc.)? Who does regular reboots (after kernelupgrade)?Why are you not doing it in your office hours?3 / 22
www.fromdual.comThe Galera Cluster for MySQL4 / 22
Maintenance time.www.fromdual.comAppLoad balancing (LB)UpgradeRAM/HWNode 1 Node 2 NodeO/S 3DB?wsrepwsrepwsrepGalera replication5 / 22
Advantages / Disadvantageswww.fromdual.comBased on InnoDB SESynchronous replication No lost transactionActive-active multi-master Cluster Read and write to any cluster node (no r/w split any more!)Read scalability and higher write throughput (Flash-Cache?)Automatic node membership controlRolling Restart (Upgrade of Hardware, O/S, DB release, etc.)True parallel replication, on row level No slave lagA bit more complicated than normal MySQL, but similar complexityas M/S Replication!No original MySQL binaries Codership MySQL binariesBe aware of Hot Spots on rows: Higher probability of deadlocks6 / 22
Quorum and split-brain What is the problem? Split-brain bad!www.fromdual.com? Galera is a pessimistic Cluster good! Quorum: FLOOR(n/2 1) more than half! 3-node Cluster (or 2 1)7 / 22
Quorumwww.fromdual.com8 / 22
www.fromdual.comInstallation and Configuration9 / 22
Installation Galera Cluster consists of: A patched Codership MySQL (mysqld) www.fromdual.comOr MariaDB Galera ClusterOr Percona XtraDB ClusterThe Galera Plugin (libgalera smm.so)Ways of installation Packets (RPM, DEB) Binary tar-ball Patch MySQL source and compile bothDownload http://galeracluster.com/downloads/10 / 22
MySQL Configurationwww.fromdual.commy.cnf[mysqld]default storage enginebinlog format InnoDB rowinnodb autoinc lock mode 2# parallel applyinginnodb flush log at trx commit 0# performance only!query cache sizequery cache type# Galera 3 experimental# Mutex! Consistency! 0 011 / 22
Galera Configurationwww.fromdual.commy.cnf (conf.d/wsrep.cnf)[mysqld]# wsrep providerwsrep provider none /lib/plugin/libgalera smm.so# wsrep cluster addresswsrep cluster address "gcomm://" "gcomm://ip node2,ip node3"wsrep cluster namewsrep node name 'Galera Cluster' 'Node A'wsrep sst methodwsrep sst auth mysqldump sst:secret12 / 22
www.fromdual.comOperations13 / 22
Initial Cluster start www.fromdual.comStart very 1st node with:wsrep cluster address "gcomm://"ormysqld safe wsrep cluster address "gcomm://" this tells the node to be the first one!All other nodes normal:service mysqld start14 / 22
Rolling Restart www.fromdual.comScenario: Hardware-, O/S-, DB- and Galera-Upgrade MySQL configuration change During full operation!!! (99.999% HA, 5x9 HA) Rolling Restart Start one node after the other in a cycleNew features or settings are used after RollingRestart is completed15 / 22
Load Balancing Connectors Connector/JPHP: MySQLnd replication and load balancingplug-inSW Load Balancer www.fromdual.comGLB, LVS/IPVS/Ldirector, HAProxyHW Load Balancer16 / 22
Location of Load Balancingwww.fromdual.com17 / 22
www.fromdual.comDemo?18 / 22
Online Schema Upgrade (OSU)www.fromdual.com Schema Upgrade DDL run against the DB Change DB structure Non transactional2 Methods: Total Order Isolation (TOI) (default) Rolling Schema Upgrade (RSU)wsrep osu method {TOI RSU}19 / 22
Online Schema Upgrade www.fromdual.comTotal Order Isolation (TOI) (default) Part of the database is locked for the duration of the DDL. Simple, predictable and guaranteed data consistency. - Locking operation Good for fast DDL operationsRolling Schema Upgrade (RSU) DDL will be only processed locally at the node. Node is desynchronized for the duration of the DDL processing. After DDL completion, delayed write sets are applied (similar to IST). DDL should be manually executed at each node. only blocking one node at a time - potentially unsafe and may fail if new and old schema areincompatibleGood for slow DDL operations20 / 22
Wir suchen noch: www.fromdual.comDatenbank Enthusiast/in fürSupport / remote-DBA / Beratung21 / 22
Q&Awww.fromdual.comQuestions ?Discussion?We have time for some face-to-face talks. FromDual provides neutral and independent: Consulting Remote-DBA Support for MySQL, Galera, Percona Server and MariaDB Trainingwww.fromdual.com22 / 22
www.fromdual.com 2 / 22 About FromDual GmbH FromDual provides neutral and independent: Consulting for MySQL, Galera Cluster, MariaDB and Percona Server Support for all MySQL and Galera Cluster Remote-DBA Services for all MySQL MySQL Training Open Source Business Allian
Standard Replication & Galera Cluster Introduction Purpose & Advantages Standard Replication Layout Configuring Replication Galera Basics Configuring Galera
Product: Galera Cluster for MySQL, open source Thousands of users in e-commerce, telecom, gaming, insurance, betting healthcare, media, marketing, advertising, travel, universities, software-as-service, Paas, Iaas, etc Galera Cluster is inc
Installing Galera Cluster with MariaDB Introduction Galera Cluster Overview Installin
Galera dashboard page An out-of-the-box page on the Dashboard tab that shows the current statistics on the Galera Cluster. These statistics include queue length, throttle time, replication status, cluster status, and others.
MySQL Teamwork: Replication or Galera Cluster, joerg.bruehe@fromdual.com, 2016 April, CC-BY-SA 26 / 42 Galera Properties (2)-MySQL sources need patching (Codership offers binaries, ditto MariaDB and Percona)-Beware of hot spots (rows)-Conflict detection is late, full rollb
Galera Cluster Configuration—Misconfiguration of the Galera Cluster causes the database deadlocks. The original configuration used cluster-wide optimistic locking, resulting in failure and roll-back of the distributed quota
Puerto Galera San Antonio Pto. Galera Fish Sanctuary 4,828.00 (250 NTZ) 2006 Dulangan and Tabinay Puerto Galera Mangrove Conservation and Ecotourism Area 17.8 2015 Naujan Sitio Tujod, Herrera Tujod Fish Sanctuary 30.00 2006 Masaguing Masaguing Fish Sanctuary
of its Animal Nutrition Series. The Food and Drug Administration relies on information in the report to regulate and ensure the safety of pet foods. Other reports in the series address the nutritional needs of horses, dairy cattle, beef cattle, nonhuman primates, swine, poultry, fish, and small ruminants. Scientists who study the nutritional needs of animals use the Animal Nutrition Series to .
