【MySQL】テーブル名をワイルドカードでバックアップ

2022.05.01
MySQL
備忘録

今回はシェルスクリプトでMySQLのテーブルをバックアップする方法について解説していきます。シェルスクリプトからテーブルを参照する方法や、テーブル名をワイルドカードで指定してバックアップ(ダンプ)する方法について紹介します。このテクニックで作成したシェルスクリプトをcronに登録することで、自動バックアップ環境を構築することも可能です。

1. 前置き(環境編)
2. シェルスクリプト(完成版)
3. 解説編
4. 復元
5. おわりに

前置き(環境編)

次の様なテーブル群があるものとします。テーブル名にプレフィックスをつけることで、1つのデータベース内に複数アカウントを持たせるような構造にすることを目的としています。

mytable.user
mytable.task
mytable.template

dev.user
dev.task
dev.template

release.user
release.task
release.template
　　：
（以下省略）

もしまとめてこれらのテーブルをダンプをする場合は次のコマンドで問題ありません。

mysqldump -u myuser -p -h myhost --database mydatabase > DUMP

しかし、今回紹介したいダンプ方法は、アカウント毎(mytable, dev, release, …)にダンプファイル名を分けることで、利便性を良くすることを目的としますので、次のような運用を想定しています。

1つのデータベースに複数アカウントを持たせるようにする
アカウント毎にダンプファイルを分ける
アカウントリストはテーブルに定義する
ダンプファイル名に時間(00～23)をつけることで、1時間単位のダンプを取得する

これらをダンプした結果は次のようなファイル名にします(24時間で上書きされる)。

00_DUMP_mytable
00_DUMP_dev
00_DUMP_release
01_DUMP_mytable
01_DUMP_dev
01_DUMP_release
02_DUMP_mytable
02_DUMP_dev
02_DUMP_release
　　：
23_DUMP_mytable
23_DUMP_dev
23_DUMP_release

シェルスクリプト(完成版)

まずは完成したシェルスクリプトを紹介します。

#!/bin/sh
#
# system_accountテーブルのアカウント名を取得し
# テーブルをリスト毎にバックアップ格納ディレクトリにダンプする
# 
# ダンプファイル名例：10_DUMP_mytable (先頭2文字は0～23の時間)
#

# バックアップ格納ディレクトリ
t_dir='/home/user01/backup/hour/'

# ダンプファイル名に付ける時間
t_time=`date "+%H"`

# mysqlパラメータ
sqlparam='-u myuser -h myhost --password=mypassword mydatabase'

# アカウント名取得
sql='select name from `system_account`'

result=$(mysql ${sqlparam} -B -N -e "${sql}")
if [[ $? -eq 0 ]]; then
	# 対象DBダンプ(system_accountテーブルから読み込んで)
	echo "${result}" | while read data
	do
		column=(`echo "$data"`)

		# ダンプファイル名
		fname='${t_dir}${t_time}_DUMP_${column[0]}'

		cmd="mysql ${sqlparam} -N -e 'show tables like \"${column[0]}.%\"' | xargs mysqldump ${sqlparam} > ${fname}"

		eval ${cmd}
	done
else
	echo 'db error.'
	exit 1
fi

exit 0

解説編

それでは前述したシェルスクリプトの重要な部分について解説をしていきます。まずは、アカウント名をテーブルから取得しループします。

# アカウント名取得
sql='select name from `system_account`'

result=$(mysql ${sqlparam} -B -N -e "${sql}")
# 対象DBダンプ(system_accountテーブルから読み込んで)
echo "${result}" | while read data
do
	column=(`echo "$data"`)
done

resultにSQLコマンドを格納し実行します。SQLの実行結果は「while read ～ do ～ done」でループ処理し、1レコード分がdataに格納されます。今回はsystem_accountテーブルからnameカラムだけを取得しているので、column[0]にテーブルのカラムデータが格納されます。

次に実際にダンプする処理を見ていきましょう。

cmd="mysql ${sqlparam} -N -e 'show tables like \"${column[0]}.%\"' | xargs mysqldump ${sqlparam} > ${fname}"
eval ${cmd}

コマンドの前半部分ではテーブル名をワイルドカードでリストアップしています。そして、それを後半のxargsでつないでダンプする仕組みとなっています。使用しているmysqlコマンドのオプションは、「-N：結果にカラム名を出力しない、-e：コマンドの実行」です。

復元

ダンプしたバックアップを復元するコマンドについても紹介しておきます。

mysql -h myhost -u myuser -p mydatabase < DUMP

おわりに

いかがでしたでしょうか。ダンプにワイルドカードが使えることで、アカウント毎にデータベースを分けなくても1つのデータベースにまとめる運用が出来ますね。

前の記事

【jQuery】クリックイベントと効かない時の対処方法 2022.04.17
次の記事

記事がありません