MySQL dump

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《MySQL dump》带大家来了解一下MySQL dump，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

写了一个mysqldump的shell

对应FreeBSD和Linux

因为本人在hosting工作，总有一些人喜欢搞幺蛾子， 一个DB70GB（对，你没看错），
或者一个DB里上万的table，每天的定时定点dump就是个大问题了，太花时间，太浪费资源。

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

1. 硬盘使用率大于75就中断，之后发email通知
2. DB大于5GB的跳过备份
3. table数大于5000的跳过备份

全部备份完后压缩，考虑到不能浪费系统资源

#!/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   $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

到这里，我们也就讲完了《MySQL dump》的内容了。个人认为，基础知识的学习和巩固，是为了更好的将其运用到项目中，欢迎关注golang学习网公众号，带你了解更多关于mysql的知识点！