コマンドラインによる MySQL データのバックアップ

WordPress で使用している MySQL のバックアップをコマンドラインで行う手順について、自分向けのメモとして作成しました。

MySQL のバックアップの方法は大きく分けると、以下の 2 通りが有ると思います。

  • phpMyAdminのような管理画面でメニュー形式で行う方法
  • サーバの shell にログインしてコマンドラインで行う方法

今回のメモは、「サーバの shell にログインしてコマンドラインで行う方法」についてです。

MySQL データのバックアップをコマンドラインで行う理由

phpMyAdminのような管理画面の場合は、メニュー形式で画面もカラフルなので直感的に分かりやすくて良いのですが、MySQL データを定期的にバックアップする場合は、以下の理由で私は好きではありません。

  • バックアップのたびにphpMyAdminのような管理画面にログインしなければならない
  • 毎回メニューを辿ってバックアップを選択し、バックアップデータをダウンロードしなければならない
  • バックアップのたびにバックアップデータのファイル名を指定しないとならない

MySQL はもともと shell のコマンドラインでデータベースの操作ができますので、以下の流れで作業を進めると、最終的にスクリプトでバックアップを行えるのではないかと思いました。

  • まずは、コマンドラインの基本的な操作を体験する
  • コマンドラインでパスワードの自動入力について調べる
  • パスワードの自動入力の操作を体験する
  • パスワードの自動入力用のスクリプトを作ってみる

最終的にスクリプトで MySQL データのバックアップができれば、毎回phpMyAdminのような管理画面にログインしてマウスを操作する必要がありませんので、労力を大幅に減らすことができます。

なお、動作確認はさくらインターネットの「さくらのレンタルサーバ」(スタンダードプラン)で行っています。

参考情報

WordPress で使用している MySQL データのバックアップについては、ネット上に読み切れないほど情報があると思いますが、今回は以下のサイトの情報を参考にさせていただきました。

MySQL のデータをバックアップするのが初めてであり、mysqldumpという MySQL のデータをバックアップするコマンドの利用も初めてでしたので、これらの情報には本当に助けられました。

MySQL のバックアップコマンド

MySQL のデータをバックアップする時に使用するコマンドは、MySQL のパッケージ含まれているmysqldumpというコマンドを使用します。

WordPress Codex 日本語版のWordPress MySQL のバックアップおよび dev.mysql.com のmysqldump - データベースバックアッププログラムではmysqldumpのオプション及び用例が解説されていますが、WordPress で利用している MySQL データのバックアップでは、以下のオプションが多く使われるのではないかと思います。

$ mysqldump --add-drop-table -h MySQLサーバ名 -u MySQLユーザ名 -p MySQLデータベース名 > 出力ファイル
mysqldump データベースバックアッププログラム
--add-drop-table DROP TABLE ステートメントを CREATE TABLE ステートメントの前に追加
-h MySQLサーバ名 接続先の MySQL サーバ名
-u MySQLユーザ名 MySQL サーバに接続する時に使用する MySQL ユーザー名
-p MySQL サーバに接続する時に接続用パスワードを入力
MySQLデータベース名 バックアップ対象のデータベース名
出力ファイル バックアップした内容を保存したいファイル名

出力されるバックアップファイルはテキストファイルですので、エディタで中身を見ることが可能です。

mysqldump を使用したバックアップ

以下に、私が行った内容を記します。

mysqldump を使った初めてのバックアップ

私はバックアップしたデータを特定のディレクトリに保存したかったので、以下のようにしてホームディレクトリにbackupというディレクトリを作成しました。その後、作成したディレクトリに移動。

$ mkdir backup
$ cd backup

以下のようにmysqldumpコマンドを実行します。

$ mysqldump --add-drop-table -h MySQLサーバ名 -u MySQLユーザ名 -p MySQLデータベース名 > 出力ファイル
例えば

  • MySQL サーバのホスト名:db01.example.com
  • MySQL ユーザ名:root
  • データベースの名前:wp-test
  • バックアップのファイル名:wp-test.backup.sql

の場合は、以下のようになります。

$ mysqldump --add-drop-table -h db01.example.com -u root -p wp-test > wp-test.backup.sql
Enter password: (データベース接続用のパスワードを入力)

エラーなく shell のプロンプトが表示されたら、念の為にバックアップしたファイルが出来たかを確認します。

$ ls -l
-rw-r--r--  1 root  users   556722 May  9 13:39 wp-test.backup.sql

gzip および bzip2 形式で圧縮

ファイルが出来たことを確認できましたので、次にバックアップしながら gzip という形式で圧縮したファイルを作成してみます。

$ mysqldump --add-drop-table -h db01.example.com -u root -p wp-test | gzip -c > wp-test.backup.sql.gz
Enter password: (データベース接続用のパスワードを入力)

一般に gzip よりも bzip2 形式のほうが圧縮率が高いと言われていますので、bzip2 形式でも作成してみます。

$ mysqldump --add-drop-table -h db01.example.com -u root -p wp-test | bzip2 -c > wp-test.backup.sql.bz2
Enter password: (データベース接続用のパスワードを入力)

これで 3 つのファイルが出来ましたので、それぞのファイルサイズを確認してみます。

$ ls -l
-rw-r--r--  1 root  users   556722 May  9 13:39 wp-test.backup.sql
-rw-r--r--  1 root  users    48206 May  9 13:40 wp-test.backup.sql.bz2
-rw-r--r--  1 root  users    93402 May  9 13:40 wp-test.backup.sql.gz

元データのファイル内でのデータ占有率に大きく左右されますが、この結果だけを見ると bz2 形式の場合は 1/10 以上、gz 形式の場合でも約 1/6 になりました。

ただし、bzip2 形式はは圧縮率が高いのですが、gzip 形式と比較するとメモリの消費量が多く、圧縮にかかる時間も長くなると言われています。これは、元のバックアップファイルのサイズが大きいほど顕著だと思います。

WordPress の記事の投稿が多くなるほど MySQL の元となるデータ量も多くなりますので、圧縮形式をどちらにするかは、状況を見ながら判断するしか無いと思います。

コマンドラインによる MySQL バックアップ時のパスワード自動入力

mysqldump の -p オプション

mysqldump では、以下のように-pオプションにパスワードを指定するとパスワード自動入力が出来ます。

その場合、-pとパスワードの間に半角空白を含みません。半角空白を含めるとその文字がデータベース名となり、最終的にはエラーが発生します。

$ mysqldump --add-drop-table -h MySQLサーバ名 -u MySQLユーザ名 -pパスワード MySQLデータベース名 > 出力ファイル

しかし、dev.mysql.com の6.1.2.1 パスワードセキュリティーのためのエンドユーザーガイドラインには以下のように記されています。

これは便利ですがセキュアではありません。一部のシステムでは、使用しているパスワードが、コマンド行を表示するためにほかのユーザーによって起動できる ps などのシステムステータスプログラムによって表示可能になります。

特にレンタルサーバ、イントラネット等では、mysqldump のコマンドラインで指定したオプションが他のユーザの目に留まる可能性がある事になりますので、

少なくともパスワードはコマンドラインオプションで指定するべきではない

ということになります。

スクリプトを作成して自動実行(パスワードを自動入力)させたい場合があると思いますが、これについても、上記の URL で以下のように記されています。

パスワードをオプションファイルに保管します。たとえば Unix の場合、ホームディレクトリの.my.cnfファイルの[client]セクションにパスワードを一覧表示することができます。パスワードを安全に保持するには、自分以外のすべてのユーザーからファイルにアクセス可能にしてはいけません。このようにするには、ファイルのアクセスモードを 400 または 600 に設定します。

このように、UNIX 系 OS 環境でパスワードの自動入力を行う場合には、~/.my.cnfを作成し、以下のように[client]セクションにパスワードを保存することができます。

$ cd ~
$ vi .my.cnf

$ cat .my.cnf
[client]
password=データベース接続用パスワード

なお、上記の例でvi .my.cnfは、vi というエディタで.my.cnfというファイルを編集する、cat .my.cnfは、.my.cnfというファイルを表示することを表しています。以降も同じです。

Windows 環境の場合は、同じく dev.mysql.com の4.2.6 オプションファイルの使用の情報が参考になると思います。

ローカルのパソコンで作成したファイルをサーバにアップロードした場合は、以下のようにしてファイル名を変更することができます。

例えば、アップロードしたファイルがmy.cnf場合は、以下のようにします。

$ mv my.cnf .my.cnf

なお、作成した ~/.my.cnfは、自分以外のユーザーが読込可能では困りますので、以下のようにファイルのアクセス権を 600 に設定します。

$ ls -al
-rw-r--r--    1 root  users        32 May  9 15:27 .my.cnf
$ chmod 600 .my.cnf
$ ls -al
-rw-------    1 root  users        32 May  9 15:27 .my.cnf

~/.my.cnf によるパスワード自動入力を試行

~/.my.cnfの作成とファイルのアクセス権の設定が終わりましたので、パスワードの自動入力を試してみます。パスワードは自動入力されるはずですので、-pオプションは指定しません。

$ mysqldump --add-drop-table -h db01.example.com -u root wp-test > wp-test.backup-autops.sql
$ ls -l
-rw-r--r--  1 root  users   556722 May  9 16:03 wp-test.backup-autops.sql

エラーの発生がなければ、mysqldumpコマンドとオプションファイル~/.my.cnfの併用で、パスワードの自動入力が出来たことになります。

シェルスクリプトによる MySQL バックアップの省力化と自動化

MySQL のバックアップを行うたびに長いオプションを打ち込んだり貼り付けたりするのは面倒ですので、shell の機能を利用してシェルスクリプトを作ります。

バックアップの省力化用スクリプト

まずは、バックアップのたびに実行させるスクリプトを作ります。スクリプトと言ってもバッチ式ですので、内容は単純です。

もしスクリプトファイルを保存するディレクトリを作成する場合は、以下のようにしてディレクトリを作成します。

$ cd ~
$ mkdir script
$ cd script

スクリプトファイルの作成

スクリプトファイルの 1 行目は、#!/bin/shが 1 行目です。以降の説明でもスクリプトファイルの 1 行目は#!/bin/shですので、間違わないようにしてください。また、#で始まる行は、コメントです。

次に、以下のようにして最小限のスクリプトを作成します。

$ vi sql-backup-1.sh
$ cat sql-backup-1.sh
#!/bin/sh
#
# Backup for MySQL Database
#
cd ~/backup

mysqldump --add-drop-table -h db01.example.com -u root wp-test > sql-backup-sh1.sql

ファイルの作成が終わったら、念の為にリストを表示して確認します。

$ ls -l
-rw-r--r--  1 root  users  395 May  9 18:01 sql-backup-sh1.sh

次に、ファイルの所有者のみがスクリプトファイルの読み書きと実行が出来るように、以下のようにしてアクセス権の変更を行います。

$ chmod 700 sql-backup-1.sh
$ ls -l
-rwx------  1 root  users  395 May  9 18:01 sql-backup-sh1.sh

スクリプトファイルのテスト

次に、以下のようにして最初のスクリプトを実行します。

$ ./sql-backup-sh1.sh

エラーがなければ shell のプロンプトが表示されます。シェルスクリプトでは ~/backup にディレクトリを移動してからバックアップを行うように指定しましたので、以下のようにしてバックアップしたファイルが出来たかを確認します。

$ ls -l ~/backup/wp-test.backup-sh1.sql
-rw-r--r--  1 root  users  556722 May  9 18:03 sql-backup-sh1.sql

スクリプトファイルの修正

バックアップファイルが作成できたことを確認できました。次に、必須ではありませんが、スクリプトの中身を少し見直します。

見直す内容は、

  • 複数の環境でも使い回しが出来るようにする
  • 変数を使ってスクリプトの修正を楽にする
  • バックアップファイルの作成が終わったらファイルのリストを表示する

です。

変数を使用することで、スクリプトに機能を追加した時、ファイル名を変更した時などの修正ミスを防ぐことが可能になります。

$ cd ~/script
$ vi sql-backup-sh2.sh
$ cat sql-backup-sh2.sh
#!/bin/sh
#
# Backup for MySQL Database
#
cd ~/backup
#
db_opt=--add-drop-table
db_host=db01.example.com
db_user=root
db_name=wp-test
bk_name=sql-backup-sh2.sql
#

mysqldump $db_opt -h $db_host -u $db_user $db_name > $bk_name
mysqldump $db_opt -h $db_host -u $db_user $db_name > $bk_name | gzip -c > $bk_name.gz

ls -l $bk_name*

今度のスクリプトは、テキスト形式の他に gz 形式の圧縮ファイルも作成するようにしました。さらに、バックアップファイルの作成後にlsコマンドでファイルリストの表示も行います。

スクリプトファイルの作成が終わったら、先ほどと同じくアクセス権の変更と実行権限を与え、その後スクリプトを実行します。

$ chmod 700 sql-backup-2.sh
$ ls -l
-rwx------  1 root  users  395 May  9 18:01 sql-backup-sh2.sh


$ ./sql-backup-sh2.sh
-rw-r--r--  1 rpi-sy  users  556722 May  9 18:20 sql-backup-sh2.sql
-rw-r--r--  1 rpi-sy  users   93403 May  9 18:20 sql-backup-sh2.sql.gz

エラーが発生しなければ、上のようにファイルのリストを表示ます。以後は、自分なりにバックアップのタイミングを決めて、このスクリプトを実行するのみです。

今回作成したスクリプトを定期的に自動実行させる方法も有るのですが、これについては別の機会にメモしたいと思います。

あまり意味のない内容でしたが、最後までお読みいただきありがとうございました。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です