MySQL 导出导入

最后修改于 2023 年 1 月 10 日

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

数据已验证。

在本 MySQL 教程中，我们展示了在 MySQL 中导出和导入数据的几种方法。

电子书