MySQL增量備份恢複和基於時間點與位置的恢複

標籤：utf8   efault   資料檔案   記錄檔   啟動mysql   記錄   str   伺服器   centos7.3   

為什麼使用增量備份？

完全備份有兩種方式，一種是使用tar打包資料檔案，另一種是使用mysqldump進行完全備份。完全備份存在的問題很容易看到，每次都是把所有的資料內容進行備份，備份資料中有大量的重複資料，並且完全備份的時間和恢複的時間很長。解決完全備份存在的問題就是使用增量備份的方式，增量備份就是備份自上一次備份之後增加或改變的檔案或者內容。

增量備份的特點：

增量備份的優點是沒有重複資料，備份量不大，時間短。缺點也很明顯，需要上次完全備份及完全備份之後所有的增量備份才能恢複，而且對所有增量備份進行逐個反推恢複，操作較為繁瑣。
MySQL沒有提供直接的增量備份方法，但是可以通過MySQL的二進位日誌（binary logs）簡介間接實現增量備份。二進位日誌對備份的意義如下：

• （1） 二進位日誌儲存了所有更新或者可能更新資料庫的操作。
• （2） 二進位日誌在啟動MySQL伺服器後開始記錄，並在檔案達到max_binlog_size所設定的大小或者接收到flush-logs命令後重新建立新的記錄檔。
• （3） 只需要定時執行flush-logs方法重新建立新的日誌，產生二進位檔案序列，並及時把這些日誌儲存到安全的地方就完成了一個時間段的增量備份。

實驗環境：

CentOS7.3上的MySQL-5.7.17

一、 增量備份恢複1. 建立資料庫、資料表並添加資料

#systemctl stop firewalld.service           //關閉防火牆#setenforce 0                           //關閉增強安全功能#systemctl restart mysqld.service       //重啟mysql#mysql –u root –p                   //登入進入mysql>create database school;                //建立資料庫school>use school;                            //進入school>create table info (name varchar(10),score decimal(5,2));   //建立資料表info>insert into info (name,score) values (‘zhangsan’,88);      //向info中插入資料>insert into info (name,score) values (‘lisi’,88);          >select * from info;                                    //查看資料表info中資料>exit

2.修改mysql設定檔，開啟二進位日誌功能，並重啟mysql服務

#vim /etc/my.cnf[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1log-bin=mysql-bin                       //添加二進位日誌功能#systemctl restart mysql.service            //重啟mysql服務

3.使用mysqldump備份school資料庫

#mysqldump –u root –p school > /opt/school.sql

4.執行flush-logs產生新的二進位檔案

#mysqladmin –u root –p flush-logs#ls /usr/local/mysql/data               //產生了新的二進位檔案mysql-bin.000002

5.添加test01資料並執行flush-logs產生新的二進位檔案

#mysql –u root –p>use school;>insert into info (name,score) values (‘test01’,60);>exit#mysqladmin –u root –p flush-logs#ls

6.添加test02資料並執行flush-logs產生新的二進位檔案

#mysql –u root –p>use school;>insert into info (name,score) values (‘test02’,60);>exit#mysqladmin –u root –p flush-logs#ls

7.類比誤操作刪除test01、test02

#mysql –u root –p>use school;>delete from info where name=’test01’;>delete from info where name=’test02’;>exit

8.使用二進位檔案進行恢複操作，需要注意的是恢複的順序，要先恢複最先產生的二進位檔案，然後依次執行。

#mysqlbinlog --no-defaults mysql-bin.000002 | mysql –u root –p#mysql –u root –p>use school;>select * from info;                //test01已經恢複，可知test01刪除記錄儲存在mysql-bin.000002中>exit#mysqlbinlog –no-defaults mysql-bin.000003 | mysql –u root –p#mysql  -u root –p>use school;>select * from info;                //test02已恢複，test02的刪除記錄儲存在mysql-bin.000003中>exit

9.查看二進位變更檔案內容所用命令為

#mysqlbinlog --no-defaults --base64-output=decode-rows –v mysql-bin.000002

二．mysql基於時間點的恢複1.先建立好school資料庫，info表，在表中增加資料name=’tom’,score=88;name=’jerry’,score=882.修改mysql設定檔，開啟二進位日誌功能，並重啟mysql服務

#vim /etc/my.cnf[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 1log-bin=mysql-bin#systemctl restart mysql.service

3.進行完全備份及增量備份

#mysqldump –u root –p school > /opt/school.sql              //進行完全備份#mysqladmin –u root –p flush-logs                           //進行增量備份產生mysql-bin.000002

4.類比誤操作，添加test01，刪除tom，添加test02

>insert into info(name,score) values (‘test01’,88);>delete from info where name=’tom’;>insert into info(name,score) values (‘test02’,88);

5.添加增量備份

#mysqladmin –u root –p flush-logs                           //產生增量備份mysql-bin.000003

6.利用二進位變更檔案查看具體操作，並匯入建立文字文件方便管理

#mysqlbinlog --no-defaults --base64-output=decode-rows –v mysql-bin.000002 > /opt/info.txt#vim /opt/info.txt

7.找到對應操作的時間標記和位置標記

• 180705 9:55:42 錯誤操作時間 --stop-datetime
• 180705 9:55:49 正確操作時間 --start-datetime
• at 563 上一次正確動作節點 --stop-position
• at 660 下一次正確動作節點 --start-position

8.先利用完全備份恢複tom和jerry的資料

>drop table info;#mysql –u root –p school < /opt/school.sql>mysql –u root –p>use school;>show tables;>select * from info;>exit

9.基於時間點的恢複

#mysqlbinlog –no-defaults –stop-datetime=’2018-07-05 9:55:42’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p             //注意時間格式需要用-串連

進入info後可以發現test01已恢複

##mysqlbinlog –no-defaults –start-datetime=’2018-07-05 9:55:49’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p

進入info可發現test02已恢複，從而基於時間點的恢複完成

三．基於位置的恢複1.先準備好誤作業環境，將test01，test02環境

>delete from info where name=’test01’;>delete from info where name=’test02’;

2.基於位置的恢複

#mysqlbinlog –no-defaults –stop-position =’563’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p                   //上一次正確動作節點#mysqlbinlog –no-defaults –start-position=’660’ /usr/local/mysql/data/mysql-bin.000002 | mysql –u root –p                   //下一次正確動作節點

進入school的info中再次查看，可以發現test01和test02已經恢複。

MySQL增量備份恢複和基於時間點與位置的恢複