mysql backup script

root@api:/usr/local/apache-tomcat-7.0.64# cat ~/mysql-backup.sh

#!/bin/bash

backupdir='/home/freecatz/MYSQL_BACKUP/'`date +%Y%m%d`
olddir='/home/freecatz/MYSQL_BACKUP/'`date -d "7 day ago" +%Y%m%d`

function FN_DB_BACKUP(){
echo $(date +%H:%M) - $1 DATABASE FULL BACKUP START.  >>  $backupdir/$1.log
mysqldump -uroot -p'qwer1234' $1 > $backupdir/$1.sql
echo $(date +%H:%M) - $1 DATABASE FULL BACKUP FINISH.  >>  $backupdir/$1.log
mysql -uroot -p'qwer1234' -e "SELECT TABLE_SCHEMA, CONCAT(FORMAT(SUM(DATA_LENGTH + INDEX_LENGTH) / (1024*1024), 2), 'MB') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$1'" | grep -v TABLE_SCHEMA >> $backupdir/$1.log
}


function FN_MK_DIR(){
mkdir -p $backupdir

if [ -d $olddir ]; then
rm -rf $olddir
fi
}


if [ ! -d $backupdir ]; then
FN_MK_DIR
fi


FN_DB_BACKUP DBTEST1
FN_DB_BACKUP DBTEST2
FN_DB_BACKUP DBTEST3

crontab 에 주기적으로 실행 시켜 두면 된다.

# /etc/crontab: system-wide crontab
# Unlike any other crontab you don't have to run the `crontab'
# command to install the new version when you edit this file
# and files in /etc/cron.d. These files also have username fields,
# that none of the other crontabs do.

SHELL=/bin/sh
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# m h dom mon dow user  command
17 *    * * *   root    cd / && run-parts --report /etc/cron.hourly
25 6    * * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.daily )
47 6    * * 7   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.weekly )
52 6    1 * *   root    test -x /usr/sbin/anacron || ( cd / && run-parts --report /etc/cron.monthly )

00 23   * * *   root    /root/mysql-backup.sh
#