Mysql之二：利用Xtrabackup完成資料的備份和還原

#####################################

安裝和簡介

備份和還原的實作

完全+增量+二進制日志的備份和還原

一、安裝和簡介

1、簡介

Xtrabackup是由percona提供的mysql資料庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb資料庫進行熱備的工具。特點：

(1)備份過程快速、可靠；

(2)備份過程不會打斷正在執行的事務；

(3)能夠基于壓縮等功能節約磁盤空間和流量；

(4)自動實作備份檢驗；

(5)還原速度快；

2、安裝

其最新版的軟體可從 http://www.percona.com/software/percona-xtrabackup/ 獲得。本文基于RHEL5.8的系統，是以，直接下載下傳相應版本的rpm包安裝即可：

<code>rpm -ivh percona-xtrabackup-2.0.0-417.rhel5.i386.rpm</code>

二、備份和還原的實作

1、完全備份

<code>#####本文中直接使用root</code>

<code>innobackupex --user=root </code><code>/backup/</code>

<code>innobackupex --user=DBUSER --password=DBUSERPASS </code><code>/path/to/BACKUP-DIR/</code>

如果要使用一個最小權限的使用者進行備份，則可基于如下指令建立此類使用者：

<code>mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’s3cret’;</code>

<code>mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;</code>

<code>mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;</code>

<code>mysql> FLUSH PRIVILEGES;</code>

使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，複制所有關于表結構定義的相關檔案(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案，同時還會備份觸發器和資料庫配置資訊相關的檔案。這些檔案會被儲存至一個以時間指令的目錄中。

在備份的同時，innobackupex還會在備份目錄中建立如下檔案：

(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 —— 備份指令用到的配置選項資訊；

2、準備(prepare)一個完全備份

一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。

innobakupex指令的--apply-log選項可用于實作上述功能。如下面的指令：

<code># innobackupex --apply-log /path/to/BACKUP-DIR</code>

在實作“準備”的過程中，innobackupex通常還可以使用--use-memory選項來指定其可以使用的記憶體的大小，預設通常為100M。如果有足夠的記憶體可用，可以多劃分一些記憶體給prepare的過程，以提高其完成速度。

3、從一個完全備份中恢複資料

innobackupex指令的--copy-back選項用于執行恢複操作，其通過複制所有資料相關的檔案至mysql伺服器DATADIR目錄中來執行恢複過程。innobackupex通過backup-my.cnf來擷取DATADIR目錄的相關資訊。

<code># innobackupex --copy-back /path/to/BACKUP-DIR</code>

當資料恢複至DATADIR目錄以後，還需要確定所有資料檔案的屬主和屬組均為正确的使用者，如mysql，否則，在啟動mysqld之前還需要事先修改資料檔案的屬主和屬組。如：

<code># chown -R mysql:mysql /mydata/data/</code>

4、使用innobackupex進行增量備份

每個InnoDB的頁面都會包含一個LSN資訊，每當相關的資料發生改變，相關的頁面的LSN就會自動增長。這正是InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之後發生改變的頁面來實作。

要實作第一次增量備份，可以使用下面的指令進行：

<code># innobackupex --incremental /backup --incremental-basedir=BASEDIR</code>

其中，BASEDIR指的是完全備份所在的目錄，此指令執行結束後，innobackupex指令會在/backup目錄中建立一個新的以時間命名的目錄以存放所有的增量備份資料。另外，在執行過增量備份之後再一次進行增量備份時，其--incremental-basedir應該指向上一次的增量備份所在的目錄。

需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。

“準備”(prepare)增量備份與整理完全備份有着一些不同，尤其要注意的是：

(1)需要在每個備份(包括完全和各個增量備份)上，将已經送出的事務進行“重放”。“重放”之後，所有的備份資料将合并到完全備份上。

(2)基于所有的備份将未送出的事務進行“復原”。

于是，操作就變成了：

<code># innobackupex --apply-log --redo-only BASE-DIR</code>

接着執行：

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1</code>

而後是第二個增量：

<code># innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2</code>

其中BASE-DIR指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄，INCREMENTAL-DIR-2指的是第二次增量備份的目錄，其它依次類推，即如果有多次增量備份，每一次都要執行如上操作；

三、完全+增量+二進制日志的備份和還原

完全備份

<code>#####備份前表中資料如下</code>

<code>mysql> </code><code>select</code> <code>* from tb1;</code>

<code>#####使用Xtrabackup對所有庫做完全備份</code>

<code>innobackupex --user=root </code><code>/backup</code>

第一次增量備份

<code>第一次增量備份</code>

<code>mysql> insert into tb1 (name) values (</code><code>'firstday'</code><code>);</code>

<code>| 9 | firstday |</code>

<code>#####basedir指向完全備份目錄</code>

<code>innobackupex --incremental </code><code>/backup</code> <code>--incremental-basedir=</code><code>/backup/2013-09-19_15-45-49/</code>

第二次增量備份

<code>mysql> insert into tb1 (name) values (</code><code>'secondday'</code><code>);</code>

<code>| 9 | firstday |</code>

<code>| 10 | secondday |</code>

<code>#####basedir指向第一次增量備份目錄</code>

<code>innobackupex --incremental </code><code>/backup</code> <code>--incremental-basedir=</code><code>/backup/2013-09-19_15-47-49/</code>

第一次滾動日志插入新資料

<code>mysql> flush logs;</code>

<code>#####檢視目前使用二進制日志檔案</code>

<code>mysql> show master status;</code>

<code>#####删除制定二進制日志檔案之前的所有檔案(不包含制定檔案)</code>

<code>mysql> PURGE BINARY LOGS TO </code><code>'mysql-bin.000002'</code><code>;</code>

<code>mysql> insert into tb1 (name) values (</code><code>'thirdday'</code><code>);</code>

<code>| 11 | thirdday |</code>

第二次滾動日志插入資料

<code>mysql> insert into tb1 (name) values (</code><code>'forthday'</code><code>);</code>

<code>#####最後資料為</code>

<code>| 13 | thirdday |</code>

<code>| 14 | forthday |</code>

基于多個二進制日志生成單個SQL格式檔案

<code>#####檢視快照時刻所處位置的時間點</code>

<code>[root@localhost ~]</code><code># mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000002</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#130919 15:59:00 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.28-log created 130919 15:59:00 #時間</code>

<code>#####基于時間做限定來合并多個日志檔案為一個SQL格式的檔案(很明顯是從2的開始到3的結束)</code>

<code>mysqlbinlog --start-datetime=</code><code>'2013-09-19 15:59:00 '</code> <code>/mydata/data/mysql-bin</code><code>.000002 </code><code>/mydata/data/mysql-bin</code><code>.000003 > </code><code>/backup/increment-</code><code>`</code><code>date</code> <code>+%F`.sql</code>

準備操作(合并所有Xtrabackup備份資料)

<code>#####準備完全備份</code>

<code>innobackupex --apply-log --redo-only </code><code>/backup/2013-09-19_15-45-49/</code>

<code>#####合并第一次增量備份</code>

<code>innobackupex --apply-log --redo-only </code><code>/backup/2013-09-19_14-00-12/</code> <code>--incremental-</code><code>dir</code><code>=</code><code>/backup/2013-09-19_14-07-35/</code>

<code>#####合并第二次增量備份</code>

<code>innobackupex --apply-log --redo-only </code><code>/backup/2013-09-19_15-45-49/</code> <code>--incremental-</code><code>dir</code><code>=</code><code>/backup/2013-09-19_15-50-46/</code>

模拟資料庫損壞

<code>service mysqld stop</code>

<code>rm</code> <code>-rf </code><code>/mydata/data/</code><code>*</code>

完全+增量的資料還原

<code>#####一次性即可還原合并後用Xtrabackup備份過的所有檔案</code>

<code>innobackupex --copy-back </code><code>/backup/2013-09-19_15-45-49/</code>

<code>chown</code> <code>-R mysql.mysql </code><code>/mydata/data/</code>

<code>service mysqld start</code>

<code>mysql> use soulboy</code>

利用二進制日志完成即時點恢複

<code>#####禁止二進制日志記錄功能</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin=0;</code>

<code>#####即時點回複</code>

<code>mysql> </code><code>source</code> <code>/backup/increment-2013-09-19</code><code>.sql</code>

<code>#####開啟二進制日志記錄功能</code>

<code>mysql> </code><code>set</code> <code>sql_log_bin=1;</code>

<code>#####還原成功，至此所有資料還原成功</code>

本文轉自 ftmoonfans 51CTO部落格，原文連結:http://blog.51cto.com/soulboy/1299070

Mysql之二：利用Xtrabackup完成資料的備份和還原

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述