MySQL System design for high availability
with
MHA and HAproxy
2014/11/26
Takeshi Yako
Slide 2
Slide 2 text
Servers
app1 app2
mysql1 mysql2 mysql3
HAproxy
App App
HAproxy
MySQL Master MySQL Slave MySQL Slave
MHA Node
MHA Node
MHA Node
MHA Manager
Slide 3
Slide 3 text
Read and Write
Jeff Kubina
Hard Disk
Slide 4
Slide 4 text
Read
HAproxy
App App
HAproxy
MySQL Master MySQL Slave MySQL Slave
127.0.0.1:3307 127.0.0.1:3307
mysql1:3306 mysql2:3306 mysql3:3306
Slide 5
Slide 5 text
Write
HAproxy HAproxy
MySQL Master MySQL Slave MySQL Slave
127.0.0.1:3308 127.0.0.1:3308
mysql1:3306
App App
Slide 6
Slide 6 text
Health Checks
opensource.com
Open Health:
stethoscope
Slide 7
Slide 7 text
HAproxy
HAproxy HAproxy
MySQL Master MySQL Slave MySQL Slave
HAproxy checks
MySQL read
Slide 8
Slide 8 text
MHA
MySQL Master MySQL Slave MySQL Slave
MHA Manager
MHA checks MySQL Master
Slide 9
Slide 9 text
Read Failover
Simon Cocks
Reading a book at
the beach
Slide 10
Slide 10 text
Read Failover step1
HAproxy HAproxy
MySQL Master MySQL Slave MySQL Slave
Health Check Fail
Slide 11
Slide 11 text
Read Failover step2
HAproxy
App App
HAproxy
MySQL Master MySQL Slave
HAproxy detaches
down server
127.0.0.1:3307 127.0.0.1:3307
mysql1:3306 mysql3:3306
Slide 12
Slide 12 text
Write Failover
Erin Kohlenberg
writing in the
journal
Slide 13
Slide 13 text
Write Failover step1
MySQL Master MySQL Slave MySQL Slave
MHA Manager
Health Check Fail
Slide 14
Slide 14 text
Write Failover step2
MySQL Master MySQL Master MySQL Slave
MHA Manager
MHA changes MySQL Master
Slide 15
Slide 15 text
Write Failover step3
MySQL Master MySQL Master MySQL Slave
MHA Manager
MHA changes HAproxy config
HAproxy HAproxy
Slide 16
Slide 16 text
Write Failover step4
HAproxy HAproxy
MySQL Slave MySQL Slave
127.0.0.1:3308 127.0.0.1:3308
App App
MySQL Master
mysql2:3306
HAproxy detaches
down server
Slide 17
Slide 17 text
Thank you
Slide 18
Slide 18 text
Bibliography
mysql-master-ha
MHA for MySQL: Master High Availability Manager and tools for MySQL
https://code.google.com/p/mysql-master-ha/
HAproxy
The Reliable, High Performance TCP/HTTP Load Balancer
http://www.haproxy.org/