We have been told many times that modifying live database should be done with extreme care, we should always make a backup before doing something big to the database. However, there are countless stories on the Internet about losing data due to various reason, one of them is forgetting to create a backup (Gitlab is an example: https://about.gitlab.com/2017/02/01/gitlab-dot-com-database-incident/). I was facing the same issue when upgrading MySQL server to a new version. Luckily I was able to restore most of the data but it was still a very good lesson for me. One of lesson I learned is how we could restore the data from the
The database I worked with had many tables. There were about 5 of them using MyISAM engine while others were using InnoDB engine. I was asked to upgrade the entire the server from MySQL 5.7 to 8.0. Well, I was careless when following the
In-place upgrade recommended here without creating a backup first: https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html. What I did was that I started a new instance of MySQL 8.0 (running in parallel with MySQL 5.7) while pointing its
datadir option to the same
datadir being used by MySQL 5.7, without shutting down 5.7 first. When MySQL 8.0 started, it modified the
ibdata1 file somehow and caused this file to be no longer compatible with MySQL 5.7. At the same time, the 8.0 server could not update this
ibdata1 file correctly and it failed to load the database into the server. As the result, I could no longer use or see this database in both 5.7 and 8.0.
After many failed tries of recovering this
ibdata1 file, I had to import the SQL dump file given by the client into the MySQL 8.0. This dump file was actually a backup done by him (thanks god) but it was 1 month older than the newest data we had. That was not cool and I had to find a way to restore the data somehow.
Luckily most of the important & big tables are using MyISAM engine so I can simply copy the
*.MYI files into the datadir of the new MySQL and immediately I had restored them successfully. But it was tricky with other tables. What I had were two
frm files for each table using InnoDB engine. We cannot simply copying these two files into the data directory because the MySQL would complain about incorrect tablespace or something. Trust me, I tried many methods which recommends using
DISCARD TABLESPACE and
IMPORT TABLESPACE but none of them worked with me for some reason (Ex: https://medium.com/magebit/recover-innodb-database-from-frm-and-ibd-files-99fdb0deccad, you should try it first before reading further)
Finally I found an article which gave me some leads: https://mattstauffer.com/blog/restoring-lost-innodb-mysql-databases-after-all-data-and-hope-was-lost-with-drop-tables/. In that article, the author mentions the use of Twindb toolkit for restoring the data. I went to Twindb website, tried their tool: https://recovery.twindb.com/. Amazingly after uploading my ibd and frm files, I could see all the data. However, I could not actually get the data because they asked me to pay first ($99 USD, a bit too much for me). But at least I knew that my data was recoverable and Twindb tool could do it. Reading the previous article further, I found that they used TwinDB data recovery toolkit (https://twindb.com/undrop-tool-for-innodb/, https://github.com/twindb/undrop-for-innodb). The issue was that there were no explanation on how I could 'actually' retrieve the data. The instruction at https://twindb.com/undrop-tool-for-innodb/ only shows me how to extract the table structure and run some commands for fetching the records, but I wanted to export it into something which I can easily import back to my MySQL server. How could I do that? (I still don't know why they did not say anything about that)
Anyway, I managed to run the tool and get the actual data which I could import into the database using
mysql command. Here is how I did it
1. Install the Twindb data recovery tool
It means cloning the git repos (https://github.com/twindb/undrop-for-innodb), run
make to build the binary file. After this step, you will get the binary files called
c_parser in the folder which you cloned
2. Create a
.sql file containing the SQL for creating your table.
How to get the table structure? You can either use the info in your current database or you can extract the CREATE statement from the
.frm file using
mysqlfrm command (https://dev.mysql.com/doc/mysql-utilities/1.5/en/mysqlfrm.html)
3. Parse the innodb file.
Here is the command I used (assuming I have a file called
people.ibd containing the data of
people table in my database.
./stream_parser -f people.ibd
After parsing, you will get a folder called
pages-people.idb. Inside this folder you will see another folder called
FIL_PAGE_INDEX, and again inside this subfolder is a file with a long serial as its name and the extension is
.page. We are ready to move to the next step
4. Extract the database into
Here is the command I used
./c_parser -6f pages-people.ibd/FIL_PAGE_INDEX/0000000000002305.page -t people-create.sql > dumps/default/people 2>dumps/default/people.sql
6mean the ibd file was generated by a version of MySQL 5.6+ (in my case it was 5.7),
ffor specifying the
.pagefile we are going to parse
-t people-create.sql: the file contains the CREATE TABLE statement as I said in step 2
> dumps/default/people: the dump data will be in this file. This is actually a text file which is compatible with the command
LOAD LOCAL DATA FILE.
dumps/defaultis simply the folder I used for storing the exported data.
2>dumps/default/people.sql: this is the
.sqlfile which will contain the
LOAD LOCAL DATA FILEstatement. So in the end we can simply run this file to import the data.
But the tool is not perfect, and it cannot magically restore 100% the data you have lost (in my case it was 98% though). There might be some additional things you need to do (which happened to me):
- Some value was exported incorrectly so I had to modified it manually in the output text file (I also adjusted many after importing)
- Your MySQL server might not allow running
LOAD LOCAL DATA FILEso you need to check and enable the option (in the mysql config file)
Anyway, I am glad to see most of my data return and it was worth doing all those complex steps. Actually I hope you will not need this blog because what you should do is to make sure creating a backup first. Hope this helps some of you who are frustring.