官方文档是最好的老师，可以选择不同版本食用， LOAD DATA、INTO OUTFILE

本文验证环境为：mysql：5.7.35、centOS 7

数据导入导出是我们日常开发过程中经常面临的问题，小点的数据量平时可以用客户端直接手动操作，但是在一些大量表和数据迁移时这种方式就显得有点捉襟见肘了，同时当前对生产运维部署要求简单化，做到一键处理部署或迁移，学习MySql提供的一些命令可能会事半功倍！

一、数据导出（备份）

1. SELECT ... INTO Statement

例：

select * from employees ORDER BY lname LIMIT 1000
    INTO OUTFILE '/var/lib/mysql/employee_data_1.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY ''
    LINES TERMINATED BY '\n';

常见问题：
1). 因为mysql的安全控制，只能导出到mysql用户下的固定目录datadir和tmpdir，先查看数据库导出目录，避免因为权限问题导致报错。

# ERROR 1 (HY000): Can't create/write to file

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> show variables like 'tmpdir';
+---------------+--------------------+
| Variable_name | Value              |
+---------------+--------------------+
| tmpdir        | /var/lib/mysql/tmp |
+---------------+--------------------+
1 row in set (0.00 sec)

2). 导出文件名不能重复，重复运行sql需要修改导出文件名。

3). 因为在安装MySQL的时候限制了导入与导出的目录权限。只允许在规定的目录下才能导入，经查资料：

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.01 sec)

NULL，表示禁止。
如果value值有文件夹目录，则表示只允许该目录下文件（PS：测试子目录也不行）。
如果为空，则表示不限制目录。

按照对应查询结果调整配置文件或文件的目录即可。

如何在本地将Server端的数据导出到本地以上命令是直接在Server端运行的，导入(LOAD DATA)命令可以用local参数在将本地的文件导入Server端数据库，那有没有可以在客户端就能把数据导出来的命令呢，根据查找还真有人和我有一样的疑问😄 。

mysql -h ${hostname or ip} -u ${db_user} ${db_name} -e 'SELECT concat('this',' ','works') FROM ${table_name}' > /tmp/myfile.txt

例子中使用concat函数实现了分隔符的作用。

/Library/mysql-8.0.28-macos11-arm64/bin ./mysql -h *.*.*.* -u ycool -ss -r -p ycooldb -e "SELECT concat('id',',','value') FROM test" > /Users/yukui/WORKSPACE/test.txt
Enter password:
/Library/mysql-8.0.28-macos11-arm64/bin cat ~/WORKSPACE/test.txt
id,value
id,value
id,value

参数-ss -r -s(--silent) 、s(去除首行名称) 、raw (--raw)

常见问题：

mysql只接受localhost，不接受远程连接，参考1
其他坑，参考2

2.mysqldump

mysqldump -h ${hostname or ip} -u ${db_user} -p --no-create-info ${db_name} ${table_name} --column-statistics=0  -set-gtid-purged=OFF > /Users/yukui/WORKSPACE/test.txt

导出的文件只能通过相应的命令导入，文件内容不只有数据还有mysql生成的一些其他信息。

二、数据导入

1. LOAD DATA Statement

The LOAD DATA statement reads rows from a text file into a table at a very high speed. The file can be read from the server host or the client host, depending on whether the LOCAL modifier is given. LOCAL also affects data interpretation and error handling.

LOAD DATA 是一个高速从文件中读取数据的语句，文件可以再Mysql 服务端或者是本地客户端，这取决于‘LOCAL’参数，此参数也影响着文件的解析和异常处理。

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

1). LOCAL参数

如果指定了LOCAL，则被认为与连接的客户端有关：
- 如果指定了LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。
- 如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。
如果LOCAL没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。当在服务器主机上为文件定位时，服务器使用以下规则：
- 如果给定了一个绝对的路径名称，则服务器使用此路径名称。
- 如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。
- 如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。
注意，这些规则意味着名为./myfile.txt的文件会从服务器数据目录中被读取，而名为myfile.txt的同样的文件会从默认数据库的数据库目录中读取。例如，下面的LOAD DATA语句会从db1数据库目录中读取文件data.txt，因为db1是当前数据库。即使语句明确把文件载入到db2数据库中的表里，也会从db1目录中读取。

mysql> USE db1;

mysql> LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

未使用Local 参数时，需要关注Mysql secure_file_priv 参数会影响读取文件权限，参考上文LOAD DATA Statement中的常见问题(3).

2). [REPLACE | IGNORE]

导入数据时，可能会遇到数据包含重复的主键和唯一键，[REPLACE | IGNORE] 指明会有以下作用。
REPLACE: 执行覆盖
IGNORE: 丢弃数据

当指定LOCAL时会和IGNORE 有同样的效果，因为Server端没有办法可以中断传输操作。

即没有使用LOCAL也没有使用IGNORE，当数据解析错误时都会中断LOAD操作，使用了才会将error转变为warning。

3). FIELD 和 LINE

字段和行的处理。

以下为默认：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' 
LINES TERMINATED BY '\n' STARTING BY ''

字段以Tab间隔，没有引号包裹，转义符号为\，行以\n结尾（注意window可能以\r\n结尾），行开始于''。

还可以用 IGNORE number 来过滤文件前N行数据（如：表头等）。

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

例：

LOAD DATA LOCAL INFILE '/var/lib/mysql/tmp/mcbiccode_backup.txt' IGNORE 
INTO TABLE mcbiccode 
character set utf8mb4 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES 
(tag,modificationflag,biccode) 
set countrycode = substr(trim(biccode),5,2),
tag = trim(tag),
modificationflag = trim(modificationflag),
biccode = trim(biccode);

常见问题：

(1). 无法读取文件 ERROR 13 (HY000): Can't get stat of '/root/aaa' (Errcode: 13)

(2). 使用Local报错不允许的命令 The used command is not allowed 打开Server端的此配置，参考

mysql> show global variables like 'local_infile'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+
1 row in set (0.00 sec)

(3). 数据库用户没有File_priv权限，参考

mysql> select * from mysql.user where user='test'\G;

(4). 索引和外键冲突执行Load关闭检查，执行完开启。

SET foreign_key_checks = 0

临时关闭索引

In some extreme cases, you can create the indexes even faster by turning them off with ALTER TABLE ... DISABLE KEYS before loading the file into the table and re-creating the indexes with ALTER TABLE ... ENABLE KEYS after loading the file. See Section 8.2.4.1, “Optimizing INSERT Statements”.

[ALTER TABLE ... ENABLE KEYS]

2. mysqlimport

The [mysqlimport] utility provides another way to load data files; it operates by sending a [LOAD DATA] statement to the server. See Section 4.5.5, “mysqlimport — A Data Import Program”.

mysqlimport 原理其实就是LOAD DATA。

三、问题排查

因为涉及改动my.cnf文件和重启服务，如果遇到异常报错，

1. 根据mysql日志路径查看日志

show variables like 'log_error';
show variables like 'general_log_file';

2. 系统日志

分别使用systemctl status mysqld.service和journalctl -xe查看服务启动失败的原因

MySQL数据导出(INTO OUTFILE)和导入(LOAD DATA)

一、数据导出（备份）

1. SELECT ... INTO Statement

2.mysqldump

二、数据导入

1. LOAD DATA Statement

1). LOCAL参数

2). [REPLACE | IGNORE]

3). FIELD 和 LINE

常见问题：

2. mysqlimport

三、问题排查

1. 根据mysql日志路径查看日志

2. 系统日志