Step 1: Find the missing products
Import the MySQL Backup to a test MySQL DB
1mysql -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.
1test-system $ mysql -u root shop -B -e 'select products_id from products order by products_id' > backup_ids
2live-system $ mysql -u root shop -B -e 'select products_id from products order by products_id' > live_ids
3diff --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.
1SELECT 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.
1mysqldump -t -u root shop products_content specials products products_images products_to_categories products_description products_attributes \
2 customers_basket customers_basket_attributes \
3 personal_offers_by_customers_status_0 personal_offers_by_customers_status_1 personal_offers_by_customers_status_2 \
4 personal_offers_by_customers_status_3 personal_offers_by_customers_status_4 personal_offers_by_customers_status_5 reviews \
5 --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.
1SELECT products_id, reviews_id FROM reviews WHERE products_id IN (1,2,3,4,5)
1mysqldump -t -u root shop reviews_description --where 'reviews_id IN (6,9)' >> restore.sql
Step 3: Restore
Test the dump with a copy of the current live System. Expect duplicates.
1mysql -f -v -u root shop < restore.sql
xt:commerce is also removing the images, so you better have a current backup of the files.
1tar -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.
1tar -ztvf [...]
Better method and more secure might be some DB compare / db diff application that can go over all the data from both databases.