###################################################################################
# MySQL/MariaDB backup script
# Justin Silver
# http://www.justinsilver.com
#
# Use cron to schedule this script to run as frequently as you want.
###################################################################################

# User with SELECT, SHOW VIEW, EVENT, and TRIGGER, or... root
USERNAME="DB사용자아이디"
PASSWORD="DB비밀번호"

# Archive path
ARCHIVE_PATH="/var/적당한 폴더/daily"  #<- weekly, monthly로 변경가능

# Archive filename
ARCHIVE_FILE="DB_`date +%F_%H-%M-%S`.tbz2" ##여러개의 서버를 사용하는 사람은  prefix를 다르게 하면 관리가 용이하다.

# Archives older than this will be deleted
ARCHIVE_DAYS="3" ##3일간만 보관 3일 이전의 것들은 자동으로 삭제된다. 주간 백업 22일, 월간백업은 93일정도면 3개씩 남는다.

# Change working directory
cd $ARCHIVE_PATH

# Get all of the databases
for database in `mysql -u $USERNAME -p"$PASSWORD" -Bse 'show databases'`; do

        # Skip ones we don't want to back up
        if [ "performance_schema" == "$database" ]; then continue; fi
        if [ "information_schema" == "$database" ]; then continue; fi

        # Use Nice to dump the database
        nice mysqldump -u $USERNAME -p"$PASSWORD" --events $database > $database.sql

done

# Use Nice to create a tar compressed with bzip2
nice tar -cjf $ARCHIVE_FILE *.sql

# Remove the SQL files
nice rm -rf *.sql

# Remove old archive files
nice find . -mtime +$ARCHIVE_DAYS -exec rm {} \;