Earlier before we blogged about ways to recover corrupt MySQL database with TwinDB Data Recovery Toolkit. However learning curve of the toolkit is rather steep. It takes time to get familiar with InnoDB internals and the tool. And let’s be honest nobody gets prepared for data loss – people who prepare for disaster recovery take and verify backups. So when an accident happens precious time has to be spent on learning and scripting.

At TwinDB we automate everything. With data recovery automation is not straightforward, but we did it. In the post I will show how to recover corrupted MySQL database in the fastest possible way.

MySQL Data Recovery Portal

MySQL Data Recovery Portal is a web interface to 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:

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

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 and give it a URL so it’ll download it directly to our servers

Upload file from local computer

If the database is small it’s easier to upload it from your local computer.
Press “Browse…” to choose the archive and press “Upload”

Simple, but works fine for small database only. Usually databases are large and they 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 .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 that case the URL will look like ssh://root@db.mydomain.com:/path/to/mysql.tar.gz

Press a “Download” button and it’ll start downloading the file.

Review recovered databases/tables

As soon as the archive is transferred to our servers the data recovery process starts. It’s not instant it depends on the database size, but the portal reports the progress to give an idea how long it’s going to 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:

Recovery statistics for each database:

I would like to comment meaning of some fields in the table:

Dictionary – it’s a green check mark (good news) if a record for this table was recovered in the InnoDB dictionary.

Pages – it’s green if stream_parser found InnoDB pages that contain data of this table.

Tree – it’s green if B+ tree for this table is complete – i.e. stream_parser found all pages with the data. Currently it’s not implemented yet, that’s why it shows “N/A”.

Schema – it’s green if the table schema was recovered.

Expected – number of expected records in the table. Each InnoDB index page has a records number in a this page. This value is a sum of expected records in all found pages. Sometimes, there are duplicate pages in InnoDB tablespace, so 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 number of Expected records.

Loaded – number of records successfully loaded into MySQL. It’s a number of records you will get in an SQL dump when you download this table.

Lost records – it will be a 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 an SQL dump. After the payment is done it will show a link to download 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

The post Recover corrupted or crashed MySQL database online appeared first on Backup and Data Recovery for MySQL.