備份與恢復MySQL數據庫的三種方法介紹

下文給大家帶來關于備份與恢復MySQL數據庫的三種方法介紹，感興趣的話就一起來看看這篇文章吧，相信看完備份與恢復MySQL數據庫的三種方法介紹對大家多少有點幫助吧。

MySQL數據庫的備份與恢復的三種方法

1.利用mysqldump實現從邏輯角度完全備份mysql，配合二進制日志備份實現增量備份

2.利用lvs快照從物理角度實現幾乎熱備的完全備份，配合二進制日志備份實現增量備份

3.利用percona公司的xrabackup實現完全熱備份與增量熱備份

實驗環境：RHEL5.8 ,SElinux關閉，MySQL是tar包初始化安裝版本5.5.28

一.測試環境準備

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

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

建立一目錄用于存放二進制日志

mkdir /mybinlog

chown mysql:mysql /mybinlog

修改my.cnf

vim /etc/my.cnf

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

innodb_file_per_table = 1      ##啟用InnoDB表每表一文件，默認所有庫使用一個表空間

啟動mysqld

service mysqld start

1.3 創建一個測試庫與測試表

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 創建用于存放備份的目錄

mkdir /myback

chown -R mysql:mysql /myback  

二,用mysqldump實現備份

2.1 mysqldump用來溫備，所以我們得為所有庫加讀鎖,并且滾動一下二進制日志，并記錄當前二進制文件位置

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

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

--all-databases 備份所有庫

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

--routines 存儲過程與函數

--triggers 觸發器

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

--flush-logs 日志滾動一次

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

備份二進制日志

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

2.2 模擬數據庫意外損壞，測試完整恢復

rm -rf /data/mydata/*  

rm -rf  /mybinlog/*

初始化mysql并啟動mysql

cd /usr/local/mysql

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

rm -rf /mybinlog/* ##因為我們不是全新初始化的，可能會有報錯的二進制日志，我們不需要

service mysqld start  ##啟動時會重新生成新的二進制日志的

恢復到備份狀態,備份前先關閉對恢復過程的二進制日志記錄，因為記錄恢復語句是毫無意義的

mysql> set global sql_log_bin=0;

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

打開記錄并查看恢復狀況

mysql> set global sql_log_bin=1;

mysql> show databases;

打開二進制記錄并查看恢復狀況

mysql> set global sql_log_bin=1;

mysql> show databases;

2.3 模擬一種場景，我往linux表中新添加了數據，然后不小心將這個表刪了，我們要恢復到刪除之前的狀態，并且新加的數據還存在。

2.3.1 新增數據

mysql> use benet;

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

mysql> drop table linux;

mysql> show master status;   ##查看當前所在二進制日志中的位置

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |     9005 |              |                  |  

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

我們先恢復完整數據，再恢復完整備份后到刪除之前的數據，對應二進制日志就是完整備份后的二進制日志位置到刪除表之前的位置

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

mysql > set global sql_log_bin=0;

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

2.3.3 查看刪除表時的記錄位置

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 由上圖可知刪除是在8893時做的，將二進制文件中完整備份到刪除表之前的記錄導出

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

--start-position 指定從哪開始導出二進制日志

--stop-position 指定到哪結束

--start-datetime 從哪個時間開始格式如"2005-12-25 11:25:56"

--stop-datetime 到哪個時間結束

由于這個二進制日志是我們完整恢復后才啟用的，所以我們直接從頭開始即可，如果你的二進制日志很多，請查看完整備份中記錄的備份時的位置，從那開始到刪除之前即可

將這段二進制記錄應用到mysql的庫中

mysql < /tmp/change.sql

進入數據庫查看數據有沒有恢復

mysql> select * from linux;

基于mysqldump通常我們就是完整備份+二進制日志來進行恢復的。

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

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

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

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

3.3 同樣如第一步那樣準備環境

3.4 在MySQL中為所有表加讀鎖，不要關閉終端，否則鎖將失效,滾動日志

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 另開一終端速度建立快照,我的那個卷組是/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 就這樣一次完整備份就完成了，下面來測試能否正常使用

servivce mysqld stop

rm -R /data/mydata/*  

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

service mysqld start  ##如果能正常啟動代表沒有問題，起不來請看數據目錄權限

3.7 如果在完整備份后MySQL出現故障，與mysqldump一樣，先恢復上次的完整備份，再利用二進制日志恢復，二進制恢復再啰嗦一遍，找到完整備份時的二進制位置，把從那時到故障前的日志用mysqlbinlog導出來，然后批處理方式導入到MySQL中。這個同mysqldump中實驗一致就不重復了。

用lvm的快照來備份速度是非常快的，而且幾乎熱備，恢復也很快速，操作也簡單，完整恢復后再將相應二進制恢復即可。

四:基于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基本環境與第一步的一致

4.3 為備份建立一個只有備份權限的用戶

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/

數據會完整備份到/myback/中目錄名字為當前的日期，extrabackup會備份所有的InnoDB表，MyISAM表只是復制表結構文件、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。除了保存數據外還生成了一些extrabackup需要的數據文件

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

2)xtrabackup_binlog_info mysql云服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。

3)xtrabackup_binlog_pos_innodb  二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。

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

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

4.4 測試恢復MySQL,用extrabackup來完整恢復

service mysqld stop

rm -Rf /data/mydata

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

--apply-log 的意義在于把備份時沒commit的事務撤銷，已經commit的但還在事務日志中的應用到數據庫

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

--copy-back數據庫恢復，后面跟上備份目錄的位置

chown -R mysql:mysql /data/mydata  

service mysqld start ##如果能啟動代表恢復正常

4.5 我們來實驗一下增量備份

4.5.1 在表中新增一些數據

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 指定基于哪個備份做增量備份，最后是增量備份保存的目錄

增量備份只能對InnoDB引擎做增量備份，對MyISAM的表是完全復制

4.6 測試增量備份恢復

service mysqld stop

rm -Rf /data/mydata/*

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

--redo-only 指的是把備份時commit的但還在事務日志中的應用到時數據，但是還沒提交的不撤消，

因為這個事務可能在增量備份中提交，假如的撤消了增量備份中就提交不，因為事務已經不完整

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

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/ 這個是完整備份的目

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

這個會使增量備份中的的數據合并到完整備份中，如果還有增量備份，繼續合并，恢復時恢復完整備份即可

恢復數據，并起動MySQL

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

chown -R mysql:mysql /data/mydata

service mysqld start

查看數據有沒丟失

如果在增量備份后數據庫出現故障，我們需要通過完整備份+到現在為止的所有增量備份+最后一次增量備份到現在的二進制日志來恢復。

看了以上關于備份與恢復MySQL數據庫的三種方法介紹，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。