mysql備份執行個體攻略

本文系統Centos6.0

首先普及下備份術語：

• 熱備份：讀、寫不受影響；

• 溫備份：僅可以執行讀操作；

• 冷備份：離線備份；讀、寫操作均中止；

我們備份，一般備份以下幾個部分：

1.資料檔案

2.記錄檔比如交易記錄，二進位日誌）

3.預存程序，儲存函數，觸發器

4.設定檔十分重要，各個設定檔都要備份）

一、常用的備份工具

1.Mysql內建的備份工具

• mysqldump 邏輯備份工具，支援所有引擎，MyISAM引擎是溫備，InnoDB引擎是熱備，備份速度中速，還原速度非常非常慢，但是在實現還原的時候，具有很大的操作餘地。具有很好的彈性。

• mysqlhotcopy 物理備份工具，但只支援MyISAM引擎，基本上屬於冷備的範疇，物理備份，速度比較快。

2.檔案系統備份工具

• cp 冷備份，支援所有引擎，複製命令，只能實現冷備，物理備份。使用歸檔工具，cp命令，對其進行備份的，備份速度快，還原速度幾乎最快，但是靈活度很低，可以跨系統，但是跨平台能力很差。

• lvm 幾乎是熱備份，支援所有引擎，基於快照(LVM，ZFS)的物理備份，速度非常快，幾乎是熱備。隻影響資料幾秒鐘而已。但是建立快照的過程本身就影響到了資料庫線上的使用，所以備份速度比較快，恢複速度比較快，沒有什麼彈性空間，而且LVM的限制：不能對多個邏輯卷同一時間進行備份，所以資料檔案和交易記錄等各種檔案必須放在同一個LVM上。而ZFS則非常好的可以在多邏輯卷之間備份。

3.其它工具

• xtrabackup 開源工具 MyISAM是溫備份，InnoDB是熱備份 ，是ibbackup商業工具的替代工具

二、備份執行個體：

在此本文已經做了lvm的一個分區：mysql5.6編譯安裝完畢；

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013V626-0.jpg" title="1.jpg" />

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013V0R-1.jpg" title="1.jpg" />

mysql的datadir=/usr/data 也就是我們lvm掛載的地方

在mysql的test庫有兩張表；t1大小為388M左右；t2大小為210M左右；

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013V603-2.jpg" title="1.jpg" />

給大家看下這邊測試中的My.cnf

[root@centos test]# vim /etc/my.cnf[mysqld]datadir=/usr/datasocket=/tmp/mysql.sockuser=mysqlserver-id=1log-bin=mysql-binreport-port=3306port=3306symbolic-links=0[mysql_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

我們這邊備份操作一共是四種方式：

方式1>；mysqldump：

mysqldump備份資料庫完全備份+增加備份，速度相對較慢，適合中小型資料庫）MyISAM是溫備份，InnoDB是熱備份）

mysqldump 是採用SQL層級的備份機制，它將資料表導成 SQL 指令檔，在不同的 MySQL 版本之間升級時相對比較合適，這也是最常用的備份方法。mysqldump 比直接拷貝要慢些。對於中等層級業務量的系統來說，備份策略可以這麼定：第一次完全備份，每天一次增量備份，每周再做一次完全備份，如此一直重複。而對於重要的且繁忙的系統來說，則可能需要每天一次全量備份，每小時一次增量備份，甚至更頻繁。為了不影響線上業務，實現線上備份，並且能增量備份，最好的辦法就是採用主從複製機制(replication)，在 slave 機器上做備份。

方式2>；直接cp資料庫檔案：

當你使用直接備份方法時，必須保證表不在被使用。如果伺服器在你正在拷貝一個表時改變它，拷貝就失去意義。保證你的拷貝完整性的最好方法是關閉伺服器，拷貝檔案，然後重啟伺服器。如果你不想關閉伺服器，要在執行表檢查的同時鎖定伺服器。如果伺服器在運行，相同的制約也適用於拷貝檔案，而且你應該使用相同的鎖定協議讓伺服器“安靜下來”。當你完成了備份時，需要重啟伺服器(如果關閉了它)或釋放加在表上的鎖定(如果你讓伺服器運行)。要用直接拷貝檔案把一個資料庫從一台機器拷貝到另一台機器上，只是將檔案拷貝到另一台伺服器主機的適當資料目錄下即可。要確保檔案是MyIASM格式或兩台機器有相同的硬體結構，否則你的資料庫在另一台主機上有奇怪的內容。你也應該保證在另一台機器上的伺服器在你正在安裝資料庫表時不訪問它們

方式3>；lvm快照：

lvm快照從物理角度實現幾乎熱備的完全備份，配合二進位記錄備份實現增量備份，速度快適合比較煩忙的資料庫

前提：

• 資料檔案要在邏輯卷上；

• 此邏輯卷所在卷組必須有足夠空間使用快照卷；

• 資料檔案和交易記錄要在同一個邏輯卷上；

方式4>；xtrabackup 備份工具。xtrabackup 備份資料庫，實現完全熱備份與增量熱備份MyISAM是溫備份，InnoDB是熱備份）

3、執行個體操作：

方式1>；mysqldump：

●查看當前log status;

mysql> show master status;

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013T960-3.jpg" title="1.jpg" />

●備份全庫；確定備份檔案

mysqldump -u root -p --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs > /backup/201308071550.full.sql

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013U352-4.jpg" title="1.jpg" />

--all-databases   #備份所有庫--lock-all-tables #為所有表加讀鎖--routinge        #預存程序與函數--triggers        #觸發器--events          #記錄事件--master-data=2   #在備份檔案中記錄當前二進位日誌的位置，並且為注釋的，1是不注釋掉在主從複製中才有意義--flush-logs      #日誌滾動一次

●查看此時的log status;

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013U410-5.jpg" title="1.jpg" />

●新增幾條資料，以便增量備份；

mysql> insert into test.t1(id,user_name,user_passwd)values(10000,'劉德華','123'),(10001,'張學友','123'),(10002,'郭富城','123');

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013W2Y-6.jpg" title="1.jpg" />

●備份binlog日誌

cp /usr/data/mysql-bin.000007 /backup/201308071550.full.binlog.000001

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013R4N-7.jpg" title="1.jpg" />

●類比資料庫損壞，進行恢複

rm -rf /usr/data/*killall mysqld/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/data/ --user=mysqlservice mysqld startmysql -u root -pmysql> source /backup/201308071550.full.sql

●到此，你會發現已經恢複到了我們全備的時候的資料；並且是沒有那後來插入的“三大天王”；

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013VF2-8.jpg" title="1.jpg" />

●恢複我們後來插入的資料；看看有沒有天王的資料；

[root@centos ~]# mysqlbinlog /backup/201308071550.full.binlog.000001 |mysql test

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013QE0-9.jpg" title="1.jpg" />

到此、：mysqldump的操作就結束了，復原模式，第一次全備加binlog日誌；

方式2>；直接cp資料庫檔案(此方法較簡單，個人建議在cp前，進行lock)

◆首先鎖表：

mysql> flush tables with read lock;

◆cp資料檔案：

mkdir /backup/`date +%F-%H-%M-%S`cp -pr /usr/data/* /backup/2013-08-07-17-02-19/

◆解鎖：

mysql> unlock tables;

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013Q307-10.jpg" title="1.jpg" />

◆開始類比破壞資料庫並恢複：

rm -rf /usr/data/*killall mysqld /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/data/ --user=mysqlcp -pr /backup/2013-08-07-17-02-19/* /usr/data/service mysqld start

◆到此就恢複正常了；

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013W1H-11.jpg" title="1.jpg" />

方式3>；lvm快照

★鎖表，重新整理log；

mysql> flush tables with read lock;mysql> flush logs;

★另一終端，lvm快照

lvcreate -L 1G -n mysql-snapshot -s -p r /dev/mylvm/lansgg

註：我的/dev/mylvm這個vg一共是5G，給lansgg這個lv分了4G，還剩下1G；

-L 1G              #邏輯卷大小-n mysql-snapshot  #產生快照的名字-s                 #指定源邏輯卷-p r               #permission 使用權限設定

解鎖：

mysql> unlock tables;

★掛載快照，將快照裡的資料cp到一個目錄裡面，卸載，刪除快照

mkdir /backup/lvmsnapshotmount /dev/mylvm/mysql-snapshot /mnt/cp -pr /mnt/* /backup/lvmsnapshot/umount /mnt/lvremove /dev/mylvm/mysql-snapshot

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013Q342-12.jpg" title="1.jpg" />

★進行資料破壞，恢複

rm -rf /usr/data/*killall mysqldcp -pr /backup/lvmsnapshot/* /usr/data/

恢複完畢

Ps:以上的增量備份都可以同方式一的方法一樣；

方式4>；xtrabackup 備份工具

▲安裝所需的包：

yum install libaio libaio-devel perl-Time-HiRes curl curl-devel zlib-devel openssl-devel perl cpio expat-devel gettext-devel perl-ExtUtils-MakeMaker perl-DBD-MySQL.*  -y

▲安裝xtrabackup:

wget http://www.percona.com/redir/downloads/XtraBackup/LATEST/binary/Linux/i686/percona-xtrabackup-2.1.3-608.tar.gztar zxvf percona-xtrabackup-2.1.3-608.tar.gzcd percona-xtrabackup-2.1.3/bin/cp * /usr/bin/

▲備份全庫：

innobackupex --ibbackup=xtrabackup --user=root --password=123 --defaults-file=/etc/my.cnf /tmp/data/

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013S9D-13.jpg" title="1.jpg" />

省略~~~ 看結尾

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013S323-14.jpg" title="1.jpg" />

▲查看備份檔案：

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013WP5-15.jpg" title="1.jpg" />

資料會完整備份到/tmp/data 中目錄名字為當前的日期，xtrabackup會備份所有的InnoDB表，MyISAM表只是複製表結構檔案、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案，同時還會備份觸發器和資料庫配置資訊相關的檔案。除了儲存資料外還產生了一些xtrabackup需要的資料檔案

• xtrabackup_checkpoints  備份類型如完全或增量）、備份狀態如是否已經為prepared狀態）和LSN(記錄序號)範圍資訊；每個InnoDB頁(通常為16k大小)都會包含一個記錄序號，即LSN。LSN是整個資料庫系統的系統版本號碼，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。

• xtrabackup_binlog_info mysql伺服器當前正在使用的二進位記錄檔及至備份這一刻為止二進位日誌事件的位置。

• xtrabackup_binary 備份中用到的xtrabackup的可執行檔。

• backup-my.cnf 備份命令用到的配置選項資訊。

• xtrabackup_logfile 記錄標準輸出資訊xtrabackup_logfile

▲測試恢複資料：

killall mysqldrm -rf /var/lib/mysql/*innobackupex --apply-log /tmp/data/2013-08-11_14-54-26/innobackupex --copy-back /tmp/data/2013-08-11_14-54-26/chown  -R mysql.mysql /var/lib/mysql/service mysqld start

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013TO8-16.jpg" title="1.jpg" />

完整恢複；

註：


--apply-log 的意義在於把備份時沒commit的事務撤銷，已經commit的但還在交易記錄中的應用到資料庫

--copy-back資料庫恢複，後面跟上備份目錄的位置

2、▲增量備份測試：

插入資料：

mysql> insert into test.t1(id,user_name) values(10,'張三');

增量備份：

innobackupex --ibbackup=xtrabackup --user=root --password=123 --incremental --incremental-basedir=/tmp/data/2013-08-11_14-54-26/ /tmp/data/#--incremental 指定是增量備份#--incremental-basedir 指定基於哪個完整備份做增量備份，最後是增量備份儲存的目錄ps：增量備份只能對InnoDB引擎做增量備份，對MyISAM的表是完全複製

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013SM7-17.jpg" title="1.jpg" />

增量備份測試恢複：

killall mysqldrm -rf /var/lib/mysql/*innobackupex --ibbackup=xtrabackup --apply-log --redo-only /tmp/data/2013-08-11_14-54-26/innobackupex --ibbackup=xtrabackup --apply-log /tmp/data/2013-08-11_14-54-26/ --incremental-dir=/tmp/data/2013-08-11_15-16-47/innobackupex --copy-back /tmp/data/2013-08-11_14-54-26/chown -R mysql.mysql /var/lib/mysql/service mysqld start

650) this.width=650;" src="http://www.bkjia.com/uploads/allimg/131229/13013WY5-18.jpg" title="1.jpg" />

證明，增量測試恢複；

3、擴充使用：

▼完整備份單庫並打包：

innobackupex --ibbackup=xtrabackup --user=root --password=123 --defaults-file=/etc/my.cnf --database=test --stream=tar /tmp/data/ > /tmp/data/testfull201308111624.tar

▼完整備份單庫並打包壓縮：

innobackupex --ibbackup=xtrabackup --user=root --password=123 --defaults-file=/etc/my.cnf --database=test --stream=tar /tmp/data/ |gzip > /tmp/data/testfull201308111628.tar.gz

▼單庫增量備份：

innobackupex --ibbackup=xtrabackup --user=root --password=123 --database=test --incremental --incremental-basedir=/tmp/data/2013-08-11_14-54-26/ /tmp/data/add/

本文出自 “Coffee_藍山” 部落格，請務必保留此出處http://lansgg.blog.51cto.com/5675165/1270430