Linux Shell实战之四 Mysql主从同步

rwrr322

环境介绍：
本脚本测试环境是基于CentOS 6.5 x86_x64的源码安装的Mysql，Mysql版本为mysql-5.1.63，并且均为全新安装，Mysql服务均启动。
Master  192.168.1.3   主机名：master
Slave   192.168.1.4   主机名：slave
Selinux及Iptables均关闭清空。主从两台机器已经做过ssh双机互信(ssh双机互信可参见 《SSH双机互信及错误解决大全》 Mysql的编译安装可参加《搭建Discuz论坛的两种方式 》，脚本文件在附件当中！)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228

#!/bin/bash #Target: Auto Make And Check Mysql Master-Slave #Date: 2015-05-17 #Author: Jacken #QQ 654001593 #QQ Group 170544180 #Version: 1.0 # #Note：Have To Exec 1 On Master,Then Exec 2 On SLAVE_DIR,On Correspond Machine To Select "Show Status" #Only Allow Define Variable shopt -s -o nounset # ##################################################################### ##################################################################### ##########Define Varabile #Define Master And Slave Database Ipaddress. MASTER_IP=192.168.1.3 SLAVE_IP=192.168.1.4 # #Define Master Super User Name And Password. MASTER_USER=root MASTER_PASSWD=master123 # #Define Slave Super User Name And Password. SLAVE_USER=root SLAVE_PASSWD=slave123 # #Define Master-to-Slave Grant  User & Password & Host GRANT_USER=grantuser GRANT_PASSWD=grantpasswd GRANT_HOST=% # #Sync Database RSDB=*.* # #Define Mysql install Directory MASTER_DIR=/usr/local/mysql SLAVE_DIR=/usr/local/mysql # #Define Device Name. DEV=eth0 # #Get Ipaddress For Exec Scripts Machine GETIP=$(ifconfig $DEV| sed -n '/inet addr:/p'|awk '{print $2}'| awk -F: '{print $2}') ##################################################################### ##################################################################### # #Only For Super User To Exec if [ $UID -ne 0 ];then     echo -e '\e[31mSorry,This Script Must Be Super User To Exec!\e[0m'     exit 2 fi # # ##################################################################### ##################################################################### # #Define Master database Operations. MYSQL_MIN="$MASTER_DIR/bin/mysql -u$MASTER_USER -p$MASTER_PASSWD -e" # function MASTER_DO { #Backup And Judge My.cnf if [ -f /etc/my.cnf ];then     cp /etc/my.cnf /etc/my.cnf$$ else     cp $MASTER_DIR/share/mysql/my-medium.cnf  /etc/my.cnf fi #Grant Slave Database Access Master Database. $MASTER_DIR/bin/mysql -u$MASTER_USER -p$MASTER_PASSWD -e "grant all on $RSDB to '$SLAVE_USER'@'$SLAVE_IP' identified by '$SLAVE_PASSWD';" #Grant Master-Slave Sync User. $MYSQL_MIN "grant replication slave on $RSDB to \"$GRANT_USER\"@\"$GRANT_HOST\" identified by \"$GRANT_PASSWD\";" $MYSQL_MIN "flush privileges;" } # # ##################################################################### ##################################################################### # #Define Slave database Operations. # function SLAVE_DO(){ MYSQL_SIN="$SLAVE_DIR/bin/mysql -u$SLAVE_USER -p$SLAVE_PASSWD -e" #Get Master Database status. MASTER_INFO=`$SLAVE_DIR/bin/mysql -h$MASTER_IP -u$SLAVE_USER -p$SLAVE_PASSWD -e "show master status;"|sed -n '/mysql/p'` # #Get Log_File LOG_FILE=`echo $MASTER_INFO|awk  '{print $1}'` #Get Log_Pos LOG_POS=`echo $MASTER_INFO|awk '{print $2}'` # #Get Slave Database Status SYNC_INFO=`$MYSQL_SIN "show slave status \G;"|grep Runnning > /dev/null 2>&1` # #Judge Yes Or No SLAVE_IO_STATUS=`echo $SYNC_INFO|sed -n '/IO/p'|awk -F: '{print $2}'` SLAVE_SQL_STATUS=`echo $SYNC_INFO|sed -n '/SQL/p'|awk -F: '{print $2}'` #Grant Master User To Access Slave Database. `$MYSQL_SIN "grant all privileges on $RSDB to \"$MASTER_USER\"@\"$MASTER_IP\" identified by \"$MASTER_PASSWD\";"` # #Backup And Judge /etc/My.cnf if [ -f /etc/my.cnf ];then     cp /etc/my.cnf /etc/my.cnf$$ else     cp $MASTER_DIR/share/mysql/my-medium.cnf  /etc/my.cnf fi # #Modify Slave Server-id sed -i '/^server-id/s/1/2/' /etc/my.cnf # #Add Master Information On Slave $MYSQL_SIN "change master to master_host='$MASTER_IP',master_user='$GRANT_USER',master_password='$GRANT_PASSWD',master_log_file='$LOG_FILE',master_log_pos=$LOG_POS;" $MYSQL_SIN "slave start;" } # # ##################################################################### ##################################################################### # #Judge Master-Slave Status On Slave To Exec! function SYNC_STATUS_S { MYSQL_SIN="$SLAVE_DIR/bin/mysql -u$SLAVE_USER -p$SLAVE_PASSWD -e" #Get Sync Information SYNC_INFO=`$MYSQL_SIN 'show slave status \G'|grep Running` # #Judge Yes Or No SLAVE_IO_STATUS=`echo $SYNC_INFO|awk  '{print $2}'` SLAVE_SQL_STATUS=`echo $SYNC_INFO|awk '{print $4}'` # #Reply Master-Slave Status Information. if [[ "$SLAVE_IO_STATUS" == 'Yes' && "$SLAVE_SQL_STATUS" == 'Yes' ]];then     echo -e '\e[32mThe Database Sync Is Successfuly!\e[0m' else     echo -e '\e[31mThe Database Sync Is Failure!\e[0m'     echo -e '\e[31mPlease Check!Exit....\e[0m'     exit 2 fi } #Judge Master-Slave Status On Master To Exec! function SYNC_STATUS_M { MYSQL_MIN="$MASTER_DIR/bin/mysql -h$SLAVE_IP -u$MASTER_USER -p$MASTER_PASSWD -e" # #Get Sync Information SYNC_INFO=`$MYSQL_MIN 'show slave status \G'|grep Running` # #Judge Yes Or No SLAVE_IO_STATUS=`echo $SYNC_INFO|awk  '{print $2}'` SLAVE_SQL_STATUS=`echo $SYNC_INFO|awk '{print $4}'` #Reply Master-Slave Status Information. if [[ "$SLAVE_IO_STATUS" == 'Yes' && "$SLAVE_SQL_STATUS" == 'Yes' ]];then     echo -e '\e[32mThe Database Sync Is Successfuly!\e[0m' else     echo -e '\e[31mThe Database Sync Is Failure!\e[0m'     echo -e '\e[31mPlease Check!Exit....\e[0m'     exit 2 fi } # ##################################################################### ##################################################################### # #Judge Master Database Machine! function JUDGE_MASTER_ID() { if [ ! "$GETIP"  ==  "$MASTER_IP" ];then     echo -e '\e[31mYour Choice Error,This Option Only Select On Master Machine!\e[0m'     exit 2 fi } #         ##################################################################### ##################################################################### # #Judge Master Database Machine! function JUDGE_SLAVE_ID() { if [ ! "$GETIP"  ==  "$SLAVE_IP" ];then     echo -e '\e[31mYour Choice Error,This Option Only Select On Slave Machine!\e[0m'     exit 2 fi } #         ##################################################################### ##################################################################### ##################################################################### # PS3="Please Input Number:" select i in "Configure Master Database(On Master)" "Configure Slave Database(On Slave)" "Show Master-Slave Status(On Master)" "Show Master-Slave(On Slave)" "Exit" do CHOOSE=$REPLY case $CHOOSE in ##################################################################### 1) JUDGE_MASTER_ID MASTER_DO     if [ $? -eq 0 ];then         echo -e '\e[32mConfigure Master Database Successfully!\e[0m'     else         echo -e '\e[31mConfigure Master Database Failure!\e[0m'     fi ;; ##################################################################### 2) JUDGE_SLAVE_ID SLAVE_DO     if [ $? -eq 0 ];then         echo -e '\e[32mConfigure Slave Database Successfully!\e[0m'     else         echo -e '\e[31mConfigure Slave Database Failure!\e[0m'     fi ;; ##################################################################### 3) JUDGE_MASTER_ID SYNC_STATUS_M ;; ##################################################################### 4) JUDGE_SLAVE_ID SYNC_STATUS_S ;; ##################################################################### 5) echo -e '\e[32mByeBye.\e[0m' exit ;; ##################################################################### *) echo -e '\e[31mInput Error,Please Input Again!\e[0m' esac done

脚本效果图：
要先在Master上执行 1，然后再在Slave上执行2，其它的顺序就无所谓了。(其实脚本可以在一台机器上运行，就是在脚本中通过ssh调用另一端的Mysql，还有如果主从失败如何自动修复？以后有时间再写Version 2吧！～)





Mysql主从同步脚本v1.rar (1.84 KB, 下载次数: 0)

2015-5-18 13:02 上传

点击文件名下载附件

国安信仰

不错，学习了！