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
MySQL Partial Recover / Xt:commerce
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.