使用Xtrabackup完整備份中恢復單表

powdba發表於2018-12-12
MySQL目前採取的備份策略都是xtrabackup全備+binlog備份，如果當某天某張表意外的刪除，那麼如何快速從xtrabackup全備中恢復單表呢？從MySQL 5.6版本開始，支援可傳輸表空間（Transportable Tablespace），那麼利用這個功能就可以實現單表的恢復，同樣利用這個功能還可以把innodb表移動到另外一臺伺服器上。
下面進行從xtrabackup全備恢復單表的測試。
環境介紹
  • 資料庫版本：MySQL-5.7.22
前期準備：
1. 資料庫要求：
  • innodb_file_per_table=1
  • 當匯入表空間時，目的庫的頁大小要和源庫的頁大小相匹配。
  • DISCARD TABLESPACE 不支援分割槽表。如果你在分割槽表上使用命令 ALTER TABLE … DISCARD TABLESPACE 你會看到如下錯誤: ERROR 1031 (HY000): 表引擎沒有這個選項。
  • DISCARD TABLESPACE 命令不支援有父子關係的表。如果 FOREIGN_KEY_CHECKS 被設定成1. 在使用命令之前我們可以將這一引數設定為0. FOREIGN_KEY_CHECKS=0.
  • ALTER TABLE … IMPORT TABLESPACE 命令在匯入表時不會檢查主外來鍵關係。
2.安裝工具mysql-utilities：
# yum install mysql-utilities -y
或者：
# wget -c https://downloads.mysql.com/archives/get/file/mysql-utilities-1.6.5.tar.gz
# tar -xvf mysql-utilities-1.6.5.tar.gz
# cd mysql-utilities-1.5.3
# python ./setup.py build
# python ./setup.py install
檢視原表中的資料：
root@localhost : test:55: > select count(*) from sbtest2;
+———-+
| count(*) |
+———-+
|  8503320 |
+———-+
1 row in set (32.21 sec)
2.1 執行備份：
# innobackupex –defaults-file=/etc/my.cnf –user=root –password=123456 /data/backup
apply-log：
# innobackupex –defaults-file=/etc/my.cnf –apply-log /data/backup
刪除sbtest1表
mysql> drop table sbtest.sbtest1;
利用mysql-utilities工具讀取表結構（不支援MariaDB哦）
2.2 mysqlfrm相關引數介紹：
–basedir ：如 –basedir=/usr/local/percona-5.6.21
–server ： 如 –server=user:password@192.168.1.100:3306
–diagnostic ： 開啟按位元組模式來恢復frm結構
–user ：啟動MySQL使用者，通常為mysql
[root@yuelei1 test]# mysqlfrm –diagnostic sbtest2.frm
# WARNING: Cannot generate character set or collation names without the –server option.
# CAUTION: The diagnostic mode is a best-effort parse of the .frm file. As such, it may not identify all of the components of the table correctly. This is especially true for damaged files. It will also not read the default values for the columns and the resulting statement may not be syntactically correct.
# Reading .frm file for sbtest2.frm:
# The .frm file is a TABLE.
# CREATE TABLE Statement:

CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL,
  `c` char(360) NOT NULL,
  `pad` char(180) NOT NULL,
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

#…done.

[root@yuelei1 test]# mysqlfrm –basedir=/opt/mysql sbtest2.frm –user=root –port=3307
# Spawning server with –user=root.
# Starting the spawned server on port 3307 … done.
# Reading .frm files
#
# Reading the sbtest2.frm file.
#
# CREATE statement for sbtest2.frm:
#

CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT `0`,
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT “,
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT “,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

#…done.

[root@yuelei1 test]# mysqlfrm –server=powdba:abc123@127.0.0.1:3306 sbtest2.frm –user=root –port=3307    
WARNING: Using a password on the command line interface can be insecure.
# Source on 127.0.0.1: … connected.
# Spawning server with –user=root.
# Starting the spawned server on port 3307 … done.
# Reading .frm files
#
# Reading the sbtest2.frm file.
#
# CREATE statement for sbtest2.frm:
#

CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT `0`,
  `c` char(120) COLLATE utf8_bin NOT NULL DEFAULT “,
  `pad` char(60) COLLATE utf8_bin NOT NULL DEFAULT “,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

#…done.
注：
使用–diagnostic比–basedir和–server模式的3倍；這應該是mysqlfrm在使用–diagnostic模式時，無法進行字元編碼校驗所致引起的。
建議：
能用–server模式時，儘量使用–server同時保證提供mysqld環境與原生產環境的一致。
2.3 執行恢復
加一個寫鎖，確保安全
mysql> lock tables sbtest2 write;
丟棄表空間
mysql> alter table sbtest2 discard tablespace;  
Query OK, 0 rows affected (0.00 sec)
從備份中拷貝ibd檔案，並且修改許可權
# cp /data/backup/test/sbtest2.ibd /data/mysql/data/dba_test
# chown -R mysql.mysql /data/mysql/data
這裡有警告，可以忽略。詳情可以看：https://yq.aliyun.com/articles/59271
檢驗查詢資料是否一致：
mysql> alter table sbtest2 import tablespace;
Query OK, 0 rows affected, 1 warning (0.08 sec)

root@localhost : dba_test:51: > select count(*) from sbtest2;
+———-+
| count(*) |
+———-+
|  8503320 |
+———-+
1 row in set (54.43 sec)
最後解鎖：
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

總結：
1、通過xtrabackup全備中的ibd檔案以及frm檔案恢復資料成功，這樣也就代表著xtrabackup就算備份失敗，只要有部分ibd檔案以及frm檔案保證完好，MySQL也是可以進行資料恢復的，在極端情況下也能儘可能的減少損失。但是由於xtrabackup是通過記錄redo日誌的方式來儲存備份過程中產生的增量資料，這一部分增量資料目前還沒有辦法恢復。
2、在MySQL大表遷移方案提供了一種思路：直接拷貝ibd檔案的方式


相關文章