DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

MySQL Partial Recover / Xt:commerce

08.05.2009
| 2889 views |
  • submit to reddit
        Situation: A amount of products got accidentally removed from the database via a the Admin Web-Interface. A backup exists as full MySQL dump. 
Task: Restore all removed products

Step 1: Find the missing products

Import the MySQL Backup to a test MySQL DB
mysql -u root shop < backup.sql

Create a list of product id's from the live and the test system. Compare the values and extract the missing id's.

test-system $ mysql -u root shop -B -e 'select products_id from products order by products_id' > backup_ids
live-system $ mysql -u root shop -B -e 'select products_id from products order by products_id' > live_ids
diff --suppress-common-lines -y backup_ids live_ids

Format the output to a SQL IN () statement. (vim :%s/\n/,/g). Verify the product id's do not longer exist on the live system.

SELECT products_id FROM products WHERE products_id IN (1,2,3,4,5)

Step 2: Create a dump of the missing products.

xt:commerce is using the "function remove_product($product_id)" in "admin/includes/classes/categories.php" to remove the products. That function makes a couple of "DELETE FROM" SQL Statement on a hand-full of tables.

We dump the tables and tell mysqldump with the --where option, the list of product id's we need.

mysqldump -t -u root shop products_content specials products products_images products_to_categories products_description products_attributes customers_basket customers_basket_attributes personal_offers_by_customers_status_0 personal_offers_by_customers_status_1 personal_offers_by_customers_status_2 personal_offers_by_customers_status_3 personal_offers_by_customers_status_4 personal_offers_by_customers_status_5 reviews --where 'products_id IN (1,2,3,4,5)' > restore.sql

Note: There also is a table called "reviews_description" where entry's get removed by the "remove_product" function. 

SELECT products_id, reviews_id FROM reviews WHERE products_id IN (1,2,3,4,5)

mysqldump -t -u root shop reviews_description --where 'reviews_id IN (6,9)' >> restore.sql

Test the dump with a copy of the current live System. Expect duplicates.

mysql -f -v -u root shop < restore.sql

xt:commerce is also removing the images, so you better have a current backup of the files.

tar -zxvf backup.tar.gz -C / -P -k --wildcards '*shop/htdocs/images/product_images/*/1_*.jpg' '*shop/htdocs/images/product_images/*/2_*.jpg'

use this for testing.

tar -ztvf [...]

Better method and more secure might be some DB compare / db diff application that can go over all the data from both databases.