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:
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:
[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:
[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.
1 Like
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?
1 Like
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?
1 Like
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.
2 Likes
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:
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.
1 Like
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.
2 Likes
Thank you for the amazing assistance @matthewb.
1 Like