使用mysqldump以文字分隔格式來dump資料

這裡將介紹如何使用mysqldump來建立文字分隔格式的dump檔案。在呼叫mysqldump時使用--tab=dir_name選項時，使用dir_name作為輸出目錄並且在輸出目錄是為每個表生成兩個檔案。表名是這些檔案的基礎名稱。對錶t，檔名為t.sql和t.txt。其中.sql檔案包含的是表的 create table語句。.txt檔案包含的是表資料，每一條記錄一行。下面的命令將資料庫mysql的內容dump到/mysqldata/tmp目錄中

在用mysqldump備份時候遇到1290的錯誤

從提示看到是因為mysql服務啟用了--secure-file-priv，所以才無法執行。這個選項或系統變數用被來限制透過load data和select ... into outfile語句和load_file()函式所執行匯入和匯出操作的資料量。 secure_file_priv有以下設定: .如果為空，變數不生效。沒有安全設定。 .如果設定為一個目錄名，伺服器對匯入和匯出操作只對這個目錄中的檔案生效，但目錄必須存在，伺服器不會建立目錄。 .如果設定NULL，伺服器禁止匯入和匯出操作。 檢視資料庫當前設定:

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+
3 rows in set (0.01 sec)

看到secure_file_priv當前設定為NULL,說明限制匯入和匯出操作。

檢視配置檔案my.cnf發現沒有設定secure_file_priv引數，那麼這個引數預設應該是為NULL。

[mysql@localhost mysql]$ cat my.cnf
[mysqld]
basedir=/mysqlsoft/mysql
datadir=/mysqldata/mysql
bind-address=*
user=mysql
port=3306
log-error=/mysqldata/mysql/mysql.err
pid-file=/mysqldata/mysql/mysqld.pid
socket = /mysqlsoft/mysql/mysql.sock
character-set-server=utf8mb4
default-storage-engine=INNODB
explicit_defaults_for_timestamp = true
innodb_flush_method=O_DIRECT
binlog_format = mixed
log-bin=/mysqldata/mysql/binlog
max_binlog_size = 100m
binlog_cache_size = 4m
server-id=1

修改配置檔案my.cnf參加secure_file_priv=

[mysql@localhost mysql]$ vi my.cnf
.....
secure-file-priv=

重啟mysql資料庫

[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

再次檢查secure_file_priv引數設定

mysql> show global variables like '%secur%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         |       |
+--------------------------+-------+
3 rows in set (0.00 sec)

再執行匯出操作成功

[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp  mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

資料庫伺服器會生成包含資料內容的.txt檔案，因此它是屬於執行資料庫服務的系統賬號。伺服器使用select ... into outfile來寫這個檔案，因此你必須有file許可權才能執行這個操作，如果一個特定的.txt檔案已經存在的話會出錯。

資料庫伺服器為被dump的表傳送create定義語句來mysqldump，它將被寫入到.sql檔案中。因此這些檔案屬於執行mysqldump命令的使用者。

--tab選項最好只在本地伺服器上執行dump。如果將它用於遠端伺服器，--tab選項所指定的目錄必須在本地伺服器和遠端伺服器上都要存在，燕且.txt檔案會被伺服器寫入遠端目錄(資料庫伺服器所在主機)，而.sql檔案將被mysqldump寫入本地目錄(客戶端所在主機)。

對於mysqldump --tab，伺服器預設情況下將表資料寫入.txt檔案，每行表記錄一行，列值之間使用製表符，列值周圍不使用引號，換行符作為行結束符。

為了能在寫入資料檔案時使用不同的格式，mysqldump支援以下選項:
.--fields-terminated-by=str
列值分隔符(預設為:tab製表符)

.--fields-enclosed-by=char
將列值括在其中的字元(預設為:沒有)

.--fields-optionally-enclosed-by=char
用來括起非數值列值的字元(預設為:沒有)

.--fields-escaped-by=char
用於轉義特殊字元的字元(預設為:沒有轉義)
.--lines-terminated-by=str
行終止符(預設為:新行)

根據你所為這些選項指定的值，為了讓命令直譯器能正確處理可能需要在命令列中為這些值使用引號或轉義。另外可以以十六進位制格式來指定。假設你想用雙引號引用列值。為了達到這個目的應該為--fields-enclosed-by選項指定雙引號。但雙引號通常對於命令直譯器有特定含義並且必須特定對待。例如，在Unix中，可以使用引號來指定雙引號:
--fields-enclosed-by='"'

在任何平臺中，可以指定十六進位制值:
--fields-enclosed-by=0x22

同時使用幾個資料格式化選項是很常見的。例如，為了使用透過回車/換行符對(\r\n)作為行終止符的逗號分隔值格式來dump表，可以執行以下命令:

[mysql@localhost tmp]$ mysqldump -uroot -pxxzx7817600 --tab=/mysqldata/tmp --fields-terminated-by=, --fields-enclosed-by='"'  --lines-terminated-by=0x0d0a mysql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[mysql@localhost tmp]$ ls -lrt
總用量 3128
-rw-r--r--. 1 mysql mysql    1871 7月   9 17:54 rewrite_rules.sql
-rw-r--r--. 1 mysql mysql    1876 7月  10 15:37 columns_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 columns_priv.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 count.txt
-rw-r--r--. 1 mysql mysql    1298 7月  10 15:37 count.sql
-rw-rw-rw-. 1 mysql mysql       7 7月  10 15:37 cs.txt
-rw-r--r--. 1 mysql mysql    1359 7月  10 15:37 cs.sql
-rw-r--r--. 1 mysql mysql    2917 7月  10 15:37 db.sql
-rw-rw-rw-. 1 mysql mysql     198 7月  10 15:37 db.txt
-rw-rw-rw-. 1 mysql mysql     114 7月  10 15:37 engine_cost.txt
-rw-r--r--. 1 mysql mysql    1630 7月  10 15:37 engine_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 event.txt
-rw-r--r--. 1 mysql mysql    3350 7月  10 15:37 event.sql
-rw-r--r--. 1 mysql mysql    1557 7月  10 15:37 func.sql
-rw-rw-rw-. 1 mysql mysql     398 7月  10 15:37 func.txt
-rw-r--r--. 1 mysql mysql    1578 7月  10 15:37 gtid_executed.sql
-rw-rw-rw-. 1 mysql mysql     964 7月  10 15:37 help_category.txt
-rw-r--r--. 1 mysql mysql    1563 7月  10 15:37 help_category.sql
-rw-r--r--. 1 mysql mysql    1471 7月  10 15:37 help_keyword.sql
-rw-rw-rw-. 1 mysql mysql    9748 7月  10 15:37 help_keyword.txt
-rw-r--r--. 1 mysql mysql    1486 7月  10 15:37 help_relation.sql
-rw-rw-rw-. 1 mysql mysql   10771 7月  10 15:37 help_relation.txt
-rw-r--r--. 1 mysql mysql    1592 7月  10 15:37 help_topic.sql
-rw-rw-rw-. 1 mysql mysql  712538 7月  10 15:37 help_topic.txt
-rw-rw-rw-. 1 mysql mysql      32 7月  10 15:37 imptest.txt
-rw-r--r--. 1 mysql mysql    1337 7月  10 15:37 imptest.sql
-rw-r--r--. 1 mysql mysql    1879 7月  10 15:37 innodb_index_stats.sql
-rw-rw-rw-. 1 mysql mysql    2647 7月  10 15:37 innodb_index_stats.txt
-rw-rw-rw-. 1 mysql mysql     395 7月  10 15:37 innodb_table_stats.txt
-rw-r--r--. 1 mysql mysql    1745 7月  10 15:37 innodb_table_stats.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 ndb_binlog_index.txt
-rw-r--r--. 1 mysql mysql    1842 7月  10 15:37 ndb_binlog_index.sql
-rw-rw-rw-. 1 mysql mysql      76 7月  10 15:37 person.txt
-rw-r--r--. 1 mysql mysql    1571 7月  10 15:37 person.sql
-rw-r--r--. 1 mysql mysql    1420 7月  10 15:37 plugin.sql
-rw-rw-rw-. 1 mysql mysql      53 7月  10 15:37 plugin.txt
-rw-r--r--. 1 mysql mysql    3067 7月  10 15:37 proc.sql
-rw-rw-rw-. 1 mysql mysql     806 7月  10 15:37 proc.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 procs_priv.txt
-rw-r--r--. 1 mysql mysql    1970 7月  10 15:37 procs_priv.sql
-rw-rw-rw-. 1 mysql mysql      60 7月  10 15:37 proxies_priv.txt
-rw-r--r--. 1 mysql mysql    1862 7月  10 15:37 proxies_priv.sql
-rw-rw-rw-. 1 mysql mysql     297 7月  10 15:37 server_cost.txt
-rw-r--r--. 1 mysql mysql    1530 7月  10 15:37 server_cost.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 servers.txt
-rw-r--r--. 1 mysql mysql    1730 7月  10 15:37 servers.sql
-rw-r--r--. 1 mysql mysql    4150 7月  10 15:37 slave_master_info.sql
-rw-r--r--. 1 mysql mysql    2406 7月  10 15:37 slave_relay_log_info.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 slave_worker_info.txt
-rw-r--r--. 1 mysql mysql    2221 7月  10 15:37 slave_worker_info.sql
-rw-r--r--. 1 mysql mysql    1342 7月  10 15:37 t.sql
-rw-rw-rw-. 1 mysql mysql      52 7月  10 15:37 t.txt
-rw-rw-rw-. 1 mysql mysql       3 7月  10 15:37 t1.txt
-rw-r--r--. 1 mysql mysql    1291 7月  10 15:37 t1.sql
-rw-r--r--. 1 mysql mysql    1306 7月  10 15:37 t2.sql
-rw-rw-rw-. 1 mysql mysql       4 7月  10 15:37 t2.txt
-rw-rw-rw-. 1 mysql mysql     162 7月  10 15:37 tables_priv.txt
-rw-r--r--. 1 mysql mysql    2068 7月  10 15:37 tables_priv.sql
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 test.txt
-rw-r--r--. 1 mysql mysql    1304 7月  10 15:37 test.sql
-rw-r--r--. 1 mysql mysql    1487 7月  10 15:37 time_zone.sql
-rw-rw-rw-. 1 mysql mysql   11122 7月  10 15:37 time_zone.txt
-rw-rw-rw-. 1 mysql mysql       0 7月  10 15:37 time_zone_leap_second.txt
-rw-r--r--. 1 mysql mysql    1493 7月  10 15:37 time_zone_leap_second.sql
-rw-r--r--. 1 mysql mysql    1435 7月  10 15:37 time_zone_name.sql
-rw-rw-rw-. 1 mysql mysql   41245 7月  10 15:37 time_zone_name.txt
-rw-r--r--. 1 mysql mysql    1548 7月  10 15:37 time_zone_transition.sql
-rw-rw-rw-. 1 mysql mysql 1998482 7月  10 15:37 time_zone_transition.txt
-rw-r--r--. 1 mysql mysql    1670 7月  10 15:37 time_zone_transition_type.sql
-rw-rw-rw-. 1 mysql mysql  161600 7月  10 15:37 time_zone_transition_type.txt
-rw-rw-rw-. 1 mysql mysql     878 7月  10 15:37 user.txt
-rw-r--r--. 1 mysql mysql    4390 7月  10 15:37 user.sql
-rw-r--r--. 1 mysql mysql    1576 7月  10 15:37 general_log.sql
-rw-r--r--. 1 mysql mysql    1757 7月  10 15:37 slow_log.sql
mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)
[mysql@localhost tmp]$ cat t.sql
-- MySQL dump 10.13  Distrib 5.7.26, for Linux (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.7.26-log
/*!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 utf8 */;
/*!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 `t`
--
DROP TABLE IF EXISTS `t`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `date` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!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-07-10 15:37:05
[mysql@localhost tmp]$ cat t.txt
"1","jingyong",\N
"2","yeyali",\N
"3","huangyan",\N
"4","wenyao",\N

如果使用任何資料格式化選項來轉儲表資料，則在稍後重新載入資料檔案時需要指定相同的格式，以確保正確地解釋檔案內容。

重新載入文字分隔格式的備份檔案
對於使用mysqldump --tab命令所生成的備份，每個表在輸出目錄中都有一個包含create table語句的.sql檔案和一個包含表資料的.txt檔案。為了重新載入表，首先進入到輸出目錄。然後使用msyql命令來處理.sql檔案建立一個空表，再執行mysqlimport來處理.txt檔案載入資料:

[mysql@localhost tmp]$ mysql -uroot -pxxzx7817600  mysql < t.sql mysql: [Warning] Using a password on the command line interface can be insecure. [mysql@localhost tmp]$ mysqlimport -uroot -pxxzx7817600 --fields-terminated-by=, --fields-enclosed-by='"' --lines-terminated-by=0x0d0a mysql /mysqldata/tmp/t.txt mysqlimport: [Warning] Using a password on the command line interface can be insecure. mysql.t: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)

另一種替mysqlimport來載入資料檔案的方式是使用load data infile語句:

mysql> load data infile '/mysqldata/tmp/t.txt' into table t fields terminated by ',' enclosed by '"' lines terminated by 0x0d0a;
Query OK, 4 rows affected (0.03 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from t;
+----+----------+------+
| id | name     | date |
+----+----------+------+
|  1 | jingyong | NULL |
|  2 | yeyali   | NULL |
|  3 | huangyan | NULL |
|  4 | wenyao   | NULL |
+----+----------+------+
4 rows in set (0.00 sec)