MySQLバックアップとリカバリ(一)mysqldump
10514 ワード
バックアップ方式:ホット・スペア:サービスを中断しないホット・スペア:バックアップ中は書き込み不可、リード・ロックによるコールド・スペア:サービスを停止する必要がある
バックアップポリシー1:mysqldump+binlog完全バックアップ+増分、mysqldumpで定期的に全備し、毎日(実際の状況に応じて)バイナリログをバックアップする.
mysqldumpはmysqlのクライアントに似ているので、mysqldumpでバックアップする場合は、mysqlサービスを起動します.一般的なパラメータ:
-u username|--user=usernameバックアップのユーザーを指定
-p password | --password=password
-h host | --host=host
-p port | --port=port
-A--all-databasesすべてのデータベースをバックアップ
-B --databases db_nameバックアップするデータベースの選択
-C--compress圧縮可能
-d--no-dataは空のライブラリをバックアップし、データをバックアップしません.
-E--events同時バックアップイベントスケジューラコード
-F--flush-logsバックアップ前にログをスクロール
--ignore-tables db_name.tb_nameバックアップを必要としないテーブルを指定
--lock-tablesテーブルをバックアップする場合は、バックアップ前にテーブルをロックします
-x--lock-all-tablesバックアップ前にすべてのライブラリのすべてのテーブルをロック
--single-transactionはバックアップのために大きなトランザクションを作成し、スナップショット原理に基づいてMVCCのInnoDBホットスペアを実現します.このオプションをオンにすると-xは使用されません.InnoDBがトランザクションをサポートするテーブルだけが一貫性を保証します.
--master-data=0|1|2レプリケーションアーキテクチャでは、CHANGE MASTER TO statementのようなホストサーバを構成する文をどのように処理するかを指定します.
手順の簡単な説明:
バックアップポリシー1:mysqldump+binlog完全バックアップ+増分、mysqldumpで定期的に全備し、毎日(実際の状況に応じて)バイナリログをバックアップする.
mysqldumpはmysqlのクライアントに似ているので、mysqldumpでバックアップする場合は、mysqlサービスを起動します.一般的なパラメータ:
-u username|--user=usernameバックアップのユーザーを指定
-p password | --password=password
-h host | --host=host
-p port | --port=port
-A--all-databasesすべてのデータベースをバックアップ
-B --databases db_nameバックアップするデータベースの選択
-C--compress圧縮可能
-d--no-dataは空のライブラリをバックアップし、データをバックアップしません.
-E--events同時バックアップイベントスケジューラコード
-F--flush-logsバックアップ前にログをスクロール
--ignore-tables db_name.tb_nameバックアップを必要としないテーブルを指定
--lock-tablesテーブルをバックアップする場合は、バックアップ前にテーブルをロックします
-x--lock-all-tablesバックアップ前にすべてのライブラリのすべてのテーブルをロック
--single-transactionはバックアップのために大きなトランザクションを作成し、スナップショット原理に基づいてMVCCのInnoDBホットスペアを実現します.このオプションをオンにすると-xは使用されません.InnoDBがトランザクションをサポートするテーブルだけが一貫性を保証します.
--master-data=0|1|2レプリケーションアーキテクチャでは、CHANGE MASTER TO statementのようなホストサーバを構成する文をどのように処理するかを指定します.
手順の簡単な説明:
[root@node1 ~]# mysqldump -uroot -pmypass --all-databases --master-data=2 --lock-all-tables --flush-logs > /backup/full_db_`date +%F`.sql
[root@node1 ~]# ls /backup/full_db_2014-07-29.sql -l
-rw-r--r--. 1 root root 523830 Jul 29 11:08 /backup/full_db_2014-07-29.sqlMariaDB [hellodb]> SELECT * FROM scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 11 | 6 | 1 | 96 |
| 12 | 7 | 1 | 86 |
| 13 | 7 | 7 | 83 |
| 14 | 8 | 4 | 57 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)
MariaDB [hellodb]> DELETE FROM scores WHERE ID>10 AND ID<=14;
Query OK, 4 rows affected (0.08 sec)
MariaDB [hellodb]> SELECT * FROM hellodb.scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
11 rows in set (0.00 sec)
/* ， ， */
MariaDB [hellodb]> SET sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SHOW VARIABLES LIKE '%SQL_LOG_BIN%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 495 | | |
+------------------+----------+--------------+------------------+
MariaDB [hellodb]> INSERT INTO scores (StuID,CourseID,Score) VALUES (3,3,3),(4,4,4),(5,5,5);
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
/* 495 */
MariaDB [hellodb]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 495 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> SET sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
/* binlog */
MariaDB [hellodb]> INSERT INTO scores (StuID,CourseID,Score) VALUES (10,11,12),(13,14,15);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
/* LAST_INSERT_ID() AUTO_INCREMENT */
MariaDB [hellodb]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 19 |
+------------------+
1 row in set (0.00 sec)
/* ID=15 ， */
MariaDB [hellodb]> SELECT * FROM scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 19 | 10 | 11 | 12 |
| 18 | 5 | 5 | 5 |
| 17 | 4 | 4 | 4 |
| 16 | 3 | 3 | 3 |
| 15 | 8 | 3 | 93 |
| 20 | 13 | 14 | 15 |
+----+-------+----------+-------+
16 rows in set (0.00 sec)
/* hellodb ， */
MariaDB [(none)]> DROP DATABASE hellodb;
Query OK, 9 rows affected (0.47 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.01 sec)
/* --flush-logs ， */
cp /mydata/data/mysql-bin.000006 /backup/
[root@node1 data]# cd /backup/
/* DROP DATABASE ， binlog */
[root@node1 backup]# mysqlbinlog 'mysql-bin.000006' | grep 'DROP DATABASE' -B 15
#140729 13:59:59 server id 1 end_log_pos 594 Intvar
SET INSERT_ID=19/*!*/;
# at 594
#140729 13:59:59 server id 1 end_log_pos 730 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1406613599/*!*/;
INSERT INTO scores (StuID,CourseID,Score) VALUES (10,11,12),(13,14,15)
/*!*/;
# at 730
#140729 13:59:59 server id 1 end_log_pos 802 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1406613599/*!*/;
COMMIT
/*!*/;
# at 802
#140729 14:15:57 server id 1 end_log_pos 889 Query thread_id=26 exec_time=0 error_code=0
SET TIMESTAMP=1406614557/*!*/;
DROP DATABASE hellodb
/* */
[root@node1 backup]# mysqlbinlog 'mysql-bin.0000061' --stop-position=730 > /backup/inr-001.sql
/* , ， */
[root@node1 backup]# mysql < /backup/full_db_2014-07-29.sql/* hellodb ， */
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
MariaDB [(none)]> USE hellodb;
Database changed
MariaDB [hellodb]> SELECT * FROM scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 11 | 6 | 1 | 96 |
| 12 | 7 | 1 | 86 |
| 13 | 7 | 7 | 83 |
| 14 | 8 | 4 | 57 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
15 rows in set (0.00 sec)
/* ， ， */
[root@node1 backup]# mysqlbinlog 'mysql-bin.000006' | grep --color 'DELETE FROM' -C 5
/*!*/;
# at 316
#140729 11:17:42 server id 1 end_log_pos 423 Query thread_id=25 exec_time=0 error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1406603862/*!*/;
DELETE FROM scores WHERE ID>10 AND ID<=14
/*!*/;
# at 423
#140729 11:17:42 server id 1 end_log_pos 495 Query thread_id=25 exec_time=0 error_code=0
SET TIMESTAMP=1406603862/*!*/;
COMMIT
[root@node1 backup]# mysqlbinlog 'mysql-bin.000006' --stop-position=423 > /backup/inc-001.sql
[root@node1 backup]# mysqlbinlog 'mysql-bin.000006' --start-position=423 --stop-position=730 > /backup/inc-002.sql
[root@node1 backup]# mysql < inc-001.sql
/* ，ID 11 14 */
MariaDB [(none)]> SELECT * FROM hellodb.scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
11 rows in set (0.00 sec)
/* ， ， ， ，
sql-bin-log ， ，
binlog， ， ，
， 。*/
[root@node1 backup]# mysql < inc-002.sql
MariaDB [(none)]> SELECT * FROM hellodb.scores;
+----+-------+----------+-------+
| ID | StuID | CourseID | Score |
+----+-------+----------+-------+
| 1 | 1 | 2 | 77 |
| 2 | 1 | 6 | 93 |
| 3 | 2 | 2 | 47 |
| 4 | 2 | 5 | 97 |
| 5 | 3 | 2 | 88 |
| 6 | 3 | 6 | 75 |
| 7 | 4 | 5 | 71 |
| 8 | 4 | 2 | 89 |
| 9 | 5 | 1 | 39 |
| 10 | 5 | 7 | 63 |
| 20 | 13 | 14 | 15 |
| 19 | 10 | 11 | 12 |
| 15 | 8 | 3 | 93 |
+----+-------+----------+-------+
13 rows in set (0.00 sec)