匯出 Raw Data

1. 使用 mysqldump 命令匯出 Raw Data 並導向到檔案 dump.txt。

   [root@kvm3 ~]# mysqldump -uroot -p123qwe dyw animals > dump.txt
   

2. 查看 mysqldump 命令匯出的 Raw Data 檔 dump.txt。

   [root@kvm3 ~]# cat dump.txt
   /*M!999999\- enable the sandbox mode */
   -- MariaDB dump 10.19  Distrib 10.11.11-MariaDB, for Linux (x86_64)
   --
   -- Host: localhost    Database: dyw
   -- ------------------------------------------------------
   -- Server version	10.11.11-MariaDB
   
   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   /*!40101 SET NAMES utf8mb4 */;
   /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
   /*!40103 SET TIME_ZONE='+00:00' */;
   /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
   /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
   /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
   /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
   
   --
   -- Table structure for table `animals`
   --
   
   DROP TABLE IF EXISTS `animals`;
   /*!40101 SET @saved_cs_client     = @@character_set_client */;
   /*!40101 SET character_set_client = utf8mb4 */;
   CREATE TABLE `animals` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(20) NOT NULL,
     `foot` tinyint(3) unsigned NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
   /*!40101 SET character_set_client = @saved_cs_client */;
   
   --
   -- Dumping data for table `animals`
   --
   
   LOCK TABLES `animals` WRITE;
   /*!40000 ALTER TABLE `animals` DISABLE KEYS */;
   INSERT INTO `animals` VALUES
   (1,'dog',4),
   (2,'cat',4),
   (3,'bird',2),
   (4,'chicken',2);
   /*!40000 ALTER TABLE `animals` ENABLE KEYS */;
   UNLOCK TABLES;
   /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
   
   /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
   /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
   /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
   /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
   /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
   /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
   /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
   
   -- Dump completed on 2026-01-05  9:39:39
   

3. 使用 mysqldump 命令匯出 Raw Data，選項 --tab=/tmp 指定存檔目錄為 /tmp，回應 /tmp/animals.txt 已經存在。

   [root@kvm3 ~]# mysqldump -uroot -p123qwe --tab=/tmp dyw animals
   mysqldump: Got error: 1086: "File '/tmp/animals.txt' already exists" 
   when executing 'SELECT INTO OUTFILE'
   

4. 找到並刪除路徑 /tmp/systemd-private-* 中的 animals.txt。

   [root@kvm3 ~]# find /tmp -name animals.txt -exec rm -f {} \;
   

5. 再使用 mysqldump 命令匯出 Raw Data，選項 --tab=/tmp 指定存檔目錄為 /tmp。

   [root@kvm3 ~]# mysqldump -uroot -p123qwe --tab=/tmp dyw animals
   

6. 找到並顯示路徑 /tmp/systemd-private-* 中的 animals.txt 內容。

   [root@kvm3 ~]# find /tmp -name animals.txt -exec cat {} \;
   1	dog	4
   2	cat	4
   3	bird	2
   4	chicken	2
   

7. mysqldump 命令匯出的 Raw Data 檔，不只存資料表內容的 animals.txt，還有 sql 格式檔 /tmp/animals.sql。

   [root@kvm3 ~]#  cat /tmp/animals.sql
   /*M!999999\- enable the sandbox mode */
   -- MariaDB dump 10.19  Distrib 10.11.11-MariaDB, for Linux (x86_64)
   --
   -- Host: localhost    Database: dyw
   -- ------------------------------------------------------
   -- Server version	10.11.11-MariaDB
   
   /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
   /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
   /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
   /*!40101 SET NAMES utf8mb4 */;
   /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
   /*!40103 SET TIME_ZONE='+00:00' */;
   /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=” */;
   /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
   
   --
   -- Table structure for table `animals`
   --
   
   DROP TABLE IF EXISTS `animals`;
   /*!40101 SET @saved_cs_client     = @@character_set_client */;
   /*!40101 SET character_set_client = utf8mb4 */;
   CREATE TABLE `animals` (
     `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
     `name` varchar(20) NOT NULL,
     `foot` tinyint(3) unsigned NOT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
   /*!40101 SET character_set_client = @saved_cs_client */;
   
   /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
   
   /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
   /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
   /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
   /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
   /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
   
   -- Dump completed on 2026-01-05  9:45:03