Skip to content

Restoring database from .frm .ibd files

Initial state

Whole database server has been dumped by Percona xtrabackup utility. There are all file of all databases.

Goal

Restore ONE database on MariaDB Ver 15.1 server.

Recovering

Files created by xtrabackup of host, with only one databases: /files/ But all files from root ! Example:

# ls -ls /files/
total 3028204
     20 drwxr-xr-x 2 root root      20480 Nov 27 09:35 db1
2568196 -rw-r--r-- 1 root root 2629828608 Nov 27 09:37 ibdata1
  49152 -rw-r----- 1 root root   50331648 Nov 27 09:37 ib_logfile0
  49152 -rw-r----- 1 root root   50331648 Nov 27 09:37 ib_logfile1
  12288 -rw-r----- 1 root root   12582912 Nov 27 09:37 ibtmp1
      4 -rw-r--r-- 1 root root         78 Nov 27 09:05 xtrabackup_binlog_info
      4 -rw-r--r-- 1 root root         41 Nov 27 09:35 xtrabackup_binlog_pos_innodb
      4 -rw-r--r-- 1 root root        143 Nov 27 09:37 xtrabackup_checkpoints
      4 -rw-r--r-- 1 root root        625 Nov 27 08:49 xtrabackup_info
 349376 -rw-r--r-- 1 root root  357761024 Nov 27 09:37 xtrabackup_logfile
      4 -rw-r--r-- 1 root root          1 Nov 27 09:35 xtrabackup_master_key_id

Mysql DB path: /var/lib/mysql

Step 1 Preparation

Configure mysql in `/etc/mysql/mariadb.conf.d/50-server.cnf'

[mysqld]
innodb_file_per_table=ON

Create new database:

mysql -e 'drop database db1; create database db1;'

Get utility dbsake from https://dbsake.readthedocs.io/en/latest/

curl -o dbsake -s http://get.dbsake.net
chmod +x dbsake

Step 2

Checking on table:

hexdump -C /var/lib/mysql/db1/statistic.frm | awk '/00000020/ {print $10}'
00

Execute prepare by xtrabackup

xtrabackup --prepare --export --target-dir=/files/

Version of xtrabackup should be nearest to version of created backup. It can be checked:

# grep ^tool_ xtrabackup_info
tool_name = xtrabackup
tool_command = --backup --databases-exclude=trade --stream=xbstream --user=root --password=...
tool_version = 2.3.10

# xtrabackup -v
xtrabackup: recognized server arguments: --innodb_file_per_table=1 
xtrabackup version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)

On version 8.3 was error:

!!!!

Import tables schema to the new database:

for tbl in `ls -1 /files/db1/*.frm`; do ./dbsake frmdump $tbl | mysql db1; done;

Generate DISCARD and IMPORT sql scripts:

mysql -N -B <<EOF > discard.sql
SELECT CONCAT('ALTER TABLE ',table_name,' DISCARD TABLESPACE;') AS a
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1';
EOF

mysql -N -B <<EOF > import.sql
SELECT CONCAT('ALTER TABLE ',table_name,' IMPORT TABLESPACE;') AS a
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db1';
EOF

Discard existing tablespaces. This step will remove *.ibd files

mysql db1 < discard.sql --force

ls -la /var/lib/mysql/db1/*.ibd
ls: cannot access '/var/lib/mysql/db1/*.ibd': No such file or directory

Copy .ibd files to database path:

cp /files/db1/*.ibd /var/lib/mysql/db1/
cp /files/db1/*.cfg /var/lib/mysql/db1/
chown -R mysql:mysql /var/lib/mysql/db1/

Import tablespaces:

mysql db1 < import.sql --force

Troubleshooting

On step import tablespace many errors:

ERROR 1808 (HY000) at line 97: Schema mismatch (Expected FSP_SPACE_FLAGS=0x29, .ibd file contains 0x0.)
ERROR 1808 (HY000) at line 1: Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x0.)
ERROR 1808 (HY000) at line 2: Schema mismatch (Expected FSP_SPACE_FLAGS=0x15, .ibd file contains 0x0.)
ERROR 1808 (HY000) at line 1: Schema mismatch (Table flags don't match, server table has 0x21 and the meta-data file has 0x1; .cfg file uses ROW_FORMAT=COMPACT)
> set global innodb_default_row_format=DYNAMIC;

> select @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic                     |
+-----------------------------+

http://mysqlentomologist.blogspot.com/2019/05/on-importing-innodb-tablespaces-and-row.html