LinuxEye - Linux系统教程
TAG标签RSS订阅[设为首页] [加入收藏]

LinuxEye - Linux系统教程

热门标签:

当前位置: 主页 > 数据库 >

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

时间:2013-07-12 16:59来源:转载 编辑:linuxeye 点击: 次
实现无人工干预下mysqlz中MM自动切换，S自动寻找存活的M同步数据， 图示如下： 1.正常情况下，写只有一个服务器在提供服务，另外一个备份，中间通过keepalived实现 2.只读服务器，可以

实现无人工干预下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
! Configuration File for 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=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
echo "MYSQL is good"
else
MYSQL_OK=0
echo "MYSQL is fail"
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done


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  dailed
NodeIP01=192.168.1.53
NodeIP02=192.168.1.52

Node01_MYSQL_OK=1
function check_Node01_mysql_helth (){

/usr/bin/nc   -z  $NodeIP01 3306 >/dev/null 2>&1
if [ $? = 0 ] ;then
Node01_MYSQL_OK=1
echo "$NodeIP01 MYSQL is good"
else
Node01_MYSQL_OK=0
echo "$NodeIP01 MYSQL is fail" >>$CDR/check.log
fi
return $Node01_MYSQL_OK

}

Node02_MYSQL_OK=1
function check_Node02_mysql_helth (){

/usr/bin/nc   -z  $NodeIP02 3306 >/dev/null 2>&1
if [ $? = 0 ] ;then
Node02_MYSQL_OK=1
echo "$NodeIP02 MYSQL is good"
else
Node02_MYSQL_OK=0
echo "$NodeIP02 MYSQL is fail" >>$CDR/check.log
fi
return $Node02_MYSQL_OK

}

CHECK_TIME=3

while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
echo "test" >>$CDR/check.log
check_Node01_mysql_helth
if [ $Node01_MYSQL_OK = 1 ] ; then
CHECK_TIME=0
#exit 0
fi
if [ $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 1
fi
sleep 1
done

CHECK_TIME=3

while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_Node02_mysql_helth
if [ $Node02_MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $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
exit 1
fi
sleep 1
done

修改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  dailed
NodeIP01=192.168.1.53
NodeIP02=192.168.1.52

Node01_MYSQL_OK=1
function check_Node01_mysql_helth (){

/usr/bin/nc   -z  $NodeIP01 3306 >/dev/null 2>&1
if [ $? = 0 ] ;then
Node01_MYSQL_OK=1
else
Node01_MYSQL_OK=0
fi
return $Node01_MYSQL_OK

}

Node02_MYSQL_OK=1
function check_Node02_mysql_helth (){

/usr/bin/nc   -z  $NodeIP02 3306 >/dev/null 2>&1
if [ $? = 0 ] ;then
Node02_MYSQL_OK=1
else
Node02_MYSQL_OK=0
fi
return $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: Waiting for master to send event
                  Master_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: Waiting for master to send event
                  Master_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 in set (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 in set (0.00 sec)

数据同步成功。

5.启动Master1 中mysql：

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

转载请保留固定链接： https://linuxeye.com/database/1824.html
------分隔线----------------------------
标签：mysql集群
栏目列表
推荐内容
热点内容

Copyright © 2021 LINUXEYE
沪ICP备2021018617号-2