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

삽질 + 구글링으로 완성.

	#!/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

view raw backup.sh hosted with ❤ by GitHub

	#!/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 ..

view raw restore.sh hosted with ❤ by GitHub