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 laoguang;
mysql> use laoguang;
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 laoguang;
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=5 exec_time=0 error_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
　　下載地址：
　　4.1 下載安裝xtrabackup，我用的是percona-xtrabackup-2.0.3-470.rhel5.i386.rpm
rpm -ivh percona-xtrabackup-2.0.3-470.rhel5.i386.rpm
　　4.2 MySQL基本環境與第一步的一致
　　4.3 為備份建立一個只有備份許可權的使用者
mysql> create user identified by 'redhat';
mysql> revoke all privileges,grant option from ;
mysql> grant reload,lock tables,replication client on *.* to ;
mysql> flush privileges;
　　4.4 完整備份一次MySQL
innobackupex --host=locahost --user=percona --password=redhat /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 ##如果能啟動代表恢復正常

.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
　　檢視資料有沒丟失
　　如果在增量備份後資料庫出現故障，我們需要透過完整備份+到現在為止的所有增量備份+最後一次增量備份到現在的二進位制日誌來恢復。