第11回　MySQLの高度な管理とチューニングテクニック

鶴長 鎮一
MySQLユーザ会
2001/7/24

いざというときのためのバックアップ

　予期せぬトラブルから復旧できなかった場合……もしそれが委託運用していたものだったら……想像しただけで背筋が凍る思いです。こんなとき、最後に頼れるのはバックアップのみです。

　MySQLで使用されるデータファイルはバイナリ互換が保たれているため、ファイルをバックアップしておけばCPUやOSが異なるコンピュータでも稼働させることが可能です。ただし、ファイルの大きさは各OSの制限を受けるのであらかじめ確認しておきましょう。Linux（kernel 2.2.x）では2Gbytesが最大ファイルサイズになります。

　データファイルは、格納ディレクトリにあるファイルをtarでまとめるなどして別サーバに移しておくか、テープメディアに記録しておきます（バックアップが必要なファイルについてはコラム「MySQLのデータファイル構造」参照）。

■ダンプを使ったフルバックアップ

　フルバックアップの方法としてはもう1つ、ダンプの利用があります。

# mysqldump データベース名 > 出力ファイル

　こうすることで、データベースの中身をすべてSQL文で吐き出します。バックアップ目的以外に、MySQL以外のSQLデータベースへの移植にも有効です。元に戻す際は、

# mysqladmin create データベース名 # mysql データベース名 < 元ファイル

とします。バックアップを元に戻す際は、空のデータベースが前提であることをお忘れなく。

■差分バックアップの設定

　2つの方法ともフルバックアップを想定したものです。しかし、フルバックアップを毎日または毎時行うのは運用上望ましくありません。データベースサーバに限らず、データのバックアップにはデータの差分、すなわち更新された部分だけをバックアップする方法があります。この方法はMySQLにも備わっています。

　MySQLで差分バックアップを行うには、起動時のオプションに--log-updateを指定します。--logオプションを追加したように、スタートアップスクリプトファイルを編集してstartセクション中の該当の個所にオプションを追加し、設定を有効にするためにMySQLを再起動します。すると、データディレクトリに「サーバ名.001」という名前でログファイルが作成されていると思います。試しに更新SQL文を発行してみましょう。

# mysql test mysql> insert into list values (100,"name100","memo100"); Query OK, 1 row affected (0.05 sec)

　次にサーバ名.001を見てみます。

# /..XXX../mysqld, Version: 3.23.XX-gamma-log at 010719 10:43:47 use test; insert into list values (100,"name100","memo100");

と記録されているはずです。ダンプファイルなど、一番最近のフルバックアップを復元した後、更新ログを流し込みます。

#mysql < 更新ログファイル

　更新ログをローテーションしたい場合は、

# mysqladmin flush-logs

を実行します。データディレクトリに「サーバ名.002」というファイルが用意されていると思います。あとは、最も古い（小さい）番号が付いたファイルを所定の場所に保管します。復旧に時間はかかるものの、データ喪失を最小限に抑えることができます。

■レプリケーションバックアップ環境の構築

　バックアップをさらに突き詰めると、もう1つサーバを用意しておき、常にデータの同期を取らせておく方法が考えられます。この手段は「レプリケーション」と呼ばれ、単なるバックアップにとどまらず、双方向にして負荷分散機能を組み入れたものもあります。残念ながらMySQLにそこまでの機能はありませんが、1方向のみのレプリケーションでも十分バックアップの機能を果たします。

　まず稼働しているサーバとは別にバックアップ用のMySQLサーバを用意します。バックアップ用のサーバは、マスターサーバから更新されたデータを読み取り、自サーバのデータを更新します。まず、マスターサーバの方から準備します。バックアップサーバからの問い合わせを許可するようにします。

# mysql mysql mysql> grant file on *.* to ユーザー名@"%"; Query OK, 0 rows affected (0.00 sec)

注：この設定はユーザー名のみのアクセス制限になり、ホストやパスワードによる規制は行っていません

　次に、サーバの準備をしている間にデータの更新が行われないようにサーバを停止します。

# mysqladmin shutdown

　データファイルをtarコマンドでまとめます。

# tar cpvfz xxx.tgz /..xx../データファイルが存在するディレクトリ

　上記のバックアップファイルをFTPなどを使ってバックアップサーバ側に転送しておきます。さらに、マスターサーバの起動を行う前に、起動時のオプションを追加します。

--server-id=ユニークな任意の正数 --log-bin

　オプションを追加してサーバを起動すれば、マスター側の準備は完了です。次にバックアップ側の準備を行います。先ほどマスター側で作られたバックアップファイルを展開します。

# cd データベースファイルディレクトリ（/var/lib/mysqlや/usr/local/varなど） # tar xpvfz ***.tgz

　展開されたファイルのオーナーがmysqldデーモンを実行しいるユーザーと同一になっているか確認してください。もし、同一でなければchownコマンドを使ってファイルのオーナーを変更しておきましょう。

　次にMySQLの起動オプションを追加します。

--server-id=ユニークな任意の正数（ほかのMySQLサーバのIDと重ならないように注意） --master-host=マスターホストのアドレス --master-user=マスター側に設定されているユーザー名 --replicate-do-db=バックアップを行うデータベース

　以上を追加したらバックアップサーバを起動します。試しにマスター側にINSERTやUPDATEを行ってみましょう。間髪入れず、バックアップ側にも反映されているはずです。もしうまく動かない場合は、ロギングを利用してどこでエラーが発生しているかを確認しましょう。エラーのほとんどは参照権によるものなので、もう一度GRANT文から見直しましょう。

セキュリティの向上

　MySQLサーバを外部に開かれたネットワークに設置する機会も多いと思います。ささいなデータしか扱っていないとしても、サーバ管理者としてセキュリティをおろそかにすることはできません。残念ながらMySQLはセキュリティホールフリーというわけではありません。実際、セキュリティ対策用のバージョンアップが行われたケースも少なからずありました。しかしながら、いまだに解決されていない問題はありません。見つかったセキュリティホールには迅速な対応が行われています。また、運用でこれらの問題を回避することも可能です。

　まずデータベースの参照権を設定する際は、ユーザー名やパスワードだけでなく、問い合わせ元のIPアドレスも限定するようにします。ここでホスト名ではなくIPアドレスにするのは、ホスト名を偽って接続してきた場合の予防処置です。IPアドレスでも偽ることは可能ですが、ホスト名よりは危険度は低くくなります。ユーザー名、パスワード、問い合わせ元IPアドレスの3つをサーバ接続条件にすることで、セキュリティはより堅牢になります。具体的には、GRANT文を次のように使用します。

$ mysql -u root mysql> grant delete,insert,references,select,update on データベース名.* to ユーザー名@問い合わせホスト identified by 'パスワード';

最後に

　これで11回続いた連載も最後になりました。MySQLの可能性を知ってもらうという気持ちで続けてきましたが、少しは参考になりましたでしょうか。

　本連載を読んでいただいてMySQLを知った方、MySQLは知っていたがどこまでできるか初めて知った方。ともに「とにかく作ってみたい！」と意欲を燃やしていただくことができていれば本望です。皆さんの多くはこの先システム開発に携わっていく、またはすでに携わっている方だと思います。モニタに向かい、時折クライアントとの折衝を行う日々の中で、「何かをしたい！」という気持ちを素直に持つことができる幸せをかみしめつつ業務が行えれば、これほど充実した生活はないと思います。

　間もなくMySQL 4.0.0の片鱗も見えてくるころです。新MySQLの大きな可能性にも期待しましょう。

2/2

Index
快速MySQLでデータベースアプリ！
	ロギング機能と動作ステータスの取得 　サーバ稼働状況の調査
	データファイルのメンテナンスとチューニング 　データファイルの修復と最適化 　サーバプロセスのチューニング
	いざというときのためのバックアップ 　ダンプを使ったフルバックアップ 　差分バックアップの設定 　レプリケーションバックアップ環境の構築
	セキュリティの向上
	最後に

連載 快速MySQLでデータベースアプリ！

---

Linux Squareフォーラム データベース関連記事

	連載：快速MySQLでデータベースアプリ！（全11回） 軽快な動作で知られるRDBMS、MySQLでDBアプリの構築を行う。MySQLのインストールに始まり、PerlやRubyなどのスクリプトでデータベースを操作する方法までを完全解説
	連載：今から始める MySQL入門（連載中） 定番のLAMP（Linux＋Apache＋MySQL＋PHP）構成でWebアプリケーション開発に挑戦！ サンプルアプリの構築を進めながら、基礎知識や操作方法について詳しく解説する
	連載：Oracleマイスター養成講座（全6回） 本連載では、Oracleの管理・チューニング方法を紹介していく。これからOracleを始める人、そしてOracleをより深く理解したい人のための、一歩踏み込んだ実用講座
	連載：DB2マイスター養成講座（全7回） 本連載では、DB2 UDBの実践的な運用・管理方法を紹介していく。DB2を利用するうえで必要な知識を、実運用を前提にDB2のプロが解説
	特集：エンタープライズ市場に向かうMySQL 5.0［前編］ MySQL 5.0の新機能をアルファ版でチェック 1月に公開された5.0アルファ版は大幅に拡張されており、エンタープライズ市場への進出を予感させる
	特集：Linuxで動くリレーショナルデータベース・カタログ データベースサーバのOSとしてLinuxを採用するケースが増えている。Linuxで動作する7つの主なリレーショナルデータベースを紹介する。製品導入の際の参考にしてほしい
	more

MONOist組み込み開発フォーラムの中から、Linux関連記事を紹介します

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）