レンタルサーバー・自宅サーバー設定・構築のヒント
レンタルサーバー・自宅サーバー設定・構築のヒント - レンタルサーバー・自宅サーバーの設定・構築情報を公開しています。

MySQLのバイナリログを使ったロールバック(リストア)を行ってみる

2012年11月12日 2015年5月14日
mysql backup restore

以前の記事 MySQLのログの種類とログの仕方を調べてみた(実施例) で、MySQLのログの採取方法について簡単に解説してみました。 その中のバイナリログは、ロールバック(リストア)できることをちょっとだけ振れていました。

MySQLでバイナリログを使って、誤ったオペレーションなどでデータベースを壊してしまったりした場合、そ の誤ったオペレーションの直前まで戻す(ロールバック(リストア))ことができます。

ただ、一般的にMySQLを最初に起動したときから、ずーーーっと、全バイナリログをとって(保存)いるなんてことは、ほとんどないですよね。

一般的には、バイナリログからのロールバック(リストア)といっても、バイナリログだけから復元するわけではありません。 (もちろん、先に記述したようにMySQLの最初の起動時からのバイナリログがあれば別です。

通常は、以下の2点を使ってロールバック(リストア)を行います。

  • フルバックアップ(mysqldump あるいは停止してのバイナリファイルバックアップ)
  • フルバックアップ採取後のバイナリログ

今回は、このフルバックアップ+バイナリログによるロールバック(リストア)の手順について簡単に解説してみます。

MySQLフルバックアップ+バイナリログによるロールバック(リストア)の手順

テスト用にデータベースのバイナリログを採取するように設定します。

まずは、常にMySQLでバイナリログをとるように設定しておきます。
そのためにMySQLの設定ファイル ( /etc/my.cnf ) を編集します。

$ vi /etc/my.cnf
...

[mysqld]
...
#  Binary log
log_bin="/var/log/mysql/bin.log"
...
各ロギングの設定例では、mysqlの設定ファイルを一律 /etc/my.cnf としています。
これは、RedHat系(CentoOS,Scientific Linuxなど)のデフォルトの設定ファイルになります。
Deabin系(Debian,Ubuntuなど)では、/etc/mysql/my.cnf というディレクトリ配下がデフォルトになっています。ファイルの場所の違いに注意してください。 もちろん、設定する内容は同じです。

ここでは、最低限の設定のみ行っています。詳しくは、MySQLのログの種類とログの仕方を調べてみた(実施例) を参照してください。
設定を終えたら、mysqlをリスタートしてきます。

$ /etc/init.d/mysql restart
...

データベースとテーブルを作成します。

次に、ここではあくまでテスト用ですが、必要なデータベース、テーブルを作成します。

$ mysql -uroot -p
Enter password: 
..
mysql> CREATE DATABASE sample_db;
Query OK, 1 row affected (0.00 sec)

mysql> USE sample_db;
Database changed
mysql> SHOW TABLES;
Empty set (0.00 sec)

mysql> CREATE TABLE animals (
    ->              id MEDIUMINT NOT NULL AUTO_INCREMENT,
    ->              name CHAR(30) NOT NULL,
    ->              PRIMARY KEY (id)
    ->              );
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
    ->                                   ("lax"),("whale"),("ostrich");
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

mysql> exit
Bye

ここでは、
データベース名: sample_db
テーブル名: animals
6レコードを追加してみました。


データベースのフルバックアップを作成します。

次に、ここではあくまでテスト用ですが、データベース、テーブルのバックアップを作成します。

ここでバックアップは、あくまで、正常な状態のデータベースのバックアップということに注意してください。 少なくとも1つの正常なバックアップが無ければ、ロールバックは難しいです。
cronなどを使って、常にバックアップをとるようにしておきましょう。(参考 : mysqldumpを使ってデータベースの30日分をバックアップする)
$ mysqldump -uroot -p sample_db > sample_db_backup.sql
Enter password: 

フルバックアップを作成した時点で、バイナリログの更新(ファイルの入れ替え)を行っておきます。

$ mysql -u root -p sample_db -e "flush logs;"
Enter password: 
ここで、flush logs; を実行するとバイナリログのファイルが変更されます。
以下は、flush logs; を実行する前が、bin.000002.log というバイナリログファイル名だった時に、flush logs; を実行した場合のログファイル名です。
/var/log/mysql/bin.000003.log
                   ^^^^^^
                   ファイル名が最新の番号が付加された名前に切り替わります。
と、シーケンシャルに番号が変わります。

これを実施するか、バイナリログ位置をダンプしておくか、いずれかをやっておくと後で楽です。
$ mysql -u root -p sample_db -e "show master status;"
Enter password: 
+------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------+----------+--------------+------------------+
| bin.000003 |     1904 |              |                  |
+------------+----------+--------------+------------------+
ここでは、bin.000003 の 位置 : 1904 を探せばよいので、楽になります。

データベースのアップデートを行います。

次に、ここではあくまでテスト用ですが、データベース、テーブルの情報を更新します。

$ mysql -uroot -p -Dsampld_db
Enter password: 
..
mysql> update animals set name="wolf" where name="dog";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update animals set name="lion" where name="cat";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update animals set name="giraffe" where name="wolf";
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update animals set name="tigger" where name="lion";
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | giraffe |
|  2 | tigger  |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

mysql> exit
Bye

ここでの例では、単純に以下のようにデータをアップデートしています。

“dog” → “wolf” → “giraffe”
“cat” → “lion” → “tigger”


データベースのロールバックを行います。

先のデータベースの更新の中で、

“dog” → “wolf” → “giraffe”
赤文字の箇所が誤っていたとしましょう。

ここでは、“wolf” → “giraffe” へ変更する前までデータベースをロールバックしてみます。

  1. データベースのフルバックアップをとる。

    最悪失敗した時のために、現状復旧するためのバックアップをとります。あくまで最悪の場合のためのバックアップなので、以降の作業でこのバックアップを使用することはありません。

    $ mysqldump -uroot -p sample_db > sample_db_backup_latest.sql
    Enter password: 

  2. バイナリログファイルのバックアップを保存しておく。

    これから、リストア(ロールバック)作業を行う上で、ロールバックといってもデータベースが昔のデータベースになるわけではなく、データベースを更新して、昔のデータベースの内容に合わせることになります。 つまり、データベースは更新されますから、バイナリログも更新されます。

    バイナリログが更新されると、ロールバックする際に非常に面倒ですし、何がなにやら混乱のもとです。
    そのため、現状のバイナリログを一旦コピー保存します。

    $ mkdir /var/log/mysql/rollback/
    $ cp /var/log/mysql/bin* /var/log/mysql/rollback/.

  3. データベースを正常なフルバックアップがとれている箇所までロールバック(リストア)する。

    以前の手順の中で、正常なデータベースのフルバックアップから、 その正常な位置までリストア(復元)します。

    $ mysql -uroot -p -Dsample_db < sample_db_backup.sql
    Enter password: 
    上の最悪のためのバックアップではないことに注意してください。あくまで、正常な状態でとったフルバックアップからリストアします。

  4. バイナリログからロールバックしたい位置を確認する。

    先の手順でフルバックアップした際に、
    flush logs; あるいは、show master status; を実行したことと思います。
    そこから、探す開始位置を決め、あとは、日時から、探します。

    1. バイナリログの対象ファイルを探す

      バイナリログファイルは、一つとは限りません。そのため、どのファイルを探せば良いか苦労することがあります。 そこで、先に実行したflush logs; あるいは、show master status; が役に立ちます。
      それぞれで探す対象となるバイナリログファイル名を探します。

      • flush logsを実行した場合は、

        ファイルの更新日時で、フルバックアップ以降のバイナリログを確認できます。

        $ ls -l /var/log/mysql/
        -rw-r----- 1 mysql adm 36532 2012-11-08 03:38 bin.000001
        -rw-r----- 1 mysql adm   786 2012-11-08 04:15 bin.000002
        -rw-r----- 1 mysql adm  3555 2012-11-09 11:06 bin.000003

        上記のような場合、mysqldump を実行した日付が 2012-11-08 04:15 なら、bin.000003 以降のファイルがバイナリログの対象となります。


      • show master statusを実行した場合は、

        出力情報のバイナリログファイル名と位置情報から、フルバックアップ以降のバイナリログを確認できます。
        show master status の出力情報が以下のような場合、

        $ mysql -u root -p sample_db -e "show master status;"
        Enter password: 
        +------------+----------+--------------+------------------+
        | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------+----------+--------------+------------------+
        | bin.000003 |     1904 |              |                  |
        +------------+----------+--------------+------------------+

        単純に、bin.000003 の 位置 : 1904 以降のファイルがバイナリログの対象となります。



    2. ロールバック位置を特定します。

      ロールバック位置を探すための対象となるバイナリログファイルが決まったら、そのファイルの中からロールバック位置を特定します。

      $ mysqlbinlog /var/log/mysql/rollback/bin.000003 --start-position 1904
      ...
      # at 2261
      #121112 11:05:16 server id 1  end_log_pos 2334 	Query	thread_id=143	exec_time=0	error_code=0
      SET TIMESTAMP=1352685916/*!*/;
      BEGIN
      /*!*/;
      # at 2334
      #121112 11:05:16 server id 1  end_log_pos 2453 	Query	thread_id=143	exec_time=0	error_code=0
      SET TIMESTAMP=1352685916/*!*/;
      update animals set name="giraffe" where name="wolf"
      /*!*/;
      # at 2453
      #121112 11:05:16 server id 1  end_log_pos 2480 	Xid = 685
      COMMIT/*!*/;
      ...

      ある程度日時で絞り込めるようなら、以下のように指定するこができます。

      $ mysqlbinlog /var/log/mysql/rollback/bin.000003 --start-datetime "2012-11-09 09:00:00" --stop-datetime "2012-11-09 10:00:00"
      ...
      # at 2261
      #121112 11:05:16 server id 1  end_log_pos 2334 	Query	thread_id=143	exec_time=0	error_code=0
      SET TIMESTAMP=1352685916/*!*/;
      BEGIN
      /*!*/;
      # at 2334
      #121112 11:05:16 server id 1  end_log_pos 2453 	Query	thread_id=143	exec_time=0	error_code=0
      SET TIMESTAMP=1352685916/*!*/;
      update animals set name="giraffe" where name="wolf"
      /*!*/;
      # at 2453
      #121112 11:05:16 server id 1  end_log_pos 2480 	Xid = 685
      COMMIT/*!*/;
      ...

      できれば、ファイルに出力して確認するのが良いでしょう。

      ここで
      end_log_pos 2453
      が確認できたので、その手前までをリストア(復元)すればOKです。


  5. バイナリログからロールバックする。

    先の手順から、ロールバックする位置が確定したら、あとは、バイナリログからSQLダンプし、それを流し込むだけです。

    $ mysqlbinlog /var/log/mysql/rollback/bin.000003 --start-position 1904 --stop-position 2334 | mysql -uroot -p -Dsample_db
    Enter password: 

    これでOKのはずです。

    データを出力して確認してみます。

    $ mysql -uroot -p -Dsample_db
    Enter password: 
    ...
    mysql> SELECT * FROM animals;
    +----+---------+
    | id | name    |
    +----+---------+
    |  1 | wolf    |
    |  2 | lion    |
    |  3 | penguin |
    |  4 | lax     |
    |  5 | whale   |
    |  6 | ostrich |
    +----+---------+
    6 rows in set (0.00 sec)
    
    mysql>

    確かに “dog” → “wolf” までで、→ “giraffe” は、アップデートされていませんね。
    ちゃんと、思った位置へ戻ったようです。



非常に簡単な例で解説してみました。
本当のデータベースなら、もっと複雑なSQL文になりますから、位置を探すのが難しいことでしょう。
上記の手順でのポイントは、やっぱりフルバックアップ時のバイナリログ位置をどうするか?です。これさえできていれば、あとでロールバックする際に、位置を見つける時に一手間楽になるはずです。

いずれにしろバイナリログは、何かと役に立つのは間違いないでしょう。バックアップ+バイナリログによるセキュリティ強化は、行っておくべきでしょうね。
ご利用のブラウザは、広告ブロック(AdBlockなど) が適用となっていませんか?
このサイトでは、コンテンツの一部が非表示、あるいは、コメント、お問い合わせの投稿ができない検索ができないことがあります。


関連記事 :

mysqlのレプリケーションを使う(2) バイナリログを削除する

以前に mysqlのレプリケーションを使う で、mysqlのレプリケーション(replication) について記事にしました。 今回は、その ...

mysqlのレプリケーションを使う

今回は、mysqlのレプリケーション(replication)です。 レプリケーション(replication)とは、 直訳 ...

mysqlでマルチマスター レプリケーションを設定してみる

以前の記事 mysqlのレプリケーションを使う で mysqlを使ったレプリケーションの設定を行ってみました。 今回は、そのmysqlのレプリ ...

mysqlでSSL接続する

mysqlでは、SSL接続にて情報を暗号化することができます。 少なくとも、CentOS,ScientificLinux,Debian,Ubu ...

mysqlのレプリケーションを使う(3) エラー1062の対処

以前に mysqlのレプリケーションを使う mysqlのレプリケーションを使う(2) バイナリログを削除する で、m ...



コメントを投稿 :

お名前 *

メールアドレス *
( メールアドレスが公開されることはありません。)

サイトアドレス

コメント *

* 印の項目は、入力が必要な項目です。




最近投稿の記事

[ 画像提供元 : Amazon ] 先日、1TBのディスクの入れ替え時にバックアップをとろうとディスクコピーを行いました。 その際 ...

Windows で Linux ファイルシステム Ext4 のディスクをマウントするには? Ext3Fsd が、おそらく、最も簡単なツール ...

今回は、Windows で Compass を使ってみました。 Compass とは、 Sass(サス、Syntactica ...

今回は、Anti Adblock を使ってみました。 Anti Adblock とは、 そもそも Adblock という ウェブ ...

デスクトップ環境でない サーバーで、Webページのキャプチャー画像をコマンドで撮る には、wkhtmltoimage, CutyCapt ...


さくらのVPS 全プラン リニューアルです。(石狩(北海道)も選択可)


root権限ありで ¥685 / 月 ~ と非常にリーズナブルな CPU 1(core)の 512 プランから、 最高 CPU 10(core), メモリ 32(GB), SSD容量 800(GB) までとプランが充実。
ディスクは、SSDとHDDの選択が可能になった他、データセンターは人気の東京、石狩(北海道)となりました。

また、どのプランでも好きなOSが選べます
( CentOS, Fedora, Scientific Linux, FreeBSD, Ubutu, Debian )

管理人もおすすめのVPSです。
試用期間がありますから、一度、お試しを!!

詳しくは、http://vps.sakura.ad.jp/さくらのVPSのサイトへ へどうぞ!!

カテゴリ


Serverman@VPS 完全1ヶ月無料 キャンペーン実施中です。


Serverman@VPS 完全1ヶ月無料 キャンペーン実施中です。
最近、スワップにも対応した Serverman@VPS は、かなりリーズナブルかもです。

  • メモリ1GB~2GBのEntryプラン :月額:490円
  • メモリ2MB~4GBのStandardプラン :月額:980円
  • メモリ4GB~8GBのProプラン :月額:1,980円

新規申し込みで1ヶ月間完全無料となるキャンペーンを実施中です。
Serverman@VPSの特徴は、安さとIPv6対応です。また、初期設定費0円もポイントです。

IPv6でちょっと遊んでみたい方には、おすすめかもしれませんね。最低利用期間もありませんから、気に入らないときは即解約もできます。

Serverman@VPSの詳細については、 http://dream.jp/vps/ Serverman@VPSのサイトへへどうぞ。



KVM採用 ConoHa VPSは、時間単位で借りれる便利なVPSです。


ConoHa VPS は、初期設定費0円最低利用期間無し時間単位で清算可能、 さらに、Web APIを使って自動化を図ることもできる便利なVPSです。

海外サーバー設置も可能で、ローカル接続にも対応と、かなり、機能豊富なサーバーです。

新規ユーザ登録で、クーポンもらえますから、まずは、お試しですね。

ConoHa VPSの詳細については、
http://www.conoha.jp/ へどうぞ。

KVM採用 お名前.com VPS(KVM) 2G プラン 初期設定費無料 キャンペーン 実施です。


メモリ2GBプラン CPU:3core、Disk:200GB
月額:1,153円から (初期設定:1,680円0円)

さくらのVPSがリニューアルされてもなんのその。
1GBメモリ / 2Core を ¥834 – の格安価格で提供中です!
間違いなくスペックからすると割安感ありです。
年間割引時の途中解約で返金がないのは、 ちょっと残念ですが、それでもこの割安感は魅力です。

まずは、お試しですね。

お名前.com VPS(KVM)の詳細については、
http://www.onamae-server.com/vps/ へどうぞ。(お試し期間が15日あります。)



  • ソーシャルブックマーク

  • はてなブックマークへ追加するはてな登録数
ページトップへ
Time : 0.2391 [s]