How-to setup MySQL HA by using keepalived

Love The Way You Lie 2021-09-20 03:26 206阅读 0赞

With MySQL replication and keepalived, we can setup a quite robust high available MySQL environment in a few steps:

Environment:  
Host1: db01.wordpress.com  
Host2: db02.wordpress.com  
DBVIP: mysql.wordpress.com 10.0.0.1

**1. Setup MySQL Master-Master replication**  
Ref: [Setup MySQL replication][]

**2. Install [keepalived ][keepalived]at both hosts**

**3. Config keepalived**

1) Add keepalived config file /etc/keepalived/keepalived.conf  
Config file for host db01:

! Configuration File for keepalived
    global_defs {
          notification_email {
            alexzeng@wordpress.com
          }
          notification_email_from alexzeng@wordpress.com
          smtp_server mx.wordpress.com
          smtp_connect_timeout 30
          router_id mysql-ha
          }
     
    vrrp_script check_mysql {
       script "/mysql/keepalived_check.sh db02.wordpress.com"
       interval 2
       weight 2
    }
     
    vrrp_instance VI_1 {
          state BACKUP
          interface eth1
          virtual_router_id 51
          priority 100
          advert_int 1
          nopreempt  # only needed on higher priority node
          authentication {
          auth_type PASS
          auth_pass 1111
          }
     
          track_script {
            check_mysql
          }
          virtual_ipaddress {
            10.0.0.1/24 dev eth1 label eth1:1
          }
          notify_master /mysql/keepalived_master.sh
          notify_backup /mysql/keepalived_backup.sh
    }

Config file of host db02:

Copy the config file in db01, and change this line:From   script "/mysql/keepalived_check.sh db02.wordpress.com"to   script "/mysql/keepalived_check.sh db01.wordpress.com"

2) Add scripts to both nodes  
/mysql/keepalived\_check.sh : monitor MySQL (for the host/network down, keepalived has internal mechanism to monitor them)

/mysql/keepalived\_master.sh : it will be called when the node becomes master

/mysql/keepalived\_backup.sh : it will be called when the node becomes slave

**4. Start keepalived at both nodes**

**5. Test it**  
Scenarios:  
A. Stop MySQL at the master node  
B. Shutdown master node network  
C. Shutdown master node OS  
D. Split-brain (the nodes cannot connect to each other) – In my test, keepalived didn’t do anything in this situation.

Check result:  
1) Check emails  
2) Check IP using ifconfig at both nodes  
2) Connect to DB without stop:

In my test, the db cannot be connected for just 2 seconds.

<table> 
 <tbody style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
  <tr style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
   <td style="border-width:0px;background:none;line-height:1.1em;vertical-align:baseline;font-size:1em;color:rgb(175,175,175);"> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      1 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      2 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      3 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      4 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      5 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      6 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      7 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      8 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      9 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      10 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      11 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      12 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      13 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      14 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      15 
    </div> 
    <div style="border-width:0px 3px 0px 0px;border-right-style:solid;border-right-color:rgb(108,226,108);line-height:1.1em;text-align:right;vertical-align:baseline;font-size:1em;">
      16 
    </div></td> 
   <td style="border-width:0px;background:none;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">Mon Oct 29 22:30:51 GMT+7 2012</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">+---------------+</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">| @@</code> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:rgb(255,20,147);">hostname</code>&nbsp;&nbsp;&nbsp; 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">|</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">+---------------+</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">| db01&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | </code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">+---------------+</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">Mon Oct 29 22:30:52 GMT+7 2012</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">ERROR 2003 (HY000): Can</code> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#0000FF;">'t connect to MySQL server on '</code> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">mysql.wordpress.com' (111)</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">Mon Oct 29 22:30:53 GMT+7 2012</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">ERROR 2003 (HY000): Can</code> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#0000FF;">'t connect to MySQL server on '</code> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">mysql.wordpress.com' (111)</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">Mon Oct 29 22:30:54 GMT+7 2012</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">+---------------+</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">| @@</code> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:rgb(255,20,147);">hostname</code>&nbsp;&nbsp;&nbsp; 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">|</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">+---------------+</code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">| db02&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | </code> 
    </div> 
    <div style="border:0px;line-height:1.1em;vertical-align:baseline;font-size:1em;"> 
     <code style="background:none;border:0px;line-height:1.1em;vertical-align:baseline;font-family:Consolas, 'Bitstream Vera Sans Mono', 'Courier New', Courier, monospace;font-size:1em;color:#000000;">+---------------+</code> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

转载于:https://blog.51cto.com/solin/1951808

[Setup MySQL replication]: https://alexzeng.wordpress.com/2012/09/13/how-to-setup-mysql-replication-in-simple-steps/
[keepalived]: http://www.keepalived.org/