MySQL バックアップ 条件指定バックアップや差分バックアップの方法は?
  • facebookページ
  • twitterページ
  • 2019.12.27

    MySQL バックアップ 条件指定バックアップや差分バックアップの方法は?

    MySQLのバックアップについて、コピペで使えるコマンドサンプルを紹介しながらまとめています。

    以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。

    サンプルデータベースのインストール方法は、以下の記事を参考にしてください。

    【関連記事】
    MySQLの入門には、GUIツールで慣れ、サンプルDBを使った学習が効果的

    バックアップ実行コマンドmysqldumpの使い方

    MySQLのデータバックアップには、mysqldumpコマンドを使用します。使い方は以下のとおりです。

    MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

    以下、mysqlのrootアカウントのパスワードがmy-secret-pwという前提で解説しています。

    まずは、show databasesでデータベース一覧を確認してみましょう。

    employeesというデータベースが存在することが確認できました。

    以下のコマンドで、employeesデータベースをbackup.sqlというファイル名でバックアップします。

    上記の警告は、コマンドラインインタフェースでパスワードを使用するとセキュリティ的に問題であることを示しています。以下のように実行し、「Enter password」でパスワード(ここではmy-secret-pw)を入力することで警告が解消できます。

    なお、mysqldumpに必要な権限は、バックアップ対象のテーブルに対するselect権限、ビューを含む場合はshow view権限、トリガーを含む場合は、trigger権限、–single transactionオプションを使わない場合はlock tables権限など。オプション指定によっては、さらに別の権限が必要なケースもあります。

    出力されたバックアップファイル(ここではbackup.sql)は、以下のような内容になります。

    データベース構造を構築するためのDDLと、データ自体をinsertするSQLで構成されています。

    以下は、departmentsテーブルを構築するためのDDLです。

    以下は、dept_empテーブルにデータをinsertするためのSQLです。

    テーブルを指定して、MySQLをバックアップするには、DB名の後にテーブル名を指定

    データベースまるごとではなく、対象のテーブルのみバックアップしたい場合は、データベース名の後ろにテーブル名を指定します。複数テーブルの指定が可能です。

    上記コマンドは、employeesデータベースのうち、departments、titles、dept_empテーブルをバックアップします。

    mysqldumpのリストアは、mysqlコマンドにダンプファイルをリダイレクト

    バックアップしたファイルを使ってリストア(リロード)するには、以下のコマンドを実行します。

    異なるバージョン間でリストアを行う場合は、データベースのデフォルト値が異なっている場合があるため注意が必要です。

    【関連記事】
    MySQLバージョン サポート終了期間に注意して、GA版から選ぶ

    mysqldumpのオプション

    データベース構造のみ(スキーマ)のみバックアップするには、-tオプション

    DDLのみバックアップされ、データ部(insert文など)は省略されます。

    データのみバックアップするには、–no-dataオプション

    -dオプションでも同様です。ネットでは、「-dはドロップしそうで怖いから使わない」という意見もありました。

    データベースへのアクセスを止めずにバックアップするには、–single-transactionオプション

    InnoDBテーブルの場合に限って、–single-transactionオプションを指定すると、アプリケーションを止めることなく整合性が取れた状態でダンプを行います。

    条件に合致したデータのみバックアップするには–whereオプション

    –whereの条件に合ったデータのみをバックアップします。

    上記のコマンドは、employeesデータベースのemployees(社員)テーブルのうち、emp_no(社員番号)が50000以下のデータのみ抽出します。

    テーブルを複数指定した場合は、すべてのテーブルに同一のwhere条件を適用します。

    差分バックアップを実行するには、有償のmysqlbackupを使用

    大容量データベースの場合、毎回フルバックアップを取得しているとディスク領域を圧迫するため、差分バックアップが望ましいケースが有ります。

    差分バックアップを取得するには、mysqlbackupを使用します。

    参考) mysqlbackup コマンドリファレンス

    mysqlbackupは、MySQLのEnterprise サブスクリプションの一部として含まれています。

    MyISAMテーブルのバックアップ用のmysqlhotcopyは、5.7で削除

    バックアップ対象テーブルが、旧来のMyISAMテーブルの場合は、テーブルの元ファイルを直接コピーするmysqlhotcopyを利用することで高速にバックアップを行うことができました。

    MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.6.10 mysqlhotcopy — データベースバックアッププログラム

    しかし、MySQL5.7では削除されているため、mysqldumpを使うようにしましょう。

    まとめ

    ポテパンダの一言メモ
    • MySQLバックアップには、mysqldumpコマンドを使う
    • 特定テーブルのみのバックアップは、DB名の後ろにテーブル名を指定する
    • 差分バックアップは、有償サブスクリプションのmysqlbackupを使用


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE