实现无人工干预下mysqlz中MM自动切换,S自动寻找存活的M同步数据,图示如下: 1.正常情况下,写只有一个服务器在提供服务,另外一个备份,中间通过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
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上同样运行 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;查到的。 [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
/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"
/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
[root@Slave1]# crontab -l *****/opt/shell/checkmysql.sh *****/opt/shell/checkstatus.sh
/etc/init.d/mysqld start /etc/init.d/keepalived start
mysql> show slave status \G;***************************1. row ***************************Slave_IO_State:Waitingfor master to send eventMaster_Host:192.168.1.53
/etc/init.d/mysqld stop Shutting down MySQL....[ OK ]
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
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) 数据同步成功。 /etc/init.d/mysqld start StartingMySQL..[ OK ]Slave1,Slave2状态:[root@mail shell]# crontab -l *****/opt/shell/checkmysql.sh *****/opt/shell/checkstatus.sh |
|
来自: 昵称21365845 > 《MySQL集群》