# Recreating .frm files with mysqlfrm on Percona XtraDB cluster 5.7
**URL:** https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636
**Category:** Percona XtraDB Cluster 5.x
**Tags:** mysql, percona
**Created:** [November 17, 2022, 1:52pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636 "2022-11-17T13:52:06Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@MastaMike](https://forums.percona.com/u/MastaMike)
#### Post date: [November 17, 2022, 1:52pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/1 "2022-11-17T13:52:06Z")
Hello to everyone dedicating the time to read this thread.
I have recently stumbled upon a problem while restoring a MySQL 5.7 database into a Percona XtraDB cluster 5.7.
The restoration process was done with the `mysqlfrm` command supplied by the mysql-utilities package.
Basically what I do during the restoration process is done by the following command:
```nohighlight
mysqlfrm -q --user=root '--server=root:password@localhost:3306' --port=3310 /path/to/restore | sed s/^$/';'/ | mysql -B -u root -p
```
Now… the issue is that when restoring to a Percona XtraDB cluster 5.7 I get a completely different results than restoring to a regular MySQL 5.7 server.
When performing the `mysqlfrm` command with the above flags on the MySQL 5.7 server I am successfully restoring the database without any problems or issues.
but when done on a Percona XtraDB cluster 5.7 i receive syntax issues and some of the .frm files can not be opened for reading.
for example:
```nohighlight
[root@percona-1 ~]# mysqlfrm -q --user=root '--server=root:password@localhost:3306' --port=3310 /path/to/restore/table1.frm
WARNING: Using a password on the command line interface can be insecure.
ERROR: Failed to correctly read the .frm file. Please try reading the file with the --diagnostic mode.
# WARNING: The following files could not be read. You can try the --diagnostic mode to read these files.
# /root/bafi_login/jobs.frm
```
I have noticed some inconsistencies in the output errors and understand that while supplying `--diagnostic` flag to the `mysqlfrm` command I received the syntax error for queries containing the `ROW_FORMAT` variable.
it seems as if it opens it up for reading and changes `ROW_FORMAT=Compact` to `ROW_FORMAT=5`.
Now the second issue is when removing `--diagnostic`, it appears that some tables would not open for reading and by that fail to recreate the whole database.
These are the version of the tools I am using:
```nohighlight
[root@percona-1 ~]# rpm -q mysql-utilities
mysql-utilities-1.6.5-1.el7.noarch
[root@percona-1 ~]# rpm -q mysql-connector-python
mysql-connector-python-2.0.4-1.el7.noarch
[root@percona-1 ~]# mysqlfrm --version
MySQL Utilities mysqlfrm version 1.6.5
```
I am completely lost in ideas as to what can cause the restoration process to fail on the Percona XtraDB cluster 5.7.
I have searched the `/var/log/mysql.log` for answers and the `/var/log/messeges` but it seems that there is no information that can pinpoint the problem.
Any help would be greatly appreciated.
---
### Author:  [@matthewb](https://forums.percona.com/u/matthewb)
#### Post date: [November 17, 2022, 2:28pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/2 "2022-11-17T14:28:24Z")
Hello @MastaMike,
Why are you using this tool? Do you have a corrupted backup? This is not a standard tool to be used in any type of normal backup/restore process unless something insanely disastrous occurred. mysqlfrm is used to recreate a CREATE TABLE sql statement when you don’t have the original table. Is that the case? Can you give some more background information to your situation?
---
### Author:  [@MastaMike](https://forums.percona.com/u/MastaMike)
#### Post date: [November 17, 2022, 2:46pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/3 "2022-11-17T14:46:30Z")
Hey @matthewb,
thank you for the response.
Well… I am performing the backup with innobackupex.
I took into consideration that the best way to restore the InnoDB tables is by recreating the .frm files with mysqlfrm command and then rsyncing the .ibd and .cfg files into the data-dir directory.
mmm…do you have better suggestions on how to restore the database?
---
### Author:  [@matthewb](https://forums.percona.com/u/matthewb)
#### Post date: [November 17, 2022, 2:56pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/4 "2022-11-17T14:56:54Z")
Hi @MastaMike,
First off, innobackupex is dead, dead, dead and has been for almost 3 years (if not longer). You should be using the latest Percona Xtrabackup 2.4 (for 5.7). Additionally, even if using the dead innobackupex, all of the information needed to restore is captured by this tool. There is absolutely no reason why you would need to be using mysqlfrm for standard restore. The .frm files themselves are the schema. If you have them already along with the .idb datafiles, there is is nothing you need to do other than simple restore.
Side note: .frm files are gone in MySQL 8.0 and replaced with a centralized internal data dictionary.
Here’s a quick rundown:
```
xtrabackup --backup --target-dir /var/lib/mysql --stream xbstream > /path/to/backup.xbs
```
That will backup everything needed to do a restore in the future, all contained into a single file.
Restore:
```
xbstream -C /var/lib/mysql -vx < /path/to/backup.xbs -- This extracts all files from the archive
xtrabackup --prepare --target-dir /var/lib/mysql --use-memory 4G -- This prepares the backup. A necessary/required step.
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql
```
That’s it. Super simple.
---
### Author:  [@MastaMike](https://forums.percona.com/u/MastaMike)
#### Post date: [November 20, 2022, 1:32pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/5 "2022-11-20T13:32:50Z")
Hey @matthewb,
So I was trying to initiate the solution you offered but found out I can’t really perform a restore for a single database without deleting the whole data dir.
For each restore, I would need to recreate the whole data dir from scratch
In addition to that, I tried to perform the same thing with innobackupex but failed to find a suitable solution for my case.
I want to give an example of my struggle.
let’s say I have 7 databases residing on MySQL 5.7 server and I want to import only one database into my percona cluster I will perform as follows.
From the MySQL 5.7 server I will run the following command and create a single database backup as specified in the man page:
```nohighlight
xtrabackup --user=root --password='passw0rd' --backup --databases='example mysql sys performance_schema' --stream xbstream > /backup/example.xbs
```
I will import the .xbs backup to the percona cluster and would need to delete my data dir in order to successfully import the backup (rm -rvf /var/lib/mysql/\*) after completing that process I continued as you explained in the solution.
That’s all nice and simple I have tested it and it works flawlessly.
But what if I have already a two-functioning database on my percona xtradb cluster and I only want to import a backup without deleting the whole data dir how would I need to execute this?
tried to find something that would let me recreate a database from backup without affecting additional databases on the same cluster but it seems that whatever I tried I always destroyed my local test lab.
---
### Author:  [@matthewb](https://forums.percona.com/u/matthewb)
#### Post date: [November 20, 2022, 9:13pm UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/6 "2022-11-20T21:13:59Z")
> [@MastaMike](#):
>
> I will import the .xbs backup to the percona cluster and would need to delete my data dir in order to successfully import the backup
Correct. This is due to the fundamental design of how InnoDB (the primary engine inside MySQL) works. The `ibdata1` file contains metadata information about every InnoDB table in every database. There is no easy way to simply “restore a single database” because of this design.
What you can do, is a table-by-table import for all tables inside a particular database. Use `xtrabackup --prepare --export` to prepare the backup as usual, but also mark every table as importable using [transportable tablespaces](https://dev.mysql.com/doc/refman/5.7/en/innodb-table-import.html).
---
### Author:  [@MastaMike](https://forums.percona.com/u/MastaMike)
#### Post date: [November 21, 2022, 5:46am UTC](https://forums.percona.com/t/recreating-frm-files-with-mysqlfrm-on-percona-xtradb-cluster-5-7/18636/7 "2022-11-21T05:46:17Z")
Thank you for the amazing assistance @matthewb.