Description:
mysqldump allows dumping (i.e. exporting) data from MySQL to stdout in "sql" format or in XML format.
It also accepts loading (i.e. importing, or restoring) data from stdin in "sql" format, but it does *not* appear to accept this with XML formatted data, even if the data were dumped using mysqldump.
This asymmetry is inconvenient, badly documented (at least within MySQL's manual, and within the mysqldump man page, and should be remedied.
How to repeat:
mysql> CREATE DATABASE my_db CHARACTER SET utf8 COLLATE utf8_unicode_ci;
mysql> USE my_db;
mysql> CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT, bar VARCHAR(100), PRIMARY KEY (id));
mysql> INSERT INTO foo (bar) VALUES ("baz");
mysql> SELECT * FROM foo;
+----+------+
| id | bar |
+----+------+
| 1 | baz |
+----+------+
1 row in set (0.00 sec)
mysql> ^DBye
$ mysqldump my_db --xml -umy_db -pmy_db > my_db.xml
Warning: Using a password on the command line interface can be insecure.
$ cat my_db.xml
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="my_db">
<table_structure name="foo">
<field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" />
<field Field="bar" Type="varchar(100)" Null="YES" Key="" Extra="" Comment="" />
<key Table="foo" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="1" Null="" Index_type="BTREE" Comment="" Index_comment="" />
<options Name="foo" Engine="InnoDB" Version="10" Row_format="Compact" Rows="1" Avg_row_length="16384" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="0" Auto_increment="2" Create_time="2018-05-30 22:20:56" Collation="utf8_unicode_ci" Create_options="" Comment="" />
</table_structure>
<table_data name="foo">
<row>
<field name="id">1</field>
<field name="bar">baz</field>
</row>
</table_data>
</database>
</mysqldump>
$ mysql
mysql> DROP TABLE foo;
Query OK, 0 rows affected (0.02 sec)
mysql> ^DBye
$ mysqldump my_db --xml -umy_db -pmy_db < my_db.xml
Warning: Using a password on the command line interface can be insecure.
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="my_db">
</database>
</mysqldump>
$ mysql
mysql> use my_db;
Database changed
mysql> SELECT * FROM foo;
ERROR 1146 (42S02): Table 'my_db.foo' doesn't exist
Suggested fix:
Implement code to allow mysqldump to ingest XML-formatted data, via stdin, that it had previously dumped to stdout, just as already exists for SQL-formatted data.
Description: mysqldump allows dumping (i.e. exporting) data from MySQL to stdout in "sql" format or in XML format. It also accepts loading (i.e. importing, or restoring) data from stdin in "sql" format, but it does *not* appear to accept this with XML formatted data, even if the data were dumped using mysqldump. This asymmetry is inconvenient, badly documented (at least within MySQL's manual, and within the mysqldump man page, and should be remedied. How to repeat: mysql> CREATE DATABASE my_db CHARACTER SET utf8 COLLATE utf8_unicode_ci; mysql> USE my_db; mysql> CREATE TABLE foo ( id INT NOT NULL AUTO_INCREMENT, bar VARCHAR(100), PRIMARY KEY (id)); mysql> INSERT INTO foo (bar) VALUES ("baz"); mysql> SELECT * FROM foo; +----+------+ | id | bar | +----+------+ | 1 | baz | +----+------+ 1 row in set (0.00 sec) mysql> ^DBye $ mysqldump my_db --xml -umy_db -pmy_db > my_db.xml Warning: Using a password on the command line interface can be insecure. $ cat my_db.xml <?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="my_db"> <table_structure name="foo"> <field Field="id" Type="int(11)" Null="NO" Key="PRI" Extra="auto_increment" Comment="" /> <field Field="bar" Type="varchar(100)" Null="YES" Key="" Extra="" Comment="" /> <key Table="foo" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="id" Collation="A" Cardinality="1" Null="" Index_type="BTREE" Comment="" Index_comment="" /> <options Name="foo" Engine="InnoDB" Version="10" Row_format="Compact" Rows="1" Avg_row_length="16384" Data_length="16384" Max_data_length="0" Index_length="0" Data_free="0" Auto_increment="2" Create_time="2018-05-30 22:20:56" Collation="utf8_unicode_ci" Create_options="" Comment="" /> </table_structure> <table_data name="foo"> <row> <field name="id">1</field> <field name="bar">baz</field> </row> </table_data> </database> </mysqldump> $ mysql mysql> DROP TABLE foo; Query OK, 0 rows affected (0.02 sec) mysql> ^DBye $ mysqldump my_db --xml -umy_db -pmy_db < my_db.xml Warning: Using a password on the command line interface can be insecure. <?xml version="1.0"?> <mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <database name="my_db"> </database> </mysqldump> $ mysql mysql> use my_db; Database changed mysql> SELECT * FROM foo; ERROR 1146 (42S02): Table 'my_db.foo' doesn't exist Suggested fix: Implement code to allow mysqldump to ingest XML-formatted data, via stdin, that it had previously dumped to stdout, just as already exists for SQL-formatted data.