Recover Corrupted Or Crashed MySQL Database Online
Earlier, we blogged about ways to recover corrupt MySQL database with the TwinDB Data Recovery Toolkit. However, the learning curve of the toolkit is rather steep. It takes time to get familiar with the InnoDB internals and the tool itself. And let’s be honest, nobody’s ever prepared for data loss – people who prepare for disaster recovery take and verify backups. So when an accident happens, we spend precious time on learning and scripting.
At TwinDB, we automate everything. With data recovery, automation is not straightforward, but we still succeeded. In this post, I will show how to recover a corrupted MySQL database in the fastest possible way.
MySQL Data Recovery Portal
MySQL Data Recovery Portal is a web interface of the TwinDB Data Recovery Toolkit. It implements data recovery algorithms from different failures.
To recover a corrupted or crashed MySQL database a user has to do three steps:
- Upload MySQL data files.
- Review recovered databases/tables.
- Download the database SQL dump.
Upload MySQL Data Files
So, there is a corrupted datadir in
ls -la /var/lib/mysql/
-rw-rw---- 1 mysql mysql 8155824128 Jan 6 05:59 ibdata1
-rw-rw---- 1 mysql mysql 5242880 Jan 6 05:59 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 Jan 6 05:59 ib_logfile1
drwx------ 2 mysql mysql 4096 Jan 5 20:38 mysql
srwxrwxrwx 1 mysql mysql 0 Jan 5 21:07 mysql.sock
drwx------ 2 mysql mysql 4096 Jan 5 20:38 performance_schema
drwx------ 2 mysql mysql 4096 Jan 5 20:38 test
drwx------ 2 mysql mysql 4096 Jan 5 21:08 tpcc1000
Now, we need to archive it and upload the file to the portal.
tar zcf mysql.tar.gz /var/lib/mysql/
There are two ways to deliver
mysql.tar.gz to the portal: upload it from a local computer, or give it a URL, so it downloads it directly to our servers.
Upload Files From Local Computer
Of course, if the database is small, it’s easier to upload it from your local computer.
Press “Browse…” to choose the archive and press “Upload”.
It’s a simple way, however, it works for small databases only. Usually, databases are large and run on remote servers. The portal can download the archive from a given URL, too.
Upload Files From Remote Source
If you put the archive on a web server, the portal will download it directly to our servers.
The URL scheme can be http or https, or whatever wget supports. If you put the archive on an HTTPS server, make sure the certificate is valid.
Another option is to fetch the archive from ssh server. It’s secure, but needs some configuration. You need to add our public SSH key to
echo "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDwAK/H0Xa7aOOnjDdav+MQVdbr85QuUOuM0R1i83S8fcmYxpX0BVrTb257VjvrYDGfEMJrZABiuSpyaFq483cJRs6LGeG4jI+44Ae39ZBZbxyPvh3Roxppz3furg/PScB/9ugUyBhApZymObBMnfHa5jl4jv9P1cSdJAdDxekVc/iz6Rocq3cWfCDxqsU8GdtpttypuAwskI1WGA9yCE0dQuxvCVy/RpCEeEzfLwoDf/fAxESKp6aBYu89GC0HaxMs+qt1tG+J20xQFKm2L0yS3G6XkX8o/qhF57FHrBZ1U9RUBu9Kvnk+k6LgJKQzm0sy8NpgnKd90UCsUmtchlSX firstname.lastname@example.org" >> /root/.ssh/authorized_keys
In this case the URL will look like
Press the “Download” button, and it’ll start downloading the file.
Review Recovered Databases/Tables
As soon as our servers receive the archive, the data recovery process starts. It’s not instant, it depends on the database size, but the portal’s progress reports will give you an idea about how long it will take.
When all tables are ready, the portal shows a data recovery review window. Here you can see how many tables per database were recovered:
I would like to comment on the meaning of some fields in the table:
Dictionary – a green check mark (good news) if a record for this table was recovered in the InnoDB dictionary.
Pages – green if
stream_parser found InnoDB pages that contain data of this table.
Tree – green if the B+ tree for this table is complete, i.e.
stream_parser found all pages with the data. This feature isn’t active yet, that’s why it shows “N/A”.
Schema – green if the table schema was recovered.
Expected – number of expected records in the table. Each InnoDB index page has a record number on this page. This value is a sum of expected records in all found pages. Sometimes, there are duplicate pages in the InnoDB tablespace, consequently the number can be higher than actual number of records in the table.
Dumped – number of records
c_parser dumped from all InnoDB pages. Ideally, it should match the number of Expected records.
Loaded – number of records successfully loaded into MySQL. It’s the number of records you will get in an SQL dump when you download this table.
Lost records – green “No” if no records are believed to be lost. The table is 100% recovered if Lost records is “No” and Tree is a green check mark.
Notes – if an error happened during recovery it may give a hint what went wrong.
For MyISAM some fields make no sense (Pages, Dictionary). For them it’ll show “N/A” values.
You can also see the actual recovered data:
Download the Database SQL Dump.
If the data in the recovered database looks good, the next step is to download the database in the SQL dump. After the payment goes through, you’ll see a download link for the database dump:
You can download the dump to the local computer or use the given
wget command to download the dump on a remote server.
Once the database is recovered, you can go ahead and configure backups so the database is never lost again 🙂