The mysqldump program can be used to make logical database backups. Although the vast majority of people use it to create SQL dumps, it is possible to dump both schema structure and data in XML format. There are a few bugs (#52792, #52793) in this feature, but these are not the topic of this post.
XML output from mysqldumpDumping in XML format is done with the --xml or -X option. In addition, you should use the --hex-blob option otherwise the BLOB data will be dumped as raw binary data, which usually results in characters that are not valid, either according to the XML spec or according to the UTF-8 encoding. (Arguably, this is also a bug. I haven't filed it though.)
For example, a line like:
dumps the sakila database to the following XML format:
mysqldump -uroot -pmysql -X --hex-blob --databases sakila
I don't want to spend too much time discussing why it would be useful to make backups in this way. There are definitely a few drawbacks - for example, for sakila, the plain SQL dump, even with
<field Field="actor_id" Type="smallint(5) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
<field Field="first_name" Type="varchar(45)" Null="NO" Key="" Extra="" />
<field Field="last_name" Type="varchar(45)" Null="NO" Key="MUL" Extra="" />
<field Field="last_update" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" />
<key Table="actor" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="actor_id" Collation="A" Cardinality="200" Null="" Index_type="BTREE" Comment="" />
<key Table="actor" Non_unique="1" Key_name="idx_actor_last_name" Seq_in_index="1" Column_name="last_name" Collation="A" Cardinality="200" Null="" Index_type="BTREE" Comment="" />
<options Name="actor" Engine="InnoDB" Version="10" Row_format="Compact" Rows="200" Avg_row_length="81" Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="233832448" Auto_increment="201" Create_time="2009-10-10 10:04:56" Collation="utf8_general_ci" Create_options="" Comment="" />
<field name="last_update">2006-02-15 03:34:33</field>
...many more rows and table structures...
--hex-blobis 3.26 MB (3.429.358 bytes), whereas the XML output is 13.7 MB (14,415,665 bytes). Even after zip compression, the XML formatted dump is still one third larger than the plain SQL dump: 936 kB versus 695 kB.
Restoring XML output from mysqldumpA more serious problem is that MySQL doesn't seem to offer any tool to restore XML formatted dumps. The
LOAD XMLfeature, kindly contributed by Erik Wetterberg could be used to some extent for this purpose. However, this feature is not yet available (it will be available in the upcoming version MySQL 5.5), and from what I can tell, it can only load data - not restore tables or databases. I also believe that this feature does not (yet) provide any way to properly restore hex-dumped BLOB data, but I really should test it to know for sure.
In between sessions of the past MySQL users conference I cobbled up an XSLT stylesheet that can convert
mysqldump's XML output back to SQL script output. It is available under the LGPL license, and it is hosted on google code as the mysqldump-x-restore project. To get started, you need to download the mysqldump-xml-to-sql.xslt XSLT stylesheet. You also need a command line XSLT processor, like xsltproc. This utility is part of the Gnome libxslt project, and is included in packages for most linux distributions. There is a windows port available for which you can download the binaries.
xsltprocis in your path, and the XML dump and the
mysqldump-xml-to-sql.xsltare in the current working directory, you can use this command to convert the XML dump to SQL:
On Unix-based systems you should be able to directly pipline the SQL into
xsltproc mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql
The stylesheet comes with a number of options, which can be set through
mysql -uroot -pmysql < `xsltproc mysqldump-xml-to-sql.xslt sakila.xml`
--stringparamoption. For example, setting the
Nwill result in an SQL script that only contains DML statements:
xsltproc --stringparam schema N mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql
Nwill result in an SQL script that only contains DDL statements:
. There are additional options to control how often a
xsltproc --stringparam data N mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql
COMMITshould be issued, whether to add
DROPstatements, whether to generate single row
INSERTstatements, and to set the
What's next?Nothing much really. I don't really recommend people to use
mysqldump's XML output. I wrote mysqldump-x-restore for those people that inherited a bunch of XML formatted dumps, and don't know what to do with them. I haven't thouroughly tested it - please file a bug if you find one. If you actually think it's useful and you want more features, please let me know, and I'll look into it. I don't have much use for this myself, so if you have great ideas to move this forward, I'll let you have commit access.
That is all.