目次
- MariaDBのバックアップ・リストア
- mysqldumpでストアドルーチンを含む定義をバックアップ
- mysqldumpでデータのみをバックアップ
- mysqldumpでdumpした定義やデータをリストア
- HeidiSQLでストアドルーチンを含む定義をバックアップ
- HeidiSQLでデータのみをバックアップ
- HeidiSQLでバックアップした定義・データのリストア
- Mariabackupは今のところ使っていませせん
MariaDBのバックアップ・リストア
自分が知る限り・・ですが、利用されている頻度が高いのは以下の２つです。
- mysqldumpを利用する方法
- HeidiSQLを利用する方法
とりあえず、この２つの方法について、よく使いそうなパターンだけ整理します。
指定した一つのデータベースのバックアップをとるケースとします。
バックアップは定義とデータを分けてとります。
ほとんどの場合、定義の変更はバージョンアップ時など数か月に一回しか発生しませんが、データの変更は毎日・毎時発生するので、バックアップの頻度としてはデータのみの方が圧倒的に多くなるからです。
mysqldumpでストアドルーチンを含む定義をバックアップ
ストアドルーチンを含む定義をバックアップするコマンドです。
実行ユーザ（uname）、対象データベース（dbname）やアウトプットファイル（dump_output.sql）は環境にあわせて適宜書き換えてください。
あと「-h hostname」は接続するサーバのホスト名にしてください。
指定しないとlocalhostになるので注意してください。
mysqldump --no-data --routines --events -u uname -p -h hostname dbname > dump_output.sql
オプション「--no-data」がデータを出力しない指定です。
あと「--routines --event」は、ストアドルーチンやイベントの定義も出力するようにとの指定で、これがないとそれらは出力されません。
mysqldumpでデータのみをバックアップ
データのみをバックアップするコマンドです。
mysqldump --no-create-info --hex-blob -u uname -p -h hostname dbname > dump_output_data.sql
オプションの「--no-create-info」が定義を出力しない指定です。
もうひとつ「--hex-blob」をつけているのは、バイナリデータ（blobとか）を16進の形式でダンプするようにとの指定で、これをしとかないと、リストアするときに「Invalid utf8 character string」みたいな警告がでるときがあります。
mysqldumpでdumpした定義やデータをリストア
定義とデータを分けてバックアップしても、リストアするときは、必ず定義→データの順に行います。
mysqldumpでデータを出力するとInsert文のみでDelete文ははいりませんから、定義のリストアで一旦空にしておかないとエラーになって取り込めません。
上記の例の場合だと、こんな順番でリストアします。
mysql -u uname -p dbname < dump_output.sql
mysql -u uname -p dbname < dump_output_data.sql
HeidiSQLを利用する方法
上記の「mysqldump」は、CMDを起動して、カレントディレクトリに移動して、コマンドを実行・・というのに慣れていない人には、若干敷居が高いです。
また、CMDは文字コードがSJISなので、DBはUTF-8なのに出力したファイルがSJISでできてしまうという気持ち悪さもあります。
そんな部分を回避できる方法が「HeidiSQL」を利用したバックアップです。
DBを右クリックして表示するメニューで「データベースをSQLとしてエクスポート」を選択します。
ダイアログで取得したい内容を指定します。
HeidiSQLでストアドルーチンを含む定義をバックアップ
ストアドルーチンを含む定義をバックアップする場合は、こうします。
テーブル：のドロップと作成にチェックをつけて、データ：は「データなし」です。
これでストアドルーチンを含むすべてのテーブル定義がＳＱＬファイルに出力します。
mysqldumpの場合は、通常のテーブルの定義のあとに、ストアドルーチン・・のように分かれて出力されましたが、HeidiSQLを使った場合は名前順にごちゃまぜに出力される点に注意してください。
それがまずい場合は、通常のテーブルとストアドルーチンなどで異なる接頭語をつけるなど名前つけルールで回避することを推奨します。
HeidiSQLでデータのみをバックアップ
データのみをバックアップする場合は２パターンあります。
まず、mysqldumpと同様にDeleteなしのinsert文のみ出力する場合です。
テーブル：のチェックをはずして、データ：で「挿入」を選択します。
次に、Delete文も出力する場合です。
データ：で「削除して挿入（既存データを削除）」を選択します。
デモ環境などで、データのみもとに戻したいような場合は、こちらのほうが便利です。
なお、バイナリデータは１６進数で出力されます。
ただ、出力されるSQLはちょっとだけ違っていて
- mysqldumpの場合：0x89504E470D0A
- HeidiSQLの場合：_binary 0x89504e470d0a
のようになりますが、取り込みはどちらも問題なくできます。
HeidiSQLでバックアップした定義・データのリストア
リストアは「mysqldump」の時と同じようにCMDで「mysql」コマンドを使ってやってもいけます。
もちろん、HeidiSQLでリストアもできます。
リストアはHeidiSQL上で「SQLファイルを実行」を選び、バックアップしたファイルを指定するだけです。
ここでも、テーブル定義のリストアを先にして、データでinseertエラーがでないように注意することは必要です。
Mariabackupは今のところ使っていませせん
MariaDBのバックアップなので、当然「MariaBackup」という選択肢があります。
ただ、今のところ使ったことがありませんので、今回は触れてません。
上記の方法だと、どちらもSQL文を出力してバックアップするので、大きなデータだと非常に時間がかかる場合があるので、そういう場合はMariaBackupを検討すべきなのでしょうが、今のところ、SQLで出力される見通しのよさと安心感を犠牲にしてまでバックアップ・リストアの速度を望まないといけないようなケースに遭遇してません。
なので。
MariaBackupに関しては紹介記事のリンクをはるだけにとどめます。
今回はこんなもんですかね。
ではでは。
