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

muchs.cn

成都創(chuàng)新互聯(lián)專注于溆浦企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站建設(shè),成都商城網(wǎng)站開(kāi)發(fā)。溆浦網(wǎng)站建設(shè)公司,為溆浦等地區(qū)提供建站服務(wù)。全流程按需搭建網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)

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

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

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

 

 

一：我們?cè)谶@里采用的是mysqldump工具進(jìn)行備份：

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

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

1）binlog日志：

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

開(kāi)啟binlog日志的方式：

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

 

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

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

 

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

讓我們看一下：

 

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

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

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

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

 bin-log因?yàn)槭嵌M(jìn)制文件，不能通過(guò)文件內(nèi)容查看命令直接打開(kāi)查看，mysql提供兩種方式查看方式，在介紹之前，我們先對(duì)數(shù)據(jù)庫(kù)進(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ù)庫(kù)

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

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

 

 

接下來(lái)我們重新在生成一個(gè)binlog的日志文件：然后將之前的用戶ID為2（zhangsan）的用戶刪掉，之后在創(chuàng)建一個(gè)用戶為T(mén)om的人員。

#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ù)庫(kù)當(dāng)中還有誰(shuí)存在：

 

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

 

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

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

mysql> show binlog events;

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

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

Query                   //為查詢事件

Table_map                 //為表映射事件

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

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

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

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

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

 

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

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

 

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

恢復(fù)步驟：

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

# cd /usr/local/mysql/data/

# mysqlbinlog  -v mysql-bin.000002

 

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

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

由于之前沒(méi)有做過(guò)全庫(kù)備份，所以要使用所有binlog日志恢復(fù)，所以生產(chǎn)環(huán)境中需要很長(zhǎng)時(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

 

接下來(lái)是見(jiàn)證奇跡的時(shí)候到了；接下來(lái)往下看

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

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ù)了，說(shuō)明這次備份成功了，在這里為大家介紹幾個(gè)命令讓大家參考一下

mysqlbinlog 選項(xiàng)示例

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

--start-datetime

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

--stop-datetime

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

--start-position        

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

--stop-position

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

剛才我們使用的mysqlbinlog記下來(lái)為大家接單的介紹下;

語(yǔ)法格式： mysqlbinlog [options] log_file ...

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

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

二進(jìn)制日志文件的格式包含行模式、語(yǔ)句模式和混合模式（也即有服務(wù)器決定在什么情況下記錄什么類型的日志），基于語(yǔ)句的日志中事件信息包含執(zhí)行的語(yǔ)句等，基于行的日志中事件信息包含的是行的變化信息等?；旌夏Ｊ降娜罩局袃煞N類型的事件信息都會(huì)記錄。

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

先切換到binlog所在的目錄下

#mysqlbinlog mysql-bin.000001

#mysqlbinlog -v mysql-bin.000001

#mysqlbinlog -vv mysql-bin.000001

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

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

 

2）mysqldump工具的介紹：

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

mysqldump主要用于數(shù)據(jù)量很小的時(shí)候可以備份，當(dāng)數(shù)據(jù)量龐大的時(shí)候就顯得力不存心了，就不建議使用mysqldump工具進(jìn)行備份，后續(xù)會(huì)為大家?guī)?lái)新的備份工具。

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

 

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

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

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

 

 

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

 

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

 

 

 

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

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

檢查開(kāi)啟binlog

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

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

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

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

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)在開(kāi)始進(jìn)入備份的工作環(huán)節(jié)：

方案：mysqldump全庫(kù)備份+binlog還原

1、mysqldump備份方案：

 

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

2、備份步驟

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

# mkdir /opt/mysqlbackup

# mkdir /opt/mysqlbackup/daily

 

（2）全庫(kù)備份

這里我們模擬周一的完整備份數(shù)據(jù)庫(kù)任務(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全庫(kù)備份之前的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'"

 

 

 

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

 

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

mysql> commit;

 

 

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

 

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

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

使用mysqldump的備份進(jìn)行全庫(kù)恢復(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ù)回來(lái)了，但備份后產(chǎn)生的數(shù)據(jù)卻丟失了（tom3）所以還得利用binlog進(jìn)一步還原

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

先查看下binlog的信息：

 

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

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

 

 

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

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

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

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

 

 

通過(guò)mysqlbinlog命令所顯示的結(jié)果可以看到誤操作delete的開(kāi)始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事件位置作為開(kāi)始，即跳過(guò)刪除事件，恢復(fù)刪除事件之后對(duì)數(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日志文件的作用，以及打開(kāi)方式，另外里面包括了我們對(duì)數(shù)據(jù)庫(kù)的修改sql語(yǔ)句，它是以每一個(gè)單獨(dú)的事件存儲(chǔ)在里面的

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

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

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

 

 

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

 

 

 

 

分享題目：MySQL數(shù)據(jù)庫(kù)的災(zāi)難備份與恢復(fù)
轉(zhuǎn)載源于：http://muchs.cn/article38/ihjcpp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站收錄、網(wǎng)站內(nèi)鏈、營(yíng)銷型網(wǎng)站建設(shè)、全網(wǎng)營(yíng)銷推廣、標(biāo)簽優(yōu)化、域名注冊(cè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)