mysql dump协议_MySQL dump

写了一个mysqldump的shell

对应FreeBSD和Linux

因为本人在hosting工作，总有一些人喜欢搞幺蛾子， 一个DB70GB(对，你没看错)，

或者一个DB里上万的table，每天的定时定点dump就是个大问题了，太花时间，太浪费资源。

所以搞了个shell，希望有能帮助到的同学

硬盘使用率大于75就中断，之后发email通知

DB大于5GB的跳过备份

table数大于5000的跳过备份

全部备份完后压缩，考虑到不能浪费系统资源 nice -n 19 gzip 把nice值设置为19.

#!/bin/sh

# -- PATH --

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

TODAY=$(date +%Y%m%d)

DAY_AGO=6

case "$(uname)" in

Linux)

EXPIRE_DATE="$(date --date="$DAY_AGO days ago" '+%Y%m%d')"

;;

FreeBSD)

EXPIRE_DATE=$(date -v-${DAY_AGO}d +%Y%m%d)

;;

esac

USAGE_df_LIMIT=75

USAGE_du_LIMIT=5120

TABLE_LIMIT=5000

HOSTNAME=$(hostname)

# -- BACKUP --

MYSQL_DIR=/home/mysql

BACKUP_DIR=/home/backup

TODAY_DIR=$TODAY

ARCHIVE_FILE=$(uname -n)-dump_${TODAY}.tar

# -- mysqldump options --

VERSION=$(cat /etc/mysql-version.txt)

case "$VERSION" in

mysql40)

dump_options="--hex-blob \

--quote-names \

--force \

--max_allowed_packet=128M"

;;

mysql5[157]|*)

dump_options="--single-transaction \

--hex-blob \

--quote-names \

--events \

--force \

--max_allowed_packet=128M \

--default-character-set=binary"

;;

esac

# -- Notice Disk Usage 75% over --

SendMail () {

piconv -f utf8 <

From: root@$HOSTNAME

To: abc@gmail.com

Subject: [MySQL](mysqldump)$HOSTNAME Disk Usage $USAGE_df_LIMIT over

----- $HOSTNAME -----

# df -h /home

$(df -h /home)

# ls -l /home/backup

$(ls -l /home/backup)

# tail /var/log/mysqldump.log

$(tail /var/log/mysqldump.log)

EOL

}

# -- Database more then 5GB capacity / 5000 Table is exclude-db-list --

{

IFS_BACK=$IFS

IFS="

"

[ -n $BACKUP_DIR/exclude-db-list ] && cat /dev/null > $BACKUP_DIR/exclude-db-list

for dir_name in `find ${MYSQL_DIR} -maxdepth 1 \! -name mysql -type d`; do

dir_usage=$(du -ms $dir_name | cut -f1)

dir_db=$(ls -d $dir_name | sed -e "s#${MYSQL_DIR}/##" -e "s/@002d/-/g")

table=$(find $dir_name -name '*.MYD' -or -name '*.ibd' | wc -l)

if [ $dir_usage -ge $USAGE_du_LIMIT -o $table -ge $TABLE_LIMIT ]; then

grep -q $dir_db $BACKUP_DIR/exclude-db-list || echo $dir_db >> $BACKUP_DIR/exclude-db-list

echo "`date '+%Y-%m-%d %H:%M:%S'` exclude $dir_db -> exclude-db-list"

fi

done

IFS=$IFS_BACK

# -- Remove old backup directory --

cd $BACKUP_DIR

if ls -d [0-9]* >/dev/null 2>&1; then

for dir in `ls -d [0-9]*`; do

if echo $dir | egrep -q '[0-9]{8}$'; then

if [ $dir -le $EXPIRE_DATE ]; then

rm -Rf $dir

echo "`date '+%Y-%m-%d %H:%M:%S'` delete $dir backup directory"

fi

fi

done

fi

# -- Create new backup db files --

install -d ${BACKUP_DIR}/${TODAY_DIR}

for db in `mysql -N -e 'SHOW DATABASES' | \

egrep -v '(information_schema|performance_schema)' | \

grep -vf $BACKUP_DIR/exclude-db-list | \

grep -vf $BACKUP_DIR/exclude-custom-list`;

do

# -- Disk Usage rate check 75% less --

usage_rate=`df -h /home | awk '/home/ {print $5}' | tr -d '%'`

if [ $usage_rate -ge $USAGE_df_LIMIT ]; then

echo "`date '+%Y-%m-%d %H:%M:%S'` Disk Usage rate ${usage_rate}% mysqldump is ${USAGE_df_LIMIT}% or more Stop!"

SendMail

exit 1

else

# mysqldump start !

echo "`date '+%Y-%m-%d %H:%M:%S'` $db mysqldump start"

echo $db >> $BACKUP_DIR/$TODAY_DIR/database.list

nice -n 19 mysqldump --opt --databases $db \

$dump_options \

| nice -n 19 gzip > $BACKUP_DIR/$TODAY_DIR/${db}.sql.gz

if [ $? -eq 0 ]; then

echo "`date '+%Y-%m-%d %H:%M:%S'` $db mysqldump finish"

else

nice -n 19 mysqlcheck -s --auto-repair $db

echo "`date '+%Y-%m-%d %H:%M:%S'` $db mysqldump fail"

fi

fi

done

# -- mysqldump created by tar archive of Today --

cd $BACKUP_DIR && nice -n 19 tar -cf $ARCHIVE_FILE $TODAY_DIR

# -- Remove backup data --

if [ $? -eq 0 ]; then

# -- Remove mysqldump data of Today --

rm -Rf $BACKUP_DIR/$TODAY_DIR

if ls -f $(uname -n)-dump_[0-9]*.tar >/dev/null 2>&1; then

for file in `ls -f $(uname -n)-dump_[0-9]*.tar`; do

find $BACKUP_DIR -maxdepth 1 -ctime +$DAY_AGO -type f -name $file -delete

done

fi

echo "`date '+%Y-%m-%d %H:%M:%S'` $ARCHIVE_FILE all-mysqldmp Finish!"

else

echo "`date '+%Y-%m-%d %H:%M:%S'` $ARCHIVE_FILE all-mysqldmp Fail!"

exit 1

fi

} 2>&1 | tee -a /var/log/mysqldump.log