mysqldump+binlog的備份與恢復 - WordPress 多站點站羣

資料備份型別
按業務劃分：可分為完全備份，增量備份，差異備份。
完全備份：就是對整個資料庫的資料和資料結構進行備份，好處是很直觀，容易被人理解。不足之處：由於每天都對系統進行完全備份，因此在備份資料中大量是重複的，這些重複資料佔用空間，增加成本，備份量大，所需時間長。
增量備份：就是每次備份的資料只是相當於上一次備份後增加和修改過的資料。優點：沒有重複的資料，節省空間，縮短備份時間。缺點：發生災難時，恢復資料麻煩。另外這種備份的可靠性也差，在這種備份下，各備份資料間的關係就像鏈子一樣，一環套一環，其中任何一個備份資料出了問題都會導致整條鏈子脱節。
差異備份：就是每次備份的資料是相對於上一次全備份之後新增加的和修改過的資料。
資料備份方式：可分為熱備，温備，冷備。
熱備份：是指在資料庫執行中直接備份，對正在執行的資料庫沒有任何影響。
冷備份：是指在資料庫停止的情況下進行的備份，這種備份最為簡單，一般只需要拷貝相關的資料庫物理檔案即可。
温備份：備份同樣是在資料庫執行時進行，但是會對當前資料庫的操作有所影響，例如加一個全域性讀鎖以保證備份資料的一致性。
備份流程圖

mysql 備份工具介紹
mysqldump: 邏輯備份工具，適用於所有儲存引擎，可用於温備，能實現完全備份，部分備份，對 IonoDB 儲存引擎支援熱備；
cp,tar 等檔案系統工具：物理備份工具，適用於所有儲存引擎，用於冷備，能實現完全備份，部分備份；
lvm2 的快照：備幾乎熱，藉助於檔案系統工具實現物理備份。
mysqlhotcopy: 幾乎冷備，僅適用於 myisam 儲存引擎；
mysqldump+binlog 實現資料庫的備份與恢復
1. 準備備份目錄

[root@centos7 ~]# mkdir -p /backup/binlog

2. 準備備份的資料庫及表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

MariaDB [datas]> select * from tb1;
+——+——+
| id | name |
+——+——+
| 11 | haha |
+——+——+
1 row in set (0.00 sec)

3. 對資料庫進行完整備份

1
2
3
4
5
6
7

[root@centos7 ~]# mysqldump –all-databases –lock-all-tables –flush-log –master-data=2 > /backup/`date +%F_%T`-all.sql
–lock-all-tables: 鎖定所有表
–flush-logs: 鎖定表之後執行 flush logs 命令
–master-data={0|1|2}
0: 不記錄
1：記錄 change master to 語句；此語句未被註釋
2：記錄為註釋語句

4. 向表中插入資料

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

MariaDB [datas]> insert into tb1 values (12,’hehe’),(13,’yaya’);
Query OK, 2 rows affected (0.23 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [datas]> select * from tb1;
+——+——+
| id   | name |
+——+——+
|   11 | haha |
|   12 | hehe |
|   13 | yaya |
+——+——+
3 rows in set (0.00 sec)

5. 進行增量備份，備份二進位制日誌

1
2
3
4

[root@centos7 mysql]# mysqlbinlog –start-position=245 –stop-position=451 mysql-bin.000008> /backup/binlog/`date +%F_%T`.sql
[root@centos7 mysql]# cd /backup/binlog/
[root@centos7 binlog]# ls
2017-12-03_16:43:29.sql

6. 繼續插入資料，在沒備份的情況下刪除資料庫，模擬誤操作。

1
2
3
4
5

MariaDB [datas]> insert into tb1 values (16,’yuyu’);
Query OK, 1 row affected (0.00 sec)

MariaDB [datas]> drop database datas;
Query OK, 1 row affected (0.09 sec)

7. 恢復資料，由於在最後沒有備份就刪除了資料庫，所以我們首先需要保護最後的二進位制日誌，檢視刪除之前的 position 值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

[root@centos7 binlog]# cd  /var/lib/mysql/
[root@centos7 mysql]# mysqlbinlog  mysql-bin.000008
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#171203 16:35:29 server id 2  end_log_pos 245     Start: binlog v 4, server v 5.5.52-MariaDB created 171203 16:35:29 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG ‘
UbcjWg8CAAAA8QAAAPUAAAABAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABRtyNaEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAikwNAA==
‘/*!*/;
# at 245
#171203 16:36:38 server id 2  end_log_pos 314     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1512290198/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 314
#171203 16:36:38 server id 2  end_log_pos 424     Query    thread_id=2    exec_time=0    error_code=0
use `datas`/*!*/;
SET TIMESTAMP=1512290198/*!*/;
insert into tb1 values (14,’hehe’),(15,’yaya’)
/*!*/;
# at 424
#171203 16:36:38 server id 2  end_log_pos 451     Xid = 10
COMMIT/*!*/;
# at 451
#171203 16:54:34 server id 2  end_log_pos 520     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1512291274/*!*/;
BEGIN
/*!*/;
# at 520
#171203 16:54:34 server id 2  end_log_pos 618     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1512291274/*!*/;
insert into tb1 values (16,’yuyu’)
/*!*/;
# at 618
#171203 16:54:34 server id 2  end_log_pos 645     Xid = 20
COMMIT/*!*/;
# at 645
#171203 16:54:56 server id 2  end_log_pos 728     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1512291296/*!*/;
drop database datas
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

將最後的二進位制日誌備份

[root@centos7 mysql]# mysqlbinlog –start-position=520 –stop-position=645 mysql-bin.000008> /backup/binlog/`date +%F_%T`.sql

8. 匯入之前的所有備份

1
2
3
4

[root@centos7 ~]# mysql < /backup/2017-12-03_15:05:00-all.sql [root@centos7 ~]# mysql < /backup/binlog/2017-12-03_16:43:29.sql [root@centos7 ~]# mysql < /backup/binlog/2017-12-03_16:51:11.sql [root@centos7 ~]# mysql < /backup/binlog/2017-12-03_17:10:02.sql 9. 檢視資料庫及資料 1 2 3 4 5 6 7 8 9 10 11 12 MariaDB [datas]> select * from tb1;
+——+——+
| id   | name |
+——+——+
|   11 | haha |
|   14 | hehe |
|   15 | yaya |
|   12 | hehe |
|   13 | yaya |
|   16 | yuyu |
+——+——+
6 rows in set (0.00 sec)

資料已經全部恢復了。

mysqldump+binlog 的備份與恢復

mysqldump+binlog 的備份與恢復