If you have had an unfortunate incident and deleted all customers the recovery is not as simple as restoring from a backup, if you have had orders or customer creation since your last backup.
Before we start¶
Check if you have had any orders since your last backup.
If you haven't then a full import of the backup will fix any issues.
Starting from backup¶
First backup your current database, we will need the order data later.
Now get your backup of the database and import this into a fresh database, for now we will call this mage_restore.
Next import the dump you took from your current database into another new
database. We do this to ensure data integrity. Call this something like
Pulling customers from backup¶
From the database we created earlier, mage_restore, we need to extract the customer data.
This can be done by creating a new sql dump of only the relevant tables.
For our purposes we will use this query:
mysqldump -t --skip-extended-insert mage_restore customer_address_entity customer_address_entity_datetime customer_address_entity_decimal customer_address_entity_int customer_address_entity_text customer_address_entity_varchar customer_entity customer_entity_datetime customer_entity_decimal customer_entity_int customer_entity_text customer_entity_varchar wishlist wishlist_item > customers_restore.sql
--insert-ignoreif you had customer registration after deleting the customers
This will create a new sql file
customers_restore.sql which will hold all
Import customers into backup¶
Now we can import the customers back into our backup copy.
This can be done with:
mysql full_restore < customers_restore.sql
full_restore database will now hold all customer data and
However, if you will still have orphaned orders as a result of deleting the customer data, and we will fix that in the next step.
Matching Orders to Customers¶
Because of the customer deletion we will now have orphaned orders, we need to recreate the association between customers and their orders.
This query should correctly associate all orders back to customers:
UPDATE full_restore.sales_order AS orig INNER JOIN mage_restore.sales_order AS res ON orig.entity_id=res.entity_id SET orig.customer_id=res.customer_id;
ignoreto the query if you have some partial data left
Now run the next query in order to add the association back to the order grid:
UPDATE sales_order_grid INNER JOIN sales_order ON sales_order_grid.entity_id = sales_order.entity_id SET sales_order_grid.customer_id = sales_order.customer_id;
Partial data fix¶
If when running the previous query you had to include the
option in your first update query you will need to run another update
This will have been caused by a customer with an account recreating one because theirs was deleted.
We can fix these with the following query:
UPDATE full_restore.sales_order AS orig INNER JOIN mage_restore.sales_order AS res ON orig.entity_id=res.entity_id INNER JOIN mage_restore.customer_entity AS c ON res.customer_id=c.entity_id INNER JOIN magento2.customer_entity AS c2 ON c.email=c2.email SET orig.customer_id=c2.entity_id WHERE res.customer_id IS NOT NULL AND orig.customer_id IS_NULL;
We now have a complete copy of all data including orders and customers from before the deletion.
You can now either switch over your magento installation to use
dump it out and reimport it back into your main database.