Mangeto Database is huge and complicated, everyone knows about this truth. It’s a big obstacle for many developers who start working on a new project or grab latest changes on production server. The following procedure will help you to reduce size of production database a lot before downloading to you local environment.

Like usual, you will need to make a database dump first with this command :

mysqldump -u[user_name] -p[password] -h[host_name] [database_name] > [path_to_dump_file].sql


Then you will need to create a new temporary database and import the database dump above. try this command in your Mysql console

CREATE DATABASE [temporary_database_name] DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


Exit mysql console then you can import database with this command in terminal :

mysql -u[user_name] -p[password] -h[host_name] [temporary_database_name] < [path_to_dump_file].sql


after having database imported, log in your Mysql console again and execute this SQL script:

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;
TRUNCATE log_url;
truncate log_url_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;

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_order_aggregated_created;
TRUNCATE sendfriend_log;
TRUNCATE tag;
TRUNCATE tag_relation;
TRUNCATE tag_summary;
TRUNCATE wishlist;
TRUNCATE log_quote;
TRUNCATE report_event;
TRUNCATE report_viewed_product_index;
TRUNCATE report_compared_product_index;
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_order_aggregated_created 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;
ALTER TABLE report_viewed_product_index AUTO_INCREMENT=1;
ALTER TABLE report_compared_product_index AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;