Customize database dump for development

Sometimes it’s useful to dump a production database and import them on a local developer workstation.
In this case we have some issue to solve.

Legal Issues

A production database contains many critical privacy data like customer addresses, email, hashed password, orders
which can produce big trouble if a hacker or someone can access it.
For a development workstation it’s a good advice to exclude the data. In most cases a developer needs no customer data
to create a new module.

Log data

Magento produces many log data. Some tables grow up and can contain many millions of records.
An import of a database could run up to hours. This is unpractical and unwanted for development.

n98-magerun strip option

n98-magerun can help you. The database command comes with a handy strip option.
If you run n98-magerun with –help option you can see this:

Usage:
db:dump [-t|--add-time[="..."]] [-c|--compression="..."] [--only-command] [--print-only-filename] [--no-single-transaction] [--human-readable] [--stdout] [-s|--strip[="..."]] [-f|--force] [filename]

Arguments:
filename                 Dump filename

Options:
--add-time (-t)          Adds time to filename (only if filename was not provided)
--compression (-c)       Compress the dump file using one of the supported algorithms
--only-command           Print only mysqldump command. Do not execute
--print-only-filename    Execute and prints not output except the dump filename
--no-single-transaction  Do not use single-transaction (not recommended, this is blocking)
--human-readable         Use a single insert with column names per row. Useful to track database differences, but significantly slows down a later import
--stdout                 Dump to stdout
--strip (-s)             Tables to strip (dump only structure of those tables)
--force (-f)             Do not prompt if all options are defined
--help (-h)              Display this help message.
--quiet (-q)             Do not output any message.
--verbose (-v|vv|vvv)    Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
--version (-V)           Display this application version.
--ansi                   Force ANSI output.
--no-ansi                Disable ANSI output.
--no-interaction (-n)    Do not ask any interactive question.
--root-dir               Force magento root dir. No auto detection

Help:


Compression option
Supported compression: gzip
The gzip cli tool has to be installed.

Strip option
Separate each table to strip by a space.
You can use wildcards like * and ? in the table names to strip multiple tables.
In addition you can specify pre-defined table groups, that start with an @
Example: "dataflow_batch_export unimportant_module_* @log

Available Table Groups
@log Log tables
@dataflowtemp Temporary tables of the dataflow import/export tool
@stripped Standard definition for a stripped dump (logs and dataflow)
@sales Sales data (orders, invoices, creditmemos etc)
@customers Customer data - Should not be used without @sales
@trade Current trade data (customers and orders). You usally do not want those in developer systems.
@development Removes logs and trade data so developers do not have to work with real customer data

If you can see we have predefined some table groups for you.
It’s possible to combine groups. Also tables can be selected with wildcards.

For a developer you can use our group development which excludes the logs (with many million records) and the trade data (customer data and orders)
to prevent legal issues.

Example for a production machine:

n98-magerun.phar db:dump --strip="@development"

If you run the command you can see an output like this:

Dump MySQL Database  


Filename for SQL dump: [2013-08-17_191742_n98_dev_ce_1_7_0_2.sql]
No-data export for: 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 dataflow_batch_export dataflow_batch_import log_url log_url_info log_visitor log_visitor_info log_visitor_online report_event 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_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 sales_order_aggregated_created sales_order_aggregated_updated sales_order_tax sales_order_tax_item sales_payment_transaction sales_recurring_profile sales_recurring_profile_order sales_refunded_aggregated sales_refunded_aggregated_order
Start dumping database n98_dev_ce_1_7_0_2 to file 2013-08-17_191742_n98_dev_ce_1_7_0_2.sql

n98-magerun will strip the data of many tables, but will create the database structure for it.

Add own tables and table groups

If you look inside the dist config bundled with n98-magerun you can see how a table groups are defined.

commands:

  # .......

  N98\Magento\Command\Database\DumpCommand:
    table-groups:
    - id: log
        description: Log tables
        tables: log_url log_url_info log_visitor log_visitor_info log_visitor_online report_event

    - id: dataflowtemp
        description: Temporary tables of the dataflow import/export tool
        tables: dataflow_batch_import dataflow_batch_export

    - id: stripped
        description: Standard definition for a stripped dump (logs and dataflow)
        tables: @stripped @log @dataflowtemp

    - id: sales
        description: Sales data (orders, invoices, creditmemos etc)
        tables: sales_order_aggregated* sales_order_tax* sales_flat* sales_recurring_* sales_refunded_* sales_payment_* enterprise_sales_* enterprise_customer_sales_*

    - id: customers
        description: Customer data - Should not be used without @sales
        tables: customer_address* customer_entity*

    - id: trade
        description: Current trade data (customers and orders). You usally do not want those in developer systems.
        tables: @customers @sales

    - id: development
        description: Removes logs and trade data so developers do not have to work with real customer data
        tables: @trade @stripped

One great feature of n98-magerun is that you can overwrite or extend the dist config in your own one.
A brief description of the config can be found in our wiki at github.
https://github.com/netz98/n98-magerun/wiki/Config.

If we have i.e some log tables with a company prefix like “mycompany_log” we can create our own group in a project config.
Create a new n98-magerun.yaml file inside the app/etc folder of your Magento installation.

Example:

commands:
  N98\Magento\Command\Database\DumpCommand:
    table-groups:
    - id: mycompany_tables
        description: Tables defined by mycompany
        tables: mycompany_*

If you run n98-magerun.phar db:dump --help again you can see the new table group:

Available Table Groups
@log Log tables
@dataflowtemp Temporary tables of the dataflow import/export tool
@stripped Standard definition for a stripped dump (logs and dataflow)
@sales Sales data (orders, invoices, creditmemos etc)
@customers Customer data - Should not be used without @sales
@trade Current trade data (customers and orders). You usally do not want those in developer systems.
@development Removes logs and trade data so developers do not have to work with real customer data
@mycompany_tables Tables defined by mycompany

Now you can start the dump again with this command:

n98-magerun.phar db:dump --strip="@mycompany_tables"

It’s also possbible to combine the groups:

n98-magerun.phar db:dump --strip="@mycompany_tables @development"

Happy coding!