mysql 특정 이름으로 시작하는 DB 모두 백업/복구

backup.sh

#!/bin/bash

echo -n "MySQL account: "
read account

echo -n "MySQL $account password: "
read -s password

suffix="$(date +%Y%m%d%H%M%S)"
zip="$(which zip)"
# 압출될 파일명(devbackup_19780622102354.zip)
zip_file="devbackup_${suffix}.zip"

# sample_로 시작하는 db 모두 백업
db_list=`echo "show databases like 'sample_%';" | mysql -N -u$account -p$password`

for db in $db_list;do
    file="${db}_${suffix}.sql"
    echo "$db backup..."
    mysqldump -u$account -p$password $db > $file
done

echo "create zip file..."

$zip $zip_file ./*.sql

echo "delete sql files..."

rm -f *.sql

restore.sh

#!/bin/bash

if [ $# -eq 0 ];then
    echo "zip 파일명을 입력하여 주십시오.\n"
    echo "예) $ ./mysql_restore.sh test.zip"
    exit 1
fi

echo -n "MySQL account: "
read password

echo -n "MySQL $account password: "
read -s password

unzip="$(which unzip)"
temp_dir="mysql_temp"

echo "extract sql files..."

if [ ! -d $temp_dir ];then
    mkdir $temp_dir
fi

$unzip $1 -d $temp_dir

cd $temp_dir

file_list=`echo *.sql`

for file in $file_list;do
    db_prefix=`echo $file | cut -d'_' -f1`
    db_name=`echo $file | cut -d'_' -f2`
    db_name=${db_prefix}_${db_name}

    `echo "create database if not exists $db_name;" | mysql -N -u$account -p$password`
    mysql -u$account -p$password $db_name < $file
done

cd ..