在slave上做sql级的备份

脚本很简单，放在这留个记号吧。





#!/bin/bash

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

# FILE:slave_db_bak.sh

# DATE:2010-02-07

# SUMMARY:backup local DB with mysqldump

# CREATOR:

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


CMDPATH=/usr/local/mysql/bin
L_DATE=$(date +%F)
HOST=$(cat /proc/sys/kernel/hostname)
DATA_PATH=/data/backup
LOG=$DATA_PATH/${HOST}-mysql-backup.log
PASSWD='#!'
COM="$CMDPATH/mysqldump -uroot -p${PASSWD} --single-transaction --hex-blob --all-database"

#Check DATA_PATH ,if not exist then make it.

[[ -d $DATA_PATH ]] || mkdir -p $DATA_PATH

#Check slave status.

STATUS1=$($CMDPATH/mysql -uroot -p${PASSWD} -e "show slave status\G" | grep -E -c 'Slave_IO_Running: Yes')
STATUS2=$($CMDPATH/mysql -uroot -p${PASSWD} -e "show slave status\G" | grep -E -c 'Slave_SQL_Running: Yes')

if [[ $STATUS1 -eq 1 ]] && [[ $STATUS2 -eq 1 ]]
then

   echo "Slave is ok,wait seconds......"
   ($COM > $DATA_PATH/${HOST}-all-${L_DATE}-$(date +%H%M%S).sql && echo "$L_DATE $(date +%T) Backup is ok!" >> $LOG || echo "$L_DATE $(date +%T) Backup error!" >> $LOG)
   echo "Mysql Backup complete."

else
   [[ $STATUS1 -eq 0 ]] && [[ $STATUS2 -eq 1 ]] && echo "$L_DATE $(date +%T) Slave IO Running error!" >> $LOG
   [[ $STATUS1 -eq 1 ]] && [[ $STATUS2 -eq 0 ]] && echo "$L_DATE $(date +%T) Slave SQL Running error!" >> $LOG
   [[ $STATUS1 -eq 0 ]] && [[ $STATUS2 -eq 0 ]] && echo "$L_DATE $(date +%T) Slave SQL & IO Running error!" >> $LOG

fi