[关闭]
@saltyang 2018-06-19T11:40:51.000000Z 字数 6601 阅读 1005

Mysql HA：Mysql + Keepalived

Mysql, 主主， keepalived

Mysql Sync Replication

  • 写事务会向master的块设备与一些用来复制的日志（bin_log）写入记录，完成后立即向应用程序发出事物完成的确认信息，master不关心该事物是否已经被slaves复制。对于Slaves来说，他们会在另外的时间里，从master上拿到自己所需要的数据进行复制
  • master保存事务与"Sync replication"大致相同，不同的地方在于它仅需要等待slaves中的任一台slave回复确认信息即可。这台回复的slave，它仅仅需要将该事务日志记录从master取回，写入至自己的中继日志(relay log)并将日志刷新至块设备。所以并不意味着该事务数据已经在数据库中执行(在mysql中就是sql thread是否已执行)。master一收到slave的确认消息即向应用程序返回事务已完成。

本系统采用Keepalived + Mysql主主模式来保证数据库的高可用

  1. Linux: Centos 6.9
  2. Mysql Version: 5.5
  3. master_mysql1 ip: 192.168.1.146
  4. master_mysql2 ip: 192.168.1.148
  5. VIP: 192.168.1.149

ENV Prepare

  1. yum install keepalived mysql; #Install mysql and keepalived
  2. chkconfig keepalived on; chkconfig mysqld on; # Add keepalived and mysql to machine start

Mysql Config

  1. [mysqld]
  2. log-bin=mysql-bin
  3. server-id=146
  4. auto-increment-increment = 2
  5. uto-increment-offset = 1
  1. [mysqld]
  2. log-bin=mysql-bin
  3. server-id=148
  4. auto-increment-increment = 2
  5. uto-increment-offset = 1
  1. # master_mysql1
  2. mysql > grant replication client,replication slave on *.* to backup@192.168.1.148 identified by '123456';
  3. mysql >  flush privileges;
  5. # master_mysql2
  6. mysql > grant replication client,replication slave on *.* to backup@192.168.1.146 identified by '123456';
  7. mysql >  flush privileges;
  1. # master_mysql1
  2. mysql > show master status;
  4. # master_mysql2
  5. mysql > change master to master_host='192.168.1.146',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=263; # mysql-bin.000001 and 263 is output of last command
  6. mysql > start slave;
  7. mysql > show slave status\G  #excute this command and check two args below ，yes is success
  8.            Slave_IO_Running: Yes
  9.            Slave_SQL_Running: Yes
  1. # master_mysql2
  2. mysql > show master status;
  4. # master_mysql1
  5. mysql > change master to master_host='192.168.1.148',master_user='backup',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=278; # mysql-bin.000002 and 278 is output of last command
  6. mysql > start slave;
  7. mysql > show slave status\G  #excute this command and check two args below ，yes is success
  8.            Slave_IO_Running: Yes
  9.            Slave_SQL_Running: Yes

Keepalived Config

  1. ! Configuration File for keepalived
  3. global_defs {
  4.    notification_email {
  5.      salt_yang@puyacn.com
  6.    }
  7.    notification_email_from service@webackup.cn
  8.    smtp_server smtp.mxichina.cn
  9.    smtp_connect_timeout 30
  10.    router_id MASTER
  11. }
  12. vrrp_script check_mysql {
  13.    script "/etc/keepalived/checkmysql.sh check"
  14.    interval 30
  15. }
  17. vrrp_instance MASTER {
  18.     state BACKUP
  19.     interface eth4
  20.     virtual_router_id 51
  21.     priority 100
  22.     advert_int 1
  23.     authentication {
  24.         auth_type PASS
  25.         auth_pass 1111
  26.     }
  27.     virtual_ipaddress {
  28.         192.168.1.149
  29.     }
  30.     track_script {
  31.         check_mysql
  32.     }
  33.     debug
  34.     nopreempt
  35.     notify_master "/etc/keepalived/checkmysql.sh master"
  36.     notify_backup "/etc/keepalived/checkmysql.sh backup"
  37. }
  1. ! Configuration File for keepalived
  3. global_defs {
  4.    notification_email {
  5.      salt_yang@puyacn.com
  6.    }
  7.    notification_email_from service@webackup.cn
  8.    smtp_server smtp.mxichina.cn
  9.    smtp_connect_timeout 30
  10.    router_id BACKUP
  11. }
  12. vrrp_script check_mysql {
  13.    script "/etc/keepalived/checkmysql.sh check"
  14.    interval 30
  15. }
  17. vrrp_instance BACKUP {
  18.     state BACKUP
  19.     interface eth5
  20.     virtual_router_id 51
  21.     priority 90
  22.     advert_int 1
  23.     authentication {
  24.         auth_type PASS
  25.         auth_pass 1111
  26.     }
  27.     virtual_ipaddress {
  28.         192.168.1.149
  29.     }
  30.     track_script {
  31.         check_mysql
  32.     }
  33.     debug
  34.     nopreempt
  35.     notify_master "/etc/keepalived/checkmysql.sh master"
  36.     notify_backup "/etc/keepalived/checkmysql.sh backup"
  37. }
  1. #!/bin/dash
  2. # Script to handle MySQL from keepalived.
  3. #
  4. # Usage: checkmysql.sh action
  5. #
  6. # Where action is :
  7. #
  8. # check : MySQL is running
  9. #
  10. # backup: set to backup state.
  11. # fault : set to fault state. Killing MySQL
  12. # master: set to master state. start MySQL
  13. #
  14. #
  15. # Note: you can use $MAINTENANCE (/etc/keepalived/maintenance) to disable MySQL checks
  16. # in case of short MySQL maintenance
  17. #
  19. # Usage func :
  20. [ "$1" = "--help" ] && { sed -n -e '/^# Usage:/,/^$/ s/^# \?//p' < $0; exit; }
  23. #
  24. # CONFIG
  25. #
  26. MYSQL="/usr/bin/mysql"
  27. # must return "1" string:
  28. CHECKSQL="SELECT 1"
  29. CHECKSQLSTR="1"
  30. # MySQL server settings
  31. MYSQLPID="/var/run/mysqld/mysqld.pid"
  32. MYSQLINIT="/etc/init.d/mysqld"
  33. MYSQLUSER="root"
  34. MYSQLPWD="111111"
  35. # Keepalived
  36. KEEPALIVEDPID=/var/run/keepalived.pid
  37. KEEPALIVED=/etc/init.d/keepalived
  38. # warmup delay
  39. MAXWAIT=240
  40. # MySQL fatal errors, which will provoke a node switch
  41. # (should be completed with next bad events...)
  42. MYSQL_FATAL_ERRORS='(2000|2001|2002|2003|2005|2008|28000)'
  43. # maintenance flag: used to do maintenance on MySQL without switch between nodes
  44. MAINTENANCE="/etc/keepalived/maintenance"
  47. #
  48. # CONFIG LOGGER
  49. #
  50. LOG="logger -t KeepMYSQL[$$] -p syslog" # do not use -i
  51. LOGWARN="$LOG.warn"
  52. LOGERR="$LOG.err"
  54. check() {
  55.     # master and slave need check mysql status,
  56.     # if mysql is down in master ,need switch master to slave. But it's not needed in salve.
  57.     if ip addr | grep "${VIP}"
  58. 	then
  59. 		if check_mysql
  60. 		then
  61. 		    $LOGWARN "MySQL is ok"
  62.         else
  63.             $MYSQLINIT restart
  64. 			$LOGWARN "Restart Mysqld service"
  65.             if check_mysql
  66.             then
  67.                 $LOGWARN "After restart mysqld service, but it's also down. Switch keepalive status"
  68. 		        $KEEPALIVED restart
  69.             fi
  70.         fi
  71.     else
  72.         $LOGWARN "This is Mysql Salve."
  73. 	    if check_mysql
  74. 		then
  75. 		    $LOGWARN "MySQL is ok"
  76.         else
  77.             $MYSQLINIT restart
  78. 			$LOGWARN "Restart Mysqld service"
  79.             if check_mysql
  80.             then
  81.                 $LOGWARN "After restart mysqld service, but it's also down. Switch keepalive status"
  82.             fi
  83.         fi
  84.     fi
  86.     return $?
  87. }
  90. set_master() {
  91.     # Starting MySQL
  92.     if [ $( pidof mysqld | wc -w ) -gt 0 ]
  93. 	then
  94. 		$LOGWARN "MySQL already started ? What did I have to do ?"
  95.     else
  96.         $LOGWARN "Starting MySQL ..."
  97. 		$MYSQLINIT start
  98.         for i in $( seq 1 $MAXWAIT )
  99.         do
  100.             sleep 1
  101.             if check_mysql
  102.             then
  103.                 break
  104.             fi
  105.         done
  106.     fi
  108.     if check_mysql
  109.     then
  110.         $LOGWARN "Checking MySQL MyISAM tables ..."
  111. 		mysql_check mysql
  112. 		$MYSQL -ABN -e "select TABLE_SCHEMA, TABLE_NAME from tables WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT LIKE '%_schema' AND TABLE_SCHEMA NOT LIKE 'mysql'" information_schema | while read db table
  113.         do
  114.             mysql_check $db $table
  115.         done
  116.     else
  117.         $LOGWARN "MySQL is broken and need a manual repair :("
  118.     fi
  119. }
  122. # Do a mysqlcheck
  123. mysql_check() {
  124.     db=$1
  125. 	table=$2
  126.     param="$db $table"
  127.     if [ -z "$table" ]
  128. 	then
  129. 		param="-B $db"
  130.     fi
  131.     cmd="${MYSQL}check --medium-check -F --auto-repair $param"
  132.     $LOGWARN "$cmd"
  133.     $cmd 2>&1 | while read l
  134. 	do
  135. 		$LOGWARN "$l"
  136.     done
  137. }
  139. # Check that MySQL is responding
  140. # Return:
  141. # 0 if everything is OK (or in maintenance mode)
  142. # 1 if SQL did not return the expected string
  143. # 2 if MySQL did not reply
  144. check_mysql() {
  145.     if [ -e $MAINTENANCE ]
  146.     then
  147.         return 0
  148.     fi
  150.     m=$( $MYSQL --connect-timeout=2 -ABN -e "$CHECKSQL" mysql -u$MYSQLUSER -p$MYSQLPWD 2>&1 )
  151. 	mcode=$?
  153.     # Check MySQL error codes. Not all errors are fatal, like "1023 too many connections"
  154.     if [ $mcode -gt 0 ]
  155. 	then
  156. 		merrno=$( echo "$m" | grep -Eo 'ERROR ([0-9]+) ' | cut -d" " -f2 )
  157. 		$LOGWARN "[MySQL is unavailable] $m"
  158. 		if echo "$merrno" | grep -qE "$MYSQL_FATAL_ERRORS"
  159.         then
  160.             return 2
  161.         else
  162.             return 0 # not fatal ...
  163.         fi
  165.     # Check MySQL reply to SQL query
  166.     elif [ "$m" = "$CHECKSQLSTR" ]
  167.     then
  168.         return 0
  169.     else
  170.         $LOGWARN "MySQL did not return expected value: '$CHECKSQLSTR' != '$m'"
  171.         return 1
  172.     fi
  173.     return 1
  174. }
  177. case "$1" in
  178. 	check)
  179. 		check
  180. 		exit $?
  181.     ;;
  182.     backup)
  183.         $LOGWARN "=> set to backup state <="
  184. 		set_master
  185. 		exit $?
  186.     ;;
  187.     master)
  188.         $LOGWARN "=> set to master state <="
  189. 		set_master
  190. 		exit $?
  191.     ;;
  192. esac
添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注