MySQL數(shù)據(jù)庫的災(zāi)難備份與恢復(fù)

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

             MySQL數(shù)據(jù)庫的災(zāi)難恢復(fù)與備份

數(shù)據(jù)庫對于公司來說是重中之重；記錄著公司的龐大數(shù)據(jù)，關(guān)系到公司的財(cái)產(chǎn)，以及客戶的資料，如果一旦丟失將會為公司造成無法估量的損失。

但是如果做好備份工作可以避免這種情況的發(fā)生；所以說作為一名合格的DBA人員來說掌握數(shù)據(jù)庫的備份和回復(fù)是必不可少的技能。另外在工作當(dāng)中公司也會進(jìn)行一些計(jì)劃：比如說數(shù)據(jù)庫的災(zāi)難與恢復(fù)的測試。今天為大家?guī)淼木褪顷P(guān)于MySQL的備份與恢復(fù)。

一：我們在這里采用的是mysqldump工具進(jìn)行備份：

mysqldump備份結(jié)合binlog日志恢復(fù)

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

1）binlog日志：

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

開啟binlog日志的方式：

/etc/my.cnf主配置文件當(dāng)中開啟：

之后保存文件，重啟MySQL服務(wù)

讓我們看一下，是否已經(jīng)開啟了binlog服務(wù)呢？

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

讓我們看一下：

在這里我們對binlog日志進(jìn)行個(gè)總結(jié)：

1：記錄數(shù)據(jù)庫發(fā)生改變的sql語句

2：可以主從復(fù)制

3：最主要的是可以恢復(fù)丟失的數(shù)據(jù)

 bin-log因?yàn)槭嵌M(jìn)制文件，不能通過文件內(nèi)容查看命令直接打開查看，mysql提供兩種方式查看方式，在介紹之前，我們先對數(shù)據(jù)庫進(jìn)行一下增刪改的操作，否則log里邊數(shù)據(jù)有點(diǎn)空。 

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

#mysql  -uroot -p -e "create database test"=============>創(chuàng)建一個(gè)test的數(shù)據(jù)庫

#mysql -uroot -p -e "use test;create table tb1(id int primary key auto_increment,name varchar(20))"==========>在test數(shù)據(jù)庫當(dāng)中新建表tb1；ID為自動(dòng)增長和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

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

接下來我們重新在生成一個(gè)binlog的日志文件：然后將之前的用戶ID為2（zhangsan）的用戶刪掉，之后在創(chuàng)建一個(gè)用戶為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"

現(xiàn)在讓我們看一下數(shù)據(jù)庫當(dāng)中還有誰存在：

接下來讓我們看一下我們的二進(jìn)制日志文件當(dāng)中的內(nèi)容；以及如何進(jìn)行恢復(fù)：

可以看到現(xiàn)在我們只有兩個(gè)二進(jìn)制日志文件：

接下來我們查看下二進(jìn)制日志文件當(dāng)中的信息

mysql> show binlog events;

默認(rèn)顯示可找到的第一個(gè)二進(jìn)制日志文件中的事件，包含了日志文件名、事件的開始位置、事件類型、結(jié)束位置、信息等內(nèi)容

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

Query                   //為查詢事件

Table_map                 //為表映射事件

Write_rows                //為我們執(zhí)行的insert事件

Xid                          //Xid時(shí)間是自動(dòng)提交事務(wù)的動(dòng)作

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

剛才查看的是默認(rèn)的二進(jìn)制文件為000001；接下來我們查看下第二個(gè)二進(jìn)制文件

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

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

接下來我們開始進(jìn)行數(shù)據(jù)的恢復(fù){恢復(fù)之前刪掉的ID=2的用戶}

無論是本地二進(jìn)制日志文件還是遠(yuǎn)程服務(wù)器上的二進(jìn)制日志文件，無論是行模式、語句模式還是混合模式的二進(jìn)制日志文件，被mysqlbinlog工具解析后都可直接應(yīng)用與MySQL Server進(jìn)行基于時(shí)間點(diǎn)、位置或數(shù)據(jù)庫的恢復(fù)。

恢復(fù)步驟：

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

# cd /usr/local/mysql/data/

# mysqlbinlog  -v mysql-bin.000002

從中可以看出delete事件發(fā)生position是287，事件結(jié)束position是416

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

由于之前沒有做過全庫備份，所以要使用所有binlog日志恢復(fù)，所以生產(chǎn)環(huán)境中需要很長時(shí)間恢復(fù)，導(dǎo)出相關(guān)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

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

刪除test數(shù)據(jù)庫

mysql>drop database test;

利用binlog恢復(fù)數(shù)據(jù)

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

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

# 恢復(fù)完成后，我們檢查下表的數(shù)據(jù)是否完整

zhangsan用戶已經(jīng)成功的恢復(fù)了，說明這次備份成功了，在這里為大家介紹幾個(gè)命令讓大家參考一下

mysqlbinlog 選項(xiàng)示例

常見的選項(xiàng)有以下幾個(gè)：

--start-datetime

從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之后的日志事件。

--stop-datetime

從二進(jìn)制日志中讀取指定時(shí)間戳或者本地計(jì)算機(jī)時(shí)間之前的日志事件。

--start-position        

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

--stop-position

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

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

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

輸出內(nèi)容會因日志文件的格式以及mysqlbinlog工具使用的選項(xiàng)不同而略不同。

mysqlbinlog的可用選項(xiàng)可參考man手冊。

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

為了便于查看記錄了行變化信息的事件在當(dāng)時(shí)具體執(zhí)行了什么樣的SQL語句可以使用mysqlbinlog工具的-v（--verbose）選項(xiàng)，該選項(xiàng)會將行事件重構(gòu)成被注釋掉的偽SQL語句，如果想看到更詳細(xì)的信息可以將該選項(xiàng)給兩次如-vv，這樣可以包含一些數(shù)據(jù)類型和元信息的注釋內(nèi)容，如

先切換到binlog所在的目錄下

#mysqlbinlog mysql-bin.000001

#mysqlbinlog -v mysql-bin.000001

#mysqlbinlog -vv mysql-bin.000001

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

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

2）mysqldump工具的介紹：

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

mysqldump主要用于數(shù)據(jù)量很小的時(shí)候可以備份，當(dāng)數(shù)據(jù)量龐大的時(shí)候就顯得力不存心了，就不建議使用mysqldump工具進(jìn)行備份，后續(xù)會為大家?guī)硇碌膫浞莨ぞ摺?/span>

mysqldump可以針對單個(gè)表、多個(gè)表、單個(gè)數(shù)據(jù)庫、多個(gè)數(shù)據(jù)庫、所有數(shù)據(jù)庫進(jìn)行導(dǎo)出的操作

#mysqldump [options] db_name [tbl_name ...] //導(dǎo)出指定數(shù)據(jù)庫或單個(gè)表

#mysqldump [options] --databases db_name ... //導(dǎo)出多個(gè)數(shù)據(jù)庫

#mysqldump [options] --all-databases //導(dǎo)出所有

mysqldump -uroot -p --flush-logs test > /opt/test.sql   //--flush-logs這個(gè)選項(xiàng)就會完整備份的時(shí)候重新開啟一個(gè)新binlog

數(shù)據(jù)庫的導(dǎo)入

在前面我們介紹了mysql的binlog和mysqldump工具，下面我們來學(xué)習(xí)如何實(shí)現(xiàn)mysqldump全庫備份+binlog的數(shù)據(jù)恢復(fù)

環(huán)境準(zhǔn)備與備份還原：

檢查開啟binlog

先創(chuàng)建一些原始數(shù)據(jù)

mysql> reset master;===========清除之前的所有二進(jìn)制文件，并且生成一個(gè)新的二進(jìn)制文件

mysql> create database test_db;===========創(chuàng)建一個(gè)test_db的庫

mysql> use test_db;==================進(jìn)入test_db庫

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

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

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

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

查看下表中的內(nèi)容：

前期準(zhǔn)備工作已經(jīng)就緒，現(xiàn)在開始進(jìn)入備份的工作環(huán)節(jié)：

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

1、mysqldump備份方案：

每周一凌晨1點(diǎn)全庫備份

2、備份步驟

（1） 創(chuàng)建備份目錄

# mkdir /opt/mysqlbackup

# mkdir /opt/mysqlbackup/daily

（2）全庫備份

這里我們模擬周一的完整備份數(shù)據(jù)庫任務(wù)

#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日志文（注：生產(chǎn)環(huán)境中可能不只一個(gè)binlog文件）

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

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

接下來模擬操作失誤，將數(shù)據(jù)修改錯(cuò)誤：

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

mysql> commit;

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

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

（3）現(xiàn)在我們使用mysqldump的全庫備份和binlog來恢復(fù)數(shù)據(jù)。

使用mysqldump的備份進(jìn)行全庫恢復(fù)

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

查詢一下數(shù)據(jù)

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

從顯示結(jié)果可以看到使用mysqldump備份將數(shù)據(jù)還原到了備份時(shí)的狀態(tài)，剛才刪除的數(shù)據(jù)（id=2）恢復(fù)回來了，但備份后產(chǎn)生的數(shù)據(jù)卻丟失了（tom3）所以還得利用binlog進(jìn)一步還原

因?yàn)閯h除是在全庫備份后發(fā)生的，而mysqldump全庫備份時(shí)使用--flush-logs選項(xiàng)，所以只需要分析全庫備份后的binlog即mysql_bin.000002。

先查看下binlog的信息：

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

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

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

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

如果想看的在詳細(xì)點(diǎn)可以通過

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

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

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

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

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

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

查看恢復(fù)結(jié)果：

# 

從上面顯示可以看出數(shù)據(jù)恢復(fù)到正常狀態(tài)

為我們今天的mysql災(zāi)難備份與恢復(fù)做個(gè)總結(jié)：

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

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

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

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

生產(chǎn)環(huán)境中Mysql數(shù)據(jù)庫的備份是周期性重復(fù)的操作，所以通常是要編寫腳本實(shí)現(xiàn)，通過crond計(jì)劃任務(wù)周期性執(zhí)行備份腳本，這是下次為大家?guī)淼?，有什么不足希望大家多多指?/span>