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
1
2
3
4
5
<code>--using apt-get, </code><code>for</code> <code>Ubuntu</code>
<code>apt-get </code><code>install</code> <code>keepalived</code>
<code> </code>
<code>--using yum, </code><code>for</code> <code>Redhat</code>
<code>yum </code><code>install</code> <code>keepalive</code>
<code></code>
3. Config keepalived
1) Add keepalived config file /etc/keepalived/keepalived.conf
Config file for host db01:
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<code>! Configuration File </code><code>for</code> <code>keepalived</code>
<code>global_defs {</code>
<code> </code><code>notification_email {</code>
<code> </code><code>[email protected]</code>
<code> </code><code>}</code>
<code> </code><code>notification_email_from [email protected]</code>
<code> </code><code>smtp_server mx.wordpress.com</code>
<code> </code><code>smtp_connect_timeout 30</code>
<code> </code><code>router_id mysql-ha</code>
<code>vrrp_script check_mysql {</code>
<code> </code><code>script </code><code>"/mysql/keepalived_check.sh db02.wordpress.com"</code>
<code> </code><code>interval 2</code>
<code> </code><code>weight 2</code>
<code>}</code>
<code>vrrp_instance VI_1 {</code>
<code> </code><code>state BACKUP</code>
<code> </code><code>interface eth1</code>
<code> </code><code>virtual_router_id 51</code>
<code> </code><code>priority 100</code>
<code> </code><code>advert_int 1</code>
<code> </code><code>nopreempt </code><code># only needed on higher priority node</code>
<code> </code><code>authentication {</code>
<code> </code><code>auth_type PASS</code>
<code> </code><code>auth_pass 1111</code>
<code> </code><code>track_script {</code>
<code> </code><code>check_mysql</code>
<code> </code><code>virtual_ipaddress {</code>
<code> </code><code>10.0.0.1</code><code>/24</code> <code>dev eth1 label eth1:1</code>
<code> </code><code>notify_master </code><code>/mysql/keepalived_master</code><code>.sh</code>
<code> </code><code>notify_backup </code><code>/mysql/keepalived_backup</code><code>.sh</code>
Config file of host db02:
<code>Copy the config </code><code>file</code> <code>in</code> <code>db01, and change this line:From script </code><code>"/mysql/keepalived_check.sh db02.wordpress.com"</code><code>to script </code><code>"/mysql/keepalived_check.sh db01.wordpress.com"</code>
2) Add scripts to both nodes
/mysql/keepalived_check.sh : monitor MySQL (for the host/network down, keepalived has internal mechanism to monitor them)
<code>#!/bin/bash</code>
<code># monitor mysql status</code>
<code># if this node mysql is dead and its slave delay less than 120 seconds, then stop its keepalived. The other node will bind the IP.</code>
<code>export</code> <code>MYSQL_HOME=</code><code>/mysql</code>
<code>export</code> <code>PATH=$MYSQL_HOME</code><code>/bin</code><code>:$PATH</code>
<code>mysql=</code><code>"$MYSQL_HOME/bin/mysql"</code>
<code>delay_file=</code><code>"$MYSQL_HOME/slave_delay_second.log"</code>
<code>slave_host=$1</code>
<code>$mysql -u root --connect_timeout=3 --execute=</code><code>"select version();"</code>
<code>if</code> <code>[ $? -</code><code>ne</code> <code>0 ]; </code><code>then</code>
<code> </code><code>delayseconds=`</code><code>cat</code> <code>$delay_file`</code>
<code> </code><code>if</code> <code>[ $delayseconds -</code><code>le</code> <code>120 ]; </code><code>then</code>
<code> </code><code>/etc/init</code><code>.d</code><code>/keepalived</code> <code>stop</code>
<code> </code><code>fi</code>
<code> </code><code>exit</code> <code>1 </code><code>#bad</code>
<code>fi</code>
<code># Get slave delay time and save it</code>
<code>$mysql -urepluser -prepluser -h$slave_host --connect_timeout=3 -e</code><code>"select version();"</code>
<code>if</code> <code>[ $? -</code><code>eq</code> <code>0 ]; </code><code>then</code>
<code> </code><code>delayseconds=`$mysql -urepluser -prepluser -h$slave_host --connect_timeout=3 -e</code><code>"show slave status\G"</code><code>|</code><code>grep</code> <code>Seconds_Behind_Master|</code><code>awk</code> <code>'{print \$2}'</code><code>`</code>
<code> </code><code>if</code> <code>[[ </code><code>"$delayseconds"</code> <code>=~ ^[0-9]+$ ]] ; </code><code>then</code>
<code> </code><code>echo</code> <code>"$delayseconds"</code> <code>> $delay_file</code>
<code> </code><code>else</code>
<code> </code><code>echo</code> <code>"9999"</code> <code>> $delay_file</code>
<code> </code><code>fi</code>
<code>exit</code> <code>0 </code><code>#good</code>
/mysql/keepalived_master.sh : it will be called when the node becomes master
<code>my_host=`</code><code>hostname</code><code>`</code>
<code>current_date=`</code><code>/bin/date</code> <code>+</code><code>"%b %d %H:%M:%S"</code><code>`</code>
<code>From=</code><code>"$my_host"</code>
<code>[email protected]</code>
<code>Subject=</code><code>"$my_host is MASTER"</code>
<code>Msgboday=</code><code>"$current_date : mysql.wordpress.com is online at $my_host"</code>
<code>echo</code> <code>"$Msgboday"</code> <code>| </code><code>/usr/bin/mailx</code> <code>-s </code><code>"$Subject"</code> <code>"$mail_list"</code>
/mysql/keepalived_backup.sh : it will be called when the node becomes slave
<code>Subject=</code><code>"$my_host is BACKUP"</code>
<code>Msgboday=</code><code>"$current_date : mysql.wordpress.com is offline at $my_host"</code>
4. Start keepalived at both nodes
<code>service keepalived start</code>
<code>or</code>
<code>/etc/init</code><code>.d</code><code>/keepalived</code> <code>start</code>
<code>Check its log</code><code>file</code> <code>at</code><code>/var/log/messages</code>
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:
<code>while</code> <code>true</code> <code>loop</code>
<code>do</code>
<code>date</code>
<code>mysql -urepluser -prepluser -hmysql.wordpress.com -e</code><code>"select @@hostname;"</code>
<code>sleep</code> <code>1</code>
<code>done</code><code>;</code>
In my test, the db cannot be connected for just 2 seconds.
<code>Mon Oct 29 22:30:51 GMT+7 2012</code>
<code>+---------------+</code>
<code>| @@</code><code>hostname</code> <code>|</code>
<code>| db01 |</code>
<code>Mon Oct 29 22:30:52 GMT+7 2012</code>
<code>ERROR 2003 (HY000): Can</code><code>'t connect to MySQL server on '</code><code>mysql.wordpress.com' (111)</code>
<code>Mon Oct 29 22:30:53 GMT+7 2012</code>
<code>Mon Oct 29 22:30:54 GMT+7 2012</code>
<code>| db02 |</code>
本文轉自 運維小當家 51CTO部落格，原文連結：http://blog.51cto.com/solin/1951808，如需轉載請自行聯系原作者
![終端環境之tmux[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)