【MySQL】mysqldumpバックアップ方法の検討

こんにちはRcatです。
今回は最近いくつかのツールで使い始めているMySQLのバックアップに焦点を当てていこうと思います!

MySQL採用作品例:

はじめに

利用規約

情報や作品の活用時は事前に利用規約をご確認ください。

コメントについて

利用規約のガイドラインを確認の上コメントしてください。
則っていないコメントは削除します。

概要

mysqldumpとは

デフォルトで付属している、データベースのバックアップコマンドです。
これをうまく活用することでデータベース内のデータを構造を保持した状態でバックアップすることができます。

mydqldumpを使う

最初に本家の使い方の方を貼っておきます。
こちらをベースに使い方を考えていきます

有効化する

インストールだけだとパスが通っておらずコマンドとして使えないので、環境変数のpathを追加します。以下で言う一番上ですね。
こうすることで使えるようになります。

インストールはこちら

基本構文

基本構文は以下の通りです。
バックアップのデータ(以下ダンプ)は標準出力に出てくるので、リダイレクトが必須です。

引数にログイン情報とバックアップ対象のデータベースを最後に指定します。

ちなみに実行結果はこんな感じ。
え、10GBあるじゃんww→これは後で圧縮掛けます。

ちなみに中身はこんな感じです。
大きすぎてテキストエディタで開けないので、headコマンドで開いていますww
単純に元のデータベースを再構築するためのSQLとINSERTがずらっと入っているだけです。

Rcatの実行法

さて、基本のやり方は分かったところで、いくつかのオプションを指定しつつ使いやすくしていきます

コマンド

本家サイトや少し色々調べた結果、以下のオプションを使うことにしました。
トリガーはまだないですが、今後の為にこの辺は指定しておくことにします。

• single-transaction
  データベースの整合性を確保する

• routines
  ストアドプロシージャ等を含める

• triggers
  トリガーを含める

• --opt
  デフォルトでONで以下のセット

  • --add-drop-table
    テーブルを消すコマンドを先に挿入

  • --add-locks
    再構築時のパフォーマンス向上

  • --create-options
    テーブル作成時のオプションを保持

  • --disable-keys
    再構築時にインデックスを後で作成

  • --extended-insert
    効率的なINSERT文を作成

  • --lock-tables
    ダンプ中はテーブルをロックする

  • --quick
    バッファ最適化(巨大なテーブル対応)

  • --set-charset

実行方法

ただコマンドとして実行すると、Windows/Linuxでそれぞれ書かないといけないのでいつも通りpythonを使います。
また、python内でzip圧縮を行えるためサイズが極端に小さくできます。

というわけでプログラム本体がこちら
元々、mysqldumpは標準出力にダンプするという特徴があるので、それをpythonのsubprocessで拾いつつ、その場でzipファイルライブラリを使って圧縮します。
このやり方の場合は、一度無圧縮のファイルを取得してから圧縮→元のを消すではないので余計なディスクスペースを使用しません。

ファイルサイズは何と1/10に!すごく小さくなりました。
まぁテキストファイルなので圧縮率が高くなるのは目に見えてましたがここまで小さくできるとは。

Rcat作バックアップスクリプトの実行

というわけで上で紹介したスクリプトは配布しますので、使い方を説明します。

ライブラリは要りません。pythonさえ入っていればOK

構文

>python mysqldump_py.py -u ユーザー名 -p パスワード -s <ホスト> -d データベース名

ログイン情報とデータベース名は必須で、ホストは"localhost"の時だけ省略可能です。
その他オプションの詳細はソースの中に書いてあります。
別のフォルダに出力させたり、圧縮レベルを変えることができます。

実際に使うとこんな感じ。
ちなみに圧縮レベルを上げると処理が増えるのでダンプにかかる時間が増えます。容量と時間で決めるようにしましょう。

簡単実行スクリプトの使用

さて、ここまでの話でもなんか色々コマンド書いて難しいと思う方もいるかもしれません。
結局実行する場合は、専用のファイルを実行するだけにするのが一番簡単なので、それを作ってしまおうと思います。
なので、この中の設定だけ書き換えれば使えます。

今回使うのはdump.sh/batです。
shはLinuxでbatはWindows用です。どちらも動作確認済みとなります。

Windows版の中身はこんな感じです。
上から順に設定を記述して保存しておきます。
自分の環境の設定が出来たらダブルクリックで実行なりタスクスケジューラに入れるなりしてバックアップを取りましょう。

Linux版はこんな感じ
systemdでrootを使うとpython環境がうまく呼べなかったりするのでこっちは仮想環境のコードを残してますので、systemdにする前に一回起動しておいてください。

さっそく本スクリプトをこちらのツールにも組み込みました!

まとめ

今回はMysqldumpを使ったバックアップについてみていきました。
この方法であれば物理的にディスクが壊れても復帰できるので安心ですね!
それではまたお会いしましょう。

配布情報

配布URL

以下のURLより配布しています。
利用規約に同意の上ご利用ください。

https://script.google.com/macros/s/AKfycbxdcr8pnazR7RbjaSICTtaNWfN7h_rjQrKlZ3h9CZpPRFzRILk1OGc8mZqKbF-NXNO9/exec?name=mysqldumppy

リンク集

Pythonのインストール方法

【Linux】Systemd追加支援スクリプト

【Windows】タスクスケジューラーの追加方法

ミニPCにLinuxをインストール

MySQLのインストール&初期設定(Windows)