380 likes | 823 Views
Mastering Galera. Data Masters. Special Thanks To…. 1010 NE 2 nd Ave Miami, FL 33132 305-735-1274 www.venturehive.co. Our Sponsor!. John Jadvani 954-527-0090. Short bio about me… Andrew Simkovsky 15 years working with database technology
E N D
MasteringGalera Data Masters
Special Thanks To… 1010 NE 2nd Ave Miami, FL 33132 305-735-1274 www.venturehive.co
Our Sponsor! John Jadvani 954-527-0090
Short bio about me… • Andrew Simkovsky • 15 years working with database technology • Oracle, MySQL/MariaDB, SQL Server, Postgres • Redis, MongoDB, CouchDB, Cassandra, Neo4j • Worked across many industries • Consulting, Retail, Telecommunications, Energy • Data, Marketing, Gaming, Health care
DBTekPro www.dbtekpro.com andrew@dbtekpro.com @asimkovsky
Galera Cluster for MySQL • High availability (HA) and scaling solution for MySQL/MariaDB • A clustering solution that integrates with MySQL / MariaDB
Galera Cluster Concept MySQL/MariaDB Galera
Other HA and Scaling Solutions • Sharding • Master / Slave(s) • Master / Master • Master / Master plus Slaves
Sharding Application A..F G..L M..R …
Master / Slave(s) Application Master Slave
Master / Slave(s) Application Master Slave Slave Slave
Master / Master Application Master Master
Master / Master Plus Slaves Application Master Master Slave Slave Slave Slave Slave Slave
Basic Galera Concepts • Contains multiple nodes • Each node has a full copy of the data • Synchronous multi-master replication across all nodes • All changes to every node are replicated to all other nodes • Each node can be for reads and writes • All nodes can be accessed at the same time
Galera Concept Application Node Node Node Node Node Galera Galera Galera Galera Galera
Quorum Commit • Don’t have to wait for all nodes to answer back for your changes • “Majority” rules • Committed nodes >= (N / 2 ) + 1 • Minimum recommended number of nodes is 3
Split Brain Syndrome • Network partition between the nodes • Each node thinks its in charge • Both nodes keep taking traffic • Now both nodes have different contents • When nodes start talking to each other again, they are very confused
Split Brain Syndrome Data Center 1 Data Center 2 Application Application Node Node Galera Galera
Arbitrator Node • “Cheating” with 2 nodes • Acts as a third node • Doesn’t store any data • Aware of cluster state and replication status • Provides that third “commit” vote • Replication changes pass through it • Loss of direct connectivity between nodes can still be handled if arbitrator can talk to both
Arbitrator Node Data Center 1 Data Center 2 Application Application Node Node Galera Galera Arbitrator Data Center 3
Some Other “Gotchas” • Be careful with non-deterministic functions like NOW(), CURTIME(), etc • DELETE commands on tables without a primary key are not supported • Direct writes to system tables (“mysql” database) are not replicated • Cluster enforces optimistic concurrency control • “I got here first, my transaction is good” • All other transactions locking the same row get deadlock error • Application should be configured to retry the transaction
Stuff to install on each node • Operating system • MariaDB server • Galera • PerconaXtraBackup
Setup Steps • On each node: • Set up configuration files • Start up MySQL • On first node: during startup, you tell it that it’s the first node. • The data it contains will become the “master” copy. • For each additional node that starts up, it will seek out one of its “neighbors”, and try to sync a copy of the data to itself • Source node is called the “donor” • After syncing the data, the node joins the cluster and becomes active
Database State Transfer • When starting up, each node needs a copy of the data • The copying of the data is called a “state transfer” • Will copy from existing nodes using one of these methods: • mysqldump • rsync • xtrabackup • For mysqldump and rsync, the donor is locked for writes during the entire copy process • Percona’sxtrabackup allows writes to happen on donor node during copy process
Thank You For Coming! Please rate this Meet Up: www.meetup.com/data-masters (or go there to join!) Check out my blog and forums: www.dbtekpro.com