hgfischer / mysqlsuperdump Goto Github PK

Generate partial and filtered dumps of MySQL databases

License: Other

Makefile 10.98% Go 89.02%

mysql dumps production-database temporary-database go mysqldump

mysqlsuperdump's Introduction

MySQL Super Dump

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.

History

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.

Features

Filter dumped rows by a native WHERE clause ([where] config's section)
Replace dumped data with native SELECT functions ([select] config's section)
Disable data output of specific tables ([filter] config's section: nodata)
Ignore entire tables ([filter] config's section: ignore)

Usage

Install the latest Go compiler installed (check instructions at: http://golang.org)
Check you environment with go env:
The repository will be clones at $GOPATH/src/github.com/hgfischer/mysqlsuperdump
The binary will be installed in $GOBIN
Then run go get to download, build and install mysqlsuperdump: go get github.com/hgfischer/mysqlsuperdump
Create a config file based on example.cfg and place where you like it.
Run mysqlsuperdump -h to see command line options and voilá.

Configuration Example


[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

TO DO

Extend MySQL support, with other objects like views, triggers, etc
Refactor dumper interface to support another SQL databases
Add support for PostgreSQL

License

Please, check the LICENSE file.

mysqlsuperdump's People

Contributors

Stargazers

Watchers

mysqlsuperdump's Issues

Add support for PostgreSQL

After having the dumper refactored, we can start with a PostgreSQL implementation. It is most likely that the project will be renamed once this is possible, and the new name will be SQLSuperDumper

Replace dumped values with random values

Hey,
it would be a nice feature to replace the dumped values with a kind of random values.
For example if there is a username field in a user table, it should be possible
to fill these names with random names from a defined pool of names.

Consider exporting the mySQL type

Would you consider exporting the mySQL type? I understand the plans to refactor this into an interface to support multiple database engines, but for now I think it would be good to be able to see the documentation, since you already wrote it. It's a shame not being able to see it 😄

Paginate data during dump

Filter doesn't work

Filter options doesn't work at all. It does not matter whether I select "ignore" or "nodata", the dump still contains all the CREATE and data for the filtered tables.

Refactor dumper interface to support other SQL databases

Currently the dumper code only supports MySQL but it could be refactored to support other databases by using interfaces.

Filter entire tables from dump

Need to give the option to filter entire tables from dump

Error when DB has views

panic: sql: expected 4 destination arguments in Scan, not 2

goroutine 1 [running]:
runtime.panic(0x555840, 0xc210044c60)
    /usr/local/go/src/pkg/runtime/panic.c:266 +0xb6
main.checkError(0x7f6a8ad44e80, 0xc210044c60)
    /home/herbert/Workspace/Go/src/github.com/hgfischer/mysqlsuperdump/mysqlsuperdump.go:88 +0x4f
main.dumpCreateTable(0x7f6a8ad44fe8, 0xc210000008, 0xc210052080, 0xc210044650, 0xf)
    /home/herbert/Workspace/Go/src/github.com/hgfischer/mysqlsuperdump/mysqlsuperdump.go:168 +0x447
main.main()
    /home/herbert/Workspace/Go/src/github.com/hgfischer/mysqlsuperdump/mysqlsuperdump.go:78 +0x3db

Extend support for views, triggers, etc

Currently MySQLSuperDump only supports basic tables. Since I have no current use cases, I've created this issue to keep track of future enhancements.

Options need to support upper case

Currently the config parser produces all lowercase keys while the database direct select statements return upper and lower case mixed if the tables contain upper-case. We need to adjust the key lookups produced by the config parser to lowercase our table names to properly pull the options for the associated tables or the parameters will not be applied properly.

Example:
[filter]
sc_page_vars = ignore
cs_diffTable = ignore

When the config file contains these two lines, only the sc_page_vars table is filtered, the cs_diffTable still attempts to pass through because of the error. Pull request has been issued to correct the issue.

[filter]
cache = nodata
cache_admin_menu = nodata
cache_block = nodata
cache_bootstrap = nodata
#cache_field = nodata
cache_filter = nodata
cache_form = nodata
cache_pages = nodata
cache_update = nodata
cache_views = nodata
cache_views_data = nodata
sessions = nodata

Resulting output:

--
-- Structure for table `cache`
--

DROP TABLE IF EXISTS `cache`;

--
-- Structure for table `cache_admin_menu`
--

DROP TABLE IF EXISTS `cache_admin_menu`;

--
-- Structure for table `cache_block`
--

DROP TABLE IF EXISTS `cache_block`;

I can't make the filters work, it always dumps the whole database.

Hi,

I'm trying to filter my database by company id but i can't make it work :(

Here is my setup (don't mind the typo ;) ) :

[mysql]
# See https://github.com/Go-SQL-Driver/MySQL for details on this
dsn = user:pwd@tcp(work)/weasel2?charset=utf8
extended_insert_rows = 1000
#use_table_lock = true
max_open_conns = 50

# Use this to restrict exported data. These are optional
[where]
COMPAGNY	= ID = 3000 
# I would like to use something like : ID in (3000, 3001)

# Use this to override value returned from tables. These are optional
[select]

# Use this to filter entire table (ignore) or data only (nodata)
[filter]

It omits the filter and dumps the whole database.
I tried to use lowercase and it didn't do anything more.
The exact syntax I used in the example is : ```COMPAGNY[tab]=[space]ID[space]=[space]3000

Thanks for your time :)