Skip to main content

You are here


Exporting data in MySQL to an XML file

So I just started reading a new MySQL administration book and starting to learn cool things that I didn't even know previosly. One cool feature MySQL supports that I wasn't aware of is the ability to export and import data to and from XML files.

For example the following will export the table City from world database to an XML file.

[email protected]:~# mysql --xml -e 'SELECT * from world.City' > city.xml

To import data from the XML itself can be accomplished using the LOAD XML statement.

([email protected]) [world] LOAD XML INFILE 'city.xml' INTO TABLE City;
Query OK, 4079 rows affected, 14 warnings (0.81 sec)
Records: 4079  Deleted: 0  Skipped: 0  Warnings: 14

Additional information:


Setting phpMyAdmin to display a single database


$cfg['Servers'][$i]['only_db'] = 'databasename';



Awesome Applications: 

Resetting MySQL root password

1. End current mysql process
2. Run MySQL safe daemon with skipping grant tables

mysqld_safe --skip-grant-tables &

3. Login to MySQL as root with no password:

mysql -u root mysql

4. Run UPDATE query to reset the root password
- In MySQL command line prompt issue the following two commands:

     UPDATE user SET password=PASSWORD("NEWPASSWD") WHERE user="root";


Premium Drupal Themes by Adaptivethemes