How to restore a MySQL database and tables from .frm .ibd or .myd raw database files

By: ; Date: 2016-03-16 22:05

Tags: MySQL » Database Backup » Drupal » Wordpress

Consider a situation - you've got a well tuned MySQL database server running some popular websites. The sites are implemented with Drupal and Wordpress, but using MySQL to store the content and settings. You think everything is fine, until one day you look at Google Analytics and are aghast to see zero traffic for the previous two days. You go to the websites and are greeted by a 404 error, with the server saying there's nothing there. You try logging into the server, but cannot, your login attempt is refused. You contact the hosting provider for help, and they tell you the directory containing all your websites and other files is completely empty. Oh.. and there's a note left behind from someone giving an http-something-or-other URL to click on, demanding payment in order for the server to be restored.

I don't know how often hacks like this happen - but recovering from that situation has been my nightmare this week. To make matters worse, I had zero backups of the database, and was extremely worried the contents of three popular websites would be lost. Each site had many years of content, which might vaporize - http://visforvoltage.org has over 9 years of discussion from a community of electric vehicle owners, representing an immensely valuable resource - http://longtailpipe.com has a huge amount of useful information and news postings regarding electric vehicles and clean energy technologies - http://davidherron.com is this website, while it isn't as popular, does have some good resources.

As it turned out, the web hosting provider had a server backup that was a couple days old, and for the database there was no backup (no SQL dump of any kind) but instead the /var/lib/mysql directory. This directory keeps the raw datafiles MySQL uses to store database tables. That's what I had - no SQL files but instead raw database files from which the sites had to be reinstated.

The file names - .frm files appear to hold schema description - .ibd files contains a innoDB table - .myi and .myd are for MyISAM tables - etc.

Until now I'd thought these files were horribly dependent on the machine where they'd been generated, and that there was no way to move a MySQL database from one server to another other than to use mysqldump to generate an SQL file, and then run the mysql command to re-import the database. I've done that sequence many times to move databases from one place to another.

Database Recovery

But here I was - completely unable to generate an SQL dump of the databases, and absolutely dependent on reinstating these databases to reinstate the websites. What I did was both amazingly simple, and demonstrates what I'd thought about the raw database files was incorrect.

That is -

  • I installed MAMP on my Mac OS X laptop - to get a MySQL server installed. This laptop has a MySQL server from MacPORTS, but that one has some databases and I wanted to use a separate database server - MAMP is a packaged distribution of PHP, MySQL, Apache and more - highly recommended, see https://www.mamp.info/en/ -- UPDATE -- Today I'd recommend installing MySQL using Docker
    • Basically you need a MySQL server where you have write access to the raw data table directory. A MariaDB instance would work as well.
  • MAMP's raw database directory is `/Applications/MAMP/db/mysql/` -- I made a tarball of that directory so that I could revert if something went wrong
  • I copied the raw database directory from my old server into that directory -- making sure to not overwrite `/Applications/MAMP/db/mysql/mysql/`, `/Applications/MAMP/db/mysql/mysql_upgrade_info`, and `/Applications/MAMP/db/mysql/performance_schema`
  • Then I launched MAMP, started up its servers, and then started its copy of `phpMyAdmin`, and was elated to see the databases there.
  • Then I used mysqldump to make an SQL dump of every database: `/Applications/MAMP/library/bin -u root -p database_name >$HOME/sql/database_name.sql`
  • Then I uploaded the SQL files to my server
  • Then I imported them to the new database server: `mysql -u myusername -h database-host-name.com -p database_name <$HOME/sql/database_name.sql`

Website recovery

Another important step was to not directly reuse the website content from the old server. It had many backdoor .PHP files scattered around, and there was strong risk of other backdoors. Instead, I rebuilt the software for each site from source downloaded from trusted locations.

For the Drupal sites, I have a shell script that runs Drush Make to set up a Drupal instance, and then apply patches and copy in sites/default/files and other important customizations.

For Wordpress I had to spend time on wordpress.org manually navigating to every plugin to download its .zip file and unpack it into wp-content/plugins.

Once all that was accomplished, I re-enabled the sites in the Dreamhost control panel, and voila the sites came back to life.

Resources

I did not invent the procedure above - though maybe I would have tried that on a lark. Instead I searched for help, and found various posts giving advice.

http://www.quora.com/Jordan-Ryan/Web-Dev/How-to-Recover-innoDB-MySQL-files-using-MAMP-on-a-Mac

http://dba.stackexchange.com/questions/16875/mysql-how-to-restore-table-stored-in-a-frm-and-a-ibd-file

http://stackoverflow.com/questions/879176/how-to-recover-mysql-db-from-myd-myi-frm-files

These are filled with caveats and advice and links to further blog posts and other information.

The MySQL documentation includes advice on using this technique for database backups: http://dev.mysql.com/doc/refman/5.1/en/innodb-backup.html

« The simple cure if a Wordpress custom content type doesn't display, but gives a 404 page not found Big Brother touched Juniper Networks - backdoor allowed anyone to eavesdrop on communications »
2016 Election Acer C720 Ad block AkashaCMS Android Apple Apple Hardware History Apple iPhone Hardware April 1st Arduino ARM Compilation Asynchronous Programming Authoritarianism Automated Social Posting Bells Law Big Brother Blade Runner Blogger Blogging Books Botnet Botnets Cassette Tapes Cellphones Christopher Eccleston Chrome Chrome Apps Chromebook Chromebooks Chromebox ChromeOS CIA CitiCards Civil Liberties Clinton Cluster Computing Command Line Tools Computer Hardware Computer Repair Computers Cross Compilation Crouton Cyber Security Cybermen Daleks Darth Vader Data backup Data Storage Database Database Backup Databases David Tenant DDoS Botnet Detect Adblocker Digital Photography DIY DIY Repair Docker Doctor Who Doctor Who Paradox Drobo Drupal Drupal Themes DVD Election Hacks Emdebian Enterprise Node ESP8266 Ethical Curation Eurovision Event Driven Asynchronous Express Facebook Fake News File transfer without iTunes FireFly Fraud Freedom of Speech Gallifrey git Gitlab GMAIL Google Google Chrome Google Gnome Google+ Government Spying Great Britain Home Automation HTTPS I2C Protocol Image Conversion Image Processing ImageMagick InfluxDB Internet Internet Advertising Internet Law Internet of Things Internet Policy Internet Privacy iOS Devices iPad iPhone iPhone hacking Iron Man Iternet of Things iTunes Java JavaScript JavaScript Injection JDBC John Simms Joyent Lets Encrypt LibreOffice Linux Linux Hints Linux Single Board Computers Logging Mac OS Mac OS X Matt Lucas MEADS Anti-Missile Mercurial Michele Gomez Military Hardware Missy Mobile Applications MODBUS Mondas Monty Python MQTT Music Player Music Streaming MySQL NanoPi Nardole Net Neutrality Node Web Development Node.js Node.js Database Node.js Testing Node.JS Web Development Node.x North Korea Online advertising Online Fraud Open Media Vault Open Source Software OpenAPI OpenVPN Personal Flight Peter Capaldi Photography Plex Media Server Political Protest Power Control Privacy Production use Public Violence Raspberry Pi Raspberry Pi 3 Raspberry Pi Zero Recycling Republicans Retro-Technology Reviews Right to Repair River Song Rocket Ships RSS News Readers rsync Russia Russia Troll Factory Scheme Science Fiction Season 1 Season 10 Season 11 Security Security Cameras Server-side JavaScript Shell Scripts Silence Simsimi Skype Social Media Warfare Social Networks Software Development Space Flight Space Ship Reuse Space Ships SpaceX Spring SQLite3 SSD Drives SSD upgrade SSH SSH Key SSL Swagger Synchronizing Files Telescopes Terrorism The Cybermen The Daleks The Master Time-Series Database Torchwood Total Information Awareness Trump Trump Administration Ubuntu Virtual Private Networks VirtualBox VLC VOIP Web Applications Web Developer Resources Web Development Web Development Tools Weeping Angels WhatsApp Wordpress