MySQL的一些功能實用的Linux shell指令碼分享
Memcached啟動指令碼
?
1 | # vim /etc/init.d/memcached |
?
#!/bin/bash #======================================================================================= # chkconfig: - 80 12 # description: Distributed memory caching daemon # processname: memcached #======================================================================================= IPADDR=` /sbin/ifconfig eth1 | awk -F ':' '/inet addr/{print $2}' | sed 's/[a-zA-Z ]//g' ` PORT= "11211" USER= "root" SIZE= "2048" CONNNUM= "51200" PIDFILE= "/var/run/memcached.pid" BINFILE= "/usr/local/memcached/bin/memcached" LOCKFILE= "/var/lock/subsys/memcached" RETVAL=0 start() { echo -n $ "Starting memcached......" $BINFILE -d -l $IPADDR -p $PORT -u $USER -m $SIZE -c $CONNNUM -P $PIDFILE RETVAL=$? echo [ $RETVAL - eq 0 ] && touch $LOCKFILE return $RETVAL } stop() { echo -n $ "Shutting down memcached......" /sbin/killproc $BINFILE RETVAL=$? echo [ $RETVAL - eq 0 ] && rm -f $LOCKFILE return $RETVAL } restart() { stop sleep 1 start } reload() { echo -n $ "Reloading memcached......" /sbin/killproc $BINFILE -HUP RETVAL=$? echo return $RETVAL } case "$1" in start) start ;; stop) stop ;; restart) restart ;; condrestart) [ -e $LOCKFILE ] && restart RETVAL=$? ;; reload) reload ;; status) status $prog RETVAL=$? ;; *) echo "Usage: $0 {start|stop|restart|condrestart|status}" RETVAL=1 esac
|
?
exit $RETVAL # chmod +x /etc/init.d/memcached # chkconfig --add memcached # chkconfig --level 235 memcached on # service memcached start
|
binlog 自動清理指令碼
?
1 | # vim /data/scripts/delete_mysql_binlog.sh |
?
#!/bin/bash #======================================================================================= # 用於刪除 MySQL Master 端已經同步完的 binlog【需在 Master 端執行】,以減少磁碟空間 # 每天凌晨 5:30 分執行一次 # # 注:需在 Slave 端新增允許 Master 端訪問的帳號【帳號:check_binlog,密碼:binlog_2356】 # 執行於 MySQL Master 端【目前只用於一主一從的同步模式,對於多從的情況暫時未考慮】 #======================================================================================= PATH= /sbin : /bin : /usr/sbin : /usr/bin : /usr/local/sbin : /usr/local/bin ## Slave端連線資訊 SLAVE_ADDR= "XXX.XXX.XXX.XXX" SLAVE_USER= "check_binlog" SLAVE_PWD= "binlog_2356" LOGFILE= "/data/logs/db_sync_info.log" PINGFILE= "/tmp/mysqlping.log" ## MySQL狀態資訊檢視命令 SQLCMD= "show slave status" #======================================================================================= ## 檢查MySQL是否已經執行 if [[ ` ps aux | grep mysql[d] | wc -l` - eq 0 ]]; then echo The MySQL is not running at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 測試Slave端的連通性 nohup mysqladmin -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} ping > ${PINGFILE} retval=` grep "^error" ${PINGFILE}` rm -f ${PINGFILE} if [[ "${retval}X" != "X" ]]; then echo The MySQL Slave can not be connected at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 檢查是否合法的Slave MASTER_ADDR=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "${SQLCMD}G;" | awk '$1=="Master_Host:" {print $2}' ` LOCAL_ADDR=` /sbin/ifconfig eth1 | awk -F ':' '/inet addr/{print $2}' | sed 's/[a-zA-Z ]//g' ` if [[ "${MASTER_ADDR}" != "${LOCAL_ADDR}" ]]; then echo The MySQL Slave is not lawful at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 獲得Slave端資訊,以此來確定是否處於正常同步的情況 IO_STATUS=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "${SQLCMD}G;" | awk '$1=="Slave_IO_Running:" {print $2}' ` SQL_STATUS=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "${SQLCMD}G;" | awk '$1=="Slave_SQL_Running:" {print $2}' ` if [[ "${IO_STATUS}" != "Yes" || "${SQL_STATUS}" != "Yes" ]]; then echo The MySQL Replication is not synchronous at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 再做一次判斷,以保證資料同步絕對正常【建立測試資料】 mysql -uroot -e "create database if not exists mytestdb;" sleep 3 retval=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "show databases;" | grep mytestdb` mysql -uroot -e "drop database if exists mytestdb;" if [[ "${retval}X" = "X" ]]; then echo The MySQL Replication is not synchronous at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 在已經同步的情況,還需要判斷當前同步的binlog,以此來確定哪些已經是過期的binlog SLAVE_BINLOG1=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "${SQLCMD}G;" | awk '$1=="Master_Log_File:" {print $2}' ` SLAVE_BINLOG2=`mysql -h${SLAVE_ADDR} -u${SLAVE_USER} -p${SLAVE_PWD} -e "${SQLCMD}G;" | awk '$1=="Relay_Master_Log_File:" {print $2}' ` ## 獲得Master端,當前的binlog檔案以及binlog路徑 MASTER_BINLOG=`mysql -uroot -e "show master status;" | grep - v '^+' | tail -1 | awk '{print $1}' ` ## 主從端已經同步到相同的binlog if [[ "${SLAVE_BINLOG1}" = "${SLAVE_BINLOG2}" && "${SLAVE_BINLOG1}" = "${MASTER_BINLOG}" ]]; then CURR_BINLOG= "${MASTER_BINLOG}" ## 主從端已經同步,但從端的binlog還沒有追趕到主端最新的binlog elif [[ "${SLAVE_BINLOG1}" = "${SLAVE_BINLOG2}" && "${SLAVE_BINLOG1}" != "${MASTER_BINLOG}" ]]; then CURR_BINLOG= "${SLAVE_BINLOG1}" ## 主從端已經同步,主從端的binlog一致,但relaylog還不一致 elif [[ "${SLAVE_BINLOG1}" != "${SLAVE_BINLOG2}" && "${SLAVE_BINLOG1}" = "${MASTER_BINLOG}" ]]; then CURR_BINLOG= "${SLAVE_BINLOG2}" else echo Has noknown error at:` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi mysql -uroot -e "purge binary logs to '${CURR_BINLOG}';" if [[ $? - eq 0 ]]; then echo Clear MySQL binlog is ok at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} fi
|
?
12 |
# crontab -e 30 05 * * * /data/scripts/delete_mysql_binlog .sh > /dev/null 2>&1
|
修復MySQL主從同步
?
#!/bin/sh PATH= /sbin : /bin : /usr/sbin : /usr/bin : /usr/local/sbin : /usr/local/bin LOGFILE= /data/repair_mysql_sync_ ` date +%F`.log SQLCMD1= "show slave status" ## 檢視MySQL是否已啟動 if [[ ` ps aux | grep mysqld | grep - v grep ` "X" = "X" ]]; then echo The MySQL is not running at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 獲得MySQL從端Relay binlog的路徑 retval=` grep "^relay-log" /etc/my .cnf | grep - v relay-log- | grep '/' ` if [[ "${retval}" = "X" ]]; then RELAY_BINLOG_PATH=` ps aux | grep -w mysqld | grep - v grep | awk '{print $13}' | awk -F '=' '{print $2}' ` else RELAY_BINLOG_PATH=` dirname $( echo ${retval} | awk -F '=' '{print $2}' )` fi ## 查詢master.info檔案,用於定位Binlog資訊 MASTER_FILE=` ps aux | grep -w mysqld | grep - v grep | awk '{print $13}' | awk -F '=' '{print $2}' ` /master .info if [[ ! -e ${MASTER_FILE} ]]; then echo This Server is not MySQL Slave at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 1 fi ## 獲得當前的同步狀態 IO_STATUS=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Slave_IO_Running:" {print $2}' ` SQL_STATUS=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Slave_SQL_Running:" {print $2}' ` if [[ "${IO_STATUS}" = "Yes" && "${SQL_STATUS}" = "Yes" ]]; then echo Now, The MySQL Replication is synchronous at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} exit 0 fi ## 從master.info檔案中,獲得MySQL主端的同步資訊 REPLI_INFO=` sed '/^$/d' ${MASTER_FILE} | tail +2 | head -5` REPLI_BINLOG_FILE=` echo ${REPLI_INFO} | awk '{print $1}' ` REPLI_IPADDR=` echo ${REPLI_INFO} | awk '{print $3}' ` REPLI_USER=` echo ${REPLI_INFO} | awk '{print $4}' ` REPLI_PWD=` echo ${REPLI_INFO} | awk '{print $5}' ` ## 刪除無用的Relay binlog rm -rf ${RELAY_BINLOG_PATH}/*-relay-bin.* ## 直接從0位置開始同步 SQLCMD2= "change master to master_host='${REPLI_IPADDR}', master_user='${REPLI_USER}', master_password='${REPLI_PWD}'," SQLCMD2= "${SQLCMD2} master_log_file='${REPLI_BINLOG_FILE}', master_log_pos=0" mysql -uroot -e "stop slave;" mysql -uroot -e "${SQLCMD2};" mysql -uroot -e "start slave;" ## 如果同步的過程中,出現重複記錄導致同步失敗,就跳過 while true do sleep 2 IO_STATUS=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Slave_IO_Running:" {print $2}' ` SQL_STATUS=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Slave_SQL_Running:" {print $2}' ` BEHIND_STATUS=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Seconds_Behind_Master:" {print $2}' ` SLAVE_BINLOG1=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Master_Log_File:" {print $2}' ` SLAVE_BINLOG2=`mysql -uroot -e "${SQLCMD1}G;" | awk '$1=="Relay_Master_Log_File:" {print $2}' ` ## 出現錯誤,就將錯誤資訊記錄到日誌檔案,並跳過錯誤繼續同步 if [[ "${IO_STATUS}" != "Yes" || "${SQL_STATUS}" != "Yes" ]]; then ERRORINFO=`mysql -uroot -e "${SQLCMD1}G;" | awk -F ': ' '$1=="Last_Error" {print $2}' ` echo "The MySQL synchronous error information: ${ERRORINFO}" >> ${LOGFILE} mysql -uroot -e "stop slave;" mysql -uroot -e "set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;" mysql -uroot -e "start slave;" ## 已完成同步,就正常退出 elif [[ "${IO_STATUS}" = "Yes" && "${SQL_STATUS}" = "Yes" && "${SLAVE_BINLOG1}" = "${SLAVE_BINLOG2}" && ${BEHIND_STATUS} - eq 0 ]]; then echo The MySQL synchronous is ok at: ` date +%F " " %H-%M-%S` >> ${LOGFILE} break fi done
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/2894/viewspace-2805852/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 分享兩個實用的shell指令碼指令碼
- 幾例實用的Shell指令碼指令碼
- linux常用的shell指令碼Linux指令碼
- 【Linux】【Shell】主控指令碼實現Linux指令碼
- linux shell指令碼中 =~ 的作用Linux指令碼
- Linux Shell指令碼Linux指令碼
- 9個實用shell指令碼指令碼
- Linux shell:執行shell指令碼的幾種方式Linux指令碼
- 分享實用監控指令碼:使用Shell檢查程式是否存在指令碼
- MySQL實時計算QPS,TPS的SHELL指令碼兩例MySql指令碼
- 向大家分享一個shell指令碼的坑指令碼
- 什麼是Shell指令碼?Shell指令碼在Linux運維工作中的地位!指令碼Linux運維
- MySQL資料庫備份的shell指令碼MySql資料庫指令碼
- Linux指令碼分享Linux指令碼
- Linux必須掌握的shell指令碼基礎Linux指令碼
- Linux 【Shell指令碼經典案例】Linux指令碼
- Linux基礎五(shell指令碼)Linux指令碼
- Liunx備份mysql資料庫的shell指令碼MySql資料庫指令碼
- PJzhang:鳥哥的linux私房菜-shell指令碼-上Linux指令碼
- Shell指令碼應用(三)指令碼
- 使用shell指令碼在Linux中管理Java應用程式指令碼LinuxJava
- shell指令碼中的變數及應用指令碼變數
- 【Linux】通過shell指令碼對mysql的增刪改查以及my.cnf的配置Linux指令碼MySql
- 7 個日常實用的 Shell 拿來就用指令碼例項!指令碼
- Linux shell程式設計(一)shell指令碼中的變數詳解Linux程式設計指令碼變數
- Shell指令碼最佳實踐指令碼
- Linux shell 指令碼基礎介紹Linux指令碼
- Linux 使用 shell 指令碼處理字串Linux指令碼字串
- Linux Shell指令碼時間排程Linux指令碼
- Linux命令和shell指令碼學習Linux指令碼
- 自動化指令碼安裝mysql shell指令碼範例指令碼MySql
- MySQL最典型和實用的熱備指令碼實戰MySql指令碼
- Shell指令碼在Linux運維工作中的地位!指令碼Linux運維
- 設定Linux伺服器IP地址的shell指令碼Linux伺服器指令碼
- Linux中執行Shell指令碼的方式(三種方法)Linux指令碼
- 博主日常工作中使用的shell指令碼分享指令碼
- Shell指令碼之for迴圈語句的應用指令碼
- 一個簡單的Linux啟動jar包的shell指令碼LinuxJAR指令碼