作者:coralzd 网址 www.freebsdsystem.org/mysqlhotcopy_backup/ 转载注明出处
下面共享一个一直在线上运行一年多的mysql备份脚本。
mysqlhotcopy是一个Perl脚本,最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径,但它只能运行在数据库目录所在的机器上。mysqlhotcopy只用于备份MyISAM。它运行在Unix和NetWare中。
- #!/usr/bin/env bash
- DATE=`date +%y%m%d.%H`
- DATADIR="/data0/mysql/data"
- BACKDIR_DAILY="/data0/local/mysqld/daily"
- BACKDIR_HOURLY="/data0/local/mysqld/hourly"
- INTERVAL="$1"
- TMPDIR="/var/tmp/"
- TMPDIRH="$TMPDIR"hourly
- TMPDIRD="$TMPDIR"daily
- LOGDIR="/data0/log/dbbackup/"
- KEEPH_LOCAL=1
- USER=hotcopy
- PASSWD=hotcopy
- KEEPH_NFS=7
- KEEPD_LOCAL=3
- KEEPD_NFS=14
- HOST=`hostname -s`
- MYVERSION="5.1"
- mkdir -p $LOGDIR
- case $INTERVAL in
- hourly | HOURLY | Hourly | 1 )
- echo "" && echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
- echo "Performing HOURLY level backup -- `date +$m-$d.%H:%M:%S`" && echo ""
- echo "" && echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" >>"$LOGDIR"hourly.log
- echo "Performing HOURLY level backup -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"hourly.log
- mkdir -p $TMPDIRH
- mkdir -p $BACKDIR_HOURLY
- /usr/local/mysql/bin/mysqlhotcopy --allowold test -u $USER -p $PASSWD $TMPDIRH > /dev/null
- echo "" && echo "" && echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
- echo "Compressing -- `date +$m-$d.%H:%M:%S`"
- echo "Compressing -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"hourly.log
- cd $TMPDIRH
- find . -maxdepth 1 -type d -user mysql -exec tar czf {}-"$DATE".tar.gz '{}' \;
- find . -maxdepth 1 -type d -user mysql -exec rm -rf {} \;
- echo ""
- echo "Copying Local... -- `date +$m-$d.%H:%M:%S`"
- echo "Copying Local... -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"hourly.log
- chattr -i $BACKDIR_HOURLY
- find $BACKDIR_HOURLY -type f -mtime +7 -exec rm {} \;
- mv * $BACKDIR_HOURLY
- chattr +i $BACKDIR_HOURLY
- echo ""
- echo "Moving NFS... -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"hourly.log
- echo "Ending -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"hourly.log
- exit 0
- ;;
- daily | DAILY | Daily | 2 )
- echo "" && echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
- echo "Performing DAILY level backup -- `date +$m-$d.%H:%M:%S`" && echo ""
- echo "" && echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" >>"$LOGDIR"daily.log
- echo "Performing DAILY level backup -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"daily.log
- mkdir -p $TMPDIRD
- mkdir -p $BACKDIR_DAILY
- /usr/local/mysql/bin/mysqlhotcopy --allowold test -u $USER -p $PASSWD $TMPDIRD > /dev/null
- echo "" && echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
- echo "Compressing -- `date +$m-$d.%H:%M:%S`"
- echo "Compressing -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"daily.log
- cd $TMPDIRD
- find . -maxdepth 1 -type d -user mysql -exec tar czf {}-"$DATE".tar.gz '{}' \;
- find . -maxdepth 1 -type d -user mysql -exec rm -rf {} \;
- echo "Copying Local... -- `date +$m-$d.%H:%M:%S`"
- echo "Copying Local... -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"daily.log
- chattr -i $BACKDIR_DAILY
- find $BACKDIR_DAILY -type f -mtime +$KEEPD_LOCAL -exec rm -rf '{}' \;
- mv * $BACKDIR_DAILY
- chattr +i $BACKDIR_DAILY
- echo "Ending -- `date +$m-$d.%H:%M:%S`" >>"$LOGDIR"daily.log
- exit 0
- ;;
- * )
- echo ""
- echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
- echo "Invalid Selection" 1>&2
- exit 1
- esac
将脚本置于crond中,在指定时间来运行。例如
- 0 23,00,01,02,03,04,06,07,08,17,18 * * * /usr/local/sbin/mysqlbackup.sh 2
转载于:https://blog.51cto.com/coralzd/499262