Single table restore from Xtrabackup

MySQL & MariaDB Percona XtraBackup
1

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
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

1 Like
2

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.
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 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 contains Aug 10 11:36:14 io1-0222 mysqld[2824]: information that should help you find out what is causing the crash.

1 Like
3
  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
1 Like
4

Hi Matthew,

Ah, ok. I understand.
Thanks for your help.

BR
Johan

1 Like
5

Hi again,

Can you consider single/multi table restore from an Xtrabackup run a feature request ? :slight_smile:

BR
Johan

1 Like
6

This is already a feature of xtrabackup; you just have to follow the documentation on how to accomplish it, which I described briefly above.

1 Like
7

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:
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:

xtrabackup --prepare --export --target-dir=/tmp/partial1

3 . Discard tablespace + copy files + import tablespace:

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.

1 Like
8

Thanks guys, i’ll test this tomorrow

1 Like
9

All good now. It works to restore one or more tables

1 Like