Recover Your Magento Catalog From a MySQL Dump

Posted by Ryan Street

May 2, 2013 | 1:00 PM

Share this blog on:     

Sometimes the unthinkable happens. 

Someone on the team, (or maybe even yourself) hit the wrong button. They've deleted a bunch of products. Or they've imported a bunch of bad data that has overwritten valuable product data.

So what do you do? You simply take your backup and restore it, right? There is only one problem. If you do a full restore of the database from the night before, you are losing everything up until that point. This includes new customers, orders, reviews, etc. You only really want the catalog restored. So we can open up our sql dump and grab what we want. If your export file isn't too big, that's not a problem. If it is Gigabytes large, (which in Magento world is all too common), opening it and dissecting it is going to be a near impossibility.

So what we need to do is take our export file, grab what we want out of it, and restore our Magento catalog.

The key tables we need to grab out of the database are the catalog_product_entity tables.

`catalog_product_entity` `catalog_product_entity_datetime` `catalog_product_entity_decimal` `catalog_product_entity_gallery` `catalog_product_entity_group_price` `catalog_product_entity_int` `catalog_product_entity_media_gallery` `catalog_product_entity_media_gallery_value` `catalog_product_entity_text` `catalog_product_entity_tier_price` `catalog_product_entity_varchar`

If your hosting only takes snapshots of your whole environment, load that into a VM and you can export exactly what you need right from the command line:

mysqldump -u username -p databaseName catalog_product_entity catalog_product_entity_datetime catalog_product_entity_decimal catalog_product_entity_gallery catalog_product_entity_group_price catalog_product_entity_int catalog_product_entity_media_gallery catalog_product_entity_media_gallery_value catalog_product_entity_text catalog_product_entity_tier_price catalog_product_entity_varchar > catalog_export.sql

If you only have a sql dump to work with, you have to grab the insert statements right from the file itself.

grep -i 'insert into `catalog_product_entity' catalog_export.sql > catalog_inserts.sql [/cc] Now that you have your inserts, lets empty the tables. Enter MySQL: [cc lang="mysql"] SET foreign_key_checks = 0; /* Delete all values */ TRUNCATE `catalog_product_entity`; TRUNCATE `catalog_product_entity_datetime`; TRUNCATE `catalog_product_entity_decimal`; TRUNCATE `catalog_product_entity_gallery`; TRUNCATE `catalog_product_entity_group_price`; TRUNCATE `catalog_product_entity_int`; TRUNCATE `catalog_product_entity_media_gallery`; TRUNCATE `catalog_product_entity_media_gallery_value`; TRUNCATE `catalog_product_entity_text`; TRUNCATE `catalog_product_entity_tier_price`; TRUNCATE `catalog_product_entity_varchar`; /* Reset Auto Increment */ ALTER TABLE `catalog_product_entity` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_datetime` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_decimal` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_gallery` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_group_price` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_int` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_media_gallery` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_media_gallery_value` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_text` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_tier_price` AUTO_INCREMENT=1; ALTER TABLE `catalog_product_entity_varchar` AUTO_INCREMENT=1; SET foreign_key_checks = 1;

Now let's restore our database from the command line:

mysql -u username -p databaseName < catalog_inserts.sql [/cc] Now the tables have been restored. Let's reindex, but before we do that, we need to empty the tables. If not, we will end up with key errors. Back to MySQL: [cc lang="mysql"] TRUNCATE TABLE catalog_product_flat_1; /* ...and 2 and 3 and so on.*/ [/cc] Now perform your reindex as you see fit. [cc lang="bash"] php indexer.php --reindex catalog_product_flat 

magento-platform-checklist

Topics: Magento, Magento Technical

About Ryan Street

Search

Subscribe to Email Updates

magento-platform-checklist
New Call-to-Action

Contact Us

B2B-2.0

Recent Posts