MySQLのバックアップ・リストア方法ある期間までバックアップファイルを残す方法

何かあったときのためにmysqlのバックアップを取得しておきたいな…
できれば、定期的に取る仕組みを考えたいな…

上記をお考えの方向けの記事になります。
mysqlなどのデータベースが関わる開発を行うとバックアップについて意識しておく必要があります。
作業ミスとかで後で元に戻せるようにという理由もありますが、なにか災害などでデータが吹き飛んだ場合にバックアップを残しておくと迅速に復旧することが可能になります。

現状、MySQLを勉強されている方の参考になれればと思います。

本記事のポイントは以下の通りです。

・MySQLのバックアップの取り方
・取得したバックアップから復元（リストア）する方法

本記事では以下の環境で実行しています。
・Windows10
・MySQL8

では、順に説明していきます。

MySQLのバックアップの取り方

はじめにバックアップの取り方について説明します。
基本的にコマンドで実施すると楽なので、どういったコマンドを使用するか書いていきます。

以下のようなコマンドになります。

mysqldump -u root -p test_db > ./test_bk.sql

rootのところがユーザ名、test_dbがデータベース名になります。
後ろの「./test_bk.sql」がバックアップファイルになります。

上記を実行するとパスワード確認されますので、パスワードを入力してください。
無事終了したらバックアップファイルが作成されているかと思います。

分かりやすく書くと以下のようなイメージです。

mysqldump -u (ユーザ名) -p (データベース名) > (バックアップファイル)

後で説明しますが、復元するときにこのバックアップファイルを使用することになります。

上記は一番シンプルなコマンド例になります。
ただ上記のコマンドの場合、データベースがバックアップ中にロックされる可能性があります。
※innodbを指定していた場合

ロックされてしまいますとバックアップが完了するまで他のアクセスを受け付けなくなるため、以下のオプションを使用してロックしないようにしましょう。

「–single-transaction」

全体で書くと以下のような感じです。

mysqldump --single-transaction -u (ユーザ名) -p (データベース名) > (バックアップファイル)

上記のコマンドをbatファイルとして保存しておけば、後はダブルクリックでbatファイルを実行するだけで上記コマンドが動作することになります。

後接続先を指定したい場合は「-h」で指定することも可能です。
※指定しない場合は自分自身（localhost）になります。

他にもオプションがあります。
以下の公式サイトに説明がありますので、良ければご確認頂けたらと思います。

毎度パスワードを打つのが面倒だったり、バックアップ取得バッチは変更せずにログインするユーザを変えたいなどがある場合は設定を抜き出すと便利です。

まず、設定ファイルを作成してください。

今回はファイル名を「my.conf」としています。
中身は以下のような形です。

[mysqldump]
user=root
password=root

rootの部分にはご自身のデータベースのユーザとパスワードを設定してください。

後は実行するバックアップコマンドを以下のように変更すれば完了です。

mysqldump --defaults-extra-file=./my.conf --single-transaction test_db > ./test_bk.sql

「–defaults-extra-file=」の後に作成した設定ファイルのパスをいれればOKです。

出力されるバックアップファイルの中身はSQL文が記載されています。
CREATE文だったり、INSERT文などが出力されているイメージです。

ですので、復元の仕方はそのファイルに記載されているSQLを実行するだけになります。
概ねの流れとしては、以下のような形です。

「テーブル削除（DROP）→テーブル作成（CREATE）→データ挿入（INSERT）」

データベースに接続してSQLを実行するのでも問題ありませんが、以下のようなコマンドを実行すれば、接続とSQL実行をまとめて行うことができます。

mysql -u (ユーザ名) -p （データベース名） < （バックアップファイルパス）

上記を実行するとパスワードの確認が出てきますので、対象ユーザのパスワードを入力してENTERを押せば実行されます。

以上、MySQLのバックアップとリストア方法を書きました。

上記の例ではバックアップファイル名が固定になっていますが、このバックアップファイルに日時を付与するようにすれば、実行した日付のファイルが出来上がります。

Windowsサーバであれば、タスクスケジューラの機能を使用すれば夜間などに定期的に実行することも可能です。