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

By: ( +David Herron; 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 - ( has over 9 years of discussion from a community of electric vehicle owners, representing an immensely valuable resource - has a huge amount of useful information and news postings regarding electric vehicles and clean energy technologies - ( 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 -- 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 -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 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.


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.




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: (

« 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 Amazon Amazon Kindle Amiga Android Anti-Fascism AntiVirus Software Apple Apple Hardware History Apple iPhone Apple iPhone Hardware April 1st Arduino ARM Compilation Astronomy Asynchronous Programming Authoritarianism Automated Social Posting Ayo.JS Bells Law Big Brother Big Finish Bitcoin Mining Black Holes Blade Runner Blogger Blogging Books Botnet Botnets Cassette Tapes Cellphones Christopher Eccleston Chrome Chrome Apps Chromebook Chromebooks Chromebox ChromeOS CIA CitiCards Citizen Journalism Civil Liberties Clinton Cluster Computing Command Line Tools Computer Hardware Computer Repair Computers Cross Compilation Crouton Cryptocurrency Curiosity Rover Cyber Security Cybermen Daleks Darth Vader Data backup Data Storage Database Database Backup Databases David Tenant DDoS Botnet Detect Adblocker Developers Editors Digital Photography Diskless Booting DIY DIY Repair DNP3 Do it yourself Docker Docker Swarm Doctor Who Doctor Who Paradox Drobo Drupal Drupal Themes DVD E-Books E-Readers Early Computers Election Hacks Electric Bicycles Electric Vehicles Electron Emdebian Encabulators Energy Efficiency Enterprise Node EPUB ESP8266 Ethical Curation Eurovision Event Driven Asynchronous Express Facebook Fake News Fedora VirtualBox File transfer without iTunes FireFly Fraud Freedom of Speech Gallifrey git Gitlab GMAIL Google Google Chrome Google Gnome Google+ Government Spying Great Britain Heat Loss Hibernate Hoax Science Home Automation HTTPS I2C Protocol Image Analysis Image Conversion Image Processing ImageMagick InfluxDB Infrared Thermometers Insulation 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 Journalism Joyent Kaspersky Labs Kindle Marketplace Lets Encrypt LibreOffice Linux Linux Hints Linux Single Board Computers Logging Mac OS Mac OS X MacOS X setup Make Money Online MariaDB Mars Matt Lucas MEADS Anti-Missile Mercurial Michele Gomez Micro Apartments Military Hardware Minification Minimized CSS Minimized HTML Minimized JavaScript Missy Mobile Applications MODBUS Mondas MongoDB Mongoose Monty Python MQTT Music Player Music Streaming MySQL NanoPi Nardole NASA 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 Online Journalism Online Video Open Media Vault Open Source Governance Open Source Licenses Open Source Software OpenAPI OpenVPN Personal Flight Peter Capaldi Photography PHP Plex Plex Media Server Political Protest Postal Service Power Control Privacy Production use Public Violence Raspberry Pi Raspberry Pi 3 Raspberry Pi Zero Recycling Remote Desktop Republicans Retro-Technology Reviews Right to Repair River Song Robotics Rocket Ships RSS News Readers rsync Russia Russia Troll Factory Russian Hacking SCADA Scheme Science Fiction Search Engine Ranking Season 1 Season 10 Season 11 Security Security Cameras Server-side JavaScript Shell Scripts Silence Simsimi Skype Social Media Social Media Warfare Social Networks Software Development Space Flight Space Ship Reuse Space Ships SpaceX Spear Phishing Spring Spring Boot 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 Trump Campaign Ubuntu UDOO Virtual Private Networks VirtualBox VLC VNC VOIP Web Applications Web Developer Resources Web Development Web Development Tools Web Marketing Website Advertising Weeping Angels WhatsApp Window Insulation Wordpress YouTube YouTube Monetization