Hello,
I’m working on improving the way we do backup of our mysql server using xtrabackup, which includes testing restoration scenario.
the restoration I just did gave me an error about 2 ibd files (among 200 tables) which are reported this way
2021-03-03T09:25:18.724912Z 28 [ERROR] InnoDB: Failed to find tablespace for table `db_name`.`table_name` in the cache. Attempting to load the tablespace with space id 50561
2021-03-03T09:25:18.724992Z 28 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace other_db/table_name at filepath: ./other_db/table_name.ibd uses space ID: 50563. Cannot open filepath: ./db_name/table_name.ibd which uses the same space ID.
2021-03-03T09:25:18.724999Z 28 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2021-03-03T09:25:18.725002Z 28 [ERROR] InnoDB: The error means the system cannot find the path specified.
2021-03-03T09:25:18.725005Z 28 [ERROR] InnoDB: Could not find a valid tablespace file for `db_name/table_name`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
2021-03-03T09:25:18.725027Z 28 [Warning] InnoDB: Cannot calculate statistics for table `db_name`.`table_name` because the .ibd file is missing. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
The specified idb files are on the disk and not-zero sized.
This seems to have a big impact on the server as I can see since CPU spike and such exception message
Thread pointer: 0x7f24e4000d40
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f3c5c285e50 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xeb8fdb]
/usr/sbin/mysqld(handle_fatal_signal+0x377)[0x77bb47]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12980)[0x7f3c68660980]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7f3c6795cfb7]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7f3c6795e921]
/usr/sbin/mysqld[0x751edc]
/usr/sbin/mysqld(_ZN2ib5fatalD1Ev+0x66)[0x1090356]
/usr/sbin/mysqld[0x113c47d]
/usr/sbin/mysqld[0x113cc6d]
/usr/sbin/mysqld(_Z6fil_ioRK9IORequestbRK9page_id_tRK11page_size_tmmPvS8_+0x322)[0x1148de2]
/usr/sbin/mysqld(_Z13buf_read_pageRK9page_id_tRK11page_size_t+0xdd)[0x10fbb4d]
/usr/sbin/mysqld(_Z16buf_page_get_genRK9page_id_tRK11page_size_tmP11buf_block_tmPKcmP5mtr_tb+0x475)[0x10cd7a5]
/usr/sbin/mysqld(_Z31btr_cur_open_at_index_side_funcbP12dict_index_tmP9btr_cur_tmPKcmP5mtr_t+0x333)[0x10a7de3]
/usr/sbin/mysqld(_Z22row_search_max_autoincP12dict_index_tPKcPm+0x1b0)[0xffd780]
/usr/sbin/mysqld(_ZN11ha_innobase27innobase_initialize_autoincEv+0xb6)[0xef89b6]
/usr/sbin/mysqld(_ZN11ha_innobase4openEPKcij+0xca7)[0xeff627]
/usr/sbin/mysqld(_ZN7handler7ha_openEP5TABLEPKcii+0x33)[0x7d1ad3]
/usr/sbin/mysqld(_Z21open_table_from_shareP3THDP11TABLE_SHAREPKcjjjP5TABLEb+0x7f3)[0xd0cc03]
/usr/sbin/mysqld(_Z10open_tableP3THDP10TABLE_LISTP18Open_table_context+0x1176)[0xc16876]
/usr/sbin/mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x542)[0xc183f2]
/usr/sbin/mysqld(_Z21open_tables_for_queryP3THDP10TABLE_LISTj+0x59)[0xc18e09]
/usr/sbin/mysqld[0xccafa4]
/usr/sbin/mysqld(_Z14get_all_tablesP3THDP10TABLE_LISTP4Item+0x818)[0xccb9b8]
/usr/sbin/mysqld[0xcb641c]
/usr/sbin/mysqld(_Z24get_schema_tables_resultP4JOIN23enum_schema_table_state+0x225)[0xcc7625]
/usr/sbin/mysqld(_ZN4JOIN14prepare_resultEv+0xc5)[0xcac105]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x99)[0xc390a9]
/usr/sbin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x23a)[0xcaca6a]
/usr/sbin/mysqld[0x746966]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THDb+0x48ca)[0xc6d23a]
/usr/sbin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x3a5)[0xc6f8a5]
/usr/sbin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xaf6)[0xc70456]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x220)[0xc71f40]
/usr/sbin/mysqld(handle_connection+0x298)[0xd37c18]
/usr/sbin/mysqld(pfs_spawn_thread+0x154)[0x121e9d4]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7f3c686556db]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f3c67a3f71f]
I’m backing up this way
xtrabackup --compress --backup --stream=xbstream --parallel 10 --encrypt=AES256 --encrypt-threads=4 --encrypt-key="xxx" --databases="db_name" | xbcloud put db_name_full_2021030201 --parallel 10 --swift-container="database_backup_db_name_2021030201"
and restoring this way
(I use Restoring Individual Tables - Percona XtraBackup as reference as I assumed restoring a database is just restoring a list of tables)
# sh: xbcloud get --parallel=10 --swift-container=database_backup_db_name_2021030301 db_name_full_2021030301 --parallel=10 | xbstream -xv -C /data/backups/full_db_db_name --parallel=10
# sh: xtrabackup --decompress --decrypt=AES256 --encrypt-key="xxxx" --target-dir=/data/backups/full_db_db_name --remove-original
# sh: xtrabackup --prepare --target-dir=/data/backups/full_db_db_name --use-memory 2G --export
mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' DISCARD TABLESPACE;' ) FROM information_schema.tables a WHERE a.TABLE_SCHEMA='db_name';
# sh: cp -a full_db_db_name/db_name/* /var/lib/mysql/db_name/
# sh: chown mysql:mysql -R /var/lib/mysql/db_name/
mysql> SELECT CONCAT( 'ALTER TABLE ', a.TABLE_SCHEMA, '.', a.TABLE_NAME, ' IMPORT TABLESPACE;' ) FROM information_schema.tables a WHERE a.TABLE_SCHEMA='db_name';
I’m using a different server (and also different version) for backup and restore.
Software stack
- percona-xtrabackup:
2.4.21 - mysql source server:
5.6.43 - mysql destination server:
5.7.33
Is there something I’m doing wrong in the backup or restore process ?
Best