備份與恢復(fù)MySQL數(shù)據(jù)庫(kù)的三種方法介紹

下文給大家?guī)黻P(guān)于備份與恢復(fù)MySQL數(shù)據(jù)庫(kù)的三種方法介紹，感興趣的話就一起來看看這篇文章吧，相信看完備份與恢復(fù)MySQL數(shù)據(jù)庫(kù)的三種方法介紹對(duì)大家多少有點(diǎn)幫助吧。

MySQL數(shù)據(jù)庫(kù)的備份與恢復(fù)的三種方法

1.利用mysqldump實(shí)現(xiàn)從邏輯角度完全備份mysql，配合二進(jìn)制日志備份實(shí)現(xiàn)增量備份

2.利用lvs快照從物理角度實(shí)現(xiàn)幾乎熱備的完全備份，配合二進(jìn)制日志備份實(shí)現(xiàn)增量備份

3.利用percona公司的xrabackup實(shí)現(xiàn)完全熱備份與增量熱備份

實(shí)驗(yàn)環(huán)境：RHEL5.8 ,SElinux關(guān)閉，MySQL是tar包初始化安裝版本5.5.28

一.測(cè)試環(huán)境準(zhǔn)備

1.1 mysql的安裝就不說了，見http://laoguang.blog.51cto.com/6013350/1039208

1.2 編緝/etc/my.cnf把二進(jìn)制日志存放目錄改到其它非數(shù)據(jù)目錄,innodb每表一文件

建立一目錄用于存放二進(jìn)制日志

mkdir /mybinlog

chown mysql:mysql /mybinlog

修改my.cnf

vim /etc/my.cnf

log-bin=/mybinlog/mysql-bin    ##二進(jìn)制日志目錄及文件名前綴

innodb_file_per_table = 1      ##啟用InnoDB表每表一文件，默認(rèn)所有庫(kù)使用一個(gè)表空間

啟動(dòng)mysqld

service mysqld start

1.3 創(chuàng)建一個(gè)測(cè)試庫(kù)與測(cè)試表

mysql> create database benet;

mysql> use benet；

mysql> create table linux  (id tinyint auto_increment primary key,name char(10));

mysql> insert into linux (name) values ('apache'),('nginx'),('php');

1.4 創(chuàng)建用于存放備份的目錄

mkdir /myback

chown -R mysql:mysql /myback  

二,用mysqldump實(shí)現(xiàn)備份

2.1 mysqldump用來溫備，所以我們得為所有庫(kù)加讀鎖,并且滾動(dòng)一下二進(jìn)制日志，并記錄當(dāng)前二進(jìn)制文件位置

mysqldump --all-databases --lock-all-tables  --routines --triggers --master-data=2 \

--flush-logs > /myback/2012-12-3.19-23.full.sql

--all-databases 備份所有庫(kù)

--lock-all-tables 為所有表加讀鎖

--routines 存儲(chǔ)過程與函數(shù)

--triggers 觸發(fā)器

--master-data=2 在備份文件中記錄當(dāng)前二進(jìn)制日志的位置，并且為注釋的，1是不注釋掉在主從復(fù)制中才有意義

--flush-logs 日志滾動(dòng)一次

查看有沒有備份成功，有沒有啟用新二進(jìn)制的日志，查看備份的文件中有沒有記錄完整備份后二進(jìn)制的位置

備份二進(jìn)制日志

cp /mybinlog/mysql-bin.000001 /myback/2012-12-3.19-23.full.00001

2.2 模擬數(shù)據(jù)庫(kù)意外損壞，測(cè)試完整恢復(fù)

rm -rf /data/mydata/*  

rm -rf  /mybinlog/*

初始化mysql并啟動(dòng)mysql

cd /usr/local/mysql

./scripts/mysql_install_db --user=mysql--datadir=/data/mydata

rm -rf /mybinlog/* ##因?yàn)槲覀儾皇侨鲁跏蓟?，可能?huì)有報(bào)錯(cuò)的二進(jìn)制日志，我們不需要

service mysqld start  ##啟動(dòng)時(shí)會(huì)重新生成新的二進(jìn)制日志的

恢復(fù)到備份狀態(tài),備份前先關(guān)閉對(duì)恢復(fù)過程的二進(jìn)制日志記錄，因?yàn)橛涗浕謴?fù)語句是毫無意義的

mysql> set global sql_log_bin=0;

mysql < /myback/2012-12-3.19-23.full.sql ##如果有賬號(hào)密碼記的-u -h哦

打開記錄并查看恢復(fù)狀況

mysql> set global sql_log_bin=1;

mysql> show databases;

打開二進(jìn)制記錄并查看恢復(fù)狀況

mysql> set global sql_log_bin=1;

mysql> show databases;

2.3 模擬一種場(chǎng)景，我往linux表中新添加了數(shù)據(jù)，然后不小心將這個(gè)表刪了，我們要恢復(fù)到刪除之前的狀態(tài)，并且新加的數(shù)據(jù)還存在。

2.3.1 新增數(shù)據(jù)

mysql> use benet;

mysql> insert into linux  (name) values ('haddop'), ('mysql');

mysql> drop table linux;

mysql> show master status;   ##查看當(dāng)前所在二進(jìn)制日志中的位置

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |     9005 |              |                  |  

+------------------+----------+--------------+------------------+

我們先恢復(fù)完整數(shù)據(jù)，再恢復(fù)完整備份后到刪除之前的數(shù)據(jù)，對(duì)應(yīng)二進(jìn)制日志就是完整備份后的二進(jìn)制日志位置到刪除表之前的位置

2.3.2 先恢復(fù)完整備份，同樣恢復(fù)過程不要記錄日志

mysql > set global sql_log_bin=0;

mysql < /myback/2012-12-3.19-23.full.sql

2.3.3 查看刪除表時(shí)的記錄位置

mysqlbinlog /mybinlog/mysql-bin.000001  

# at 8893

#121202 14:14:07 server id 1  end_log_pos 9005  Query  thread_id=5exec_time=0error_code=0

SET TIMESTAMP=1354428847/*!*/;

DROP TABLE `linux` /* generated by server */

/*!*/;

DELIMITER ;

# End of log file

2.3.4 由上圖可知?jiǎng)h除是在8893時(shí)做的，將二進(jìn)制文件中完整備份到刪除表之前的記錄導(dǎo)出

mysqlbinlog --stop-position=8893 /mybinlog/mysql-bin.000001  > /tmp/change.sql

--start-position 指定從哪開始導(dǎo)出二進(jìn)制日志

--stop-position 指定到哪結(jié)束

--start-datetime 從哪個(gè)時(shí)間開始格式如"2005-12-25 11:25:56"

--stop-datetime 到哪個(gè)時(shí)間結(jié)束

由于這個(gè)二進(jìn)制日志是我們完整恢復(fù)后才啟用的，所以我們直接從頭開始即可，如果你的二進(jìn)制日志很多，請(qǐng)查看完整備份中記錄的備份時(shí)的位置，從那開始到刪除之前即可

將這段二進(jìn)制記錄應(yīng)用到mysql的庫(kù)中

mysql < /tmp/change.sql

進(jìn)入數(shù)據(jù)庫(kù)查看數(shù)據(jù)有沒有恢復(fù)

mysql> select * from linux;

基于mysqldump通常我們就是完整備份+二進(jìn)制日志來進(jìn)行恢復(fù)的。

三，利用lvm的快照來備份MySQL

要求你的MySQL的數(shù)據(jù)目錄必須在lvm卷上，下面來演示過程

3.1 建立lvm卷組，掛載到/data/mydata下，這個(gè)我就不演示了

3.2 初始化MySQL時(shí)將數(shù)據(jù)目錄指向/data/mydata,安裝過程見上鏈接

3.3 同樣如第一步那樣準(zhǔn)備環(huán)境

3.4 在MySQL中為所有表加讀鎖，不要關(guān)閉終端，否則鎖將失效,滾動(dòng)日志

mysql> flush tables with read lock;

mysql> flush logs;

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      107 |              |                  |  

+------------------+----------+--------------+------------------+

3.5 另開一終端速度建立快照,我的那個(gè)卷組是/dev/myvg/mydata

lvcreate -L 200M -n mysql-snap -s -p r /dev/myvg/mydata  

3.4 速度釋放讀鎖

mysql> unlock  tables;

3.5 掛載快照，拷備出來，卸載快照，刪除快照

mount /dev/myvg/mysql-snap /mnt

mkdir /myback/lvm

cp -pR /mnt/* /myback/lvm  

umount /mnt

lvremove /dev/myvg/mysql-snap

3.6 就這樣一次完整備份就完成了，下面來測(cè)試能否正常使用

servivce mysqld stop

rm -R /data/mydata/*  

cp -Rp /myback/lvm/* /data/mydata

service mysqld start  ##如果能正常啟動(dòng)代表沒有問題，起不來請(qǐng)看數(shù)據(jù)目錄權(quán)限

3.7 如果在完整備份后MySQL出現(xiàn)故障，與mysqldump一樣，先恢復(fù)上次的完整備份，再利用二進(jìn)制日志恢復(fù)，二進(jìn)制恢復(fù)再啰嗦一遍，找到完整備份時(shí)的二進(jìn)制位置，把從那時(shí)到故障前的日志用mysqlbinlog導(dǎo)出來，然后批處理方式導(dǎo)入到MySQL中。這個(gè)同mysqldump中實(shí)驗(yàn)一致就不重復(fù)了。

用lvm的快照來備份速度是非?？斓模?guī)缀鯚醾?，恢?fù)也很快速，操作也簡(jiǎn)單，完整恢復(fù)后再將相應(yīng)二進(jìn)制恢復(fù)即可。

四:基于xtrabackup來完全備份，增量備份，熱備份MySQL

下載地址:http://www.percona.com/software/percona-xtrabackup

4.1 下載安裝xtrabackup，我用的是percona-xtrabackup-2.0.3-470.rhel5.i386.rpm

yum install perl-DBD-MySQL

rpm -ivh percona-xtrabackup-2.0.3-470.rhel5.i386.rpm

4.2 MySQL基本環(huán)境與第一步的一致

4.3 為備份建立一個(gè)只有備份權(quán)限的用戶

mysql> create user 'percona'@'localhost' identified by 'redhat';

mysql> revoke all privileges,grant option from 'percona'@'localhost';

mysql> grant reload,lock tables,replication client on *.* to 'percona'@'localhost';

mysql> flush privileges;

4.4 完整備份一次MySQL

innobackupex --host=locahost --user=percona --password=redhat --defaults-file=/usr/local/mysql/my.cnf  /myback/

數(shù)據(jù)會(huì)完整備份到/myback/中目錄名字為當(dāng)前的日期，extrabackup會(huì)備份所有的InnoDB表，MyISAM表只是復(fù)制表結(jié)構(gòu)文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相關(guān)文件，同時(shí)還會(huì)備份觸發(fā)器和數(shù)據(jù)庫(kù)配置信息相關(guān)的文件。除了保存數(shù)據(jù)外還生成了一些extrabackup需要的數(shù)據(jù)文件

1)xtrabackup_checkpoints 備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號(hào))范圍信息；每個(gè)InnoDB頁(通常為16k大小)都會(huì)包含一個(gè)日志序列號(hào)，即LSN。LSN是整個(gè)數(shù)據(jù)庫(kù)系統(tǒng)的系統(tǒng)版本號(hào)，每個(gè)頁面相關(guān)的LSN能夠表明此頁面最近是如何發(fā)生改變的。

2)xtrabackup_binlog_info mysql云服務(wù)器當(dāng)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置。

3)xtrabackup_binlog_pos_innodb  二進(jìn)制日志文件及用于InnoDB或XtraDB表的二進(jìn)制日志文件的當(dāng)前position。

4)xtrabackup_binary  備份中用到的xtrabackup的可執(zhí)行文件；

5)backup-my.cnf 備份命令用到的配置選項(xiàng)信息；

4.4 測(cè)試恢復(fù)MySQL,用extrabackup來完整恢復(fù)

service mysqld stop

rm -Rf /data/mydata

innobackupex --apply-log /myback/2012-12-02_20-06-12/

--apply-log 的意義在于把備份時(shí)沒commit的事務(wù)撤銷，已經(jīng)commit的但還在事務(wù)日志中的應(yīng)用到數(shù)據(jù)庫(kù)

innobackupex --copy-back /myback/2012-12-02_20-06-12/

--copy-back數(shù)據(jù)庫(kù)恢復(fù)，后面跟上備份目錄的位置

chown -R mysql:mysql /data/mydata  

service mysqld start ##如果能啟動(dòng)代表恢復(fù)正常

4.5 我們來實(shí)驗(yàn)一下增量備份

4.5.1 在表中新增一些數(shù)據(jù)

mysql> insert into linux (name) values ('tomcat'), ('memcache'), ('varnish');

4.5.2 增量備份

innobackupex --user=percona--password=redhat --incremental \

--incremental-basedir=/myback/2012-12-02_20-06-12/ /myback/

--incremental 指定是增量備份

--incremental-basedir 指定基于哪個(gè)備份做增量備份，最后是增量備份保存的目錄

增量備份只能對(duì)InnoDB引擎做增量備份，對(duì)MyISAM的表是完全復(fù)制

4.6 測(cè)試增量備份恢復(fù)

service mysqld stop

rm -Rf /data/mydata/*

innobackupex --apply-log --redo-only /myback/2012-12-02_20-06-12/

--redo-only 指的是把備份時(shí)commit的但還在事務(wù)日志中的應(yīng)用到時(shí)數(shù)據(jù)，但是還沒提交的不撤消，

因?yàn)檫@個(gè)事務(wù)可能在增量備份中提交，假如的撤消了增量備份中就提交不，因?yàn)槭聞?wù)已經(jīng)不完整

將增量備份全并到完整備份中去

innobackupex --apply-log /myback/2012-12-02_20-06-12/ \

--incremental-dir=/myback/2012-12-02_20-28-49/

/myback/2012-12-02_20-06-12/ 這個(gè)是完整備份的目

--incremental-dir 后跟的是增量備份的目錄

這個(gè)會(huì)使增量備份中的的數(shù)據(jù)合并到完整備份中，如果還有增量備份，繼續(xù)合并，恢復(fù)時(shí)恢復(fù)完整備份即可

恢復(fù)數(shù)據(jù)，并起動(dòng)MySQL

innobackupex --copy-back /myback/2012-12-02_20-06-12/

chown -R mysql:mysql /data/mydata

service mysqld start

查看數(shù)據(jù)有沒丟失

如果在增量備份后數(shù)據(jù)庫(kù)出現(xiàn)故障，我們需要通過完整備份+到現(xiàn)在為止的所有增量備份+最后一次增量備份到現(xiàn)在的二進(jìn)制日志來恢復(fù)。

看了以上關(guān)于備份與恢復(fù)MySQL數(shù)據(jù)庫(kù)的三種方法介紹，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。