# Single table restore from Xtrabackup **URL:** \ **Category:** Percona XtraBackup\ **Created:** [August 10, 2022, 2:26pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978 "2022-08-10T14:26:52Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Catoman](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Catoman](https://forums.percona.com/u/Catoman)\ **Post date:** [August 10, 2022, 2:26pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/1 "2022-08-10T14:26:52Z")
Hi. Additional question to a recent question. I have managed to download the ibd file i wanted but when trying to import it into a database it causes MySQL to crash horribly. It seems like MySQL needs a .cfg file but there is no file like that in the backup. I found that xtrabackup --prepare --export --target-dir=/data/backups/mysql/ found here : [Restoring Individual Tables - Percona XtraBackup](https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/restoring_individual_tables.html?h=restore) should give you that but the xtrabackup command requires first this file : xtrabackup: Error: cannot open ./xtrabackup\_checkpoints and also xtrabackup: Warning: cannot open ./xtrabackup\_logfile. will try to find. Seems like i’m still missing something here. MySQL version 5.7.22 Xtrabackup 2.4.26 BR Johan ---
**Author:** ![Catoman](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Catoman](https://forums.percona.com/u/Catoman)\ **Post date:** [August 10, 2022, 2:42pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/2 "2022-08-10T14:42:12Z")
Example : 1. Copy the .ibd file to the server from GCP storage using xbcloud get 2. Create an empty database called restore 3. Make a copy of the table inside ‘restore’ database ( CREATE TABLE restore.$TABLE LIKE $DB.$TABLE;) 4. Remove the new tables .ibd (ALTER TABLE restore.$TABLE DISCARD TABLESPACE;) 5. Copy the .ibd file to the new location root@server [(none)]\> ALTER TABLE restore.$TABLE IMPORT TABLESPACE; Query OK, 0 rows affected, 2 warnings (0.03 sec) Seems like it succeeded but soon after MySQL crashes completely and just continues to restart. It seems something has corrupted the data (which is fine because it’s a test of course) MySQL log says this : Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.214075Z 72 [Note] InnoDB: Sync to disk Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Sync to disk Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.216909Z 72 [Note] InnoDB: Sync to disk - done! Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.216979Z 72 [Note] InnoDB: Phase I - Update all pages Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Sync to disk - done! Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Phase I - Update all pages Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.233748Z 72 [Note] InnoDB: Sync to disk Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Sync to disk Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.236654Z 72 [Note] InnoDB: Sync to disk - done! Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Sync to disk - done! Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.237471Z 72 [Note] InnoDB: Phase III - Flush changes to disk Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Phase III - Flush changes to disk Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.240139Z 72 [Note] InnoDB: Phase IV - Flush complete Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: Phase IV - Flush complete Aug 10 11:35:35 io1-0222 mysqld[2824]: 2022-08-10T09:35:35.240249Z 72 [Note] InnoDB: `restore`.`stritz_UserAbCases` autoinc value set to 0 Aug 10 11:35:35 io1-0222 mysqld[2826]: InnoDB: `restore`.`stritz_UserAbCases` autoinc value set to 0 Aug 10 11:36:14 io1-0222 mysqld[2824]: 2022-08-10 11:36:14 0x7f03d815a700 InnoDB: Assertion failure in thread 139654486927104 in file btr0pcur.cc line 454 Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: Failing assertion: btr\_page\_get\_prev(next\_page, mtr) == btr\_pcur\_get\_block(cursor)-\>page.id.page\_no() Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: We intentionally generate a memory trap. Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: Submit a detailed bug report to [http://bugs.mysql.com](http://bugs.mysql.com/). Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: If you get repeated assertion failures or crashes, even Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: immediately after the mysqld startup, there may be Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: corruption in the InnoDB tablespace. Please refer to Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: [MySQL :: MySQL 5.7 Reference Manual :: 14.22.2 Forcing InnoDB Recovery](http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html) Aug 10 11:36:14 io1-0222 mysqld[2824]: InnoDB: about forcing recovery. Aug 10 11:36:14 io1-0222 mysqld[2824]: 09:36:14 UTC - mysqld got signal 6 ; Aug 10 11:36:14 io1-0222 mysqld[2824]: This could be because you hit a bug. It is also possible that this binary Aug 10 11:36:14 io1-0222 mysqld[2824]: or one of the libraries it was linked against is corrupt, improperly built, Aug 10 11:36:14 io1-0222 mysqld[2824]: or misconfigured. This error can also be caused by malfunctioning hardware. Aug 10 11:36:14 io1-0222 mysqld[2824]: Attempting to collect some information that could help diagnose the problem. Aug 10 11:36:14 io1-0222 mysqld[2824]: As this is a crash and something is definitely wrong, the information Aug 10 11:36:14 io1-0222 mysqld[2824]: collection process might fail. Aug 10 11:36:14 io1-0222 mysqld[2824]: key\_buffer\_size=8388608 Aug 10 11:36:14 io1-0222 mysqld[2824]: read\_buffer\_size=131072 Aug 10 11:36:14 io1-0222 mysqld[2824]: max\_used\_connections=5 Aug 10 11:36:14 io1-0222 mysqld[2824]: max\_threads=40000 Aug 10 11:36:14 io1-0222 mysqld[2824]: thread\_count=2 Aug 10 11:36:14 io1-0222 mysqld[2824]: connection\_count=2 Aug 10 11:36:14 io1-0222 mysqld[2824]: It is possible that mysqld could use up to Aug 10 11:36:14 io1-0222 mysqld[2824]: key\_buffer\_size + (read\_buffer\_size + sort\_buffer\_size)_max\_threads = 15903192 K bytes of memory Aug 10 11:36:14 io1-0222 mysqld[2824]: Hope that’s ok; if not, decrease some variables in the equation. Aug 10 11:36:14 io1-0222 mysqld[2824]: Thread pointer: 0x7f0394000b20 Aug 10 11:36:14 io1-0222 mysqld[2824]: Attempting backtrace. You can use the following information to find out Aug 10 11:36:14 io1-0222 mysqld[2824]: where mysqld died. If you see no messages after this, something went Aug 10 11:36:14 io1-0222 mysqld[2824]: terribly wrong… Aug 10 11:36:14 io1-0222 mysqld[2824]: stack\_bottom = 7f03d8159e60 thread\_stack 0x40000 Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(my\_print\_stacktrace+0x2c)[0x56054b9dcdfc] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(handle\_fatal\_signal+0x443)[0x56054b2ffce3] Aug 10 11:36:14 io1-0222 mysqld[2824]: /lib/x86\_64-linux-gnu/libpthread.so.0(+0x12730)[0x7f4da28a5730] Aug 10 11:36:14 io1-0222 mysqld[2824]: /lib/x86\_64-linux-gnu/libc.so.6(gsignal+0x10b)[0x7f4da23817bb] Aug 10 11:36:14 io1-0222 mysqld[2824]: /lib/x86\_64-linux-gnu/libc.so.6(abort+0x121)[0x7f4da236c535] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(+0x691eb5)[0x56054b2c6eb5] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(+0x693fa8)[0x56054b2c8fa8] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z15row\_search\_mvccPh15page\_cur\_mode\_tP14row\_prebuilt\_tmm+0xd03)[0x56054bb1c0f3] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_ZN11ha\_innobase13general\_fetchEPhjj+0xdf)[0x56054ba1b5df] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_ZN7handler13ha\_index\_nextEPh+0x1c5)[0x56054b3529c5] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(+0xb6151c)[0x56054b79651c] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z10sub\_selectP4JOINP7QEP\_TABb+0x18f)[0x56054b79c91f] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_ZN4JOIN4execEv+0x20b)[0x56054b795d0b] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z12handle\_queryP3THDP3LEXP12Query\_resultyy+0x2e0)[0x56054b7fbce0] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(+0xb89f1b)[0x56054b7bef1b] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z21mysql\_execute\_commandP3THDb+0x4851)[0x56054b7c5eb1] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z11mysql\_parseP3THDP12Parser\_state+0x38d)[0x56054b7c80dd] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z16dispatch\_commandP3THDPK8COM\_DATA19enum\_server\_command+0x1512)[0x56054b7c9702] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(\_Z10do\_commandP3THD+0x207)[0x56054b7ca3f7] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(handle\_connection+0x2a0)[0x56054b880400] Aug 10 11:36:14 io1-0222 mysqld[2824]: /usr/sbin/mysqld(pfs\_spawn\_thread+0x157)[0x56054bd2a507] Aug 10 11:36:14 io1-0222 mysqld[2824]: /lib/x86\_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f4da289afa3] Aug 10 11:36:14 io1-0222 mysqld[2824]: /lib/x86\_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f4da2442eff] Aug 10 11:36:14 io1-0222 mysqld[2824]: Trying to get some variables. Aug 10 11:36:14 io1-0222 mysqld[2824]: Some pointers may be invalid and cause the dump to abort. Aug 10 11:36:14 io1-0222 mysqld[2824]: Query (7f0394007e70): select count(_) from stritz\_UserAbCases Aug 10 11:36:14 io1-0222 mysqld[2824]: Connection ID (thread ID): 72 Aug 10 11:36:14 io1-0222 mysqld[2824]: Status: NOT\_KILLED Aug 10 11:36:14 io1-0222 mysqld[2824]: The manual page at [MySQL :: MySQL 8.0 Reference Manual :: B.3.3.3 What to Do If MySQL Keeps Crashing](http://dev.mysql.com/doc/mysql/en/crashing.html) contains Aug 10 11:36:14 io1-0222 mysqld[2824]: information that should help you find out what is causing the crash. ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 10, 2022, 3:33pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/3 "2022-08-10T15:33:49Z")
> 1. Copy the .ibd file to the server from GCP storage using xbcloud get This is the issue. You cannot do single-table restore like this. You must get the _entire_ backup from cloud storage, then run `xtrabackup --prepare --export` on the entire backup in order to get the .cfg files. Then, you can do the remaining steps for restore. 1. Copy the .ibd **AND .cfg** file to the new location ---
**Author:** ![Catoman](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Catoman](https://forums.percona.com/u/Catoman)\ **Post date:** [August 11, 2022, 7:15am UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/4 "2022-08-11T07:15:11Z")
Hi Matthew, Ah, ok. I understand. Thanks for your help. BR Johan ---
**Author:** ![Catoman](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Catoman](https://forums.percona.com/u/Catoman)\ **Post date:** [August 11, 2022, 9:09am UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/5 "2022-08-11T09:09:41Z")
Hi again, Can you consider single/multi table restore from an Xtrabackup run a feature request ? 🙂 BR Johan ---
**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\ **Post date:** [August 11, 2022, 12:30pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/6 "2022-08-11T12:30:30Z")
This is already a feature of xtrabackup; you just have to follow the documentation on how to accomplish it, which I described briefly above. ---
**Author:** ![Marcelo\_Altmann](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/marcelo_altmann/32/11299_2.png) [@Marcelo\_Altmann](https://forums.percona.com/u/Marcelo_Altmann)\ **Post date:** [August 11, 2022, 1:14pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/7 "2022-08-11T13:14:02Z")
Hi @Catoman , you can actually restore a single or more tables from a full backup, you just need to copy some metadata files required by xtrabackup --prepare operation. You will do the following: 1. Restore required files (ibdata1 xtrabackup\_checkpoints xtrabackup\_info xtrabackup\_logfile) + the table files you want to restore. For example to restore sbtest1 I will need the following: ```auto xbcloud get --storage=s3 --s3-endpoint='http://localhost:9000' --s3-access-key='admin' --s3-secret-key='password' \ --s3-bucket='newbucket' full_backup ibdata1 xtrabackup_checkpoints xtrabackup_info xtrabackup_logfile sbtest/sbtest1.frm sbtest/sbtest1.ibd | xbstream -x -C /tmp/partial1/ ``` 2 . follow the normal steps of prepare with --export: ```auto xtrabackup --prepare --export --target-dir=/tmp/partial1 ``` 3 . Discard tablespace + copy files + import tablespace: ```auto ALTER TABLE sbtest1 DISCARD TABLESPACE; $ cp /tmp/partial1/sbtest/sbtest1.{cfg,exp,ibd} /work/ps/ins/5.7/datadir1/sbtest/ ALTER TABLE sbtest1 IMPORT TABLESPACE; ``` this should give you the sbtest1 table extracted from a full backup. Please note that the list of files for 8.0 will be a bit different. ---
**Author:** ![Catoman](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Catoman](https://forums.percona.com/u/Catoman)\ **Post date:** [August 11, 2022, 1:17pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/8 "2022-08-11T13:17:00Z")
Thanks guys, i’ll test this tomorrow ---
**Author:** ![Catoman](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@Catoman](https://forums.percona.com/u/Catoman)\ **Post date:** [August 12, 2022, 12:46pm UTC](https://forums.percona.com/t/single-table-restore-from-xtrabackup/16978/9 "2022-08-12T12:46:57Z")
All good now. It works to restore one or more tables