"BOKU"のITな日常

BOKUが勉強したり、考えたことを頭の整理を兼ねてまとめてます。

MariaDB(MySQL)のバックアップ・リストア方法を整理する

f:id:arakan_no_boku:20211207220619p:plain

目次

MariaDBのバックアップ・リストア

自分が知る限り・・ですが、利用されている頻度が高いのは以下の2つです。

  • mysqldumpを利用する方法
  • HeidiSQLを利用する方法

とりあえず、この2つの方法について、よく使いそうなパターンだけ整理します。

指定した一つのデータベースのバックアップをとるケースとします。

バックアップは定義とデータを分けてとります。

ほとんどの場合、定義の変更はバージョンアップ時など数か月に一回しか発生しませんが、データの変更は毎日・毎時発生するので、バックアップの頻度としてはデータのみの方が圧倒的に多くなるからです。

 

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としてエクスポート」を選択します。

f:id:arakan_no_boku:20220104010157p:plain

ダイアログで取得したい内容を指定します。

 

HeidiSQLでストアドルーチンを含む定義をバックアップ

ストアドルーチンを含む定義をバックアップする場合は、こうします。

f:id:arakan_no_boku:20220104010544p:plain

テーブル:のドロップと作成にチェックをつけて、データ:は「データなし」です。

これでストアドルーチンを含むすべてのテーブル定義がSQLファイルに出力します。

mysqldumpの場合は、通常のテーブルの定義のあとに、ストアドルーチン・・のように分かれて出力されましたが、HeidiSQLを使った場合は名前順にごちゃまぜに出力される点に注意してください。

それがまずい場合は、通常のテーブルとストアドルーチンなどで異なる接頭語をつけるなど名前つけルールで回避することを推奨します。

 

HeidiSQLでデータのみをバックアップ

データのみをバックアップする場合は2パターンあります。

まず、mysqldumpと同様にDeleteなしのinsert文のみ出力する場合です。

f:id:arakan_no_boku:20220104011327p:plain

テーブル:のチェックをはずして、データ:で「挿入」を選択します。

次に、Delete文も出力する場合です。

f:id:arakan_no_boku:20220104011505p:plain

データ:で「削除して挿入(既存データを削除)」を選択します。

デモ環境などで、データのみもとに戻したいような場合は、こちらのほうが便利です。

なお、バイナリデータは16進数で出力されます。

ただ、出力されるSQLはちょっとだけ違っていて

  • mysqldumpの場合:0x89504E470D0A
  • HeidiSQLの場合:_binary 0x89504e470d0a

のようになりますが、取り込みはどちらも問題なくできます。

 

HeidiSQLでバックアップした定義・データのリストア

リストアは「mysqldump」の時と同じようにCMDで「mysql」コマンドを使ってやってもいけます。

もちろん、HeidiSQLでリストアもできます。

リストアはHeidiSQL上で「SQLファイルを実行」を選び、バックアップしたファイルを指定するだけです。

f:id:arakan_no_boku:20220104013239p:plain

ここでも、テーブル定義のリストアを先にして、データでinseertエラーがでないように注意することは必要です。

 

Mariabackupは今のところ使っていませせん

MariaDBのバックアップなので、当然「MariaBackup」という選択肢があります。

ただ、今のところ使ったことがありませんので、今回は触れてません。

上記の方法だと、どちらもSQL文を出力してバックアップするので、大きなデータだと非常に時間がかかる場合があるので、そういう場合はMariaBackupを検討すべきなのでしょうが、今のところ、SQLで出力される見通しのよさと安心感を犠牲にしてまでバックアップ・リストアの速度を望まないといけないようなケースに遭遇してません。

なので。

MariaBackupに関しては紹介記事のリンクをはるだけにとどめます。

mariadb.com

今回はこんなもんですかね。

ではでは。