Mysql – How to generate the code-containing comments like thesqldump

MySQLmysqldump

I'm needing to duplicate the mysqldump command in Python, and I am trying to figure out how automatically generate the code comments like mysqldump generates.

If you're wondering why I am doing such a stupid thing, it's because I am creating a utility that will make daily dumps of the production server for use in dev environments, but I must anonymize all of the fields that contain private data during the dump. It will also change API keys to test accounts, change passwords for important user accounts, etc. (Obviously this is not our main backup system, but something tailored for use by the devs, so that they can always use a safe snapshot of the production database to work with.)

I'm able to do a lot of this already, but I can't figure out how to automatically generate the correct "code comments" (e.g., "/*!40101 SET @saved_cs_client = @@character_set_client */;") that mysqldump includes before/after the CREATE TABLE statement and the INSERT statements. Obviously I could hard code them, but that just feels wrong, and is potentially short-sighted in terms of portability of code and longevity of use.

Thanks for any ideas you can share!

Best Answer

Well you can review what mysqldump does on git and try working on it!! Though I'd not go this way...

Few things to suggest:

  • You want to hide column names

--> use --no-create-info to dump only data. (onlydata.sql)

--> Have standard table definitions ready and intact for dev server. (definition.sql)

--> Refreshing dev => load definition.sql and then onlydata.sql

  • Just to note that you can also restrict datadump with --where clauses and even limit that to dump only a few rows.

mysqldump --no-create-info --where " true limit 10000" | gzip > dump.sql.gz

.

  • You want to remove few values from the dump? Well writing tool for searching strings will again cause long time to parse!

--> You might consider setting up a special slave on which you make your changes and then do a full datadump.

--> You can prepare SQL commands to run after dump load finishes as follows: update secrettable set secretcolumn='xxxxxx' where secretcolumn='reallysecretthing';

Related Question