mysqldumpによるデータバックアップと災害復旧

目次
1、テスト環境の紹介
2、バックアップポリシー
3、バックアップ
4、災害復旧
5、まとめ
1、テストリングの紹介

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.5.36-log |
+------------+
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
mysql> SELECT * FROM mydb1.tb1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   10 |
|  2 | jack |   20 |
+----+------+------+

バックアップ・ディレクトリを2つ用意します.

[root@mariadb ~]# ls /backup/data_dir/   #               
[root@mariadb ~]# ls /backup/binlog_dir/  #                      

2、バックアップポリシー
フル・バックアップ+インクリメンタル・バックアップにより、データのバックアップおよびディザスタリカバリを実現します.
3、バックアップ
3.1、サーバーのすべてのデータベースに対して一度全バックアップを行う:

[root@mariadb ~]# mysqldump -uroot -p123456 --lock-all-tables --flush-logs --events --routines --master-data=2 --all-databases > /backup/data_dir/fulldata-`date +%F`.sql

オプションの説明:
--lock-all-tables
ライブラリ内のすべてのテーブルをロックし、全ライブラリをバックアップする場合、mysqlはいくつかのライブラリ内のテーブルに採用されるストレージエンジンがInnoDBではなく、MyISAM、CSV、MERGEなどがあるため、全ライブラリバックアップに対してホットスペアを実現できない
--flush-logs
バイナリ・ログをリフレッシュして、現在のサービスで使用されているバイナリ・ログ・ファイルを閉じ、新しいバイナリ・ログ・ファイルを生成します.これにより、後でインクリメンタル・バックアップを行うときに、新しいバイナリ・ログ・ファイルから開始します.
 --master-data=2
このオプションを有効にすると、バックアップ時にリフレッシュされたバイナリ・ログ・ファイルとpositionポイントがバックアップ・ファイルに記録され、インクリメンタル・バックアップでどのポイントからバックアップを開始すべきかを明確にすることができます.

[root@mariadb ~]# ls /backup/data_dir/
fulldata-2015-04-14.sql

3.2、データ修正を行い、増分データを発生させる
mysqlインタラクティブモードに入り、データベース、テーブルを作成し、データを挿入するなどの操作を行います.

mysql> CREATE DATABASE mydb2;
mysql> USE mydb2;
mysql> CREATE TABLE tb2 (id INT,name CHAR(15));  #          ，      
    ，               ，                   

3.3、インクリメンタルバックアップ
フルスタンバイ時に「--master-data=2」オプションが追加されているため、バックアップ時にmysqlサービスで使用されるバイナリログファイルやpositionの場所をバックアップファイルに表示することができ、これはインクリメンタルバックアップにとって非常に重要な参考点であり、また「--flush-logs」オプションがあるため、そのため、後期のインクリメンタルデータのバイナリログは新しいバイナリログファイルに書かれているので、インクリメンタルバックアップを行うときに再びログをスクロールするだけで、ログをスクロールする前のログバックアップがインクリメンタルのバイナリログです.
mysqlインタラクティブインタフェースを開き、ログをスクロールします.

mysql> SHOW MASTER STATUS; #               
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      295 |              |                  |
+------------------+----------+--------------+------------------+
mysql> FLUSH LOGS; #      
mysql> SHOW MASTER STATUS;  #              
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 |      107 |              |                  |
+------------------+----------+--------------+------------------+

バイナリ・ログ・ファイルのバックアップ:

[root@mariadb ~]# cp /var/log/mysql_log/mysql-bin.000004 /backup/binlog_dir//mysql-bin.000004-increment.`date +%F`
[root@mariadb ~]# ls /backup/binlog_dir/
mysql-bin.000004-increment.2015-04-14

このバイナリ・ログ・ファイルはインクリメンタル・バックアップのデータです.mysqlのバイナリ・ログ・ファイルはデータと一緒に保存しないでください.これにより、データ・ディレクトリが失われた後もバイナリ・ファイルを使用して災害復旧を行うことができます.今回のテストのバイナリ・ログは「/var/log/mysql_log」に保存され、データ・ディレクトリは「/mydata/data」に保存されます.
3.4、データを修正し、時間回復のためにデータ修正を行う
データベースを再変更して、新しいバイナリイベントを生成します.

mysql> INSERT INTO mydb2.tb2 (id,name) VALUES (1,'timepoint');
mysql> SELECT * FROM mydb2.tb2;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | timepoint |
+------+-----------+

4、災害復旧
4.1、アナログデータベースのデータ損失

mysql> DROP DATABASE myddb1;
Query OK, 1 row affected (0.02 sec)
mysql> DROP DATABASE myddb2;
Query OK, 1 row affected (0.03 sec)

4.2、サーバーラインオフ
データが本当に失われた場合、mysqlサーバをオフラインにし、現場で直接ネットワークケーブルを取り外すことができ、リモートサーバであればmysqlサービスを再起動することができますが、ネットワーク機能を有効にしません.

[root@mariadb ~]# /opt/lamp/mysql55/bin/mysqld_safe --skip-networking &

mysqlサービスが起動できず、チェックしても起動できない場合は、データベースを再インストールしてからリカバリするしかありません.
4.3、データ回復の準備
バイナリ・ログ機能を一時的にオフにし、リカバリ・オペレーションをバイナリ・ログ・ファイルに記録する必要はありません.

mysql> SET GLOBAL sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
mysql> FLUSH LOGS; #      ，            ，                 。
mysql> SHOW MASTER STATUS; #       
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 |      107 |              |                  |
+------------------+----------+--------------+------------------+

4.4、まず完全バックアップで回復する:

[root@mariadb ~]# mysql -uroot -p123456 < /backup/data_dir/fulldata-2015-04-14.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
mysql> SELECT * FROM  mydb1.tb1;  #mydb1          
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | tom  |   10 |
|  2 | jack |   20 |
+----+------+------+

4.5、増分バックアップでデータを回復する:

[root@mariadb ~]# mysqlbinlog /backup/binlog_dir/mysql-bin.000004-increment.2015-04-14 > /tmp/increment.sql #         sql    
[root@mariadb ~]# mysql -uroot -p123456 < /tmp/increment.sql  #    
mysql> SHOW DATABASES;  #       mydb2      
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb1              |
| mydb2              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM mydb2.tb2; #       tb2       ，        
Empty set (0.00 sec)

4.6、ポイント・イン・タイム・リカバリ

[root@mariadb ~]# ls /backup/binlog_dir/  
mysql-bin.000004.incremental.2015-04-14   #            ，        “flush logs”，                              ， "mysql-bin.000005"       。

mysqlbinlogツールを使用してmysql-binを表示します.000005バイナリ・ログ・ファイルでは、「DROP TABLE」という文が見つかりました.これまでアナログ・データベースの損失は直接使用されていた削除文であったため、削除テーブルの文が「321」というpositionにあることが観察されました.

[root@mariadb ~]# mysqlbinlog --stop-position=321 /var/log/mysql_log/mysql-bin.000005 > /tmp/321ttim.sql
[root@mariadb ~]# mysql -uroot -p123456 < /tmp/321ttim.sql
mysql> SELECT * FROM mydb2.tb2;  #   tb2      
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | timepoint |
+------+-----------+

4.7、仕上げ作業
最後にsql_をlog_bin変数の値を1に設定するか、mysqlサービスを再起動します.

mysql> SET GLOBAL sql_log_bin=1;
[root@mariadb ~]# service mysqld restart

「check table」コマンドを使用してテーブルを検証できます.
5、まとめ:
mysqldumpツールはInnoDBストレージエンジンのテーブルにホットスペアを実現するため、バックアップ時にテーブルをロックしなくてもよいし、サーバ全体のライブラリに対してバックアップを行い、MyISAMのテーブルがあれば、バックアップ前にテーブルをロックする必要があり、ライブラリにInnodbテーブルがあっても温備を実現するしかない.このツールは論理学的バックアップ方式であり、バックアップされたファイルはsql文であり、バックアップとリカバリにはmysqlプロセスの参加が必要であり、バックアップのデータ量が大きい場合、リカバリ時間が長くなります.リカバリは1つ1つのsql文を読み出してmysqlで実行され、書き込み操作であるためです.innodbライブラリのバックアップ時にホットスペアを実現するだけで、データ量が少ない場合にバックアップ操作を行うことをお勧めしますが、データが増加するにつれてmysqlのバックアップリカバリ操作に適していない場合は、他のより優れたデータバックアップリカバリスキームを選択する必要があります.