Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL System design for high availability with MHA and HAproxy

Takeshi Yako
November 26, 2014

MySQL System design for high availability with MHA and HAproxy

Takeshi Yako

November 26, 2014
Tweet

More Decks by Takeshi Yako

Other Decks in Technology

Transcript

  1. MySQL System design for high availability
    with
    MHA and HAproxy
    2014/11/26
    Takeshi Yako

    View Slide

  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

    View Slide

  3. Read and Write
    Jeff Kubina
    Hard Disk

    View Slide

  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

    View Slide

  5. Write
    HAproxy HAproxy
    MySQL Master MySQL Slave MySQL Slave
    127.0.0.1:3308 127.0.0.1:3308
    mysql1:3306
    App App

    View Slide

  6. Health Checks
    opensource.com
    Open Health:
    stethoscope

    View Slide

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

    MySQL read

    View Slide

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

    View Slide

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

    View Slide

  10. Read Failover step1
    HAproxy HAproxy
    MySQL Master MySQL Slave MySQL Slave
    Health Check Fail

    View Slide

  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

    View Slide

  12. Write Failover
    Erin Kohlenberg
    writing in the
    journal

    View Slide

  13. Write Failover step1
    MySQL Master MySQL Slave MySQL Slave
    MHA Manager
    Health Check Fail

    View Slide

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

    View Slide

  15. Write Failover step3
    MySQL Master MySQL Master MySQL Slave
    MHA Manager
    MHA changes HAproxy config
    HAproxy HAproxy

    View Slide

  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

    View Slide

  17. Thank you

    View Slide

  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/

    View Slide