使用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)