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=3306socket=/usr/local/mysql/data/mysql.sock[mysqld]port=3306socket=/usr/local/mysql/data/mysql.sockdatadir =/usr/local/mysql/data/skip_name_resolveskip-external-lockingkey_buffer_size =384Mmax_allowed_packet =1Mtable_open_cache =512sort_buffer_size =2Mread_buffer_size =2Mread_rnd_buffer_size =8Mmyisam_sort_buffer_size =64Mthread_cache_size =8query_cache_size =32Mthread_concurrency =8max_connections =1000log_bin_trust_function_creators=1transaction_isolation=read-committedslave-skip-errors=allreplicate-do-db=mydbreplicate-ignore-db=mysqlreplicate-ignore-db=ms_statelog-bin=mysql-binserver-id =11binlog_format=rowinnodb_buffer_pool_size =1120M[mysqldump]quickmax_allowed_packet =16M[mysql]no-auto-rehash[myisamchk]key_buffer_size =256Msort_buffer_size =256Mread_buffer =2Mwrite_buffer =2M[mysqlhotcopy]interactive-timeout[root@master1 ~]# cat /etc/keepalived/keepalived.conf！ConfigurationFilefor keepalivedglobal_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 MASTERinterface eth0virtual_router_id 51priority 100advert_int 1authentication {auth_type PASSauth_pass 1111}track_script {check_run}virtual_ipaddress {192.168.1.50}}[root@master1 ~]# cat /opt/keepalived_check_mysql.sh#！/bin/bashMYSQL=/usr/local/mysql/bin/mysqlMYSQL_HOST=127.0.0.1MYSQL_USER=rootMYSQL_PASSWORD=123456CHECK_TIME=3#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0MYSQL_OK=1function check_mysql_helth （）{$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD}-e "show status;">/dev/null2>&1if[ $？=0];thenMYSQL_OK=1echo "MYSQL is good"elseMYSQL_OK=0echo "MYSQL is fail"fireturn $MYSQL_OK}while[ $CHECK_TIME -ne 0]dolet"CHECK_TIME -= 1"check_mysql_helthif[ $MYSQL_OK =1];thenCHECK_TIME=0exit0fiif[ $MYSQL_OK -eq 0]&&[ $CHECK_TIME -eq 0]then/etc/init.d/keepalived stopexit1fisleep 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/bashCDR=/opt/shellcd $CDR#check nodes"s mysql alivedordailedNodeIP01=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=1echo "$NodeIP01 MYSQL is good"elseNode01_MYSQL_OK=0echo "$NodeIP01 MYSQL is fail">>$CDR/check.logfireturn $Node01_MYSQL_OK}Node02_MYSQL_OK=1function check_Node02_mysql_helth （）{/usr/bin/nc -z$NodeIP02 3306>/dev/null2>&1if[ $？=0];thenNode02_MYSQL_OK=1echo "$NodeIP02 MYSQL is good"elseNode02_MYSQL_OK=0echo "$NodeIP02 MYSQL is fail">>$CDR/check.logfireturn $Node02_MYSQL_OK}CHECK_TIME=3while[ $CHECK_TIME -ne 0]dolet"CHECK_TIME -= 1"echo "test">>$CDR/check.logcheck_Node01_mysql_helthif[ $Node01_MYSQL_OK =1];thenCHECK_TIME=0#exit 0fiif[ $Node01_MYSQL_OK -eq 0]&&[ $CHECK_TIME -eq 0]then#run.shecho "change to another master mysql of $NodeIP02">>$CDR/check.logsh$CDR/ChangeNode.sh$NodeIP02#exit 1fisleep 1doneCHECK_TIME=3while[ $CHECK_TIME -ne 0]dolet"CHECK_TIME -= 1"check_Node02_mysql_helthif[ $Node02_MYSQL_OK =1];thenCHECK_TIME=0exit0fiif[ $Node02_MYSQL_OK -eq 0]&&[ $CHECK_TIME -eq 0]then#run.shecho "change to another master mysqlof $NodeIP01">>$CDR/check.logsh$CDR/ChangeNode.sh $NodeIP01exit1fisleep 1done修改Master脚本[root@Slave1]# cat ChangeNode.sh#！/bin/bash#Change another master mysqlCDR=/opt/shellcd $CDRGip=$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 >.tmplogname=`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;"|grepMaster_Host|grep$Gipif[ $？=0];thenecho "Change is secussfull"> $CDR/change.logsed -i "2s/^/#&/g"/var/spool/cron/rootelseecho "Change is fail "fi
运行状态检测脚本：[root@Slave1]# cat checkstatus.sh#！/bin/bashCDR=/opt/shellcd $CDR#check nodes"s mysql alivedordailedNodeIP01=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|grepcheckmysql.sh|grep ^#temid=$？sleep 1scheck_Node01_mysql_helthsleep 1scheck_Node02_mysql_helthif[ $Node01_MYSQL_OK =1]&&[ $Node02_MYSQL_OK =1]&&[ $temid =0];then#if [ $Node01_MYSQL_OK = 1 ] ; thensed -i"2s/^#//g"/var/spool/cron/rootfi
分别把两个脚本放入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/mysqldstopShutting 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/mysqldstartStartingMySQL..[OK]Slave1,Slave2状态：[root@mail shell]# crontab-l*****/opt/shell/checkmysql.sh*****/opt/shell/checkstatus.shMySQL DNS反查导致连接缓慢Oracle入门教程：把表和索引放在不同的表空间里相关资讯集群 MYSQL集群

MySQL集群之MySQL Cluster （04月11日）
MySQL与MariaDB 新型主从集群配置（10/22/2015 20:32:05）
HPC集群公网开放安全加固和资源显（01/19/2015 09:10:12）

Linux下MySQL/MariaDB Galera集群（01月10日）
MySQL与MariaDB 传统主从集群配置（10/22/2015 20:27:49）
MySQL集群架构及配置过程中出现问（01/13/2015 11:37:05）

本文评论查看全部评论（0）