전 회사 다닐때 만들었던 mysql backup 스크립트 > 자유게시판

• 목록

#!/bin/bash

backup_dir='/backup/'
expire=7
user_id='test'
user_pw='test1121!!K'
mysql_bin_dir='/usr/bin'
host='localhost'
port='3306'
today="`date '+%Y%m%d'`"

##log
log_path="/var/log/db_backup"
check_date=`date "+%Y-%m-%d"`

# delete old datas
old_backup_dir="`ls -t $backup_dir 2>/dev/null`"
i=0

for dir in $old_backup_dir ; do
    i=$((i+1))
    if [ $i -gt "$expire" ] ; then
        echo "rm -rf $backup_dir/$dir"
        rm -rf $backup_dir/$dir
    fi
done


mkdir -p $backup_dir/$today/
db_list=`echo "show databases;" | $mysql_bin_dir/mysql -h $host -P $port -N -u $user_id -p"$user_pw"`

for db in $db_list ; do
    mkdir -p $backup_dir/$today/$db/

    table_list=`echo "show tables;" | $mysql_bin_dir/mysql -h $host -P $port -N -u $user_id -p"$user_pw" $db`

    for table in $table_list ; do

        echo "Backup \"$db\" Database \"$table\" Table"

        $mysql_bin_dir/mysqldump -h $host -P $port -c --allow-keywords --skip-lock-tables --create-options --routines --triggers --events --ignore-table=mysql.event --quick --single-transaction -u $user_id -p"$user_pw" $db $table > $backup_dir/$today/$db/${table}.sql 2>> $log_path/${check_date}_mysql.log

        echo "$mysql_bin_dir/mysql -h $host -P $port -u $user_id -p\"$user_pw\" $db < $table.sql" >> $backup_dir/$today/$db/restore.sh

    done

    test -f $backup_dir/$today/$db/restore.sh && chmod 700 $backup_dir/$today/$db/restore.sh

done

추천0