MySQL System design for high availability with MHA and HAproxy

Transcript

1. MySQL System design for high availability with MHA and HAproxy

   2014/11/26 Takeshi Yako

2. Servers app1 app2 mysql1 mysql2 mysql3 HAproxy App App HAproxy

   MySQL Master MySQL Slave MySQL Slave MHA Node MHA Node MHA Node MHA Manager

3. Read and Write Jeff Kubina Hard Disk

4. 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

5. Write HAproxy HAproxy MySQL Master MySQL Slave MySQL Slave 127.0.0.1:3308

   127.0.0.1:3308 mysql1:3306 App App

6. Health Checks opensource.com Open Health: stethoscope

7. HAproxy HAproxy HAproxy MySQL Master MySQL Slave MySQL Slave HAproxy

   checks 
 MySQL read

8. MHA MySQL Master MySQL Slave MySQL Slave MHA Manager MHA

   checks MySQL Master

9. Read Failover Simon Cocks Reading a book at the beach

10. Read Failover step1 HAproxy HAproxy MySQL Master MySQL Slave MySQL

    Slave Health Check Fail

11. 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

12. Write Failover Erin Kohlenberg writing in the journal

13. Write Failover step1 MySQL Master MySQL Slave MySQL Slave MHA

    Manager Health Check Fail

14. Write Failover step2 MySQL Master MySQL Master MySQL Slave MHA

    Manager MHA changes MySQL Master

15. Write Failover step3 MySQL Master MySQL Master MySQL Slave MHA

    Manager MHA changes HAproxy config HAproxy HAproxy

16. 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

17. Thank you

18. 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/