Create a Magento Database Dump Without Sensitive Data

18th January 2018

When developers set up test environments of production websites, often the database dump used for the development site contains real customer and order data. Usually this data should be removed from development environments for security reasons. You might also fail tests from compliance scanning software. To create a Magento database dump without sensitive data is as simple as ignoring a list of database tables when taking an export.

Fortunately, the people over at Sonassi have created a simple shell script that can take an export whilst stripping out the customer-related data. The file resides in your Magento root directory and then reads the database credentials from the app/etc/local.xml file in Magento 1 and the app/etc/env.php in Magento 2.

Within your Magento root, simply run the below command to fetch the .sh file which can be used to export Magento 1 databases.

$ wget sys.sonassi.com/mage-dbdump.sh

For Magento 2, you need to wget the sys.sonassi.com/mage2-dbdump.sh file.

$ wget sys.sonassi.com/mage2-dbdump.sh

Now you can use the below command to create a database dump, where passing in -d provides the export.

$ bash mage-dbdump.sh -d

Whether you’re dumping a database from Magento 1 or Magento 2, a db.sql file will be created within the var directory in the Magento project root.

For faster database exports, it is recommended that you also pass in -z when using the command. This will use gzip compression so that the database dump file size is reduced.

$ bash mage-dbdump.sh -dz

Should you need to add or remove any tables from the ignore list in the shell script, simply download a copy of the file and modify the contents to suit your needs. For example, you might wish to do this to add a database table from a third party extension into the ignore list if it contains customer information.

For both mage-dbdump.sh and mage2-dbdump.sh files, the ignored table list should exist in a pair of parentheses at the top.

IGNORE_TABLES=( dataflow_batch_export dataflow_batch_import log_customer log_quote log_summary log_summary_type log_url log_url_info log_visitor log_visitor_info log_visitor_online report_event index_event enterprise_logging_event_changes core_cache core_cache_tag core_session core_cache_tag )
IGNORE_TABLES_AGGRESSIVE=( report_compared_product_index report_viewed_product_index sales_flat_creditmemo sales_flat_creditmemo_comment sales_flat_creditmemo_grid sales_flat_creditmemo_item sales_flat_invoice sales_flat_invoice_comment sales_flat_invoice_grid sales_flat_invoice_item sales_flat_order sales_flat_order_address sales_flat_order_grid sales_flat_order_item sales_flat_order_payment sales_flat_order_status_history sales_flat_quote sales_flat_quote_address sales_flat_quote_address_item sales_flat_quote_copy sales_flat_quote_item sales_flat_quote_item_option sales_flat_quote_payment sales_flat_quote_shipping_rate sales_flat_shipment sales_flat_shipment_comment sales_flat_shipment_grid sales_flat_shipment_item sales_flat_shipment_track )
TRUNCATE_TABLES=( dataflow_batch_export dataflow_batch_import log_customer log_quote log_summary log_summary_type log_url log_url_info log_visitor log_visitor_info log_visitor_online report_viewed_product_index report_compared_product_index report_event index_event index_process_event )