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
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.
quora.com Jordan-Ryan Web-Dev How-to-Recover-innoDB-MySQL-files-using-MAMP-on-a-Mac
dba.stackexchange.com questions 16875
stackoverflow.com questions 879176
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:
dev.mysql.com refman 5.1 innodb-backup.html
