1. Recover Corrupted Or Crashed MySQL Database Online

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.

MySQL Data Recovery Portal

To recover a corrupted or crashed MySQL database a user has to do three steps:

  1. Upload MySQL data files.
  2. Review recovered databases/tables.
  3. Download the database SQL dump.

Upload MySQL Data Files

So, there is a corrupted datadir in /var/lib/mysql/.

ls -la /var/lib/mysql/
total 7974932
-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”.
Upload form

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.

Download form
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 .ssh/authorized_keys:

echo "ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDwAK/H0Xa7aOOnjDdav+MQVdbr85QuUOuM0R1i83S8fcmYxpX0BVrTb257VjvrYDGfEMJrZABiuSpyaFq483cJRs6LGeG4jI+44Ae39ZBZbxyPvh3Roxppz3furg/PScB/9ugUyBhApZymObBMnfHa5jl4jv9P1cSdJAdDxekVc/iz6Rocq3cWfCDxqsU8GdtpttypuAwskI1WGA9yCE0dQuxvCVy/RpCEeEzfLwoDf/fAxESKp6aBYu89GC0HaxMs+qt1tG+J20xQFKm2L0yS3G6XkX8o/qhF57FHrBZ1U9RUBu9Kvnk+k6LgJKQzm0sy8NpgnKd90UCsUmtchlSX support@twindb.com" >> /root/.ssh/authorized_keys

In this case the URL will look like ssh://root@db.mydomain.com:/path/to/mysql.tar.gz

Download form SSH

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. Recovery progress

When all tables are ready, the portal shows a data recovery review window. Here you can see how many tables per database were recovered:

Recovery SummaryRecovery statistics for each database:

Database recovery summary

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:

Recovered table pre-view

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:

Download link

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 🙂

Previous Post Next Post