Mfrhtyj

You can just empty the whole database and Magento will run all its install/upgrade/data scripts again on the next pageload. This will "factory reset" Magento and will destroy all your changes.

A good habit is to create a module that contains all your basic store settings inside install/upgrade/data scripts. This has the benefit that these settings are stored in version control and all the different websites you have for the store (production, staging, user acceptance testing, etc.) will be kept up to date.

If there are multiple persons working on a site, we even create a new module for every setting group so the versions don't conflict when we merge code.

In your case, I would create a module that setups all the settings you want to keep. If you wipe the database, Magento will be completely clean AND including the settings you want!

You can have a look at the install/upgrade scripts of core Magento to see how Magento installs it's initial data/settings.

If you don't need anything at all - just empty the whole DB and do a clean re-install.

Running mass-truncates always worry me with any potential lasting effects of broken key relationships.

Fast DB purging

Using this script,

cd /path/to/my/magento/store

wget -O mage-dbpurge.sh sys.sonassi.com/mage-dbpurge.sh

chmod +x mage-dbpurge.sh

./mage-dbpurge.sh



Are you 100% sure you want to purge $DBNAME? [y/N]: y

Are you 110% sure you want to purge $DBNAME? [y/N]: y



MYSQL DB PURGE COMPLETE

That will completely empty and remove every single table in the database. Do not use it if you need to retain anything.

If you're working with EE you should also truncate the enterprise url rewrite tables, as they don't have foreign key constraints and on importing data (but not explicitly setting urls) the urls of the old products will be connected to your new imported products.

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE `catalog_product_entity_url_key`;

TRUNCATE TABLE `catalog_category_entity_url_key`;

TRUNCATE TABLE `enterprise_url_rewrite`;

TRUNCATE TABLE `enterprise_url_rewrite_product_cl`;

TRUNCATE TABLE `enterprise_url_rewrite_category_cl`;

TRUNCATE TABLE `enterprise_catalog_category_rewrite`;

SET FOREIGN_KEY_CHECKS = 1;

Prior to launching a new Magento eCommerce website, at development phase, you’ll always have to carry out testing and this will require creating test orders and customers to ensure that all processes function correctly and as you would expect.

This can often end up leaving a load of test data in the system which you won’t be wanting once the site is launched. Magento is notorious for inserting data in multiple database tables so we’ve provided the SQL script below that will cleanly delete all this data from your Magento database (and reset the increment counters for sales, invoices, customers and shipping).
This has been tested and confirmed working on Magento versions from 1.4.0.1 to 1.9.0.1.

#Tested on Magento CE 1.4.1.1 - 1.9.0.1



SET FOREIGN_KEY_CHECKS=0;



##############################

# SALES RELATED TABLES

##############################

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 `log_quote`;



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 `log_quote` AUTO_INCREMENT=1;



#########################################

# DOWNLOADABLE PURCHASED

#########################################

TRUNCATE `downloadable_link_purchased`;

TRUNCATE `downloadable_link_purchased_item`;



ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;

ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;



#########################################

# RESET ID COUNTERS

#########################################

TRUNCATE `eav_entity_store`;

ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;





##############################

# CUSTOMER RELATED TABLES

##############################

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 `tag`;

TRUNCATE `tag_relation`;

TRUNCATE `tag_summary`;

TRUNCATE `tag_properties`;            ## CHECK ME

TRUNCATE `wishlist`;

TRUNCATE `log_customer`;



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 `tag` AUTO_INCREMENT=1;

ALTER TABLE `tag_relation` AUTO_INCREMENT=1;

ALTER TABLE `tag_summary` AUTO_INCREMENT=1;

ALTER TABLE `tag_properties` AUTO_INCREMENT=1;

ALTER TABLE `wishlist` AUTO_INCREMENT=1;

ALTER TABLE `log_customer` AUTO_INCREMENT=1;





##############################

# ADDITIONAL LOGS

##############################

TRUNCATE `log_url`;

TRUNCATE `log_url_info`;

TRUNCATE `log_visitor`;

TRUNCATE `log_visitor_info`;

TRUNCATE `report_event`;

TRUNCATE `report_viewed_product_index`;

TRUNCATE `sendfriend_log`;

### ??? TRUNCATE `log_summary`



ALTER TABLE `log_url` AUTO_INCREMENT=1;

ALTER TABLE `log_url_info` AUTO_INCREMENT=1;

ALTER TABLE `log_visitor` AUTO_INCREMENT=1;

ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;

ALTER TABLE `report_event` AUTO_INCREMENT=1;

ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;

ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;

### ??? ALTER TABLE `log_summary` AUTO_INCREMENT=1;



SET FOREIGN_KEY_CHECKS=1;

One thing no one has mentioned is clearing tax tables. If you don't, you'll get repeat order Id entries in the tax table (when you place orders again at the starting Order Id, you'll have the same Id showing up multiple times). The multiple items will show up on invoices/memos as multiple line items, on both the admin and client side. After testing / clearing using the methods in the above posts (and not clearing the tax tables), after a while I had 10 different tax line items showing up for a single order.

Note - tax was/is still charged correctly, but on memos and invoices it was showing all of the items. Very confusing if a client was to see that.

So to add to the above:

TRUNCATE `sales_order_tax`;

TRUNCATE `sales_order_tax_item`;

ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_tax_item` AUTO_INCREMENT=1; 

And for good measure:

TRUNCATE `sales_payment_transaction`;

ALTER TABLE `sales_payment_transaction` AUTO_INCREMENT=1; 

You should have separate db for tests. See magento test framework. All ideas to clear db after tests are wrong.

I setup a repository for maintaining simple SQL scripts to run before launching a new Magento site. So far they can help:

1. empty test data from Mage; orders, customers, reports, logs etc.


2. empty test data from various extensions


3. set desired IDs for next order, invoice, shipment and creditmemos

https://github.com/ccondrup/mage-reset
Help improve them!

Just my 2 cents...

Rather than figuring out what to delete, and only realising what I have missed later, I run a few VMs, and I use snapshots at various stages, plus I have two separate sites - one live and one test.

It's easy then to have a script to "restore" test from live when I need it. Also, if the customer has access to the test system, they can play with it too before doing anything with live - no need to restore from a backup and losing orders and other changes in the process (though it does test your backup strategy...)

I think this a much better and "safer" process than running a custom (outdated?) script to delete (too much?) stuff...

(Also, feel free to downvote this as I can't comment yet!)

MYSQL Drop and create the DB then add the user privileges and magento admin user.

DROP DATABASE <dbname>;

CREATE DATABASE <dbname>;

GRANT ALL PRIVILEGES ON <dbname>.* TO <dbusr>@localhost IDENTIFIED BY '<dbusr_pass>';

FLUSH PRIVILEGES;

Add Magento admin user:

USE DB_NAME;

LOCK TABLES `admin_role` WRITE , `admin_user` WRITE;

SET @SALT = "rp";

SET @PASS = CONCAT(MD5(CONCAT( @SALT , "ADMIN_PASSWORD") ), CONCAT(":", @SALT ));

SELECT @EXTRA := MAX(extra) FROM admin_user WHERE extra IS NOT NULL;

INSERT INTO `admin_user` (firstname,lastname,email,username,password,created,lognum,reload_acl_flag,is_active,extra,rp_token_created_at) VALUES ('Firstname','Lastname','email@example.com','USER_NAME',@PASS,NOW(),0,0,1,@EXTRA,NOW());

INSERT INTO `admin_role` (parent_id,tree_level,sort_order,role_type,user_id,role_name) VALUES (1,2,0,'U',(SELECT user_id FROM admin_user WHERE username = 'USER_NAME'),'Firstname');

UNLOCK TABLES;

Based on an answer from Mufaddal I prepared query to clear order and related data in Magento 2.2

SET FOREIGN_KEY_CHECKS=0; 

TRUNCATE `sales_creditmemo`; 

TRUNCATE `sales_creditmemo_comment`; 

TRUNCATE `sales_creditmemo_grid`; 

TRUNCATE `sales_creditmemo_item`; 

TRUNCATE `sales_invoice`; 

TRUNCATE `sales_invoice_comment`; 

TRUNCATE `sales_invoice_grid`; 

TRUNCATE `sales_invoice_item`; 

TRUNCATE `sales_order`; 

TRUNCATE `sales_order_address`; 

TRUNCATE `sales_order_grid`; 

TRUNCATE `sales_order_item`; 

TRUNCATE `sales_order_payment`; 

TRUNCATE `sales_order_status_history`; 

TRUNCATE `quote`; 

TRUNCATE `quote_address`; 

TRUNCATE `quote_address_item`; 

TRUNCATE `quote_item`; 

TRUNCATE `quote_item_option`; 

TRUNCATE `quote_payment`; 

TRUNCATE `quote_shipping_rate`; 

TRUNCATE `sales_shipment`; 

TRUNCATE `sales_shipment_comment`; 

TRUNCATE `sales_shipment_grid`; 

TRUNCATE `sales_shipment_item`; 

TRUNCATE `sales_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 `cache_tag`;

-- TRUNCATE `tag`;

-- TRUNCATE `tag_relation`;

-- TRUNCATE `tag_summary`;

TRUNCATE `wishlist`; 

-- TRUNCATE `log_quote`;

-- TRUNCATE `report_event`;

ALTER TABLE `sales_creditmemo` AUTO_INCREMENT=1; 

ALTER TABLE `sales_creditmemo_comment` AUTO_INCREMENT=1; 

ALTER TABLE `sales_creditmemo_grid` AUTO_INCREMENT=1; 

ALTER TABLE `sales_creditmemo_item` AUTO_INCREMENT=1; 

ALTER TABLE `sales_invoice` AUTO_INCREMENT=1; 

ALTER TABLE `sales_invoice_comment` AUTO_INCREMENT=1; 

ALTER TABLE `sales_invoice_grid` AUTO_INCREMENT=1; 

ALTER TABLE `sales_invoice_item` AUTO_INCREMENT=1; 

ALTER TABLE `sales_order` AUTO_INCREMENT=1; 

ALTER TABLE `sales_order_address` AUTO_INCREMENT=1; 

ALTER TABLE `sales_order_grid` AUTO_INCREMENT=1; 

ALTER TABLE `sales_order_item` AUTO_INCREMENT=1; 

ALTER TABLE `sales_order_payment` AUTO_INCREMENT=1; 

ALTER TABLE `sales_order_status_history` AUTO_INCREMENT=1; 

ALTER TABLE `quote` AUTO_INCREMENT=1; 

ALTER TABLE `quote_address` AUTO_INCREMENT=1; 

ALTER TABLE `quote_address_item` AUTO_INCREMENT=1; 

ALTER TABLE `quote_item` AUTO_INCREMENT=1; 

ALTER TABLE `quote_item_option` AUTO_INCREMENT=1; 

ALTER TABLE `quote_payment` AUTO_INCREMENT=1; 

ALTER TABLE `quote_shipping_rate` AUTO_INCREMENT=1; 

ALTER TABLE `sales_shipment` AUTO_INCREMENT=1; 

ALTER TABLE `sales_shipment_comment` AUTO_INCREMENT=1; 

ALTER TABLE `sales_shipment_grid` AUTO_INCREMENT=1; 

ALTER TABLE `sales_shipment_item` AUTO_INCREMENT=1; 

ALTER TABLE `sales_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 `cache_tag` 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;

Simply got your database phpMyadmin and then open the table sales_flat_order and then simply remove tha data from this tabel and your test orders will be cleared. No need of any code ,commands or extensions . cool.

This is a late answer, but it could be helpful.

I'm developing an e-commerce website based on Magento 1.7 and I tend to TDD.

I think we should not bother by cleaning up database manually while we can use Magento beginTransaction and rollback methods.

Here is an example

public function setUp()

{

    parent::setUp();

    /** @var Mage_Core_Model_Resource $core */

    $core = Mage::getSingleton('core/resource');

    $core->getConnection('default_write')->beginTransaction();

}



public function tearDown()

{

    parent::tearDown();

    /** @var Mage_Core_Model_Resource $core */

    $core = Mage::getSingleton('core/resource');

    $core->getConnection('default_write')->rollBack();

    Mage::reset();

}

This answer is inspired by this Magento answer, visit it for further reading.