How can I generate the code-containing comments like mysqldump?

Question

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!

Answer 1

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';

Answer 2

First, I assume you understand the basic functionality of "code-containing comments."

/*!40101 some_sql_statement */

This means "only execute some_sql_statement if you are a MySQL Server, version 4.01.01 or higher." So, there's no real need to wrap the statements in the comment syntax unless the dump files you are creating are going to handle backwards-compatibility with older versions of MySQL (or you want your backups to look exactly like mysqldump files, which isn't a bad idea). This is why the numbers vary -- the version indicates, for each statement, the version of MySQL server that would understand the statement. This is an attempt to make the files generated by mysqldump to remain as backwards-compatible as possible.

But, onto the substance of your question.

Consider the statement you used as an example:

/*!40101 SET @saved_cs_client = @@character_set_client */;

This is a bad example, because it actually is static statement you can and should hard-code. From the source code to mysqldump, it's pretty low tech:

fprintf(sql_file,
      "/*!50003 SET @saved_cs_client      = @@character_set_client */ %s\n"
      "/*!50003 SET @saved_cs_results     = @@character_set_results */ %s\n"
      "/*!50003 SET @saved_col_connection = @@collation_connection */ %s\n"
...

The %s above is just the statement delimiter (";").

This is all in switch_cs_variables() inside client/mysqldump.c in the source files.

The next three lines from the same code use information that mysqldump learned from queries it sent to the server:

      "/*!50003 SET character_set_client  = %s */ %s\n"
      "/*!50003 SET character_set_results = %s */ %s\n"
      "/*!50003 SET collation_connection  = %s */ %s\n",

Looking at the source, the values used here are from character_set_client, character_set_client (again, it's used twice), and collation_connection from the results of of SHOW CREATE [ trigger | function | procedure |event ].

Indeed, in thinking about this question, I remembered that I once wrote a similar script that generates a partial dump file, using perl. After collecting the data, I write them like this:

print "SET character_set_client  = $def->{character_set_client};\n";
print "SET character_set_results = $def->{character_set_client};\n";
print "SET collation_connection  = $def->{collation_connection};\n";

Note that I didn't bother with the version-specific comment wrapper since I don't have any need to replay these files on any system that is old enough not to recognize these statements.

Note, also, that the static statements -- that is, the SET @saved... statements ... as well as (later in the dump, not shown above) SET @@... = @saved... statements, are simply there to set the session back to its prior state after each object is created.

Don't forget that when you're selecting data from the server, MySQL has its own built-in QUOTE() function which will do more correct quoting of string literals (the data you're selecting, for insert) than anything you roll yourself.