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'
Create new database:
Get utility dbsake
from https://dbsake.readthedocs.io/en/latest/
Step 2
Checking on table:
Execute prepare by xtrabackup
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:
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:
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 |
+-----------------------------+
Links
http://mysqlentomologist.blogspot.com/2019/05/on-importing-innodb-tablespaces-and-row.html