MySQL增量備份與還原詳解

標籤：var   int   pos   vpd   values   備份檔案   時間   emctl   通過   

增量備份與恢複的特點

• 增量備份的優點是沒有重複資料，備份量不大，時間短。但缺點也很明顯，需要建立在上次完全備份及完全備份之後所有的增量才能恢複。
• MySQL沒有提供直接的增量備份方法，但是可以通過mysql二進位日誌間接實現增量備份。二進位日誌對備份的意義如下：
• （1）二進位日誌儲存了所有更新或者可能更新資料庫的操作。
• （2）二進位日誌在啟動MySQL伺服器後開始記錄，並在檔案達到所設大小或者收到flush logs 命令後重新建立新的記錄檔。
• （3）只需定時執行flush logs方法重新建立新的日誌，產生二進位檔案序列，並及時把這些檔案儲存到一個安全的地方即完成了一個時間段的增量備份。

增量備份及恢複建立基本資料庫

create databse schooluse schoolcreate table info (name varchar(10),score decimal(5,2));insert into info (name,score) values (‘zhangsan‘,80);insert into info (name,score) values (‘lisi‘,80);//資料庫建立以及表資料添加完成

• 編輯設定檔，開啟二進位日誌功能

#vim /etc/my.cnf

log-bin=mysql-bin   //添加二進位記錄檔

#systemctl restart mysqld.service

#msyqldump -u root -p school > /opt/school.sql //備份資料庫檔案

• 首先產生建立test01資料的二進位日誌：

#mysql -u root -p

>use school;>insert into info (name,score) values (test01,88);>quit//完成資料插入

#mysqladmin -u root -p flush-logs //重新整理產生mysql增量備份檔案
#cd /usr/local/mysql/data/
#ls //二進位日誌已經產生

• 查看二進位記錄檔：（確定增加test01操作是在mysql-bin.000002日誌中）

  #mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 //使用64位解碼方式

• 其次產生建立test02資料的二進位日誌：

#mysql -u root -p

>use school;>insert into info (name,score) values (test02,90);>quit//完成資料插入

#mysqladmin -u root -p flush-logs //重新整理產生mysql增量備份記錄檔
#cd /usr/local/mysql/data/
#ls //二進位日誌已經產生

• 當工作人員不慎操作，刪除了test01與test02使用者，導致資料庫不可用，而備份的完整資料中只有zhangsan和lisi賬戶，
  此時需要根據二進位記錄檔按需恢複test01或者test02；

類比誤操作：

#mysql -u root -p

>use school>delete from info where name=‘test01‘;    //執行誤操作>select * from info;    >delete from info where name=‘test02‘;    //執行誤操作>select * from info;

首先恢複完整資料庫：

#mysql -u root -p school < /opt/school.sql

• 當test01使用者添加資料在二進位日誌msyql-bin.0000002中時：
• 恢複test01：

#msyqlbinlog --no-defaults /usr/local/mysql/data/msyql-bin.0000002 | msyql -u root -p
#msyql -u root -p

>use school>select * from info;     //發現test01已經恢複

• 當test02使用者添加資料在二進位日誌msyql-bin.0000003中時：
• 恢複test02：

#mysqlbinlog --no-defaults /usr/local/mysql/data/msyql-bin.0000003 | msyql -u root -p
#mysql -u root -p

>use school>select * from info;     //發現test02已經恢複

基於時間與位置恢複

• 建立預設資料庫：

  create databse schooluse schoolcreate table info (name varchar(10),score decimal(5,2));insert into info (name,score) values (‘tom‘,80);insert into info (name,score) values (‘jerry‘,90);//資料庫建立以及表資料添加完成

• 開啟二進位日誌功能

#vim /etc/my.cnf

log-bin=mysql-bin   //添加二進位記錄檔

#systemctl restart mysqld.service

#mysqldump -u root -p school > /opt/school.sql //備份資料庫檔案,只含tom和jerry的預設使用者

#mysqladmin -u root -p flush-logs //重新整理產生mysql增量備份記錄檔
#cd /usr/local/mysql/data/
#ls //二進位日誌已經產生

類比故障：

• 當某一工作人員在添加test01與test02賬戶時，中間不小心刪除資料庫中預設賬戶tom，導致資料庫完整性被破壞。
  下面需要針對在二進位記錄檔按位置與時間恢複test01和test02資料以及tom資料；

mysql> insert into info (name,score) values (‘test01‘,88);Query OK, 1 row affected (0.00 sec)mysql> delete from info where name=‘tom‘;   //故障操作為誤刪除預設使用者tomQuery OK, 1 row affected (0.00 sec)mysql> insert into info (name,score) values (‘test02‘,70);Query OK, 1 row affected (0.00 sec)

#mysqladmin -u root -p flush-logs //此時在誤操作的基礎上已經完成了增量備份

• 由於完成Database Backup檔案只有預設tom和jerry使用者，並沒有test01和test02，若還原增量備份檔案，則會刪除tom使用者,下面將利用二進位日誌增量備份檔案斷點還原test01與test02且不刪除tom使用者；

• 首先還原完整資料庫：

  drop table info;  //刪除原有損壞的資料庫

#mysql -u root -p school < /opt/school.sql //還原備份資料庫

• 查看二進位日誌增量備份檔案：

• 尋找需要還原的斷點位置：

• 由於在斷點增量備份檔案還原時，還原順序從檔案開頭執行，到--stop-datetime錯誤操作時間點結束；

• 由於在斷點增量備份檔案還原時，還原順序從檔案開頭執行，到--stop-datetime錯誤操作時間點結束；當跳過錯誤時間點，從下一次正確操作時間點--start-datetime開始執行到備份檔案結尾。

• 還原到test01：

#mysqlbinlog --no-defaults --stop-datetime=‘2018-07-03 19:05:42‘ /usr/local/mysql/data/msyql-bin.0000001 | mysql -u root -p //注意修改日期格式xxx-xx-xx

• 跳過錯誤時間：從下一個正確操作時間開始：

#mysqlbinlog --no-defaults --start-datetime=‘2018-07-03 19:06:03‘ /usr/local/mysql/data/msyql-bin.0000001 | mysql -u root -p //注意修改日期格式xxx-xx-xx

• 這樣就跳過了錯誤操作步驟，直接選擇正確操作還原。
• 註：斷點還原只能跳過一次錯誤，若中間有多個錯誤操作不可連續還原。

節點還原

• 注意錯誤操作前面的at節點號：

#mysqlbinlog --no-defaults --stop-position=‘563‘ /usr/local/mysql/data/msyql-bin.0000001 | mysql -u root -p

• 跳過錯誤時間：從下一個正確動作節點開始：

#mysqlbinlog --no-defaults --stop-position=‘660‘ /usr/local/mysql/data/msyql-bin.0000001 | mysql -u root -p

如上，關於節點恢複實驗已經完成。

MySQL增量備份與還原詳解