MySQL 8.4 Release Notes
LOAD DATA 语句将数据文件加载到表中。该语句可以加载位于服务器主机上的文件，或者，如果指定了 LOCAL 关键字，则可以加载位于客户端主机上的文件。The LOAD DATA statement loads a
data file into a table. The statement can load a file located on
the server host, or, if the LOCAL keyword is
specified, on the client host.
LOCAL 的 LOAD
DATA 版本存在两个潜在的安全问题：The LOCAL version of LOAD
DATA has two potential security issues:
因为
LOAD DATA LOCAL是一个 SQL 语句，所以解析发生在服务器端，并且从客户端主机到服务器主机的文件传输是由 MySQL 服务器发起的，服务器会告诉客户端语句中指定的文件。理论上，一个打过补丁的服务器可以告诉客户端程序传输服务器选择的文件，而不是语句中指定的文件。这样的服务器可以访问客户端主机上客户端用户有读取权限的任何文件。（实际上，打过补丁的服务器可以对任何语句回复文件传输请求，而不仅仅是LOAD DATA LOCAL，所以一个更根本的问题是客户端不应连接到不可信的服务器。）BecauseLOAD DATA LOCALis an SQL statement, parsing occurs on the server side, and transfer of the file from the client host to the server host is initiated by the MySQL server, which tells the client the file named in the statement. In theory, a patched server could tell the client program to transfer a file of the server's choosing rather than the file named in the statement. Such a server could access any file on the client host to which the client user has read access. (A patched server could in fact reply with a file-transfer request to any statement, not justLOAD DATA LOCAL, so a more fundamental issue is that clients should not connect to untrusted servers.)在客户端从 Web 服务器连接的 Web 环境中，用户可以使用
LOAD DATA LOCAL来读取 Web 服务器进程具有读取权限的任何文件（假设用户可以对 SQL 服务器运行任何语句）。在这种环境下，相对于 MySQL 服务器的客户端实际上是 Web 服务器，而不是用户连接到 Web 服务器时运行的远程程序。In a Web environment where the clients are connecting from a Web server, a user could useLOAD DATA LOCALto read any files that the Web server process has read access to (assuming that a user could run any statement against the SQL server). In this environment, the client with respect to the MySQL server actually is the Web server, not a remote program being run by users who connect to the Web server.
为了避免连接到不受信任的服务器，客户端可以通过使用--ssl-mode=VERIFY_IDENTITY选项并使用适当的 CA 证书来建立安全连接并验证服务器身份。要实现这种级别的验证，您必须首先确保服务器的 CA 证书能够可靠地提供给副本，否则会导致可用性问题。有关更多信息，请参阅加密连接的命令选项。To avoid connecting to untrusted servers, clients can establish a
secure connection and verify the server identity by connecting
using the
--ssl-mode=VERIFY_IDENTITY option
and the appropriate CA certificate. To implement this level of
verification, you must first ensure that the CA certificate for
the server is reliably available to the replica, otherwise
availability issues will result. For more information, see
Command Options for Encrypted Connections.
为了避免LOAD DATA问题，客户端应避免使用LOCAL，除非已经采取了适当的客户端预防措施。To avoid LOAD DATA issues, clients
should avoid using LOCAL unless proper
client-side precautions have been taken.
为了对本地数据加载进行控制，MySQL 允许启用或禁用该功能。MySQL 还允许客户端将本地数据加载操作限制在指定目录中的文件。For control over local data loading, MySQL permits the capability to be enabled or disabled. MySQL also enables clients to restrict local data loading operations to files located in a designated directory.
管理员和应用程序可以按如下方式配置是否允许本地数据加载：Administrators and applications can configure whether to permit local data loading as follows:
在服务器端：On the server side:
local_infile系统变量控制服务器端的LOCAL功能。根据local_infile设置，服务器会拒绝或允许请求本地数据加载的客户端进行本地数据加载。Thelocal_infilesystem variable controls server-sideLOCALcapability. Depending on thelocal_infilesetting, the server refuses or permits local data loading by clients that request local data loading.默认情况下，
local_infile是禁用的。（这与以前版本的 MySQL 不同。）为了明确地使服务器拒绝或允许LOAD DATA LOCAL语句（无论客户端程序和库在构建时或运行时如何配置），请以禁用或启用local_infile的方式启动 mysqld。local_infile也可以在运行时设置。By default,local_infileis disabled. (This is a change from previous versions of MySQL.) To cause the server to refuse or permitLOAD DATA LOCALstatements explicitly (regardless of how client programs and libraries are configured at build time or runtime), start mysqld withlocal_infiledisabled or enabled.local_infilecan also be set at runtime.
在客户端：On the client side:
ENABLED_LOCAL_INFILECMake 选项控制 MySQL 客户端库的编译默认LOCAL功能（参见 第 2.8.7 节，“MySQL 源代码配置选项”）。因此，如果客户端没有进行明确设置，则LOCAL功能会根据 MySQL 构建时指定的ENABLED_LOCAL_INFILE设置被禁用或启用。TheENABLED_LOCAL_INFILECMake option controls the compiled-in defaultLOCALcapability for the MySQL client library (see Section 2.8.7, “MySQL Source-Configuration Options”). Clients that make no explicit arrangements therefore haveLOCALcapability disabled or enabled according to theENABLED_LOCAL_INFILEsetting specified at MySQL build time.默认情况下，MySQL 二进制发行版中的客户端库是以
ENABLED_LOCAL_INFILE被禁用的方式编译的。如果你从源码编译 MySQL，可根据是否希望未作明确安排的客户端禁用或启用LOCAL功能来配置ENABLED_LOCAL_INFILE为禁用或启用。By default, the client library in MySQL binary distributions is compiled withENABLED_LOCAL_INFILEdisabled. If you compile MySQL from source, configure it withENABLED_LOCAL_INFILEdisabled or enabled based on whether clients that make no explicit arrangements should haveLOCALcapability disabled or enabled.对于使用 C API 的客户端程序，本地数据加载功能取决于编译进 MySQL 客户端库的默认设置。要显式启用或禁用它，请调用
mysql_options()C API 函数来禁用或启用MYSQL_OPT_LOCAL_INFILE选项。请参阅 mysql_options()。For client programs that use the C API, local data loading capability is determined by the default compiled into the MySQL client library. To enable or disable it explicitly, invoke themysql_options()C API function to disable or enable theMYSQL_OPT_LOCAL_INFILEoption. See mysql_options().对于 mysql 客户端，本地数据加载功能取决于编译到 MySQL 客户端库中的默认设置。要显式禁用或启用它，请使用
--local-infile=0或--local-infile[=1]选项。For the mysql client, local data loading capability is determined by the default compiled into the MySQL client library. To disable or enable it explicitly, use the--local-infile=0or--local-infile[=1]option.对于mysqlimport客户端，默认情况下不使用本地数据加载。要显式禁用或启用它，请使用
--local=0或--local[=1]选项。For the mysqlimport client, local data loading is not used by default. To disable or enable it explicitly, use the--local=0or--local[=1]option.如果你在 Perl 脚本或其他读取选项文件中
[client]组的程序中使用LOAD DATA LOCAL，你可以向该组添加local-infile选项设置。为了防止不理解此选项的程序出现问题，请使用loose-前缀来指定它：If you useLOAD DATA LOCALin Perl scripts or other programs that read the[client]group from option files, you can add alocal-infileoption setting to that group. To prevent problems for programs that do not understand this option, specify it using theloose-prefix:[client] loose-local-infile=0
或：or:
[client] loose-local-infile=1
在所有情况下，客户端成功使用
LOCAL加载操作还要求服务器允许本地加载。In all cases, successful use of aLOCALload operation by a client also requires that the server permits local loading.
如果在服务器或客户端禁用 LOCAL 能力，尝试执行 LOAD DATA
LOCAL 语句的客户端将收到以下错误信息：If LOCAL capability is disabled, on either
the server or client side, a client that attempts to issue a
LOAD DATA
LOCAL statement receives the following error message:
ERROR 3950 (42000): Loading local data is disabled; this must be enabled on both the client and server side
MySQL 客户端库使客户端应用程序能够将本地数据加载操作限制在指定目录中的文件。某些 MySQL 客户端程序利用了此功能。The MySQL client library enables client applications to restrict local data loading operations to files located in a designated directory. Certain MySQL client programs take advantage of this capability.
使用 C API 的客户端程序可以通过 mysql_options() C API 函数的 MYSQL_OPT_LOCAL_INFILE 和 MYSQL_OPT_LOAD_DATA_LOCAL_DIR 选项来控制允许加载哪些文件（请参阅 mysql_options()）。Client programs that use the C API can control which files to
permit for load data loading using the
MYSQL_OPT_LOCAL_INFILE and
MYSQL_OPT_LOAD_DATA_LOCAL_DIR options of the
mysql_options() C API function
(see mysql_options()).
MYSQL_OPT_LOAD_DATA_LOCAL_DIR 的效果取决于 LOCAL 数据加载是启用还是禁用：The effect of MYSQL_OPT_LOAD_DATA_LOCAL_DIR
depends on whether LOCAL data loading is
enabled or disabled:
如果启用了
LOCAL数据加载，无论是 MySQL 客户端库默认启用，还是显式启用MYSQL_OPT_LOCAL_INFILE，MYSQL_OPT_LOAD_DATA_LOCAL_DIR选项都不会生效。IfLOCALdata loading is enabled, either by default in the MySQL client library or by explicitly enablingMYSQL_OPT_LOCAL_INFILE, theMYSQL_OPT_LOAD_DATA_LOCAL_DIRoption has no effect.如果禁用了
LOCAL数据加载，无论是 MySQL 客户端库默认禁用，还是显式禁用MYSQL_OPT_LOCAL_INFILE，MYSQL_OPT_LOAD_DATA_LOCAL_DIR选项可以用来指定允许本地加载文件的目录。在这种情况下，允许LOCAL数据加载，但仅限于位于指定目录的文件。MYSQL_OPT_LOAD_DATA_LOCAL_DIR值的解释如下：IfLOCALdata loading is disabled, either by default in the MySQL client library or by explicitly disablingMYSQL_OPT_LOCAL_INFILE, theMYSQL_OPT_LOAD_DATA_LOCAL_DIRoption can be used to designate a permitted directory for locally loaded files. In this case,LOCALdata loading is permitted but restricted to files located in the designated directory. Interpretation of theMYSQL_OPT_LOAD_DATA_LOCAL_DIRvalue is as follows:如果该值是空指针（默认值），则不会命名任何目录，因此不允许
LOCAL数据加载的文件。If the value is the null pointer (the default), it names no directory, with the result that no files are permitted forLOCALdata loading.如果该值是一个目录路径名，则允许
LOCAL数据加载，但仅限于位于指定目录的文件。目录路径名与要加载文件的路径名的比较是区分大小写的，无论底层文件系统是否区分大小写。If the value is a directory path name,LOCALdata loading is permitted but restricted to files located in the named directory. Comparison of the directory path name and the path name of files to be loaded is case-sensitive regardless of the case sensitivity of the underlying file system.
MySQL 客户端程序使用上述 mysql_options() 选项如下：MySQL client programs use the preceding
mysql_options() options as
follows:
mysql 客户端有一个
--load-data-local-dir选项，该选项接受一个目录路径或一个空字符串。mysql 使用此选项值来设置MYSQL_OPT_LOAD_DATA_LOCAL_DIR选项（空字符串将值设置为空指针）。--load-data-local-dir的效果取决于LOCAL数据加载是否启用：The mysql client has a--load-data-local-diroption that takes a directory path or an empty string. mysql uses the option value to set theMYSQL_OPT_LOAD_DATA_LOCAL_DIRoption (with an empty string setting the value to the null pointer). The effect of--load-data-local-dirdepends on whetherLOCALdata loading is enabled:如果启用了
LOCAL数据加载，无论是默认在 MySQL 客户端库中启用还是通过指定--local-infile[=1]，--load-data-local-dir选项将被忽略。IfLOCALdata loading is enabled, either by default in the MySQL client library or by specifying--local-infile[=1], the--load-data-local-diroption is ignored.如果禁用了
LOCAL数据加载，无论是默认在 MySQL 客户端库中禁用还是通过指定--local-infile=0，--load-data-local-dir选项将生效。IfLOCALdata loading is disabled, either by default in the MySQL client library or by specifying--local-infile=0, the--load-data-local-diroption applies.
当
--load-data-local-dir生效时，该选项的值指定本地数据文件必须所在的目录。无论底层文件系统是否区分大小写，目录路径名与要加载文件的路径名比较时都是区分大小写的。如果选项值为空字符串，则表示未命名任何目录，因此不允许进行本地数据加载的文件。When--load-data-local-dirapplies, the option value designates the directory in which local data files must be located. Comparison of the directory path name and the path name of files to be loaded is case-sensitive regardless of the case sensitivity of the underlying file system. If the option value is the empty string, it names no directory, with the result that no files are permitted for local data loading.mysqlimport为它处理的每个文件设置
MYSQL_OPT_LOAD_DATA_LOCAL_DIR，以便包含该文件的目录成为允许的本地加载目录。mysqlimport setsMYSQL_OPT_LOAD_DATA_LOCAL_DIRfor each file that it processes so that the directory containing the file is the permitted local loading directory.对于与
LOAD DATA语句对应的数据加载操作，mysqlbinlog 会从二进制日志事件中提取文件，将它们作为临时文件写入本地文件系统，并写入LOAD DATA LOCAL语句以加载这些文件。默认情况下，mysqlbinlog 会将这些临时文件写入操作系统特定的目录。可以使用--local-load选项明确指定 mysqlbinlog 应准备本地临时文件的目录。For data loading operations corresponding toLOAD DATAstatements, mysqlbinlog extracts the files from the binary log events, writes them as temporary files to the local file system, and writesLOAD DATA LOCALstatements to cause the files to be loaded. By default, mysqlbinlog writes these temporary files to an operating system-specific directory. The--local-loadoption can be used to explicitly specify the directory where mysqlbinlog should prepare local temporary files.因为其他进程可能会向默认的系统特定目录写入文件，因此建议为
--local-load选项指定一个不同的目录，用于 mysqlbinlog 的数据文件，然后在处理 mysqlbinlog 的输出时，通过指定--load-data-local-dir选项，将该同一目录指定给 mysql。Because other processes can write files to the default system-specific directory, it is advisable to specify the--local-loadoption to mysqlbinlog to designate a different directory for data files, and then designate that same directory by specifying the--load-data-local-diroption to mysql when processing the output from mysqlbinlog.
MySQL Shell 提供了许多实用工具，用于导出表、模式或服务器实例并将其加载到其他实例中。当您使用这些工具处理数据时，MySQL Shell 提供了额外的功能，例如输入预处理、多线程并行加载、文件压缩和解压缩，以及处理对 Oracle Cloud Infrastructure 对象存储桶的访问。为了获得最佳功能，请始终使用 MySQL Shell 的导出和导入工具的最新版本。MySQL Shell provides a number of utilities to dump tables, schemas, or server instances and load them into other instances. When you use these utilities to handle the data, MySQL Shell provides additional functions such as input preprocessing, multithreaded parallel loading, file compression and decompression, and handling access to Oracle Cloud Infrastructure Object Storage buckets. To get the best functionality, always use the most recent version available of MySQL Shell's dump and dump loading utilities.
MySQL Shell 的数据上传工具使用 LOAD DATA LOCAL
INFILE 语句来上传数据，因此目标服务器实例上的 local_infile 系统变量必须设置为 ON。您可以在上传数据之前进行此设置，并在上传后再次移除。该工具可以安全地处理文件传输请求，以应对本主题中讨论的安全考虑事项。MySQL Shell's data upload utilities use
LOAD DATA LOCAL
INFILE statements to upload data, so the
local_infile system variable
must be set to ON on the target server
instance. You can do this before uploading the data, and remove
it again afterwards. The utilities handle the file transfer
requests safely to deal with the security considerations
discussed in this topic.
MySQL Shell 包含这些导出和导入工具：MySQL Shell includes these dump and dump loading utilities:
- 表导出工具
util.exportTable()Table export utilityutil.exportTable() 将 MySQL 关系表导出为数据文件，该文件可以使用 MySQL Shell 的并行表导入工具上传到 MySQL 服务器实例，导入到不同的应用程序，或用作逻辑备份。该工具具有预设选项和自定义选项，以生成不同的输出格式。Exports a MySQL relational table into a data file, which can be uploaded to a MySQL server instance using MySQL Shell's parallel table import utility, imported to a different application, or used as a logical backup. The utility has preset options and customization options to produce different output formats.
- 并行表导入工具
util.importTable()Parallel table import utilityutil.importTable() 将数据文件导入到 MySQL 关系表中。数据文件可以是 MySQL Shell 表导出工具的输出，也可以是该工具预设和自定义选项支持的其他格式。该工具可以在将数据添加到表之前进行输入预处理。它可以接受多个数据文件合并到一个关系表中，并自动解压压缩文件。Imports a data file to a MySQL relational table. The data file can be the output from MySQL Shell's table export utility or another format supported by the utility's preset and customization options. The utility can carry out input preprocessing before adding the data to the table. It can accept multiple data files to merge into a single relational table, and automatically decompresses compressed files.
- 实例转储工具
util.dumpInstance()、模式转储工具util.dumpSchemas()和表转储工具util.dumpTables()Instance dump utilityutil.dumpInstance(), schema dump utilityutil.dumpSchemas(), and table dump utilityutil.dumpTables() 将实例、模式或表导出到一组转储文件，然后可以使用 MySQL Shell 的转储加载工具将其上传到 MySQL 实例。这些工具提供 Oracle 云基础设施对象存储流功能、MySQL HeatWave 服务兼容性检查和修改，以及在执行转储之前进行演练以识别问题的能力。Export an instance, schema, or table to a set of dump files, which can then be uploaded to a MySQL instance using MySQL Shell's dump loading utility. The utilities provide Oracle Cloud Infrastructure Object Storage streaming, MySQL HeatWave Service compatibility checks and modifications, and the ability to carry out a dry run to identify issues before proceeding with the dump.
- 转储加载工具
util.loadDump()Dump loading utilityutil.loadDump() 将使用 MySQL Shell 的实例、模式或表转储工具创建的转储文件导入到 MySQL HeatWave 服务数据库系统或 MySQL Server 实例中。该工具管理上传过程，并提供从远程存储的数据流、表或表块的并行加载、进度状态跟踪、恢复和重置功能，以及在转储仍在进行时进行并发加载的选项。MySQL Shell 的并行表导入工具可以与转储加载工具结合使用，在将数据上传到目标 MySQL 实例之前修改数据。Import dump files created using MySQL Shell's instance, schema, or table dump utility into a MySQL HeatWave Service DB System or a MySQL Server instance. The utility manages the upload process and provides data streaming from remote storage, parallel loading of tables or table chunks, progress state tracking, resume and reset capability, and the option of concurrent loading while the dump is still taking place. MySQL Shell’s parallel table import utility can be used in combination with the dump loading utility to modify data before uploading it to the target MySQL instance.
有关实用程序的详细信息，请参阅 MySQL Shell 实用程序。For details of the utilities, see MySQL Shell Utilities.