When you are working on Magento 1, sometimes you need to work off your local machine but with your current Product data.
You can copy your database to your local machine, but you will be stuck with all the customer data that slows down your machine when you work on your code.
Here is a quick rundown on how to remove your customer’s data from your local database.
WARNING: DO NOT DO THIS ON YOUR PRODUCTION SERVER! THIS WILL KILL YOUR CUSTOMER DATA! YOU HAVE BEEN WARNED!
1) Customer Data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | SET FOREIGN_KEY_CHECKS=0; -- reset customers TRUNCATE customer_address_entity; TRUNCATE customer_address_entity_datetime; TRUNCATE customer_address_entity_decimal; TRUNCATE customer_address_entity_int; TRUNCATE customer_address_entity_text; TRUNCATE customer_address_entity_varchar; TRUNCATE customer_entity; TRUNCATE customer_entity_datetime; TRUNCATE customer_entity_decimal; TRUNCATE customer_entity_int; TRUNCATE customer_entity_text; TRUNCATE customer_entity_varchar; TRUNCATE log_customer; TRUNCATE log_visitor; TRUNCATE log_visitor_info; ALTER TABLE customer_address_entity AUTO_INCREMENT=1; ALTER TABLE customer_address_entity_datetime AUTO_INCREMENT=1; ALTER TABLE customer_address_entity_decimal AUTO_INCREMENT=1; ALTER TABLE customer_address_entity_int AUTO_INCREMENT=1; ALTER TABLE customer_address_entity_text AUTO_INCREMENT=1; ALTER TABLE customer_address_entity_varchar AUTO_INCREMENT=1; ALTER TABLE customer_entity AUTO_INCREMENT=1; ALTER TABLE customer_entity_datetime AUTO_INCREMENT=1; ALTER TABLE customer_entity_decimal AUTO_INCREMENT=1; ALTER TABLE customer_entity_int AUTO_INCREMENT=1; ALTER TABLE customer_entity_text AUTO_INCREMENT=1; ALTER TABLE customer_entity_varchar AUTO_INCREMENT=1; ALTER TABLE log_customer AUTO_INCREMENT=1; ALTER TABLE log_visitor AUTO_INCREMENT=1; ALTER TABLE log_visitor_info AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1; |
2) Order Data
Now you will need to delete order data:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | SET FOREIGN_KEY_CHECKS=0; TRUNCATE `sales_flat_creditmemo`; TRUNCATE `sales_flat_creditmemo_comment`; TRUNCATE `sales_flat_creditmemo_grid`; TRUNCATE `sales_flat_creditmemo_item`; TRUNCATE `sales_flat_invoice`; TRUNCATE `sales_flat_invoice_comment`; TRUNCATE `sales_flat_invoice_grid`; TRUNCATE `sales_flat_invoice_item`; TRUNCATE `sales_flat_order`; TRUNCATE `sales_flat_order_address`; TRUNCATE `sales_flat_order_grid`; TRUNCATE `sales_flat_order_item`; TRUNCATE `sales_flat_order_payment`; TRUNCATE `sales_flat_order_status_history`; TRUNCATE `sales_flat_quote`; TRUNCATE `sales_flat_quote_address`; TRUNCATE `sales_flat_quote_address_item`; TRUNCATE `sales_flat_quote_item`; TRUNCATE `sales_flat_quote_item_option`; TRUNCATE `sales_flat_quote_payment`; TRUNCATE `sales_flat_quote_shipping_rate`; TRUNCATE `sales_flat_shipment`; TRUNCATE `sales_flat_shipment_comment`; TRUNCATE `sales_flat_shipment_grid`; TRUNCATE `sales_flat_shipment_item`; TRUNCATE `sales_flat_shipment_track`; TRUNCATE `sales_invoiced_aggregated`; TRUNCATE `sales_invoiced_aggregated_order`; TRUNCATE `sales_payment_transaction`; TRUNCATE `sales_order_aggregated_created`; TRUNCATE `sales_order_tax`; TRUNCATE `sales_order_tax_item`; TRUNCATE `sendfriend_log`; TRUNCATE `tag`; TRUNCATE `tag_relation`; TRUNCATE `tag_summary`; TRUNCATE `wishlist`; TRUNCATE `log_quote`; TRUNCATE `report_event`; ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1; ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1; ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1; ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1; ALTER TABLE `sales_payment_transaction` AUTO_INCREMENT=1; ALTER TABLE `sales_order_aggregated_created` AUTO_INCREMENT=1; ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1; ALTER TABLE `sales_order_tax_item` AUTO_INCREMENT=1; ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1; ALTER TABLE `tag` AUTO_INCREMENT=1; ALTER TABLE `tag_relation` AUTO_INCREMENT=1; ALTER TABLE `tag_summary` AUTO_INCREMENT=1; ALTER TABLE `wishlist` AUTO_INCREMENT=1; ALTER TABLE `log_quote` AUTO_INCREMENT=1; ALTER TABLE `report_event` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1; |
3) Mirasvit Help Desk Message Data
BONUS: You might have a Mirasvit Help Desk plugin. Here are queries to remove data from your help desk! This will delete contents of tickets, but will not delete your Gateway setups or any workflows you have.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | TRUNCATE `m_helpdesk_attachment`; TRUNCATE `m_helpdesk_customer`; TRUNCATE `m_helpdesk_draft`; TRUNCATE `m_helpdesk_email`; TRUNCATE `m_helpdesk_history`; TRUNCATE `m_helpdesk_message`; TRUNCATE `m_helpdesk_ticket`; ALTER TABLE `m_helpdesk_attachment` AUTO_INCREMENT=1; ALTER TABLE `m_helpdesk_customer` AUTO_INCREMENT=1; ALTER TABLE `m_helpdesk_draft` AUTO_INCREMENT=1; ALTER TABLE `m_helpdesk_email` AUTO_INCREMENT=1; ALTER TABLE `m_helpdesk_history` AUTO_INCREMENT=1; ALTER TABLE `m_helpdesk_message` AUTO_INCREMENT=1; ALTER TABLE `m_helpdesk_ticket` AUTO_INCREMENT=1; |
4) Webforms Pro 2 Message Data
BONUS 2: You might also have Webforms Pro 2 plugin. Here is how to remove Webforms results data:
1 2 3 4 5 6 7 | TRUNCATE `webforms_results`; TRUNCATE `webforms_results_values`; TRUNCATE `webforms_message`; ALTER TABLE `webforms_results` AUTO_INCREMENT=1; ALTER TABLE `webforms_results_values` AUTO_INCREMENT=1; ALTER TABLE `webforms_message` AUTO_INCREMENT=1; |