Question

In this part of the MySQL tutorial, we will be exporting data from MySQL database and importing data back.

Simple data export

In our first example, we will save data in a text file.

mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars';
Query OK, 8 rows affected (0.00 sec)

We select all rows (8) from the Cars table into the cars file located in the /tmp directory. We need to have permissions to write to that directory.

$ cat /tmp/cars
1     Audi  52642
2     Mercedes    57127
3     Skoda   9000
4     Volvo   29000
5     Bentley 350000
6     Citroen 21000
7     Hummer  41400
8     Volkswagen    21600

We show the contents of the file.

mysql> DELETE FROM Cars;

mysql> LOAD DATA INFILE '/tmp/cars' INTO TABLE Cars;

In the first statement we delete all rows from the table. In the second statement we load all data from the text file into the Cars table.

mysql> SELECT * FROM Cars INTO OUTFILE '/tmp/cars.csv'
  -> FIELDS TERMINATED BY ',';

In the above SQL statement, we dump all data from the Cars table into a cars.csv file. The FIELDS TERMINATED BY clause controls, how the data will be terminated in the text file. We have chosen a comma character. The CSV stands for Comma Separated Values and it is a very common and very portable file format. It can be imported by numerous other applications like OpenOffice, other databases etc.

$ cat /tmp/cars.csv 
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600

This is the contents of the cars.csv file.

mysql> DELETE FROM Cars;

mysql> LOAD DATA INFILE '/tmp/cars.csv' INTO TABLE Cars
  -> FIELDS TERMINATED BY ',';

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name     | Cost   |
+----+------------+--------+
|  1 | Audi     |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda    |   9000 |
|  4 | Volvo    |  29000 |
|  5 | Bentley  | 350000 |
|  6 | Citroen  |  21000 |
|  7 | Hummer   |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

We delete all the data and restore it from the cars.csv file.

Exporting to XML files

It is possible to export and import XML data using the mysql monitor.

$ mysql -uroot -p --xml -e 'SELECT * FROM mydb.Cars' > /tmp/cars.xml

The mysql monitor has an --xml option, which enables us to dump data in XML format. The -e option executes a statement and quits the monitor.

$ cat /tmp/cars.xml 
<?xml version="1.0"?>

<resultset statement="SELECT * FROM mydb.Cars
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="Id">1</field>
    <field name="Name">Audi</field>
    <field name="Cost">52642</field>
  </row>

  <row>
    <field name="Id">2</field>
    <field name="Name">Mercedes</field>
    <field name="Cost">57127</field>
  </row>

  <row>
    <field name="Id">3</field>
    <field name="Name">Skoda</field>
    <field name="Cost">9000</field>
  </row>

  <row>
    <field name="Id">4</field>
    <field name="Name">Volvo</field>
    <field name="Cost">29000</field>
  </row>

  <row>
    <field name="Id">5</field>
    <field name="Name">Bentley</field>
    <field name="Cost">350000</field>
  </row>

  <row>
    <field name="Id">6</field>
    <field name="Name">Citroen</field>
    <field name="Cost">21000</field>
  </row>

  <row>
    <field name="Id">7</field>
    <field name="Name">Hummer</field>
    <field name="Cost">41400</field>
  </row>

  <row>
    <field name="Id">8</field>
    <field name="Name">Volkswagen</field>
    <field name="Cost">21600</field>
  </row>
</resultset>

This is the XML file generated by the mysql monitor.

mysql> TRUNCATE Cars;

mysql> LOAD XML /tmp/cars.xml INTO TABLE Cars;

We truncate the Cars table. We load data from the XML file. Note that LOAD XML statement is available for MySQL 5.5 and newer.

Using mysqldump tool

The mysqldump is a command tool to create backups for MySQL. The word dump is used when we transfer data from one place to another. From a database file to a text file. From a memory to a file. And similar.

Dumping table structures

mysqldump -u root -p --no-data mydb > bkp1.sql

The above command dumps table structures of all tables in the mydb database to the bkq1.sql file. The --no-data option causes that the data is not saved, only the table structures.

--
-- Table structure for table `Cars`
--

DROP TABLE IF EXISTS `Cars`;
/*!40101 SET @saved_cs_client   = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Cars` (
  `Id` int(11) NOT NULL,
  `Name` varchar(50) DEFAULT NULL,
  `Cost` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Here we see a portion of the bkp1.sql file. This is the SQL for the creation of the Cars table.

Dumping data only

$ mysqldump -uroot -p --no-create-info mydb > bkp2.sql

This command dumps all data from all tables of the mydb databases. It omits the table structures. The omission of the table structures is caused by the --no-create-info option.

--
-- Dumping data for table `Cars`
--

LOCK TABLES `Cars` WRITE;
/*!40000 ALTER TABLE `Cars` DISABLE KEYS */;
INSERT INTO `Cars` VALUES (1,'Audi',52642),(2,'Mercedes',57127),(3,'Skoda',9000),
(4,'Volvo',29000),(5,'Bentley',350000),(6,'Citroen',21000),
(7,'Hummer',41400),(8,'Volkswagen',21600);
/*!40000 ALTER TABLE `Cars` ENABLE KEYS */;
UNLOCK TABLES;

Here we can see the data for the Cars table.

Dumping the whole database

$ mysqldump -uroot -p mydb > bkp3.sql

This command dumps all tables from the mydb database to the bkp3.sql file.

Restoring data

We show, how to restore the database from the backup SQL files.

mysql> DROP DATABASE mydb;
ERROR 1010 (HY000): Error dropping database (can't rmdir './mydb/', errno: 17)

mysql> SHOW TABLES;
Empty set (0.00 sec)

We drop the mydb database. An error is shown. The tables were dropped but not the database.

$ sudo ls /var/lib/mysql/mydb
cars  cars.txt
$ sudo rm /var/lib/mysql/mydb/cars
$ sudo rm /var/lib/mysql/mydb/cars.txt

The reason is that (in my case) while doing backups, some of the data were written in the mydb directory, in which MySQL stores the mydb database. These two alien files could not be removed, hence the above error. By removing the files the error is fixed.

mysql> DROP DATABASE mydb;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database       |
+--------------------+
| information_schema |
| mysql        |
| testdb       |
| world        |
+--------------------+
4 rows in set (0.00 sec)

The mydb database was fully removed.

mysql> CREATE DATABASE mydb;

mysql> USE mydb;

mysql> source bkp3.sql

We create the mydb database. Change to the database. And use the source command to execute the bkp3.sql script. The database is recreated.

mysql> SHOW TABLES;
+----------------+
| Tables_in_mydb |
+----------------+
| AA       |
| Ages       |
| Animals    |
| Authors    |
| BB       |
| Books      |
| Books2     |
| Brands     |
| Cars       |
...

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name     | Cost   |
+----+------------+--------+
|  1 | Audi     |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda    |   9000 |
|  4 | Volvo    |  29000 |
|  5 | Bentley  | 350000 |
|  6 | Citroen  |  21000 |
|  7 | Hummer   |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

The data is verified.

In this part of the MySQL tutorial, we have shown several ways how we can export and import data in MySQL.