匯出 Raw Data

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

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

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

   [root@kvm8 ~]# cat dump.txt 
   -- MySQL dump 10.17  Distrib 10.3.11-MariaDB, for Linux (x86_64)
   --
   -- Host: localhost    Database: dyw
   -- ------------------------------------------------------
   -- Server version	10.3.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 = utf8 */;
   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=16 DEFAULT CHARSET=utf8;
   /*!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,'chicken',2),(15,'bird',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 2020-01-30 14:31:26
   

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

   [root@kvm8 ~]# 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@kvm8 ~]# find /tmp/systemd-private-* -name animals.txt | xargs rm -f
   

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

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

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

   [root@kvm8 ~]# find /tmp/systemd-private-* -name animals.txt | xargs cat -
   1	dog	4
   2	cat	4
   3	chicken	2
   15	bird	2
   

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

   [root@kvm8 ~]# cat /tmp/animals.sql 
   -- MySQL dump 10.17  Distrib 10.3.11-MariaDB, for Linux (x86_64)
   --
   -- Host: localhost    Database: dyw
   -- ------------------------------------------------------
   -- Server version	10.3.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 = utf8 */;
   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=16 DEFAULT CHARSET=utf8;
   /*!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 2020-01-30 14:36:39