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
No Comments Yet
Let us know what you think