MySQL的XtraBackup進行備份和恢復(全備+增備+binlog)

2019 年 12 月 17 日
筆記

XtraBackup的全備份+XtraBackup增量備份+binlog日誌進行恢復最新數據。也可以恢復誤操作。

剛開始用mysqldump，備份100G+的資料庫，再加上伺服器繁忙，備份速度像蝸牛似的，於是尋找更高效的備份方法。

Xtrabackup備份原理：

物理備份，不鎖表，那麼怎麼保證現有的數據（備份過程中有尚未提交或已經提交但未同步事務）與複製出來的數據一致呢？

我們知道MySQL修改操作都會先記錄在ib_logfile日誌文件，再同步到磁碟，這個文件並重複使用。在複製期間會開啟一個執行緒用來監控ib_logfile日誌文件，如果有修改就從上次記錄的日誌序列號（checkpoins）開始複製新增內容到Logfile文件。複製結束後，把logfile事務日誌進行回滾，把未完成的事務同步到ibdata1和ibd裡面，來保證數據一致性。這與Mysql崩潰後恢復基本操作一樣

1.1 了解備份方式

熱備份：讀寫不受影響（mysqldump–>innodb）

溫備份：僅可以執行讀操作（mysqldump–>myisam）

冷備份：離線備份，讀寫都不可用

邏輯備份：將數據導出文本文件中（mysqldump）

物理備份：將數據文件拷貝（xtrabackup、mysqlhotcopy）

完整備份：備份所有數據

增量備份：僅備份上次完整備份或增量備份以來變化的數據

差異備份：僅備份上次完整備份以來變化的數據

1.3 安裝方式

CentOS:

# rpm -ivh http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

# yum install percona-xtrabackup #xtrabackup2.2不支援MySQL5.1的Innodb引擎,如需要可安裝2.0版本（https://www.percona.com/downloads/XtraBackup/XtraBackup-2.0.8/RPM/rhel6/x86_64/percona-xtrabackup-20-2.0.8-587.rhel6.x86_64.rpm）

1.4 了解常用參數

–user= #指定資料庫備份用戶

–password= #指定資料庫備份用戶密碼

–port= #指定資料庫埠

–host= #指定備份主機

–socket= #指定socket文件路徑

–databases= #備份指定資料庫,多個空格隔開，如–databases="dbname1 dbname2",不加備份所有庫

–defaults-file= #指定my.cnf配置文件

–apply-log #日誌回滾

–incremental= #增量備份，後跟增量備份路徑

–incremental-basedir= #增量備份，指上次增量備份路徑

–redo-only #合併全備和增量備份數據文件

–copy-back #將備份數據複製到資料庫，資料庫目錄要為空

–no-timestamp #生成備份文件不以時間戳為目錄名

–stream= #指定流的格式做備份,–stream=tar,將備份文件歸檔

–remote-host=user@ip DST_DIR #備份到遠程主機

使用xtrabackup進行MySQL資料庫備份

前面介紹mysqldump備份方式是採用邏輯備份，其最大的缺陷就是備份和恢復速度都慢，對於一個小於50G的資料庫而言，這個速度還是能接受的，但如果資料庫非常大，那再使用mysqldump備份就不太適合了。而使用lvm快照功能對資料庫進行備份，可以實現幾乎熱備的功能，但備份過程較為複雜，不過現在倒是有個工具mylvmbackup可以實現自動化備份。

前面我們也說道，使用物理備份時最快的，那有沒有辦法實現物理熱備呢？

目前主流的有兩個工具可以實現熱備：ibbackup和xtrabackup；ibbackup是商業軟體，沒伺服器授權為5000美元，非常昂貴。而xtrabackup功能比ibbackup還要強大，但卻是開源的。因此我們這裡就來介紹xtrabackup的使用。

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

(1)備份過程快速、可靠；
(2)備份過程不會打斷正在執行的事務；
(3)能夠基於壓縮等功能節約磁碟空間和流量；
(4)自動實現備份檢驗；
(5)還原速度快；

Xtrabackup中主要包含兩個工具：

xtrabackup：是用於熱備份innodb, xtradb表中數據的工具，不能備份其他類型的表，也不能備份數據表結構,xtrabackup命令只備份數據文件,並不備份數據表結構(.frm)，所以使用xtrabackup恢復的時候必須有對應表結構文件(.frm)。用innobackupex命令，此命令相當於冷備份，複製數據目錄的索引，數據，結構文件，但會有短暫的鎖表（時間依賴於MyISAM大小）。；

innobackupex：是將xtrabackup進行封裝的perl腳本，提供了備份myisam表的能力。

開始使用xtarbackup進行備份：參數按照要備份的資料庫資訊填寫.–defaults-file=/etc/my.cnf參數必須要放在第一個參數，不然會報錯，host參數和user參數要根據MySQL資料庫中的user表資訊配置不然會連接不上MySQL伺服器。

查看要備份的資料庫的資訊：

[root@localhost104 opt]# ps -ef | grep mysql root 12567 1 0 Jul27 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe –datadir=/usr/local/mysql/data/ –pid-file=/usr/local/mysql/mysql.pid mysql 12854 12567 0 Jul27 ? 00:01:58 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/usr/local/mysql/mysql_safe.log –pid-file=/usr/local/mysql/mysql.pid –socket=/usr/local/mysql/mysql.sock –port=3306

創建全備份的目錄並給相應的用戶許可權和屬組

[root@localhost104 opt]# mkdir /opt/xtrabackup_full [root@localhost104 opt]# chmod 700 /opt/xtrabackup_full

#全備份

[root@localhost 2017-07-29_14-05-49]# which xtrabackup /usr/bin/xtrabackup

[root@localhost104 opt]# innobackupex –defaults-file=/etc/my.cnf –user='root' –password='123456' –host='localhost' –port=3306 –socket='/usr/local/mysql/mysql.sock' –parallel=3 /opt/xtrabackup_full

……..

170729 12:54:48 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS… xtrabackup: The latest check point (for incremental): '20125155' xtrabackup: Stopping log copying thread. .170729 12:54:48 >> log scanned up to (20125164) 170729 12:54:49 Executing UNLOCK TABLES 170729 12:54:49 All tables unlocked 170729 12:54:49 [00] Copying ib_buffer_pool to /opt/xtrabackup/2017-07-29_12-54-44/ib_buffer_pool 170729 12:54:49 [00] …done 170729 12:54:49 Backup created in directory '/opt/xtrabackup/2017-07-29_12-54-44/' MySQL binlog position: filename 'mysql_bin.000414', position '154' 170729 12:54:49 [00] Writing /opt/xtrabackup/2017-07-29_12-54-44/backup-my.cnf 170729 12:54:49 [00] …done 170729 12:54:49 [00] Writing /opt/xtrabackup/2017-07-29_12-54-44/xtrabackup_info 170729 12:54:49 [00] …done xtrabackup: Transaction log of lsn (20125155) to (20125164) was copied. 170729 12:54:49 completed OK!

備份後的文件：

在備份的同時，備份數據會在備份目錄下創建一個以當前日期時間為名字的目錄存放備份文件：

[root@localhost 2017-07-29_12-34-51]# pwd

/opt/xtrabackup/2017-07-29_12-34-51

[root@localhost 2017-07-29_12-34-51]# ll

total 77876

-rw-r—– 1 root root 425 Jul 29 12:34 backup-my.cnf

-rw-r—– 1 root root 546 Jul 29 12:34 ib_buffer_pool

-rw-r—– 1 root root 79691776 Jul 29 12:34 ibdata1

drwxr-x— 2 root root 4096 Jul 29 12:34 mysql

drwxr-x— 2 root root 8192 Jul 29 12:34 performance_schema

drwxr-x— 2 root root 8192 Jul 29 12:34 sys

drwxr-x— 2 root root 146 Jul 29 12:34 testdb

-rw-r—– 1 root root 21 Jul 29 12:34 xtrabackup_binlog_info

-rw-r—– 1 root root 115 Jul 29 12:34 xtrabackup_checkpoints

-rw-r—– 1 root root 566 Jul 29 12:34 xtrabackup_info

-rw-r—– 1 root root 2560 Jul 29 12:34 xtrabackup_logfile

各文件說明：

[root@localhost 2017-07-29_12-34-51]# cat xtrabackup_binlog_info mysql_bin.000414 154

記錄備份的類型、開始和結束的日誌序列號 [root@localhost 2017-07-29_12-34-51]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 20125155 last_lsn = 20125164 compact = 0 recover_binlog_info = 0

[root@localhost 2017-07-29_12-34-51]# cat xtrabackup_info uuid = 45d5fa84-7417-11e7-bdad-000c297442b7 name = tool_name = innobackupex tool_command = –defaults-file=/etc/my.cnf –user=root –password=… –host=localhost –port=3306 –socket=/usr/local/mysql/mysql.sock –parallel=3 /opt/xtrabackup tool_version = 2.4.8 ibbackup_version = 2.4.8 server_version = 5.7.18-log start_time = 2017-07-29 12:34:52 end_time = 2017-07-29 12:34:56 lock_time = 0 binlog_pos = filename 'mysql_bin.000414', position '154' innodb_from_lsn = 0 innodb_to_lsn = 20125155 partial = N incremental = N format = file compact = N compressed = N encrypted = N

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

在使用innobackupex進行備份時，還可以使用–no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。

另外還需注意：備份資料庫的用戶需要具有相應許可權，如果要使用一個最小許可權的用戶進行備份，則可基於如下命令創建此類用戶：grant us

grant user 'bkpuser'@'localhost' identified by '123';

grant reload,lock tables,replication client on *.* to 'bkpuser'@'localhost';

flush privileges;

(三)增量備份

創造第一個增量數據表xtrabackup_increment_1_t

mysql> create table testdb.xtrabackup_increment_1_t(id int,name varchar(20)); Query OK, 0 rows affected (0.32 sec) mysql> show tables; +————————–+ | Tables_in_testdb | +————————–+ | add_data1 | | add_data2 | | student_t | | xtrabackup_increment_1_t | +————————–+ 4 rows in set (0.00 sec) mysql> insert into xtrabackup_increment_1_t values(1,'anzhen'),(2,'anzhu'); Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from testdb.xtrabackup_increment_1_t; +——+——–+ | id | name | +——+——–+ | 1 | anzhen | | 2 | anzhu | +——+——–+ 2 rows in set (0.03 sec)

創建增量備份目錄

[root@localhost opt]# mkdir -p /opt/xtrabackup_increment [root@localhost opt]# chmod 700 /opt/xtrabackup_increment

增量備份命令1：注意：第一次執行增備的時候–incremental-basedir這個參數是填寫最近一次全備的目錄做為增備的基礎目錄

[root@localhost opt]# innobackupex –defaults-file=/etc/my.cnf –user='root' –password='123456' –socket='/usr/local/mysql/mysql.sock' –port=3306 –incremental –incremental-basedir='/opt/xtrabackup_full/2017-07-29_12-34-51/' –parallel=3 /opt/xtrabackup_increment

#指定上次完整備份目錄

如果有completed OK!說明增量備份成功！

xtrabackup: Transaction log of lsn (20132701) to (20132710) was copied.

170729 14:05:57 completed OK!

查看相關全備份資訊：

[root@localhost104 2017-07-29_12-54-44]# cat xtrabackup_binlog_info mysql_bin.000414 154 [root@localhost104 2017-07-29_12-54-44]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 20125155 last_lsn = 20125164 compact = 0 recover_binlog_info = 0 [root@localhost104 2017-07-29_12-54-44]# pwd /opt/xtrabackup_full/2017-07-29_12-54-44

說明全備份的最後一個日誌文件是mysql_bin.000414，位置是154，from_lsn 來自的日誌號為0，to_lsn = 20125155。下個增量備份要用到這個to_lsn = 20125155。才可以全備和曾備連接起來。

查看相關增量備份資訊：

[root@localhost 2017-07-29_14-05-49]# cat xtrabackup_binlog_info mysql_bin.000414 667 [root@localhost 2017-07-29_14-05-49]# cat xtrabackup_binlog_info mysql_bin.000414 667 [root@localhost 2017-07-29_14-05-49]# cat xtrabackup_checkpoints backup_type = incremental from_lsn = 20125155 to_lsn = 20132701 last_lsn = 20132710 compact = 0 recover_binlog_info = 0 [root@localhost 2017-07-29_14-05-49]# pwd /opt/xtrabackup_increment/2017-07-29_14-05-49

說明增備份的最後一個日誌文件是mysql_bin.000414，位置是667，from_lsn 來自的日誌號為20125155，也就是最近一次全部或者增備的to_lsn號。這次增備為to_lsn = 20132701。

資訊如下：

170729 14:24:04 [00] Writing /opt/xtrabackup_increment/2017-07-29_14-23-44/xtrabackup_info 170729 14:24:04 [00] …done xtrabackup: Transaction log of lsn (20139866) to (20139875) was copied. 170729 14:24:05 completed OK!

記錄了lsn號的備份資訊。OK就成功！

（四）誤操作

mysql> show databases; +——————–+ | Database | +——————–+ | information_schema | | mysql | | performance_schema | | sys | | testdb | +——————–+ 5 rows in set (0.16 sec) mysql> drop database testdb; Query OK, 6 rows affected (0.82 sec) mysql> commit; Query OK, 0 rows affected (0.02 sec)

mysql> flush logs; Query OK, 0 rows affected (0.05 sec)

mysql> create database test_new; Query OK, 1 row affected (0.00 sec)

mysql> create table test_new.xtrabackup_increment_4_t(id int,name varchar(20)); Query OK, 0 rows affected (0.10 sec) mysql> insert into test_new.xtrabackup_increment_4_t values(32,'3zhen'),(242,'asfwthu'); Query OK, 2 rows affected (0.09 sec)

mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_new.xtrabackup_increment_4_t; +——+———+ | id | name | +——+———+ | 32 | 3zhen | | 242 | asfwthu | +——+———+ 2 rows in set (0.00 sec)

mysql> flush logs;

（四）全備恢復和增備恢復。現在要恢復四個xtrabackup_increment_4_t 數據表的數據和誤操作的數據

4.1 停掉MySQL服務

[root@localhost104 mysql]# ps -ef | grep mysql root 12567 1 0 Jul27 ? 00:00:00 /bin/sh /usr/local/mysql//bin/mysqld_safe –datadir=/usr/local/mysql/data/ –pid-file=/usr/local/mysql/mysql.pid mysql 12854 12567 0 Jul27 ? 00:02:08 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql/ –datadir=/usr/local/mysql/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/usr/local/mysql/mysql_safe.log –pid-file=/usr/local/mysql/mysql.pid –socket=/usr/local/mysql/mysql.sock –port=3306 root 48441 48401 0 13:53 pts/3 00:00:00 mysql -uroot -p root 49567 45091 0 15:45 pts/1 00:00:00 grep –color=auto mysql [root@localhost104 mysql]# [root@localhost104 mysql]# [root@localhost104 mysql]# cd /usr/local/mysql/

[root@localhost104 support-files]# ls

magic mysqld_multi.server mysql-log-rotate mysql.server [root@localhost104 support-files]# ./mysql.server stop Shutting down MySQL…… SUCCESS! [root@localhost104 support-files]# ps -ef | grep mysql root 48441 48401 0 13:53 pts/3 00:00:00 mysql -uroot -p root 49614 45091 0 15:46 pts/1 00:00:00 grep –color=auto mysql

#把原來的MySQL數據目錄進行重命名，保留原來的binlog二進位文件，用於恢復最新數據。

[root@localhost104 mysql]#cd /usr/local/mysql

[root@localhost104 mysql]#mv /usr/local/mysql/data /usr/local/mysql/data_bak

[root@localhost104 mysql]#mkdir /usr/local/mysql/data

[root@localhost104 mysql]#chown mysql:mysql /usr/local/mysql/data

4.2準備(prepare)一個完全備份

建完備份之後數據被沒有馬上可以被還原，需要回滾未提交事務，前滾提交事務，讓資料庫文件保持一致性。

innobackupex使用—apply-log來做預備備份。

一般情況下，在備份完成後，數據尚且不能用於恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。「準備」的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處於一致性狀態。

innobakupex命令的–apply-log選項可用於實現上述功能。如下面的命令：–use-memory=4G指定使用的記憶體，按記憶體的大小分配，這樣可以加快恢復的速度。

xtrabackup把備份過程中可能有尚未提交的事務或已經提交但未同步數據文件的事務，寫到xtrabackup_logfile文件，所以要先通過這個日誌文件回滾，把未完成的事務同步到備份文件，保證數據文件處於一致性。

[root@localhost 2017-07-29_12-34-51]# pwd /opt/xtrabackup/2017-07-29_12-34-51 [root@localhost 2017-07-29_12-34-51]#innobackupex –defaults-file=/etc/my.cnf –apply-log –redo-only /opt/xtrabackup_full/2017-07-29_12-34-51 –use-memory=4G –user=root –password=123456 如果執行正確，其最後輸出的幾行資訊通常如下：

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

xtrabackup: This target seems to be not prepared yet.

InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown… InnoDB: Shutdown completed; log sequence number 20125736 170729 13:30:29 completed OK!

成功後，備份可以被用來還原資料庫了

看到redo log已經出現。xtrabackup_開頭的幾個文件記錄了一些日誌偏移量的資訊和日誌名和時間等資訊。在xtrabackup_checkpoints中記錄了備份的模式：

backup_type = full-backuped。

xtrabackup_binlog_info：記錄當前最新的LOG Positionxtrabackup_binlog_pos_innodb:innodb log postionxtrabackup_checkpoints: 存放備份的起始位置beginlsn和結束位置endlsn,增量備份需要這個lsn[增量備份可以在這裡面看from和to兩個值的變化xtrabackup_info:備份的一些具體資訊日誌

四個增量表已經恢復到新的數據目錄了。

4.5 修改數據目錄的許可權

[root@localhost testdb]#chown mysql:mysql /usr/local/mysql/data

這時候我們應該起資料庫服務。

5.1啟動資料庫服務

[root@localhost testdb]#cd /usr/local/mysql

[root@localhost testdb]#bin/mysqld_safe –defaults-file='/etc/my.cnf' –user=root &

啟動成功！可以連接客戶端檢驗數據

6.1連客服端

[root@localhost testdb]# mysql -u root -h localhost -P3306 -p

MySQL>

| xtrabackup_increment_4_t|

完成恢復！

四個增備份表和全備份的數據都恢復完成！

到目前為止已經把備份數據恢復完成了。但是還沒有當天資料庫產生的最新數據，要恢復當天的數據，要使用binlog恢復。

7.1恢復當天最新的數據，要利用binlog日誌恢復。可以參照binlog的完整恢復

–獲取最後一次增量的log position [root@localhost xtrabackup_increment]# cat /opt/xtrabackup_increment/2017-07-29_14-36-51/xtrabackup_binlog_info mysql_bin.000417 154 [root@localhost xtrabackup_increment]# cd /usr/local/mysql –使用start-position 和stop-datetime 將日誌追加到最新 /usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data_bak/mysql_bin.000417 /usr/local/mysql/data_bak/mysql_bin.000418 –start-position=154 | mysql -uroot -p -P3306 –socket=/usr/local/mysql/mysql.sock

到目前為止,數據更新到最後一次flush logs的數據。如果還沒有達到要求。可以先flush logs ，然後把最新的binlog文件恢復。

MySQL的XtraBackup進行備份和恢復(全備+增備+binlog)