タグ: IT/Web 総合

トップページ > 保守運用 > サーバ > MySQL のデータベースをダンプしてバックアップする方法

MySQL のデータベースをダンプしてバックアップする方法

[初回公開] 2022年02月05日

データベースは企業で使われるシステムの他、最近ではブログなど個人サイトでも利用されることが増えており、バックアップの方法はプログラムや画像のようにファイルでコピーする方法ではないので、MySQL を例にバックアップ（ダンプ）を定期的に行う方法を紹介する。

1. データベースをバックアップ（ダンプ）したほうがよい理由

MySQL や postgreSQL などデータベースをバックアップしたほうがよい理由としては、サーバやデータベース自体が破損してデータの読み書きができなくなることが稀にあるため、大切なデータを元に戻してシステムが正常稼働するように復旧できる体制を整えることにある。

また、稼働しているシステムの OS が古くなったなどで新しい環境に移し替える際にも、バックアップしたデータベース内のデータを用いることで新システムでも利用することが可能になる。

データベースのバックアップにはテーブルの構造とその中に格納されているデータをテキストファイルに出力することを「ダンプ」と呼び、反対にダンプしたファイルをデータべースに入れることを「リストア」と呼ぶ。

2. MySQL のダンプの方法

データベースの MySQL をダウンプする場合は下記のコマンドで行うことができる。
RedHat など Linux 系のサーバの場合、MySQL を SQL で操作する場合は一度 mysql コマンドで MySQL にログインするが、ダンプする場合は mysqldunmp コマンドで直接ファイルに書き出すことが可能である。

# mysqldump -u {データベースに接続するユーザ名} -p {データベース名} > /{ダンプ先のパス}/{ダンプファイル名}.{指定する拡張子}
Enter password: 

データベースにログインする際にパスワード認証をかけている場合は、「Enter password」が表示されるので設定しているパスワードを入力してエンタキーを押下するとダンプが実行される。

3. MySQL を定期的にダンプする方法

MySQL を定期的にダンプする方法は、ダンプするコマンド（mysqldump）を Linux 系のサーバであれば定期的に動作させる cron に設定を入れることで実施できる。
今回はダンプの他に、ダンプしてから一週間経過したファイルは自動的に削除するコマンドも実行することから、それらの処理を 1 つにまとめたシェルを用意する。

3-1. ダンプと一定期間経過したファイルを削除するシェルを用意

ダンプと一定期間経過したファイルを削除するシェルを下記の手順で用意する。
まず、シェルファイルを設置するディレクトリに移動してから vi コマンドで処理を記入する。

# cd /usr/local/bin/
# vi mysql_db_backup.sh
　↓ i キーで記入モードにして下記を記述する
#!/bin/bash

DATE=`date "+%Y%m%d"`
BAKDIR=/{保存するディレクトリ}

DB_USER={データベースに接続するユーザ名}
DB_PASS={データべースに接続するパスワード}
DB_NAME={データベース名}

mysqldump -u ${DB_USER} -p${DB_PASS} ${DB_NAME} > ${BAKDIR}/${DB_NAME}_${DATE}.sql

find ${BAKDIR} -type f -name "${DB_NAME}_*.sql" -mtime +7 -exec rm -f {} \;

mysqldump の行ではデータベースへ接続する際にパスワード認証がある場合を想定して -p${DB_PASS} を加えている。
この時、-p と ${DB_PASS} の間にはスペースなど何も入れないようにする。
また、ダンプしたファイルの拡張子は .sql にしているがダンプした内容はテキストファイルなので .log や .txt など好みの拡張子を指定することができる。

find の行ではダンプファイルが作成された日時から一週間経過したファイルを検索し、該当すれば rm コマンドで削除するようにしている。
これによりダンプファイルは最大 7 つを常に保つようになるため、ダンプファイルでディスクの空き容量が無く事態を防ぐ。

次に用意したシェルファイルをコマンドで実行できるように実行権限をパーミッションの変更により付与する。

# chmod 755 mysql_db_backup.sh

最後に上記で用意したシェルが正常に動作するかは下記のコマンドで確認することができる。

# /usr/local/bin/mysql_db_backup.sh
mysqldump: [Warning] Using a password on the command line interface can be insecure.

シェルを実行した後に「[Warning] Using a password on the command line interface can be insecure.」が表示されるが、これはシェル内にデータベースに接続するためのユーザ名とパスワードを直接記入していることの警告であるが、これが表示されても指定のディレクトリにはダンプファイルが生成される。
Warning を消す場合は、ユーザ名とパスワードのみを記入したファイルを別途用意し、シェルでその別ファイルを読み込むことで解消される。

3-2. cron で定期的に実行する設定を行う

前述でデータべースをダンプして一定期間経過したファイルを削除するシェルを用意したが、これを定期的に動作させるために cron を用いて実行させる。
cron の設定は次のようにする。

# vi /var/spool/cron/root
　↓　i キーで記入モードにして下記を記述する
0 1 * * * /usr/local/bin/mysql_db_backup.sh > /dev/null 2>&1

cron の設定では vi コマンドで設定ファイルを開いているが、「# crontab -e」でも設定することが可能である。
尚、上記の設定では毎日 01:00 にシェルが動作する仕様になっている。

cron が設定されているかどうかは下記のコマンドで確認することができる。

# crontab -l
0 1 * * * /usr/local/bin/mysql_db_backup.sh > /dev/null 2>&1

関連記事