簡單原理

1、在兩臺服務(wù)器上分別部署雙主keepalived,主keepalived會在當(dāng)前服務(wù)器配置虛擬IP用于mysql對外提供服務(wù)

2、在兩臺服務(wù)器上分別部署主主mysql,用于故障切換

3、當(dāng)mysql服務(wù)器掛掉后,主keepalived會降低當(dāng)前機(jī)器權(quán)重,備keepalived服務(wù)器會把虛擬IP搶過來配置在備服務(wù)器上,使備服務(wù)器的mysql能接替工作繼續(xù)對外提供服務(wù)

4、由于keepalived只能檢測服務(wù)器是否宕機(jī)來實現(xiàn)故障自動切換,但不能針對應(yīng)用級別(mysql)的檢測,因此,需要編寫腳本實時監(jiān)測mysql服務(wù)是否運行正常,當(dāng)檢測mysql運行不正常時就降低權(quán)重,來實現(xiàn)故障自動切換

角色分配：

虛擬IP192.168.1.100初始配置在keepalived01,無需手動配置,keepalived會自動配置

準(zhǔn)備工作：

#關(guān)閉iptables

service iptables stop

chkconfig iptables off

#關(guān)閉selinux

setenforce 0

修改/etc/selinux/config文件,將SELINUX=enforcing改為SELINUX=disabled

#同步主機(jī)時間

ntpdate 202.120.2.101

=====================================================================

一、配置mysql雙主服務(wù)

#用yum安裝mysql服務(wù)

yum install MySQL-shared-compat-5.6.23-1.el6.x86_64.rpm

yum install MySQL-server-5.6.23-1.el6.x86_64.rpm

yum install MySQL-client-5.6.23-1.el6.x86_64.rpm

yum install MySQL-devel-5.6.23-1.el6.x86_64.rpm

yum install MySQL-shared-5.6.23-1.el6.x86_64.rpm

#創(chuàng)建數(shù)據(jù)目錄

mkdir -pv /home/mydata/data

chown -R mysql.mysql /home/mydata

chmod -R +w /home/mydata

#修改主配置文件

vi /etc/my.cnf

#在mysql001上

[mysqld]

datadir = /home/mydata/data #mysql的數(shù)據(jù)存放位置

port = 3306 #mysql的端口號

socket = /var/lib/mysql/mysql.sock

log-bin = master-bin

server_id = 1 #mysql的ID號

log-bin = mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = test.%

replicate-wild-ignore-table = information_schema.%

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

pid-file = /home/mydata/data/mysql.pid

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

innodb_file_per_table = on

thread_concurrency = 8

skip_name_resolve = on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#在mysql002上

[mysqld]

datadir = /home/mydata/data

port = 3306

socket = /var/lib/mysql/mysql.sock

log-bin = master-bin

server_id = 2

log-bin = mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table = mysql.%

replicate-wild-ignore-table = test.%

replicate-wild-ignore-table = information_schema.%

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

pid-file = /home/mydata/data/mysql.pid

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 1M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

innodb_file_per_table = on

thread_concurrency = 8

skip_name_resolve = on

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#初始化mysql

/usr/bin/mysql_install_db --datadir=/home/mydata/data --user=mysql

#相互為對方主機(jī)授權(quán)復(fù)制賬號

grant replication slave on *.* to 'repl_user'@'192.168.%.%' identified by 'repl_passwd';

#查看master的狀態(tài)

show master status;

#授權(quán)給check用戶,用來檢測mysql

grant replication client on *.* to 'check'@'localhost' identified by 'check';

#配置對方為自己的master

在mysql001上：

change master to \

master_host='192.168.1.201',

master_user='repl_user',

master_password='repl_passwd',

master_log_file='mysql-bin.000003',

master_log_pos=333; 【這個數(shù)字是上面show master status;】

在mysql002上：

change master to \

master_host='192.168.1.200',

master_user='repl_user',

master_password='repl_passwd',

master_log_file='mysql-bin.000003',

master_log_pos=333;

#啟動服務(wù),兩臺機(jī)器上都執(zhí)行

start slave;

#查看是否運行正常

show slave status\G;

二、配置keepalived

在mysql001上：

vi /etc/keepalived/keepalived.conf

#######################################################

! Configuration File for keepalived

global_defs {

notification_email {

2011820123@qq.com

}

notification_email_from keepalived@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MYSQL_DEVEL

}

vrrp_script check_mysqld { #設(shè)置檢測腳本

script "/etc/keepalived/check.sh" #指定檢測腳本的存放位置

interval 2 #間隔時間

weight -51 #權(quán)重,降權(quán)51

}

vrrp_instance VI_1 {

state BACKUP #注意,這里兩臺服務(wù)器都要是BACKUP

interface em1

virtual_router_id 60

priority 100

nopreempt

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysqld

}

virtual_ipaddress {

192.168.1.100 #虛擬IP

}

}

#######################################################

在mysql002上：

vi /etc/keepalived/keepalived.conf

#######################################################

! Configuration File for keepalived

global_defs {

notification_email {

2011820123@qq.com

}

notification_email_from keepalived@localhost

smtp_server 127.0.0.1

smtp_connect_timeout 30

router_id MYSQL_DEVEL

}

vrrp_script check_mysqld {

script "/etc/keepalived/check.sh"

interval 2

weight -51

}

vrrp_instance VI_1 {

state BACKUP #注意,這里兩臺服務(wù)器都要是BACKUP

interface em1

virtual_router_id 60

priority 90

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysqld

}

virtual_ipaddress {

192.168.1.100

}

}

#######################################################

"state BACKUP”,在這里兩臺服務(wù)器要都設(shè)為BACKUP,如果一臺是MASTER,另一臺是BACKUP,那么當(dāng)MASTER恢復(fù)數(shù)據(jù)之后,主keepalived要切換到MASTER上,這樣BACKUP上的數(shù)據(jù)就會丟失.因此在這兩臺mysql服務(wù)器上都設(shè)置成BACKUP,那么當(dāng)MASTER恢復(fù)后,不會自動切換回去

#重啟keepalived服務(wù)

service keepalived restart

五、編輯mysql監(jiān)控腳本

vi /etc/keepalived/check.sh

#######################################################

#/bin/bash

live=`ss -tnlp | grep 3306 | wc -l` #檢查mysql的3306端口是否存在

yes=`mysql -ucheck -pcheck -e "show slave status\G" | head -13 | tail -2 | awk -F: '{print $2}' | grep Yes | wc -l` #檢查‘show slave status’是否正常

if [ $live -ge 1 ];then #如果$live大于等于1,執(zhí)行下步操作

if [ $yes -eq 2 ];then #如果‘show slave status’出現(xiàn)兩個yes,就退出

exit 0

else

/etc/init.d/keepalived restart #否則,重啟keepalived服務(wù),退出

exit 1

fi

else

/etc/init.d/keepalived restart #如果$live小于1,那么重啟keepalived服務(wù)

exit 1

fi

#######################################################

#給腳本執(zhí)行權(quán)限

chmod +x /etc/keepalived/check.sh