MySQL數據庫的災難備份與恢復

http://xiaorenwutest.blog.51cto.com

             MySQL數據庫的災難恢復與備份

數據庫對于公司來說是重中之重；記錄著公司的龐大數據，關系到公司的財產，以及客戶的資料，如果一旦丟失將會為公司造成無法估量的損失。

但是如果做好備份工作可以避免這種情況的發生；所以說作為一名合格的DBA人員來說掌握數據庫的備份和回復是必不可少的技能。另外在工作當中公司也會進行一些計劃：比如說數據庫的災難與恢復的測試。今天為大家帶來的就是關于MySQL的備份與恢復。

一：我們在這里采用的是mysqldump工具進行備份：

mysqldump備份結合binlog日志恢復

這里我們需要知道的是mysql備份一般采用的是全庫備份加日志備份的方式，比如說每周執行一次全庫備份，每天執行一次二進制備份，這樣當MySQL發生故障的時候可以還原到故障之前的任意位置和時間。

1）binlog日志：

相信大家都知道binlog日志是用來記錄數據庫發生了改變的日志；比如增、改、刪等sql語句，另外在主從復制的時候也需要開啟此日志。

開啟binlog日志的方式：

/etc/my.cnf主配置文件當中開啟：

之后保存文件，重啟MySQL服務

讓我們看一下，是否已經開啟了binlog服務呢？

其中；filename參數指定二級制文件的文件名，其形式為filename.number，number的形式為000001、000002等。每次重啟mysql服務或運行mysql> flush logs;都會生成一個新的二進制日志文件，這些日志文件的number會不斷地遞增。除了生成上述的文件外還會生成一個名為filename.index的文件。這個文件中存儲所有二進制日志文件的清單又稱為二進制文件的索引

讓我們看一下：

在這里我們對binlog日志進行個總結：

1：記錄數據庫發生改變的sql語句

2：可以主從復制

3：最主要的是可以恢復丟失的數據

 bin-log因為是二進制文件，不能通過文件內容查看命令直接打開查看，mysql提供兩種方式查看方式，在介紹之前，我們先對數據庫進行一下增刪改的操作，否則log里邊數據有點空。 

#mysql  -uroot -p -e "reset master"=========>刪除所有的二進制文件，從新生成一個新的二進制文件

#mysql  -uroot -p -e "create database test"=============>創建一個test的數據庫

#mysql -uroot -p -e "use test;create table tb1(id int primary key auto_increment,name varchar(20))"==========>在test數據庫當中新建表tb1；ID為自動增長和name

#mysql -uroot -p -e "insert into test.tb1(name) values('lisi')"==========>在tb1表中插入用戶lisi

#mysql -uroot -p -e "insert into test.tb1(name) values('zhangsan')"==========>再次插入用戶zhangsan

讓我們看一下上面的操作是否成功

接下來我們重新在生成一個binlog的日志文件：然后將之前的用戶ID為2（zhangsan）的用戶刪掉，之后在創建一個用戶為Tom的人員。

#mysql -uroot -p -e "flush logs"

#mysql -uroot -p -e "delete from test.tb1 where id=2"

#mysql -uroot -p -e "insert into test.tb1(name) values('tom')"

# mysql -uroot -p -e "select * from test.tb1"

現在讓我們看一下數據庫當中還有誰存在：

接下來讓我們看一下我們的二進制日志文件當中的內容；以及如何進行恢復：

可以看到現在我們只有兩個二進制日志文件：

接下來我們查看下二進制日志文件當中的信息

mysql> show binlog events;

默認顯示可找到的第一個二進制日志文件中的事件，包含了日志文件名、事件的開始位置、事件類型、結束位置、信息等內容

Format_desc    |             //此事件為格式描述事件

Query                   //為查詢事件

Table_map                 //為表映射事件

Write_rows                //為我們執行的insert事件

Xid                          //Xid時間是自動提交事務的動作

Rotate                    //為日志輪換事件，是我們執行flush logs開啟新日志文件引起的。

剛才查看的是默認的二進制文件為000001；接下來我們查看下第二個二進制文件

mysql> show binlog events in 'mysql-bin.000002';

另外換可以通過show binlog events in 'mysql-bin.000002' from 219 limit 1,3;語句查看從219到301的數據，這里不在演示，在文章的后面我會為大家介紹幾條sql的語句

接下來我們開始進行數據的恢復{恢復之前刪掉的ID=2的用戶}

無論是本地二進制日志文件還是遠程服務器上的二進制日志文件，無論是行模式、語句模式還是混合模式的二進制日志文件，被mysqlbinlog工具解析后都可直接應用與MySQL Server進行基于時間點、位置或數據庫的恢復。

恢復步驟：

首先查看binlog文件：從中找到delete from test.tb1 where id=2這條語句

# cd /usr/local/mysql/data/

# mysqlbinlog  -v mysql-bin.000002

從中可以看出delete事件發生position是287，事件結束position是416

恢復流程：直接用bin-log日志將數據庫恢復到刪除位置287前,然后跳過故障點,再進行恢復下面所有的操作,命令如下

由于之前沒有做過全庫備份，所以要使用所有binlog日志恢復，所以生產環境中需要很長時間恢復，導出相關binlog文件

#mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin.000001.sql

#mysqlbinlog --stop-position=287 /usr/local/mysql/data/mysql-bin.000002 > /opt/287.sql

#mysqlbinlog --start-position=416 /usr/local/mysql/data/mysql-bin.000002 > /opt/416.sql

接下來是見證奇跡的時候到了；接下來往下看

刪除test數據庫

mysql>drop database test;

利用binlog恢復數據

#mysql -uroot -p< /opt/mysql-bin.000001.sql

#mysql -uroot -p< /opt/287.sql

# 恢復完成后，我們檢查下表的數據是否完整

zhangsan用戶已經成功的恢復了，說明這次備份成功了，在這里為大家介紹幾個命令讓大家參考一下

mysqlbinlog 選項示例

常見的選項有以下幾個：

--start-datetime

從二進制日志中讀取指定時間戳或者本地計算機時間之后的日志事件。

--stop-datetime

從二進制日志中讀取指定時間戳或者本地計算機時間之前的日志事件。

--start-position        

從二進制日志中讀取指定position 事件位置作為開始。

--stop-position

從二進制日志中讀取指定position 事件位置作為事件截至

剛才我們使用的mysqlbinlog記下來為大家接單的介紹下;

語法格式： mysqlbinlog [options] log_file ...

輸出內容會因日志文件的格式以及mysqlbinlog工具使用的選項不同而略不同。

mysqlbinlog的可用選項可參考man手冊。

二進制日志文件的格式包含行模式、語句模式和混合模式（也即有服務器決定在什么情況下記錄什么類型的日志），基于語句的日志中事件信息包含執行的語句等，基于行的日志中事件信息包含的是行的變化信息等。混合模式的日志中兩種類型的事件信息都會記錄。

為了便于查看記錄了行變化信息的事件在當時具體執行了什么樣的SQL語句可以使用mysqlbinlog工具的-v（--verbose）選項，該選項會將行事件重構成被注釋掉的偽SQL語句，如果想看到更詳細的信息可以將該選項給兩次如-vv，這樣可以包含一些數據類型和元信息的注釋內容，如

先切換到binlog所在的目錄下

#mysqlbinlog mysql-bin.000001

#mysqlbinlog -v mysql-bin.000001

#mysqlbinlog -vv mysql-bin.000001

另外mysqlbinlog和可以通過--read-from-remote-server選項從遠程服務器讀取二進制日志文件，這時需要一些而外的連接參數，如-h，-P，-p，-u等，這些參數僅在指定了--read-from-remote-server后有效。

另外其他的一些參數可以通過mysqlbinlog --help查看如果看更詳細的可以使用man手冊

2）mysqldump工具的介紹：

主要是用來備份和數據轉移的工具，主要產生一系列的sql語句，可以分裝到文件，而分裝的這個文件主要用于重建數據庫所需的sql命令，可以用來實現輕量級的快速遷移或恢復數據庫。mysqldump 是將數據表導成 SQL 腳本文件，在不同的 MySQL 版本之間升級時相對比較合適，這也是最常用的備份方法。

mysqldump主要用于數據量很小的時候可以備份，當數據量龐大的時候就顯得力不存心了，就不建議使用mysqldump工具進行備份，后續會為大家帶來新的備份工具。

mysqldump可以針對單個表、多個表、單個數據庫、多個數據庫、所有數據庫進行導出的操作

#mysqldump [options] db_name [tbl_name ...] //導出指定數據庫或單個表

#mysqldump [options] --databases db_name ... //導出多個數據庫

#mysqldump [options] --all-databases //導出所有

mysqldump -uroot -p --flush-logs test > /opt/test.sql   //--flush-logs這個選項就會完整備份的時候重新開啟一個新binlog

數據庫的導入

在前面我們介紹了mysql的binlog和mysqldump工具，下面我們來學習如何實現mysqldump全庫備份+binlog的數據恢復

環境準備與備份還原：

檢查開啟binlog

先創建一些原始數據

mysql> reset master;===========清除之前的所有二進制文件，并且生成一個新的二進制文件

mysql> create database test_db;===========創建一個test_db的庫

mysql> use test_db;==================進入test_db庫

mysql> create table tb1(id int primary key auto_increment,name varchar(20));=======創建tb1表

mysql> insert into tb1(name) values('tom1');==============在表中插入數據tom1

mysql> insert into tb1(name) values('tom2');==============在表中插入數據tom2

mysql> commit;===========完成

查看下表中的內容：

前期準備工作已經就緒，現在開始進入備份的工作環節：

方案：mysqldump全庫備份+binlog還原

1、mysqldump備份方案：

每周一凌晨1點全庫備份

2、備份步驟

（1） 創建備份目錄

# mkdir /opt/mysqlbackup

# mkdir /opt/mysqlbackup/daily

（2）全庫備份

這里我們模擬周一的完整備份數據庫任務

#mysqldump -uroot -p --flush-logs test_db > /opt/mysqlbackup/test_db_2017_06_24.sql

[root@localhost data]# ls -l /opt/mysqlbackup/

-rw-r--r--. 1 root root 1871 Sep 13 21:06 test_db_2017_06_24.sql

備份mysqldump全庫備份之前的binlog日志文（注：生產環境中可能不只一個binlog文件）

# cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/

# mysql -uroot -p -e "purge binary logs to 'mysql_bin.000002'"

接下來模擬操作失誤，將數據修改錯誤：

mysql> insert into tb1(name) values('tom3');

mysql> commit;

備份自mysqldump之后的binlog日志文件

上面的模擬的誤操作是刪除了id=1的記錄

（3）現在我們使用mysqldump的全庫備份和binlog來恢復數據。

使用mysqldump的備份進行全庫恢復

# mysql -uroot -p test_db < /opt/mysqlbackup/test_db_2017_06_24.sql

查詢一下數據

[root@localhost ~]# mysql -uroot -p -e "select * from test_db.tb1"

從顯示結果可以看到使用mysqldump備份將數據還原到了備份時的狀態，剛才刪除的數據（id=2）恢復回來了，但備份后產生的數據卻丟失了（tom3）所以還得利用binlog進一步還原

因為刪除是在全庫備份后發生的，而mysqldump全庫備份時使用--flush-logs選項，所以只需要分析全庫備份后的binlog即mysql_bin.000002。

先查看下binlog的信息：

查看mysql-bin.000002中的事件，可以看到有刪除事件

mysql> show binlog events in 'mysql_bin.000002';

使用mysqlbinlog 命令可以查看備份的binlog文件的詳細事件。

恢復流程：我們直接用bin-log日志將數據庫恢復到刪除位置前,然后跳過故障點,再進行恢復刪除后的所有操作。

如果想看的在詳細點可以通過

# mysqlbinlog -v /opt/mysqlbackup/daily/mysql_bin.000002語句查看，大家如果還不太了解，這里可以在演示一遍：

通過mysqlbinlog命令所顯示的結果可以看到誤操作delete的開始postion為219，結束position是422。

從二進制日志中讀取指定position=219事件位置作為截至，即把數據恢復到delete刪除前

# mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p

從二進制日志中讀取指定position=422事件位置作為開始，即跳過刪除事件，恢復刪除事件之后對數據的正常操作

#mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -u root -p

查看恢復結果：

# 

從上面顯示可以看出數據恢復到正常狀態

為我們今天的mysql災難備份與恢復做個總結：

1）介紹了binlog日志文件的作用，以及打開方式，另外里面包括了我們對數據庫的修改sql語句，它是以每一個單獨的事件存儲在里面的

2）mysqlbinlog工具,主要用來打開binlog日志的工具，可以查看更加詳細的信息通過-vv選項；另外也可以給binlog日志二進制文件進行備份

3）mysqldump工具；可以用來備份二進制文件，但只適合少量的數據，龐大的數據量就不太適用了

4）其實恢復就是通過二進制文件查看到之前的命令，將刪除或者操作錯誤的命令的那一段事件跳過去而執行其他沒有問題的sql語句。

生產環境中Mysql數據庫的備份是周期性重復的操作，所以通常是要編寫腳本實現，通過crond計劃任務周期性執行備份腳本，這是下次為大家帶來的，有什么不足希望大家多多指教