mysqldumpは単純な指定が上手くいくという話

mysqldumpは単純な指定が上手くいくという話 IT技術

今回は、ちょっとしたデータベース関連のTips紹介です。

世界的に使用されているMySQLや、最近シェアを伸ばしているMySQLの派生データベースであるMariaDBなどを利用して、様々なシステムが構築・運用されていて、この記事をご覧いただいている方の中にもMySQLを運用しているエンジニアの方がいるかもしれません。

今回紹介するのは、MySQLのデータベースを運用する上で欠かすことが出来ない、エクスポートとインポートについてです。この機能を利用することでバックアップとリストアを行うことが多く、定期的なデータの保全や、システムの移行など幅広い場面で必要になります。

しかし、MySQLのエクスポートは思った動きをしてくれないことがあったりして、意外とエンジニアの人もハマってしまうことがあるのが悩ましいところです。毎日使うコマンドではないので、忘れてしまっていたりして、解決に時間がかかってしまうということもあるでしょう。

今回は、そういった場面に遭遇したエンジニア(未来の私を含め)のために、MySQLのエクスポートとインポートに関しての奮闘記録と動作するコマンドを、備忘録も兼ねて掲載しておきます。

mysqldumpが失敗する人へ

MySQLのバックアップを行う場合、最も単純で伝統的な手法の一つがmysqldumpを利用することです。

mysqldumpは、MySQL内にあるデータベースの内容をSQL形式でファイルへ出力する(ダンプする)機能で、MySQLによって提供されている由緒正しい機能でもあり、信頼性も高い方法です。

mysqldumpで出力されたダンプファイル(SQLファイル)は、mysqlコマンドを使って取り込むことが想定されています。

しかし、この一連の流れを行っても、正しくデータがインポートできずに困るということがよくあります。まずは、想定されている全体の流れを確認しておきましょう。

エクスポート – 多すぎるオプション指定

うまくデータをリストアできなかった場合、mysqldumpのマニュアルや、同コマンドの解説をしてくれているサイトの情報を見ながら試行錯誤することになると思いますが、とにかく情報量が多すぎて困ってしまいます。一つ一つ確認をすると時間がかかるのです。

参考URL : mysqldumpのリファレンス (MySQL 8.0)

コマンドの紹介では、以下のようなパターンをよく見かけます。

$ mysqldump -u USER_NAME -p -h HOST_NAME -B DB_NAME > OUTPUT_FILE_NAME

指定されているオプションはいずれも基本的な物ですが、以下に少し紹介しておきます。

オプション意味
-uDatabaseへの接続ユーザー名
-pDatabaseへの接続パスワード
(公式リファレンスより)
パスワード値はオプションです。 指定しない場合、mysqldump によってプロンプトが表示されます。
-h接続ホスト
-B対象データベース名
mysqldumpの基本的なオプション指定

私の場合、別名でデータベースをインポートして開発に使ったりしようと考えていたこともあって、以下のようなオプションも試していました。

-nDatabase作成のSQL出力を抑制
mysqldumpの追加オプション

それを含めたコマンドは以下のようになります。

$ mysqldump -u dbuser -p -h localhost -n -B targetdb > ~/targetdb_2024xxxx.dump

しかし、私の場合これらの試行錯誤はいずれも期待したようにインポートすることができませんでした。

インポート

mysqldumpで出力したダンプファイル(SQLファイル)をインポートする方法についても確認しておきましょう。

基本的にはターミナル(コンソール)から直接データベースを操作する際に使うmysqlコマンドを利用することになります。以下は基本的なコマンドの形です。

$ mysql -u USER_NAME -p -h HOST_NAME DB_NAME < OUTPUT_FILE_NAME

指定されているオプションはほとんど同じで、最期にリダイレクトでファイルから実行するSQLを入力しています。

とても単純な形ですが、私の環境ではうまく動作させることができませんでした。発生した現象は様々ですが、エラーも出力されず正しく動作したような振りをしてデータが取り込まれていなかったり、期待したデータベースに取り込まれず、既存データベースの名前で取り込まれるなどが起きて、色々調べても原因の特定が難しく、出力されているダンプファイルの中身を確認してまで状況を確認しなければならない程でした。

mysqldump成功パターン – 単純なのが良い

私の場合、原因を突き止めていくために徐々にオプション指定を単純化しながら確認を進めた結果、最終的にはオプション指定の全てがない状態で成功するということになりました。

以下のコマンドは、targetdbの内容をtargetdb.sqlというダンプファイルに書き出しして、targetdb2という別名データベースに取り込みます。別環境などで同名で取り込みしたい場合には、2行目のコマンドで指定されているデータベース名を揃えるだけで動作しています。

$ mysqldump targetdb > targetdb.sql
$ mysql targetdb2 < targetdb.sql

このコマンドでデータベースの取り込みが期待した動きをしましたが、このコマンドには注意点がありますので、以下に記載しておきます。

  1. 管理権限のあるユーザー(root)で実行している
  2. 対象データベースをあらかじめ作成(CREATE DATABASE)している

1.は推奨できませんが、ユーザー名パスワードなどをコマンドオプションに指定する面倒を省く効果があります。私の場合開発環境なので遠慮なくroot権限で作業できましたが、そうでない場合の方はユーザー名オプションは不可避でしょう。

2.は本来必要ないはずなのですが、私の環境ではあらかじめ空っぽのデータベースを準備していないとインポートが確認できませんでした。出力されたダンプ(SQL)ファイルにはデータベースの作成SQLは含まれてはいるのですが、出力元のデータベース名でCREATE DATABASEされていて、取り込み時に指定するデータベース名との兼ね合いでうまくいっていないのかと推測していますが定かではありません。もしかするともっと他にオプションを指定すればあらかじめデータベースを準備しなくても取り込めるのかもしれませんが、大した手間ではないので上記コマンドで妥協しています。

事前発行したCREATE DATABASE文

念のため上記の2.で発行したCREATE DATABASEについても紹介しておきます。ごく普通のデータベース作成のSQL文です。

$ mysql

> CREATE DATABASE targetdb2 DEFAULT CHARACTER SET utf8;

> GRANT ALL ON targetdb2.* TO dbuser@localhost;

targetdb2という名前のデータベースを作成し、日本語データを扱う想定なため、標準文字セットにutf8を指定しています。

その後で、dbuserというシステムからアクセスするデータベースのユーザーへ、作成したデータベース(targetdb2)への全ての権限を付与しています。

定期的なバックアップ・リストアの確認をしよう

データベースを利用したシステムにとって、蓄積されたデータはシステム全体の資産であり生命線です。システムを動作させるためにはバックアップやリストアは必要ありませんが、不測の事態に陥った際に、バックアップがあるかないかは、システムを継続運用できるかどうかを決めることもあります。

また、ある程度安定して動作しているシステムやサービスの場合、定期的なバックアップを自動化しているということもあるでしょう。そういった場合でも、リストアの確認までを自動化していることは少ないかもしれません。バックアップがとれていたとしても、そのバックアップファイルが正しく取り込める状態である事は確認しておかなければなりません。

システムエンジニアのような開発総括を兼ねた役職の人は特に、システムの運用にバックアップの自動化が含まれていても、リストアの確認項目がなかった場合は、その旨を確認しておきましょう。有事の際にバックアップが取り込みできないという大惨事が発生した場合に、どこに責任があるのかを明確にしておかなければなりません。忘れてはならないのが、データベースシステム自体も更新があるので、リストアが必ず成功するとは限らないということです。

私自身もこんな記事を書いておきながら、開発途中のデータベースなどで自動バックアップを後回しにしていることはよくあるので、後悔する前に自分なりの手順などをまとめて簡略化・ルーティン化してしまいましょう。

最後に自分への備忘録で、うまく動作したパターンをまとめておきます。ダンプして、対象データベースを作った後で、リダイレクトで取り込みする、という流れでした。

  1. mysqldumpでファイルへエクスポート
  2. mysqlでデータベースに接続した対象データベースを作成
  3. mysqlコマンドにリダイレクトでダンプファイルを入力する
$ mysqldump targetdb > targetdb.sql

$ mysql
> CREATE DATABASE targetdb DEFAULT CHARACTER SET utf8;
> GRANT ALL ON targetdb.* TO dbuser@localhost;

$ mysql targetdb < targetdb.sql

コメント