Generate partial and filtered dumps of MySQL databases
MySQL Super Dump is a tool to efficiently create filtered and manipulated database dumps. It relies in the power of the SQL native language to do this, using WHERE clauses and complete SELECT statements with aliases to do this.
Currently it does not support every kind of MySQL structure (views, triggers, etc), but it supports the most basic stuff: schemas, tables and rows.
Once uppon a time there was a development team that liked to use dumps from the production database in their development environments to have the same content and behavior of the production system in their machines.
To avoid security problems, the system administrator created a script to dump the production database, import in a temporary database, then replace all sensitive data, like salts, passwords, customer names, emails, etc, for fake data, then export a dump of this temporary database to a file that is the dump developers would use.
However this script was taking more time to run, day by day, and each day it was using more resources from the server to run, until it exploded.
[where]
config's section)[select]
config's section)[filter]
config's section: nodata
)[filter]
config's section: ignore
)go env
:$GOPATH/src/github.com/hgfischer/mysqlsuperdump
$GOBIN
go get
to download, build and install mysqlsuperdump
: go get github.com/hgfischer/mysqlsuperdump
example.cfg
and place where you like it.
[mysql]
# See https://github.com/Go-SQL-Driver/MySQL for details on this
dsn = username:password@protocol(address)/dbname?charset=utf8
extended_insert_rows = 1000
#use_table_lock = true
max_open_conns = 50
# Use this to restrict exported data. These are optional
[where]
sales_order = created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
customer_upload = created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
newsletter_subscriber = created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
# Use this to override value returned from tables. These are optional
[select]
system_user.salt = 'reset salt of all system users'
system_user.password = 'reset password of all system users'
customer.first_name = CONCAT('Charlie ', id)
customer.last_name = 'Last'
customer.salt = 'reset salt of all customers'
customer.password = 'reset password of all customers'
customer.username = CONCAT(id, '@fiction.tld')
customer.username_canonical = CONCAT(id, '@fiction.tld')
customer.email = CONCAT(id, '@fiction.tld')
customer.email_canonical = CONCAT(id, '@fiction.tld')
newsletter_subscriber.email = CONCAT(id, '@fiction.tld')
customer_address.recipient_name = CONCAT('Recipient Name ', id)
customer_address.company = CONCAT('Company Name ', id)
customer_address.phone = CONCAT('(', id, ') 1234-1234')
sales_order_address.recipient_name = CONCAT('Recipient Name ', id)
sales_order_address.company = CONCAT('Company Name ', id)
sales_order_address.phone = CONCAT('(', id, ') 1234-1234')
system_dump_version.created_at = NOW()
# Use this to filter entire table (ignore) or data only (nodata)
[filter]
customer_stats = nodata
customer_private = ignore
Please, check the LICENSE file.