MySQL集群架构全自动化实现切换（M-M-S-S)


实现无人工干预下mysqlz中MM自动切换，S自动寻找存活的M同步数据，
图示如下：

1.正常情况下，写只有一个服务器在提供服务，另外一个备份，中间通过keepalived实现
2.只读服务器，可以多台服务器同时提供服务，也可以只有一台提供服务，keepalived都可以实现，我这边目前写的主要是一台提供服务。

环境搭建步骤：
1.给四台服务器安装mysql，keepalived。（版本一致）

2.master1中mysql，keepalived配置：

[root@master1 ~]#cat/etc/my.cnf
[client]
port            =3306
socket          =/usr/local/mysql/data/mysql.sock
[mysqld]
port            =3306
socket          =/usr/local/mysql/data/mysql.sock
datadir         =/usr/local/mysql/data/
skip_name_resolve
skip-external-locking
key_buffer_size =384M
max_allowed_packet =1M
table_open_cache =512
sort_buffer_size =2M
read_buffer_size =2M
read_rnd_buffer_size =8M
myisam_sort_buffer_size =64M
thread_cache_size =8
query_cache_size =32M
thread_concurrency =8
max_connections =1000
log_bin_trust_function_creators=1
transaction_isolation=read-committed
slave-skip-errors=all
replicate-do-db=mydb
replicate-ignore-db=mysql
replicate-ignore-db=ms_state
log-bin=mysql-bin
server-id       =11
binlog_format=row
innodb_buffer_pool_size =1120M[mysqldump]
quick
max_allowed_packet =16M[mysql]no-auto-rehash
[myisamchk]
key_buffer_size =256M
sort_buffer_size =256M
read_buffer =2M
write_buffer =2M[mysqlhotcopy]
interactive-timeout
[root@master1 ~]# cat /etc/keepalived/keepalived.conf
!ConfigurationFilefor keepalived

global_defs {
   notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
   }
   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server XX.XX.XX.XX
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_script check_run {
script "/opt/keepalived_check_mysql.sh"
interval 5}

vrrp_sync_group VG1 {group{
VI_1
}}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111}
track_script {
check_run
}
    virtual_ipaddress {192.168.1.50}}
[root@master1 ~]# cat /opt/keepalived_check_mysql.sh
#!/bin/bash

MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME=3#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD}-e "show status;">/dev/null2>&1if[ $?=0];then
MYSQL_OK=1
echo "MYSQL is good"else
MYSQL_OK=0
echo "MYSQL is fail"fireturn $MYSQL_OK
}while[ $CHECK_TIME -ne 0]dolet"CHECK_TIME -= 1"
check_mysql_helth
if[ $MYSQL_OK =1];then
CHECK_TIME=0exit0fiif[ $MYSQL_OK -eq 0]&&[ $CHECK_TIME -eq 0]then/etc/init.d/keepalived stop
exit1fi
sleep 1done


3.上面三个配置文件my.cnf文件每个机器都要server-id = 11此参数修改不一样即可
keepalived.conf 其中Slave1服务器跟Master1一样，其他两台去掉script "/opt/keepalived_check_mysql.sh"
vip也需要修改

4.mysql建立同步账号：
在Master1服务器上：

GRANT ALL PRIVILEGES ON *.* TO rep@'192.168.1.53' IDENTIFIED BY '000000';
GRANT ALL PRIVILEGES ON *.* TO rep@'192.168.1.51' IDENTIFIED BY '000000';
GRANT ALL PRIVILEGES ON *.* TO rep@'192.168.1.55' IDENTIFIED BY '000000';
flush privileges;

嫌麻烦就直接给整个网段授权：

GRANT ALL PRIVILEGES ON *.* TO rep@'192.168.1.%' IDENTIFIED BY '000000';

在Master2上同样运行

5.Slave1,Slave2上建立同步

stop slave;
change master to master_host='192.168.1.52', master_user='rep',master_password='000000', master_log_file='mysql-bin.000003', master_log_pos=796;
start slave;

注意： master_log_file,master_log_pos参数是在master1上show master status;查到的。

6.Slave1,Slave2上检测脚本
checkmysql.sh :M1,M2状态检测
ChangeNode.sh:Slave对应远程Master服务器修改以及数据同步。
checkstatus.sh :修改Master后，停止再次修改，避免数据丢失。

[root@Slave1]# cat checkmysql.sh
#!/bin/bash

CDR=/opt/shell
cd $CDR
#check nodes's mysql alived  or  dailedNodeIP01=192.168.1.53NodeIP02=192.168.1.52Node01_MYSQL_OK=1function check_Node01_mysql_helth (){/usr/bin/nc   -z  $NodeIP01 3306>/dev/null2>&1if[ $?=0];thenNode01_MYSQL_OK=1
echo "$NodeIP01 MYSQL is good"elseNode01_MYSQL_OK=0
echo "$NodeIP01 MYSQL is fail">>$CDR/check.log
fireturn $Node01_MYSQL_OK

}Node02_MYSQL_OK=1function check_Node02_mysql_helth (){/usr/bin/nc   -z  $NodeIP02 3306>/dev/null2>&1if[ $?=0];thenNode02_MYSQL_OK=1
echo "$NodeIP02 MYSQL is good"elseNode02_MYSQL_OK=0
echo "$NodeIP02 MYSQL is fail">>$CDR/check.log
fireturn $Node02_MYSQL_OK

}

CHECK_TIME=3while[ $CHECK_TIME -ne 0]dolet"CHECK_TIME -= 1"
echo "test">>$CDR/check.log
check_Node01_mysql_helth
if[ $Node01_MYSQL_OK =1];then
CHECK_TIME=0#exit 0fiif[ $Node01_MYSQL_OK -eq 0]&&[ $CHECK_TIME -eq 0]then#run.sh
echo "change to another master mysql of $NodeIP02">>$CDR/check.log
sh  $CDR/ChangeNode.sh  $NodeIP02
#exit 1fi
sleep 1done

CHECK_TIME=3while[ $CHECK_TIME -ne 0]dolet"CHECK_TIME -= 1"
check_Node02_mysql_helth
if[ $Node02_MYSQL_OK =1];then
CHECK_TIME=0exit0fiif[ $Node02_MYSQL_OK -eq 0]&&[ $CHECK_TIME -eq 0]then#run.sh
echo "change to another master mysql  of $NodeIP01">>$CDR/check.log
sh  $CDR/ChangeNode.sh $NodeIP01
exit1fi
sleep 1done

修改Master脚本

[root@Slave1]# cat ChangeNode.sh
#!/bin/bash#Change another master mysql
CDR=/opt/shell
cd $CDR

Gip=$1


#get the good master mysql status $Gip to tmp file

/usr/local/mysql/bin/mysql  -uroot -p123456  -h$Gip  -e "show master status"|grep mysql >.tmp

logname=`cat .tmp |awk '{print $1}'`
lognumber=`cat .tmp |awk '{print $2}'`
echo "$logname,$lognumber">>$CDR/good.tst

/usr/local/mysql/bin/mysql  -uroot -p123456  -h127.0.0.1-e "stop slave"/usr/local/mysql/bin/mysql  -uroot -p123456  -h127.0.0.1-e "change master to master_host='$Gip', master_user='share01',master_password='111111', master_log_file='$logname', master_log_pos=$lognumber;"/usr/local/mysql/bin/mysql  -uroot -p123456  -h127.0.0.1-e "start slave"


#check the changed is secussfull or fail

/usr/local/mysql/bin/mysql  -uroot -p123456  -h127.0.0.1-e "show slave status \G;"|grep  Master_Host|grep  $Gip
if[ $?=0];then
echo "Change is secussfull"> $CDR/change.log
sed -i '2s/^/#&/g'/var/spool/cron/root

else
echo "Change is fail "fi


运行状态检测脚本：

[root@Slave1]# cat checkstatus.sh
#!/bin/bash

CDR=/opt/shell
cd $CDR
#check nodes's mysql alived  or  dailedNodeIP01=192.168.1.53NodeIP02=192.168.1.52Node01_MYSQL_OK=1function check_Node01_mysql_helth (){/usr/bin/nc   -z  $NodeIP01 3306>/dev/null2>&1if[ $?=0];thenNode01_MYSQL_OK=1elseNode01_MYSQL_OK=0fireturn $Node01_MYSQL_OK

}Node02_MYSQL_OK=1function check_Node02_mysql_helth (){/usr/bin/nc   -z  $NodeIP02 3306>/dev/null2>&1if[ $?=0];thenNode02_MYSQL_OK=1elseNode02_MYSQL_OK=0fireturn $Node02_MYSQL_OK

}
cat /var/spool/cron/root|grep  checkmysql.sh|grep ^#
temid=$?
sleep 1s
check_Node01_mysql_helth
sleep 1s
check_Node02_mysql_helth


if[ $Node01_MYSQL_OK =1]&&[ $Node02_MYSQL_OK =1]&&[ $temid =0];then#if [ $Node01_MYSQL_OK = 1 ] ; then
sed -i  '2s/^#//g'/var/spool/cron/root
fi


分别把两个脚本放入crontab里面：

[root@Slave1]# crontab  -l
*****/opt/shell/checkmysql.sh
*****/opt/shell/checkstatus.sh


7.启动服务
在各个服务器上启动服务：

/etc/init.d/mysqld start
/etc/init.d/keepalived start


测试：
1.在Slave1上检查目前Master是哪台：

mysql> show slave status \G;***************************1. row ***************************Slave_IO_State:Waitingfor master to send eventMaster_Host:192.168.1.53


2.192.168.1.53上，停止MySQL

/etc/init.d/mysqld  stop
Shutting down MySQL....[  OK  ]


VIP自动漂移到Master2
Slave1，Slave2上状态如下：

mysql> show slave status \G;***************************1. row ***************************Slave_IO_State:Waitingfor master to send eventMaster_Host:192.168.1.52[root@Slave1]# crontab  -l
#* *   *  *  *  /opt/shell/checkmysql.sh*****/opt/shell/checkstatus.sh


3.在Master2插入数据：

mysql>select*from test;+------+-------+| id   | name  |+------+-------+|1| bobu  ||2| bobu  ||3| bobu1 |+------+-------+3 rows inset(0.00 sec)

mysql> insert into test values('4','test')

查看Slave1,Slave2数据：

mysql>select*from test;+------+-------+| id   | name  |+------+-------+|1| bobu  ||2| bobu  ||3| bobu1 ||4| test  |+------+-------+4 rows inset(0.00 sec)

数据同步成功。

5.启动Master1 中mysql：

/etc/init.d/mysqld  start
StartingMySQL..[  OK  ]Slave1,Slave2状态：[root@mail shell]# crontab  -l
*****/opt/shell/checkmysql.sh
*****/opt/shell/checkstatus.sh

相关内容