Restore single MySQL table (ibd) from Xtrabackup (video demo)

In this blog we will create MySQL backup and then restore single table from Percona Xtrabackup without doing full restore. This post is basically a work-log in response to a feedback on my previous blog about MySQL Interview with ChatGPT.

Here is a video recording of the steps described below for restoring single table from xtrabackup.

Create a compressed MySQL backup using Xtrabackup

xtrabackup --backup --compress --compress-threads=8 --target-dir=/root/backupdir

Output in backup directory

[root@ip-172-31-83-17 ~]# ls -lhtr /root/backupdir
total 3.1M
-rw-r-----. 1 root root 164K Dec 14 17:11 ibdata1.qp
drwxr-x---. 2 root root 31 Dec 14 17:11 sys
drwxr-x---. 2 root root 23 Dec 14 17:11 test
-rw-r-----. 1 root root 2.3M Dec 14 17:11 mysql.ibd.qp
-rw-r-----. 1 root root 287K Dec 14 17:11 undo_002.qp
-rw-r-----. 1 root root 287K Dec 14 17:11 undo_001.qp
drwxr-x---. 2 root root 161 Dec 14 17:11 mysql
drwxr-x---. 2 root root 8.0K Dec 14 17:11 performance_schema
-rw-r-----. 1 root root 188 Dec 14 17:11 binlog.000010.qp
-rw-r-----. 1 root root 106 Dec 14 17:11 binlog.index.qp
-rw-r-----. 1 root root 105 Dec 14 17:11 xtrabackup_binlog_info.qp
-rw-r-----. 1 root root 383 Dec 14 17:11 xtrabackup_logfile.qp
-rw-r-----. 1 root root 137 Dec 14 17:11 xtrabackup_checkpoints
-rw-r-----. 1 root root 1.4K Dec 14 17:11 ib_buffer_pool.qp
-rw-r-----. 1 root root 445 Dec 14 17:11 backup-my.cnf.qp
-rw-r-----. 1 root root 458 Dec 14 17:11 xtrabackup_info.qp
-rw-r-----. 1 root root 130 Dec 14 17:11 xtrabackup_tablespaces.qp

Dropping a table (to create dummy emergency)

[root@ip-172-31-83-17 ~]# mysql
...
mysql> drop table test.t1;
Query OK, 0 rows affected (0.02 sec)

Copy backup files to a temporary location to extract single table for restoring

[root@ip-172-31-83-17 ~]# mkdir /tmp/restore
[root@ip-172-31-83-17 ~]# cp -R /root/backupdir/* /tmp/restore

Decompress the backup files

[root@ip-172-31-83-17 ~]# xtrabackup --decompress --target-dir=/tmp/restore --remove-original
2022-12-14T17:12:18.239004-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql --log_bin=/var/lib/mysql/binlog
2022-12-14T17:12:18.239194-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --user=root --password=* --decompress=1 --target-dir=/tmp/restore --remove-original=1
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2022-12-14T17:12:18.259287-00:00 0 [Note] [MY-011825] [Xtrabackup] decompressing ./backup-my.cnf.qp
...

Prepare the backup for importing with –export option

[root@ip-172-31-83-17 ~]# xtrabackup --prepare --export --target-dir=/tmp/restore
...
2022-12-14T17:12:27.172446-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksums=1 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648 --innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2 --server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0 --innodb_undo_log_encrypt=0
2022-12-14T17:12:27.172601-00:00 0 [Note] [MY-011825] [Xtrabackup] recognized client arguments: --prepare=1 --export=1 --target-dir=/tmp/restore
xtrabackup version 8.0.30-23 based on MySQL server 8.0.30 Linux (x86_64) (revision id: 873b467185c)
2022-12-14T17:12:27.172633-00:00 0 [Note] [MY-011825] [Xtrabackup] auto-enabling --innodb-file-per-table due to the --export option
2022-12-14T17:12:27.172650-00:00 0 [Note] [MY-011825] [Xtrabackup] cd to /tmp/restore/
2022-12-14T17:12:27.172750-00:00 0 [Note] [MY-011825] [Xtrabackup] This target seems to be not prepared yet.
2022-12-14T17:12:27.173185-00:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-14T17:12:27.181337-00:00 0 [Note] [MY-011825] [Xtrabackup] xtrabackup_logfile detected: size=8388608, start_lsn=(19690717)
2022-12-14T17:12:27.185980-00:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration for recovery:
2022-12-14T17:12:27.186001-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2022-12-14T17:12:27.186011-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
...
2022-12-14T17:12:29.477906-00:00 0 [Note] [MY-013072] [InnoDB] Starting shutdown...
2022-12-14T17:12:29.580573-00:00 0 [Note] [MY-013084] [InnoDB] Log background threads are being closed...
2022-12-14T17:12:29.598559-00:00 0 [Note] [MY-012980] [InnoDB] Shutdown completed; log sequence number 19691030
2022-12-14T17:12:29.599239-00:00 0 [Note] [MY-013251] [InnoDB] Number of pools: 1
2022-12-14T17:12:29.600397-00:00 0 [Note] [MY-011825] [Xtrabackup] completed OK!

Reviewing exported tablespace files

[root@ip-172-31-83-17 ~]# cd /tmp/restore/test/
[root@ip-172-31-83-17 test]# ls -lhtr
total 116K
-rw-r--r--. 1 root root 112K Dec 14 17:12 t1.ibd
-rw-r--r--. 1 root root 706 Dec 14 17:12 t1.cfg

Creating table, discarding empty tablespace and getting ready for restore

mysql> use test
Database changed
mysql> create table t1 (id int unsigned NOT NULL AUTO_INCREMENT,
-> val varchar(10) DEFAULT NULL,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from test.t1;
Empty set (0.00 sec)
mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.00 sec)

Restoring tablespace files and importing tablespace

mysql> ! cp /tmp/restore/test/* /var/lib/mysql/test/
mysql> ! ls -lhtr /var/lib/mysql/test/
total 116K
-rw-r--r--. 1 root root 112K Dec 14 17:13 t1.ibd
-rw-r--r--. 1 root root 706 Dec 14 17:13 t1.cfg

mysql> alter table t1 import tablespace;
ERROR 1815 (HY000): Internal error: Cannot reset LSNs in table test.t1 : Tablespace not found

mysql> ! chown mysql:mysql /var/lib/mysql/test -R
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from test.t1;
+----+------+
| id | val |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
3 rows in set (0.00 sec)

Alright, that’s it. We have successfully restored our table that was “accidentally” dropped from the full Xtrabackup.

Quick recipes for Xtrabackup

Installing Percona Xtrabackup

sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install qpress
yum install percona-xtrabackup-80

Creating Backup

xtrabackup --backup --compress --compress-threads=8 --stream=xbstream --parallel=4 --target-dir=/root/backupdir > /root/backupdir/backup.xbstream

Restoring full backup

xbstream -x -p 4 -C /var/lib/mysql/ < /root/backupdir/backup.xbstream
xtrabackup --decompress --remove-original --parallel=8 --target-dir=/var/lib/mysql/
xtrabackup --prepare --target-dir=/var/lib/mysql/
chown mysql:mysql -R /var/lib/mysql

I hope this helps.

Update:

Extract Table Definition from IBD (datafile)

What if you have the ibd file but don’t have the table definition? In such scenarios you can use the ibd2sdi, the innodb tablespace extraction utility. It will extract the Serialized dictionary information (SDI) from InnoDB tablespace files. Refer this.

I’d also like to highlight the tool created by Marcelo here: https://github.com/altmannmarcelo/sdi2ddl