mysqldump―output details

Question

I am about to write a little program to process database dump files created by mysqldump and I would like to make sure that my expectations about the »anatomy« of mysqldump's output are right. So please check the statements below and correct the if they are wrong.

1. When dumping text mysqldump uses a single quote (') to wrap the text to insert.*
2. Inside these quotes mysqldump escapes single quotes, double quotes and Backslashes (',",\) with a Backslash.*
3. »newline« characters are dumped as: \n, \r\n
4. the value of each INSERT of a String is only in one line of the dump-file.

*if not specified differently in command-line arguments

In General my problem is that I could not find any documentation or resource about the »anatomy« of the generated output, I know that these are SQL statements, but especially the way String are handled is the part I am interested in, so if there exists any good Stuff read out there dealing with that, please let me know.

Thanks in ahead!

Answer 1

mysqldump is capable of dumping in three ways

• SQL
• CSV
• XML

SQL

The standard format has directives in addition to regular table creation and INSERTs

The paradigm is always the same

• CREATE TABLE
• DISABLE KEYS
• LOCK TABLE
• INSERT INTO ...
• INSERT INTO ...
• ...
• INSERT INTO ...
• UNLOCK TABLES
• ENABLE KEYS

Even with the characters you are coding and delimiting around, please consider mysqldump also processes TEXT, BLOB, BINARY, and VARBINARY fields. Unless you remember to use the --hex-blob option, you could find your delimiter processing somewhat flimsy in the presence of those datatypes.

Another aspect to deal with is if you make each row a separate INSERT using --skip-extended-insert, thus increasing the amount of delimiters to parse and count.

CSV

You could export data in CSV format. You could even set the options for delimiting.

• lines-terminated-by
• fields-terminated-by
• fields-enclosed-by
• fields-optionally-enclosed-by
• fields-escaped-by

This would eliminate the need to guessing the delimiters by presetting them.

XML

If you export the data as XML (using --xml), the anatomy of the dump may still be subject to the issues around TEXT, BLOB, BINARY, and VARBINARY fields.

EPILOGUE

If you need to examine how data is batched by mysqldump, see my post How we take dump with batch of records option?

MySQL (eh, Oracle) has basically left it to the discretion of the Community Users to parse mysqldumps. Using --hex-blob has probably saved many developer headaches in order to simplify text processing of mysqldumps. This would relieve MySQL of having to document mysqldump output characteristics.

As for your utility you are coding, you are either going to see strings delimited or hex encoded. No need to sweat details past these two possibilities.

I just thought of something else: doing mysqldump from Linux vs. Windows. Keep in mind that whichever OS platform you use to run mysqldump. code for that platform only. Otherwise, you may need two little programs, one for Linux and one for Windows. (I won't mention MacOS at this point)