MySQL partial recover / xt:commerce Atom Feed 0

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

# 's
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.

# '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.

# 's
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.

# 's
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.

# 's
1SELECT products_id, reviews_id FROM reviews WHERE products_id IN (1,2,3,4,5)
# 's
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.

# 's
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.

# 's
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.

# 's
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.