Which format to Export in phpMyAdmin

SQL or XML

[twist]

I have always used the SQL format to back up my data and have had no problems. I noticed that the XML export option has no options. I never understood all the choices in the SQL options anyway. Is exporting to XML going to be hassle free when importing the data back. Besides the larger file size is there any other disadvantage to Exporting the data into a XML file?

What does everybody here use for a export format in phpmyadmin?

[ergophobe]

I do zipped SQL format. I have no idea whether or not it's better. If it's a big file, I use the mysql command line client and do a dump and then zip it via the shell and get it with FTP.

I guess the main reason I do it is because it's easier for me to read the SQL than some other format. What options don't you understand, by the way?

Tom

[twist]

These are the list of options without the checkboxes for a sql dump

Add custom comment into header (\n splits lines):
Enclose export in a transaction
Disable foreign key checks
Structure:
Add DROP TABLE
Add IF NOT EXISTS
Add AUTO_INCREMENT value
Enclose table and field names with backquotes
Add into comments
Creation/Update/Check dates
Data:
Complete inserts
Extended inserts
Use delayed inserts
Use ignore inserts
Use hexadecimal for binary fields

There are no options for the xml file. The xml file reads like a xml file and is pretty straightfoward, it just is much larger in size to the same sql dump. I was curious if the xml file would be univerisal and could be transferred to anything, unlike the sql? Any input on the whole subject would be welcome.

[ergophobe]

Hmm... sorry. I almost never use PhpMyAdmin and I've never used the XML feature. I would guess that there are not options for the XML for the same reasons that you probably don't have options for CSV (just wildly guessing here, but I believe PhpMyAdmin will do a csv dump too or do I have that totally wrong?).

In other words, I would assume that the XML is just holding your data, but like a CSV dump it's not telling the mysql server how it should deal with that data.

As for the options you list, which ones are tripping you up? Or is it just the basic idea of what they do in the first place?

[twist]

Mostly these checkboxes are my worry,

Complete inserts
Extended inserts
Use delayed inserts
Use ignore inserts
Use hexadecimal for binary fields

For instance, don't I need "Complete inserts" and "Extended inserts"? What happens if I don't include them? There not checked by default, so are they unecessary?

[Storyman]

Twist,

I've been backing up MySQL dbs by exporting using this configuration. It has been used successfully to import tables to a new db.

Export: SQL
Structure: checked
Add Auto Increment_Value: checked
Enclose table and field names with backquotes: checked
Export type: Insert
Data: checked
(the others in Data are unchecked)
Save as file: checked
Remember Template: checked
File_name_template: _DB_
Compression: none

[ergophobe]

Twist,

You may or may not need complete inserts. Basically, it's the difference between an insert that assumes that all values are in the same order as the columns in your DB, and an insert that specifies exactly which column to put which values in.

Here's some stuff from the PphMyAdmin docs

[6.12] I want to understand some Export options.

"Complete inserts" adds the column names on every INSERT command, for better documentation (but resulting file is bigger).

"Extended inserts" provides a shorter dump file by using only once the INSERT verb and the table name.

"Enclose table and field names with backquotes" ensures that field and table names formed with special characters are protected.

"Add AUTO_INCREMENT value" ensures, that AUTO_INCREMENT value (if any) will be included in backup.

"Add into comments" includes column comments, relations and MIME types set in the PMA-DB in the dump as SQL comments (/* #*$! */).

Those and the ones not covered there are defined on the mysql manual page

[dev.mysql.com...]

under the "options" section as the flags

--complete-insert
--extended insert
--delayed
--hex-blob

You'll note that I skipped the "ignore" option. That just adds an IGNORE to each of your inserts so that if mysql encounters an error when reading your sql dump, it will treat it as a warning and keep going.

You can also get information on some of these options by reading the mysql manual on INSERT as most of these options are merely flags in an insert statement.

[dev.mysql.com...]

I hope that's enough to get you oriented.

Tom