How do I convert a single file from .idb to .sql?

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I need the database of my own site, (I backed it up with xtrabackup,) which I ended the service last year, the data is 2gb in size, but it has an .idb extension, I have not been able to convert it to .sql for about 6 hours, I am currently a Windows user, but WSL Ubuntu is installed on my computer.

And for someone who really doesn’t understand these things, it’s as hard as learning Chinese, please tell me how to convert .idb to .sql in the easiest way, Thank you guys.

1 Like
2

@neverm2 A .ibd file is an InnoDB datafile; a tablespace. You cannot convert it to SQL. This file is the raw data. You must spin up a new MySQL server and import this tablespace into that server. From there, you can use mysqldump to export the data to SQL. You must also have the schema definition.

Now, you say you used xtrabackup to take the backup. This is a linux-only tool which takes a complete backup of the entire mysql server. You should be able to restore this backup, prepare it, and then start mysql on this restored directory.

3

These are all the files I have from last year, are they all enough for restore?

iLWJy15hafKl

1 Like
4

Yes, that looks like a typical mysql data directory. You need to xtrabackup --prepare inside that directory. Then you should be able to start mysql using that directory as the $datadir (find your my.ini and set that variable). Once mysql has started, use mysqldump to dump all the data as SQL.

1 Like
5

I appreciate your help, can I do this with wsl ubuntu with mysql installed? What exactly do I need, or can you share a video or guide?

6

I have no idea if you can do it with WSL-Ubuntu, mainly because I’d never heard of that until your post :slight_smile: I do not have a guide, I’m afraid. I just have documentation on our site for using xtrabackup. Are you trying to start mysql as a windows service or are you starting it “inside” Ubuntu?

7

I think I will encounter a lot of problems this way, instead I will buy a temporary centos server, install mysql and xtrabackup and convert it to .sql that way, I guess this is better, right?

8

Get on AWS and get a free t3.micro. That’ll be plenty enough to get this job done. Use Ubuntu 22.04, install mysql, xtrabackup. Then stop mysql, erase the existing datadir (/var/lib/mysql), restore the backup to the datadir, prepare it using xtrabackup, chown -R mysql:mysql /var/lib/mysql, then start mysql. After it starts, use mysqldump to export to SQL txt file.

9

Is it okay if I create a new folder and transfer the file with WinSCP? because I can’t access var/lib/mysql with winscp

10

Copy the whole backup. A single .ibd file is only 1 table. I’d imagine that you have many more tables for your website.

11

okay, So if I do it the way I said, it’s okay, right?

12

No, because above you said “transfer the file” and that’s not correct. You need to transfer the entire datadir.

13

oh sorry, yes I’m talking about all backups not one file , I will just delete some idb tables that are of no use to me and a few tables will remain.

14

Do not delete any .ibd files! That will certainly fail your attempts at this whole process. You must restore the entire database, then you can DROP TABLE once mysql is running.

15

If I send you ssh information, can you handle it for me please? Because I don’t know, I make it so much more complicated

16

I got an error like this

gGOsKW6HsLVS
gGOsKW6HsLVS820×519 4.23 KB

17

Hi @neverm2,
Unfortunately I cannot do this for you without a support contract with Percona. Here’s a quick rundown:

systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/var/tmp/base --data-dir=/var/lib/mysql
cd /var/lib/mysql
xtrabackup --prepare --target-dir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql
18

xtrabackup --copy-back --target-dir=/var/tmp/base --data-dir=/var/lib/mysql

After this command I got this error again

root@ubt:/var/tmp/base# xtrabackup --copy-back --target-dir=/var/tmp/base/ --data-dir=/var/lib/mysql/
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments: --copy-back=1 --target-dir=/var/tmp/base/
xtrabackup version 2.4.29 based on MySQL server 5.7.44 Linux (x86_64) (revision id: 2e6c0951)
Error: datadir must be specified.

19

Sorry, the parameter is --datadir
Feel free to use --help to fix easy errors like that.

20

Thank you for your time, the code worked, I’m waiting for the copying to finish, I hope it works