文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

mysql主从同步

2017-01-13 09:10

关注

mysql主从同步

#!/bin/bash

. /etc/rc.d/init.d/functions

if [ $# -ne 0 ];then
   echo "Usage:sh `basename $0`"
   exit 1
fi

#MASTER VARIABLES
#binlog-do-db=yiishop 设置复制的数据库
MASTER_USER=root
MASTER_PASS="Qwe#663745721"
MASTER_PORT=3306
MASTER_IP="172.18.47.44"
REP_USER="slave"
REP_PWD="Qwe#663745721" 

#SLAVE VARIABLES
SLAVE_USER=root
SLAVE_PASS="Qwe#663745721"
SLAVE_PORT=3306
SLAVE_IP="172.18.47.43"
#启用多线程
WORKERS=0
MASTER_DATA_PATH=/data/backup
MASTER_STATUS_FILE=${MASTER_DATA_PATH}/mysqllogs_`date +%F`.log
MASTER_DATA_FILE=${MASTER_DATA_PATH}/mysql_backup_`date +%F`.sql.gz
 
MYSQL_DIR=/usr/bin
#指定数据库 --databases yiishop 所有 -A  -B
DATABS="--databases yiishop"
MASTER_MYSQL_CMD="$MYSQL_DIR/mysql -u$MASTER_USER -p$MASTER_PASS -h$MASTER_IP -P$MASTER_PORT"
MASTER_MYSQL_DUMP="$MYSQL_DIR/mysqldump -u$MASTER_USER -p$MASTER_PASS -h$MASTER_IP -P$MASTER_PORT $DATABS -F --single-transaction --events "

#SLAVE VARIABLES
SLAVE_MYSQL_CMD="$MYSQL_DIR/mysql -u$SLAVE_USER -p$SLAVE_PASS -h$SLAVE_IP -P$SLAVE_PORT"
TO_MAIL="490011961@qq.com"
SENDMAIL="mail -v -s "MySQL-Slave-build-status" $TO_MAIL"

[ ! -d $MASTER_DATA_PATH ] && mkdir -p $MASTER_DATA_PATH
[ `$MASTER_MYSQL_CMD -e "select user,host from mysql.user" 2> /dev/null |grep rep|wc -l` -ne 1 ] &&
$MASTER_MYSQL_CMD -e "grant replication slave on *.* to "slave"@"172.18.47.%" identified by "Qwe#663745721";" 2> /dev/null
[ $? -eq 0  ] && action "主库创建复制用户" /bin/true  
 
$MASTER_MYSQL_CMD -e "flush tables with read lock;"  2> /dev/null
[ $? -eq 0  ] && action "开始锁表" /bin/true ||  action "开始锁表" /bin/false || exit 1 
echo "-----show master status result-----" >$MASTER_STATUS_FILE
$MASTER_MYSQL_CMD -e "show master status;" 2> /dev/null >>$MASTER_STATUS_FILE
[ $? -eq 0  ] && action "查看MASTER状态" /bin/true ||  action "查看MASTER状态" /bin/false ||  exit 1
#echo "${MASTER_MYSQL_DUMP} | gzip > $MASTER_DATA_FILE"
${MASTER_MYSQL_DUMP} 2> /dev/null | gzip > $MASTER_DATA_FILE 
[ $? -eq 0  ] && action "备份主库" /bin/true || action "备份主库" /bin/false || exit 1
$MASTER_MYSQL_CMD -e "unlock tables;" >/dev/null 2>&1
[ $? -eq 0  ] && action "表锁释放" /bin/true
#cat $MASTER_STATUS_FILE

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

 
#recover

[ -d ${MASTER_DATA_PATH} ]  && cd ${MASTER_DATA_PATH} && rm -f mysql_backup_`date +%F`.sql
gzip -d mysql_backup_`date +%F`.sql.gz
[ $? -eq 0  ] && action "解压备份文件" /bin/true || action "解压备份文件" /bin/false || exit 1
$SLAVE_MYSQL_CMD  2> /dev/null < mysql_backup_`date +%F`.sql
[ $? -eq 0  ] && action "恢复数据至从库" /bin/true || action "恢复数据至从库" /bin/false || exit 1
MASTER_LOG_FILE=`tail -1 $MASTER_STATUS_FILE|cut -f1`
MASTER_LOG_POS=`tail -1 $MASTER_STATUS_FILE|cut -f2`

 
#config slave
$SLAVE_MYSQL_CMD -e "stop slave;" 2> /dev/null
#$SLAVE_MYSQL_CMD -e "SET GLOBAL replicate_do_db = "mydb1,mydb2";"
if [ $WORKERS -gt 0 ] ;then 
$SLAVE_MYSQL_CMD -e "set global slave_parallel_type="logical_clock";" 2> /dev/null
$SLAVE_MYSQL_CMD -e "set global slave_parallel_workers=6;" 2> /dev/null
fi

$SLAVE_MYSQL_CMD -e "
CHANGE MASTER TO  
MASTER_HOST="$MASTER_IP",
MASTER_PORT=$MASTER_PORT,
MASTER_USER="$REP_USER",
MASTER_PASSWORD="$REP_PWD",
MASTER_LOG_FILE="$MASTER_LOG_FILE",
MASTER_LOG_POS=$MASTER_LOG_POS;" 2> /dev/null

if [ $? -eq 0  ] ;then 
     action "执行CHANGE MASTER TO命令" /bin/true 
else
      action "执行CHANGE MASTER TO命令" /bin/false 
      $SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null >> $MASTER_STATUS_FILE
      #echo "$SENDMAIL < $MASTER_STATUS_FILE"
      $SENDMAIL  < $MASTER_STATUS_FILE 2> /dev/null
      exit 1
fi


$SLAVE_MYSQL_CMD -e "start slave;" 2> /dev/null

[ $? -eq 0  ] && action "启动从库复制" /bin/true || action "启动从库复制" /bin/false || exit 1
$SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null |egrep "IO_Running|SQL_Running"  >>$MASTER_STATUS_FILE

MasterLogFile=`$SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null |egrep -i ""| awk "{print $2}"`
RelayMasterLogFile=`$SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null |egrep -i ""| awk "{print $2}"`
ReadMasterLogPos=`$SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null |egrep -i ""| awk "{print $2}"`
ExecMasterLogPos=`$SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null |egrep -i ""| awk "{print $2}"`
REP_STATUS=`$SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null |egrep "Slave_IO_Running|Slave_SQL_Running" |grep -c "Yes"`

if [ $MasterLogFile == $RelayMasterLogFile  ] && [ $ReadMasterLogPos == $ExecMasterLogPos  ] && [ $REP_STATUS -eq 2 ];then
   action "主从复制状态检测正常" /bin/true 
else
   action "主从复制状态检测正常" /bin/false
   $SLAVE_MYSQL_CMD -e "show slave statusG" 2> /dev/null >> $MASTER_STATUS_FILE
   $SENDMAIL  < $MASTER_STATUS_FILE 2> /dev/null
   exit 1
fi

$SENDMAIL  < $MASTER_STATUS_FILE 2> /dev/null


/usr/bin/mysqldump yiicms -umaster -pQwe#663745721 --tables wx_customer_mongo wx_external wx_message_mongo wx_register_overtime wx_relevance_mongo wx_review_mongo wx_services_mongo wx_services_register wx_storage_mongo | mysql -h 120.24.211.99 yiicms -u master -pQwe#663745721

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯