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.
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.
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.
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.
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"