Over a million developers have joined DZone.

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.
Topics:

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}