技術文章 » 資料庫資料損壞怎麼辦？——完全備份、差異備份與增量備份的合理使用。

資料庫資料損壞怎麼辦？——完全備份、差異備份與增量備份的合理使用。

2020-10-15 11:00:01

目錄

一、完全備份、差異備份與增量備份概述

三者的特點

完全備份：每次對資料庫進行完整的備份（包括表的結構和資料）
特點：備份與恢復操作簡單，但佔用大量備份空間，資料重複率高，冗餘資料多，備份與恢復時間長

差異備份：以完全備份為基準，備份自從上次完全備份之後被修改過的檔案
特點：佔用空間小，但是安全性變差

增量備份：只有在上次完全備份或者增量備份後被修改的檔案才會被備份
特點：無冗餘資料，依靠二進位制紀錄檔檔案進行逐次增量備份，單個檔案丟失則資料不完整，安全性低

三者的區別舉例

備份型別第一次備份：原有資料a第二次備份:a,b第三次備份:a,b,c
完全備份aa,ba,b,c
差異備份abb,c
增量備份abc

二、完全備份範例

2.1 冷備份與恢復

資料庫所有的資料在這個目錄裡，直接整個目錄打包（需要關閉資料庫，基本不用）
在這裡插入圖片描述

[root@host3 data]# service mysqld stop //先關閉資料庫
Redirecting to /bin/systemctl stop mysqld.service
[root@host3 data]# mkdir /opt/backup //建立備份目錄，
[root@host3 data]# tar zcvf /opt/backup/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data/
//把整個資料目錄打包
[root@host3 data]# cd /opt/backup
[root@host3 backup]# ll
total 1384
-rw-r--r-- 1 root root 1413851 Oct 13 20:03 mysql_all_2020-10-13.tar.gz

當資料庫故障時，直接把壓縮包解壓，資料挪回data目錄下

2.2 mysqldump備份與恢復

2.2.1單庫備份與恢復

mysqldump -u 使用者 -p[密碼，不寫則進行互動] 庫名 > 儲存的位置（檔案以sql格式結尾）

[root@host3 mysql]# mysqldump -uroot -p school > /opt/school.sql
Enter password: 
[root@host3 mysql]# cd /opt
[root@host3 opt]# ll
total 47700
drwxr-xr-x  38 7161 31415     4096 Oct 14 10:22 mysql-5.7.20
-rw-r--r--   1 root root  48833145 Oct 23  2017 mysql-boost-5.7.20.tar.gz
drwxr-xr-x.  2 root root         6 Oct 31  2018 rh
-rw-r--r--   1 root root      2144 Oct 14 14:05 school.sql

檢視sql檔案可知，沒有備份建立資料庫的語句，所以恢復的時候需要先建立資料庫，再恢復
在這裡插入圖片描述
製造故障：

mysql> drop database school;
Query OK, 1 row affected (0.01 sec)
直接恢復
mysql> source /opt/school.sql
ERROR 1046 (3D000): No database selected   報錯，沒有資料庫可選
Query OK, 0 rows affected (0.00 sec)

單庫正確恢復方式

mysql> create database school;   資料庫名可根據需求取
Query OK, 1 row affected (0.00 sec)

mysql> use school;
Database changed

方法一：在資料庫內用source語句恢復

mysql> source /opt/school.sql
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

檢視，已恢復
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
9 rows in set (0.00 sec)

方法二：用linux命令mysql進行恢復
mysql> drop table info;  //把表刪了
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> exit;
Bye
[root@host3 opt]# mysql -uroot -p school < /opt/school.sql  //反向匯入恢復
Enter password: 

[root@host3 opt]# mysql -uroot -p -e 'show tables from school'  //檢視
Enter password: 
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

2.2.2多庫備份

mysqldump [選項] --databases 庫名1 [庫名2] … > /備份路徑/備份檔名

[root@host3 opt]# mysqldump -uroot -p --databases mysql school > /opt/mysql-school.sql
Enter password: 
[root@host3 opt]# cd /opt
[root@host3 opt]# ll
total 49684
drwxr-xr-x  38 7161 31415     4096 Oct 14 10:22 mysql-5.7.20
-rw-r--r--   1 root root  48833145 Oct 23  2017 mysql-boost-5.7.20.tar.gz
-rw-r--r--   1 root root   1098302 Oct 14 14:26 mysql-school.sql
drwxr-xr-x.  2 root root         6 Oct 31  2018 rh
-rw-r--r--   1 root root      2144 Oct 14 14:05 school.sql

進去檔案內檢視，可以看到，在多庫備份的時候，備份了建立資料庫的語句，所以恢復的時候可以直接恢復
在這裡插入圖片描述

2.2.3所有庫備份

mysqldump [選項] --all-databases > /備份路徑/備份檔名

[root@host3 opt]# mysqldump -uroot -p --all-databases > /opt/all-data.sql
Enter password: 
[root@host3 opt]# ls
all-data.sql  mysql-boost-5.7.20.tar.gz  rh
mysql-5.7.20  mysql-school.sql           school.sql

2.2.4對資料庫表資料備份

mysqldump [選項] 庫名 [表名1] [表名2] … > /備份路徑/備份檔名

[root@host3 opt]# mysqldump -uroot -p school info > /opt/school-info.sql
Enter password:

三、增量備份範例

3.1增量備份

增量備份基於二進位制紀錄檔檔案備份，二進位制紀錄檔在啟動mysql伺服器後開始記錄，並在檔案達到二進位制紀錄檔所設定的最大值或者接受到flush logs命令後重新建立新的紀錄檔檔案，生成二進位制的檔案序列，並及時把這些紀錄檔檔案儲存到安全的儲存位置，即可完成一個時間段的增量備份。
先進入my.cnf檔案
在這裡插入圖片描述
重新啟動資料庫重新整理
在這裡插入圖片描述
接下來關於所有的資料庫操作，都被記錄在000001檔案裡。
進行一系列正確錯誤操作，目的是加入第十條

ysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
9 rows in set (0.00 sec)

mysql> delete from info where name='lisi';
Query OK, 1 row affected (0.00 sec)

mysql> insert into info (name,hobby,score) values ('zhaosi',2,66);
Query OK, 1 row affected (0.00 sec)

mysql> delete from info where hobby=1;
Query OK, 6 rows affected (0.00 sec)

mysql> select * from info;
+----+--------+-------+-------+------+
| id | name   | hobby | score | addr |
+----+--------+-------+-------+------+
|  3 | wangwu |     2 |    77 | NULL |
|  5 | tianqi |     3 |    50 | NULL |
| 10 | zhaosi |     2 |    66 | NULL |
+----+--------+-------+-------+------+
3 rows in set (0.00 sec)
誤刪了很多資料

想要通過增量備份恢復正確的資料
先重新整理二進位制紀錄檔檔案

mysqladmin -uroot -p flush-logs
Enter password:

在這裡插入圖片描述

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
使用mysqlbinlog工具檢視紀錄檔檔案

每一次命令，都是一次事務的提交
在這裡插入圖片描述

3.2基於一般恢復

mysqlbinlog [–no-defaults] 增量備份檔案 | mysql -u 使用者名稱 -p
一般恢復是直接把整個二進位制檔案的內容進行恢復。

mysql> select * from info;			當前內容
+----+--------+-------+-------+------+
| id | name   | hobby | score | addr |
+----+--------+-------+-------+------+
|  3 | wangwu |     2 |    77 | NULL |
|  5 | tianqi |     3 |    50 | NULL |
| 10 | zhaosi |     2 |    66 | NULL |
+----+--------+-------+-------+------+

mysql> drop table info;
Query OK, 0 rows affected (0.02 sec)
先把表刪了，模擬故障

先進行完全備份的恢復
mysql> source /opt/school-info.sql
mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
再進行一般恢復
[root@host3 data]# mysqlbinlog --no-defaults mysql-bin.000001 |mysql -uroot -p
Enter password: 
檢視，所有操作都恢復了
[root@host3 data]# mysql -uroot -p -e 'select * from school.info'
Enter password: 
+----+--------+-------+-------+------+
| id | name   | hobby | score | addr |
+----+--------+-------+-------+------+
|  3 | wangwu |     2 |    77 | NULL |
|  5 | tianqi |     3 |    50 | NULL |
| 10 | zhaosi |     2 |    66 | NULL |
+----+--------+-------+-------+------+

3.3基於位置恢復

通過檢視檔案，可以得出

操作第一次操作第二次操作第三次操作
操作id開始219 結束434開始499 結束716開始781 結束1095
時間點開始2020-10-14 16:30:19結束2020-10-14 16:31:50開始2020-10-14 16:31:50結束2020-10-14 16:33:38開始2020-10-14 16:33:38結束2020-10-14 16:52:38

只有第二次是正確操作，第一次和第三次都誤刪資料
1、恢復資料到指定位置
mysqlbinlog --stop-position=’操作 id’ 二進位制紀錄檔 |mysql -u 使用者名稱 -p 密碼

和一般恢復一樣，先進行完全備份，變成這樣
mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+
只進行第一次的操作恢復
mysqlbinlog --no-defaults --stop-position='434' mysql-bin.000001 |mysql -uroot -p
Enter password:

在這裡插入圖片描述

2、從指定的位置開始恢復資料
mysqlbinlog --start-position=’操作 id’ 二進位制紀錄檔 |mysql -u 使用者名稱 -p 密碼
從第二次操作開始

[root@host3 data]# mysqlbinlog --no-defaults --start-position='499' mysql-bin.000001 |mysql -uroot -p
Enter password:

在這裡插入圖片描述

3.4基於時間點恢復

刪除表，恢復完全備份後

mysql> select * from info;
+----+----------+-------+-------+------+
| id | name     | hobby | score | addr |
+----+----------+-------+-------+------+
|  1 | zhangsan |     1 |    88 | NULL |
|  2 | lisi     |     2 |    66 | NULL |
|  3 | wangwu   |     2 |    77 | NULL |
|  4 | zhaoliu  |     1 |    80 | NULL |
|  5 | tianqi   |     3 |    50 | NULL |
|  6 | liyu     |     1 |    90 | NULL |
|  7 | wooo     |     1 |    99 | NULL |
|  8 | wooooo   |     1 |    99 | NULL |
|  9 | owoo     |     1 |    99 | NULL |
+----+----------+-------+-------+------+

1、從紀錄檔開頭截止到某個時間點的恢復
mysqlbinlog [–no-defaults] --stop-datetime=’年-月-日 小時:分鐘:秒’ 二進位制紀錄檔 | mysql -u 使用者名稱 -p 密碼

只進行第一次操作
[root@host3 data]# mysqlbinlog --no-defaults --stop-datetime='2020-10-14 16:31:50' mysql-bin.000001 |mysql -uroot -p
Enter password:

在這裡插入圖片描述

2、從某個時間點到紀錄檔結尾的恢復
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小時:分鐘:秒’ 二進位制紀錄檔 | mysql -u 使用者名稱 -p 密碼
進行後面的操作
Enter password:

[root@host3 data]# mysqlbinlog --no-defaults --start-datetime='2020-10-14 16:31:50' mysql-bin.000001 |mysql -uroot -p

在這裡插入圖片描述

3、從某個時間點到某個時間點的恢復
mysqlbinlog [–no-defaults] --start-datetime=’年-月-日 小時:分鐘:秒’ --stop-datetime=’年-月-日小時:分鐘:秒’ 二進位制紀錄檔 | mysql -u 使用者名稱 -p 密碼

恢復所有正確操作，不回覆誤刪的操作
完全備份恢復之後
直接進行第二步操作

[root@host3 data]# mysqlbinlog --no-defaults --start-datetime='2020-10-14 16:31:50' --stop-datetime='2020-10-14 16:33:38' mysql-bin.000001 |mysql -uroot -p
Enter password:

在這裡插入圖片描述
恢復正確資料