MySQL 导出和导入数据|极客教程

本章我们将从 MySQL 数据库导出数据并将数据导入回来。

简单的数据导出

在第一个示例中，我们将数据保存在文本文件中。

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

我们从Cars表中选择所有行（8）到/tmp目录中的 cars 文件中。我们需要具有写入该目录的权限。

$ 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

我们显示文件的内容。

mysql> DELETE FROM Cars;

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

在第一条语句中，我们删除表中的所有行。在第二条语句中，我们将所有数据从文本文件加载到 Cars 表中。

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

在上面的 SQL 语句中，我们将 Cars 表中的所有数据转储到 cars.csv 文件中。 FIELDS TERMINATED BY子句控制在文本文件中如何终止数据。我们选择了一个逗号字符。 CSV 代表逗号分隔值，它是一种非常常见且可移植的文件格式。它可以由许多其他应用（如 OpenOffice，其他数据库等）导入。

$ 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

这是 cars.csv 文件的内容。

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 |
+----+------------+--------+

我们删除所有数据，然后从cars.csv文件中将其还原。

导出到 XML 文件

可以使用mysql监视器导出和导入 XML 数据。

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

mysql监视器具有--xml选项，使我们能够以 XML 格式转储数据。 -e选项执行一条语句并退出监视器。

$ 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>

这是mysql监视器生成的 XML 文件。

mysql> TRUNCATE Cars;

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

我们截断 Cars 表。我们从 XML 文件加载数据。请注意，LOAD XML语句可用于 MySQL 5.5 及更高版本。

使用 mysqldump 工具

mysqldump是用于为 MySQL 创建备份的命令工具。当我们将数据从一个地方传输到另一个地方时，会使用转储一词。从数据库文件到文本文件。从内存到文件。和类似。

转储表结构

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

上面的命令将mydb数据库中所有表的表结构转储到bkq1.sql文件中。 --no-data选项导致不保存数据，仅保存表结构。

--
-- 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 */;

在这里，我们看到了bkp1.sql文件的一部分。这是用于创建Cars表的 SQL。

仅转储数据

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

此命令转储mydb数据库的所有表中的所有数据。它省略了表结构。表结构的遗漏是由--no-create-info选项引起的。

--
-- 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;

在这里，我们可以看到Cars表的数据。

转储整个数据库

$ mysqldump -uroot -p mydb > bkp3.sql

此命令将所有表从mydb数据库转储到bkp3.sql文件。

恢复数据

我们展示了如何从备份 SQL 文件还原数据库。

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

mysql> SHOW TABLES;
Empty set (0.00 sec)

我们删除mydb数据库。显示错误。表已删除，但数据库未删除。

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

原因是（在我的情况下）在进行备份时，一些数据写入了mydb目录，MySQL 在其中存储了mydb数据库。这两个外来文件无法删除，因此出现上述错误。通过删除文件，错误已得到解决。

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)

mydb 数据库已完全删除。

mysql> CREATE DATABASE mydb;

mysql> USE mydb;

mysql> source bkp3.sql

我们创建mydb数据库。更改为数据库。并使用 source 命令执行bkp3.sql脚本。重新创建数据库。

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 |
+----+------------+--------+