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

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.