[MariaDB/MySQL] 백업 및 복구

[백업]
1. 전체 데이터베이스 백업
    mysqldump -u[아이디] -p[패스워드] --all-databases > [백업파일명].sql
    ex) mysqldump -uadmin -padmin1234 --all-databases > backup_all_db.sql

2. 특정 데이터베이스 백업 
   mysqldump -u[아이디] -p[패스워드] [데이터베이스명] > [백업파일명].sql
   ex) mysqldump -uadmin -padmin1234 test > backup_test_db.sql

3. 원격 데이터베이스 백업

   ex) mysqldump -uadmin -padmin1234 -h 192.168.0.111 -P 22222 test > backup_test_db.sql   

   --> 포트가 3306이면 -P 옵션 생략 가능

[복구]
1. 전체 데이터베이스 복구
    mysql -u[아이디] -p[패스워드] < [백업파일명].sql
    ex) mysqldump -uadmin -padmin1234 < backup_all_db.sql


2. 하나의 데이터베이스 복구
   mysql -u[아이디] -p[패스워드] [데이터베이스명] < [백업파일명].sql
   ex) mysqldump -uadmin -padmin1234 test < backup_test_db.sql

 

[백업 shell파일]

# vi db_backup.sh

#!/bin/sh

DATE=$(date +%Y%m%d)

/usr/bin/mysqldump -uadmin -padmin1234 test > /home/test_user/db_backup/db_backup_${DATE}.sql

#!/bin/sh

DATE=$(date +%Y%m%d)

/usr/bin/mysqldump -uadmin -ppassword -h 192.168.0.100 mydb > /home/ubuntu/backup/db/dump_test__backup_${DATE}.sql

/usr/bin/mysqldump -uadmin -ppassword -h 192.168.0.100 --all-databases > /home/ubuntu/backup/db/dump_test_all_backup_${DATE}.sql


tar cvf /home/ubuntu/backup/db/dump_mydb_backup_${DATE}.tar.gz /home/ubuntu/backup/db/dump_test_mydb_backup_${DATE}.sql

tar cvf /home/ubuntu/backup/db/dump_all_backup_${DATE}.tar.gz /home/ubuntu/backup/db/dump_test_all_backup_${DATE}.sql

rm /home/ubuntu/backup/db/dump_test_mydb_backup_${DATE}.sql

rm /home/ubuntu/backup/db/dump_test_all_backup_${DATE}.sql

find /home/ubuntu/backup/db  -mtime  +15  -name \*backup\* -exec rm -rf {} \;

echo "${DATE} backup finish!"

 

[crontab 등록]

# DB Backup
0 1 * * * /home/test_user/shell/db_backup.sh >> /home/test_user/cron_db_backup_$(date "+\%Y-\%m-\%d").log 2>&1

or 

0 3 * * * /home/sbsystems/shell/db_backup.sh  >> /home/sbsystems/shell/cron.log 2>&1