Upload
francisco-goncalves
View
1.679
Download
9
Embed Size (px)
DESCRIPTION
MariaDB Galera Cluster in a master-Master Sync Replication configuration.
Citation preview
SkySQL&AB&©&2013&Confiden4al&
Agenda&
• Introduc4on&• MySQL&Replica4on&
• SemiHSynchronous&Replica4on&
• MySQL&5.6&
• MariaDB&10.0&
• MHA&
• Galera&• Shared&Disk&• DRBD&• MySQL&Cluster&
SkySQL&AB&©&2013&Confiden4al&
Introduc4on&to&HA&
“High&availability&is&a&system&design&protocol&and&associated&implementa6on&that&ensures&a&certain°ree&of&opera6onal&con6nuity&during&a&given&
measurement&period”&
SkySQL&AB&©&2013&Confiden4al&
Up4me,&Down4me,&9s&
& &90%& & & & & &1&nine&& & & &36.5&days&/&year &&
& &99%& & & & & &2&nines& & & &3.65&days&/&year&
& &99.9%&& & & & &3&nines& & & &8.76&hours&/&year&
& &99.99%& & & & &4&nines& & & &52&minutes&/&year&
& &99.999%& & & & &5&nines& & & &5&minutes&/&year&
& &99.9999%&& & & &6&nines& & & &31&seconds&/&year&
Availability&=&up4me&/&(up4me&+&down4me)&
Availability&=&MTBF&/&(MTBF&+&MTTR)&
SkySQL&AB&©&2013&Confiden4al&
Terminology&
• Synchronous&vs.&Asynchronous&• SharedHDisk&vs.&SharedHNothing&vs.&SharedHMemory&
• Single&Point&Of&Failure&H&SPOF&• Failover&vs.&Switchover&• Split&Brain&• Node&Fencing,&STONITH,&Quorum&
SkySQL&AB&©&2013&Confiden4al&
Designing&for&HA&
• Which&level&of&availability&do&I&need?&
• How&many&nines?&
• Do&I&require&no&loss&of&data?&• Could&I&loose&some&transac4ons?&
• Will&my&users&no4ce&or&care?&
• Do&I&need&automa4c&failover&or&is&manual&switchover&ok?&
• How&do&I&test&this?&• Can&I&provide&a&reasonable&service&when&X&is&down?&• Replace&X&with&each&component&of&the&service&
SkySQL&AB&©&2013&Confiden4al&
Before&we&talk&about&solu4ons&
• A&high&availability&setup&does¬&replace&backups&
• Check¶meters&
• flush_at_trx_commit&
• sync_binlog&• expire_logs_day&• binlog_format&
• sync_master_info&(on&slaves)&
• ...&• Requirements&for¶meters&may&
change&if&using&baieryHbacked&disk&
cache&
SkySQL&AB&©&2013&Confiden4al&
MySQL&Replica4on&H&Asynchronous&
• Asynchronous:&3&Phases&1. Commit&and&write&to&binlog&on&Master&
2. Ship&changes&to&relay&log&on&slave&3. Apply&changes&on&slave&&
• Master&H>&slave&rela4onship&
• MonoHthreaded&on&slaves&un4l&MySQL&5.5&
• MySQL&5.6&allows&mul4Hthreaded&
• No&conflict&resolu4on&• MasterHmaster&replica4on&or&circular&
replica4on&need&applica4on&logic&
&
Master&
Slave(s)&
SkySQL&AB&©&2013&Confiden4al&
MySQL&Replica4on&–&SemiHSynchronous&
• Added&as&a&plugin&in&MySQL®&5.5&
• Ensures&that&changes&have&been&shipped&to&at&least&one&slave&(or&4meouts)&&
• A&COMMIT&on&the&master&waits&for&ONE&
Slave&to&acknowledge&the&transac4on&
• Important:&The&Master&does¬&wait&for&the&
Slave&to&execute&the&transac4on,&only&to&write&
it&to&the&relay&log&
• So&the&Slave&SQL&Thread&may&s4ll&lag&behind&
the&Master&and&queries&to&the&Slave&may&s4ll&
return&old&data&
• Poten4ally&adds&latency&to&queries&
Master&
Slave(s)&
SkySQL&AB&©&2013&Confiden4al&
MySQL&Replica4on&for&HA&
• Master&<H>&standby&master&H&manual&failover&
• Minimal&down4me&for&changes&and&upgrades&
• SemiHsynchronous&should&be&used&
• Used&in&combina4on&with&other&HA&solu4ons&
for&geographical&replica4on&
SkySQL&AB&©&2013&Confiden4al&
MySQL®&5.6&–&New&Replica4on&Features&
• Global&Transac4on&ID&(GTID)&• Makes&it&easy&to&automate&failover&and&slave&promo4on&
• Replica4on&failover&and&admin&u4li4es&
• Mul4Hthreaded&slaves&
• Replica4on&event&checksums&
• TimeHdelayed&replica4on&
SkySQL&AB&©&2013&Confiden4al&
MariaDB&10.0&–&New&Replica4on&Features&
• Mul4Hsource&replica4on&
• Global&Transac4on&ID&(GTID)&• Different&implementa4on&from&MySQL&5.6&
• GTID&per&domain&instead&of&server&
SkySQL&AB&©&2013&Confiden4al&
MHA&
Master&High&Availability&Manager&
• Automates&master&failover&and&slave&
promo4on&
• Monitors&the&master&or&can&integrate&with&
Pacemaker/Heartbeat&
• Failover&is&an&online&opera4on&• Also&allows&manual&switchover&
• Short&down4me:&open&a&few&seconds&
• MySQLHReplica4on&consistency&
• No&performance&penalty&
• Drop&in&solu4on&on&exis4ng&deployment&
&
SkySQL&AB&©&2013&Confiden4al&
MHA&
Failover&Process&
1. Aiempt&to&contact&MySQL&master&server&
by&SSH&
2. If&master&server&is&alive&access&the&binary&
log&and&recover&events&
3. Find&the&slave&with&the&most&advanced&
relay&log&
4. Sync&all&slaves&to&the&latest&available&binlog&event&
5. STONITH&master&if&necessary&
6. Promote&slave&to&master&
SkySQL&AB&©&2013&Confiden4al&
Galera&/&MariaDB&Galera&Cluster&
• Provides&“virtually”&synchronous&replica4on&
• Works&with&InnoDB&
• No&slave&lag&• Transac4ons&are&validated&by&slaves&upon&commit&
• Cer4fica4on&and&quorum&
• Transac4ons&may&be&rolled&back&
at&this&stage&
• Mul4Hmaster&or&masterHslave&possible&
Galera Replication
wsrep
MySQL
wsrep
MySQL
wsrep
MySQL
Clients
SkySQL&AB&©&2013&Confiden4al&
Galera&/&MariaDB&Galera&Cluster&
• Provides&“virtually”&synchronous&replica4on&
• Works&with&InnoDB&
• No&slave&lag&• Transac4ons&are&validated&by&slaves&upon&commit&
• Cer4fica4on&and&quorum&
• Transac4ons&may&be&rolled&back&
at&this&stage&
• Mul4Hmaster&or&masterHslave&possible&
Galera Replication
wsrep
MySQL
wsrep
MySQL
wsrep
MySQL
Clients
SkySQL&AB&©&2013&Confiden4al&
Galera&–&Transac4on&Process&
• Transac4on&is&processed&locally&up&to&commit&4me&
Galera Replication
wsrep
MySQL
wsrep
MySQL
wsrep
MySQL
trx
Client
SkySQL&AB&©&2013&Confiden4al&
Galera&–&Transac4on&Process&
• Transac4on&is&processed&locally&up&to&commit&4me&
• Transac4on&is&replicated&to&whole&cluster&
Galera Replication
wsrep
MySQL
wsrep
MySQL
wsrep
MySQL
Client
trx
wsws
SkySQL&AB&©&2013&Confiden4al&
Galera&–&Transac4on&Process&
• Transac4on&is&processed&locally&up&to&commit&4me&
• Transac4on&is&replicated&to&whole&cluster&
• Client&gets&OK&status&
Galera Replication
wsrep
MySQL
wsrep
MySQL
wsrep
MySQL
trx
Client
SkySQL&AB&©&2013&Confiden4al&
Galera&–&Transac4on&Process&
• Transac4on&is&processed&locally&up&to&commit&4me&
• Transac4on&is&replicated&to&whole&cluster&
• Client&gets&OK&status&• Transac4on&is&applied&in&slaves&
Galera Replication
wsrep
MySQL
wsrep
MySQL
wsrep
MySQL
Client
trx trx
SkySQL&AB&©&2013&Confiden4al&
SharedHdisk&Solu4on&
• Ac4ve&–&Passive&replica4on&• Failover&requires&MySQL&crash&recovery&(and&
open&file&system&crash&recovery)&
• Combined&with&Pacemaker/Heartbeat&for&
automa4c&failover&
• Virtual&IP&most&open&used&to&fail&over&
• In&theory&the&SAN&is&a&SPOF&
SkySQL&AB&©&2013&Confiden4al&
DRBD®&&
• Synchronous&replica4on&(three&modes)&
• Ac4ve&–&Passive&replica4on&• Failover&requires&MySQL&crash&recovery&(and&
open&file&system&crash&recovery)&
• Combined&with&Pacemaker/Heartbeat&for&
automa4c&failover&
• Virtual&IP&most&open&used&to&fail&over&
• STONITH&or&other&fencing&mechanism&
needed&to&avoid&splitHbrain&scenarios&
• Available&on&Linux&
SkySQL&AB&©&2013&Confiden4al&
MySQL&Cluster&H&Features&
• Synchronous&replica4on&between&nodes&
• Through&TwoHPhase&Commit&Protocol&
• ACID&transac4ons&• Row&level&locking&• Shared¬hing&architecture&• No&single&point&of&failure&
• Automa4c&failover&
• InHmemory&storage&
• Some&data&can&be&stored&on&disk&
• Checkpoin4ng&to&disk&for&durability&• Two&types&of&indexes&• Ordered&THtrees&• Unique&hash&indexes&
• Online&opera4ons&• Add&node&groups&• Sopware&upgrade&• Some&table&altera4ons&
SkySQL&AB&©&2013&Confiden4al&
MySQL&Cluster&
Network&par44oning&protocol&
1. Is&at&least&one&node&from&each&node&group&present?&&
• If¬&then&the&cluster&cannot&con4nue&H&shutdown&2. Are&all&nodes&present&from&any&node&group?&
• If&so&then&this&is&the&only&viable&cluster&H&con4nue&3. Ask&the&arbitrator&• The&arbitrator&decides&which&”cluster”&con4nues&• If&the&arbitrator&is¬&available&the&cluster&will&shutdown&
Designed&to&avoid&splitHbrain&
SkySQL&AB&©&2013&Confiden4al&
Where&does&MySQL&Cluster&fit?&
• High&demands&on&availability&(5&nines)&
• You&need&write&scalability&• You&have&3&or&more&”machines”&available&
• Where&the&queries&and&data&model&are&simple&
• When&the&data&fits&in&memory&
• When&you&have&skilled&people&
SkySQL&AB&©&2013&Confiden4al&
Geographical&Replica4on&
• For&geographical&(mul4Hsite)&redundacy&
synchronous&solu4ons&are&open¬&
desirable&
• Standard&Replica4on&can&be&combined&
with&any&synchronous&solu4on&locally&
• MySQL&Cluster&
• DRBD&/&shared&disk&• Galera&
SkySQL&AB&©&2013&Confiden4al&
Geographical&Replica4on&
MySQL&Cluster&
• MySQL&Cluster&has&addi4onal&features&
related&to&Geographical&replica4on&
• Conflict&detec4on&and&automa4c&
resolu4on&
• Several&possible&resolu4on&methods&exist&
• Mul4ple&replica4on&channels&possible&
• Mul4Hsource&replica4on&possible&
• Binlog&injec4on&ensures&the&consitency&of&binlogs&
SkySQL&AB&©&2013&Confiden4al&
Thank&you!&
MySQL is a registered trademark of Oracle and/or its affiliates. MariaDB is a registered trademark of Monty Program Ab. SkySQL and the SkySQL logo are trademarks of SkySQL Inc. or SkySQL Ab. SkySQL is not affiliated with MySQL. All other company and product names may be trademarks or service marks of their respective owners.
SkySQL&Ab&
www.skysql.com&
www.facebook.com/skysql&
www.linkedin.com/company/skysql&
Joffrey&Michaie&