因为 MySQL 使用数据文件、日志文件和页面大小设置来初始化 InnoDB，所以建议在 MySQL 启动时读取的配置文件中定义这些设置，这应在初始化 InnoDB 之前进行。通常，InnoDB 会在 MySQL 服务器首次启动时初始化。Because MySQL uses data file, log file, and page size settings to initialize InnoDB, it is recommended that you define these settings in an option file that MySQL reads at startup, prior to initializing InnoDB. Normally, InnoDB is initialized when the MySQL server is started for the first time.

您可以将 InnoDB 选项放置在服务器启动时读取的任何配置文件的 [mysqld] 组中。MySQL 配置文件的位置在 第 6.2.2.2 节，“使用配置文件” 中有描述。You can place InnoDB options in the [mysqld] group of any option file that your server reads when it starts. The locations of MySQL option files are described in Section 6.2.2.2, “Using Option Files”.

为了确保 mysqld 仅从特定文件（和 mysqld-auto.cnf）读取选项，请在启动服务器时将 --defaults-file 作为命令行上的第一个选项：To make sure that mysqld reads options only from a specific file (and mysqld-auto.cnf), use the --defaults-file option as the first option on the command line when starting the server:

mysqld --defaults-file=path_to_option_file

要在启动过程中查看 InnoDB 初始化信息，请从命令提示符启动 mysqld，它会将初始化信息打印到控制台。To view InnoDB initialization information during startup, start mysqld from a command prompt, which prints initialization information to the console.

例如，在 Windows 上，如果 mysqld 位于 C:\Program Files\MySQL\MySQL Server 8.4\bin，可以这样启动 MySQL 服务器：For example, on Windows, if mysqld is located in C:\Program Files\MySQL\MySQL Server 8.4\bin, start the MySQL server like this:

C:\> "C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqld" --console

在类 Unix 系统上，mysqld 位于 MySQL 安装目录的 bin 目录中：On Unix-like systems, mysqld is located in the bin directory of your MySQL installation:

$> bin/mysqld --user=mysql &

如果不将服务器输出发送到控制台，请在启动后查看错误日志，以查看启动过程中打印的 InnoDB 初始化信息。If you do not send server output to the console, check the error log after startup to see the initialization information InnoDB printed during the startup process.

有关使用其他方法启动 MySQL 的信息，请参阅 第 2.9.5 节，“自动启动和停止 MySQL”。For information about starting MySQL using other methods, see Section 2.9.5, “Starting and Stopping MySQL Automatically”.

在继续配置启动之前，请回顾以下与存储相关的注意事项。Review the following storage-related considerations before proceeding with your startup configuration.

innodb_data_file_path 选项定义了 InnoDB 系统表空间数据文件的名称、大小和属性。如果在初始化 MySQL 服务器之前未配置此选项，默认行为是创建一个自动扩展的数据文件，略大于 12MB，文件名为 ibdata1：The innodb_data_file_path option defines the name, size, and attributes of InnoDB system tablespace data files. If you do not configure this option prior to initializing the MySQL server, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, named ibdata1:

mysql> SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+

完整的数据文件规范语法包括文件名、文件大小、autoextend 属性以及 max 属性：The full data file specification syntax includes the file name, file size, autoextend attribute, and max attribute:

file_name:file_size[:autoextend[:max:max_file_size]]

文件大小可以通过在数值后附加K、M或G来以千字节、兆字节或吉字节指定。如果以千字节指定数据文件大小，请以 1024 的倍数进行。否则，千字节值会四舍五入到最接近的兆字节（MB）边界。文件大小的总和至少必须略大于 12MB。File sizes are specified in kilobytes, megabytes, or gigabytes by appending K, M or G to the size value. If specifying the data file size in kilobytes, do so in multiples of 1024. Otherwise, kilobyte values are rounded to nearest megabyte (MB) boundary. The sum of file sizes must be, at a minimum, slightly larger than 12MB.

您可以使用分号分隔列表指定多个数据文件。例如：You can specify more than one data file using a semicolon-separated list. For example:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

autoextend和max属性仅可用于最后指定的数据文件。The autoextend and max attributes can be used only for the data file that is specified last.

当指定了autoextend属性时，数据文件会根据需要以 64MB 的增量自动增加大小。innodb_autoextend_increment变量控制增量大小。When the autoextend attribute is specified, the data file automatically increases in size by 64MB increments as space is required. The innodb_autoextend_increment variable controls the increment size.

要为自动扩展的数据文件指定最大大小，请在autoextend属性之后使用max属性。仅在控制磁盘使用至关重要的情况下使用max属性。以下配置允许ibdata1增长到 500MB 的限制：To specify a maximum size for an auto-extending data file, use the max attribute following the autoextend attribute. Use the max attribute only in cases where constraining disk usage is of critical importance. The following configuration permits ibdata1 to grow to a limit of 500MB:

[mysqld]
innodb_data_file_path=ibdata1:12M:autoextend:max:500M

对于第一个系统表空间数据文件，会强制执行最小文件大小，以确保有足够的空间用于双写缓冲页。下表显示了每种InnoDB页面大小的最小文件大小。默认的InnoDB页面大小为 16384 字节（16KB）。A minimum file size is enforced for the first system tablespace data file to ensure that there is enough space for doublewrite buffer pages. The following table shows minimum file sizes for each InnoDB page size. The default InnoDB page size is 16384 (16KB).

如果您的磁盘已满，您可以在另一块磁盘上添加数据文件。有关说明，请参见调整系统表空间大小。If your disk becomes full, you can add a data file on another disk. For instructions, see Resizing the System Tablespace.

单个文件的大小限制由您的操作系统决定。在支持大文件的操作系统上，您可以将文件大小设置为超过 4GB。您还可以将原始磁盘分区用作数据文件。请参见 使用原始磁盘分区作为系统表空间。The size limit for individual files is determined by your operating system. You can set the file size to more than 4GB on operating systems that support large files. You can also use raw disk partitions as data files. See Using Raw Disk Partitions for the System Tablespace.

InnoDB 不会注意到文件系统的最大文件大小，因此在最大文件大小很小的文件系统（例如 2GB）上请小心。InnoDB is not aware of the file system maximum file size, so be cautious on file systems where the maximum file size is a small value such as 2GB.

系统表空间文件默认会在数据目录中创建（datadir）。要指定其他位置，请使用 innodb_data_home_dir 选项。例如，要在名为 myibdata 的目录中创建系统表空间数据文件，可以使用如下配置：System tablespace files are created in the data directory by default (datadir). To specify an alternate location, use the innodb_data_home_dir option. For example, to create a system tablespace data file in a directory named myibdata, use this configuration:

[mysqld]
innodb_data_home_dir = /myibdata/
innodb_data_file_path=ibdata1:50M:autoextend

在为 innodb_data_home_dir 指定值时，需要加上尾部斜杠。InnoDB 不会创建目录，因此在启动服务器之前，请确保指定的目录已经存在。此外，还要确保 MySQL 服务器具有在该目录中创建文件的适当访问权限。A trailing slash is required when specifying a value for innodb_data_home_dir. InnoDB does not create directories, so ensure that the specified directory exists before you start the server. Also, ensure sure that the MySQL server has the proper access rights to create files in the directory.

InnoDB 通过将 innodb_data_home_dir 的值与数据文件名文本连接来形成每个数据文件的目录路径。如果未定义 innodb_data_home_dir，默认值为 “./”，即数据目录。（当 MySQL 服务器开始执行时，它会将当前工作目录更改为数据目录。）InnoDB forms the directory path for each data file by textually concatenating the value of innodb_data_home_dir to the data file name. If innodb_data_home_dir is not defined, the default value is “./”, which is the data directory. (The MySQL server changes its current working directory to the data directory when it begins executing.)

或者，您可以为系统表空间数据文件指定绝对路径。以下配置与前面的配置等效：Alternatively, you can specify an absolute path for system tablespace data files. The following configuration is equivalent to the preceding one:

[mysqld]
innodb_data_file_path=/myibdata/ibdata1:50M:autoextend

当你为 innodb_data_file_path 指定绝对路径时，该设置不会与 innodb_data_home_dir 设置连接起来。系统表空间文件会在指定的绝对路径中创建。在启动服务器之前，必须确保指定的目录存在。When you specify an absolute path for innodb_data_file_path, the setting is not concatenated with the innodb_data_home_dir setting. System tablespace files are created in the specified absolute path. The specified directory must exist before you start the server.

InnoDB 双写缓冲区存储区域位于双写文件中，这为双写页的存储位置提供了灵活性。在以前的版本中，双写缓冲区存储区域位于系统表空间中。innodb_doublewrite_dir 变量定义了 InnoDB 在启动时创建双写文件的目录。如果未指定目录，双写文件将在 innodb_data_home_dir 目录中创建，如果未指定，该目录默认为数据目录。The InnoDB doublewrite buffer storage area resides in doublewrite files, which provides flexibility with respect to the storage location of doublewrite pages. In previous releases, the doublewrite buffer storage area resided in the system tablespace. The innodb_doublewrite_dir variable defines the directory where InnoDB creates doublewrite files at startup. If no directory is specified, doublewrite files are created in the innodb_data_home_dir directory, which defaults to the data directory if unspecified.

要在 innodb_data_home_dir 目录以外的位置创建双写文件，请配置 innodb_doublewrite_dir 变量。例如：To have doublewrite files created in a location other than the innodb_data_home_dir directory, configure innodb_doublewrite_dir variable. For example:

innodb_doublewrite_dir=/path/to/doublewrite_directory

其他双写缓冲区变量允许定义双写文件的数量、每个线程的页数以及双写批量大小。有关双写缓冲区配置的更多信息，请参阅 第 17.6.4 节，“双写缓冲区”。Other doublewrite buffer variables permit defining the number of doublewrite files, the number of pages per thread, and the doublewrite batch size. For more information about doublewrite buffer configuration, see Section 17.6.4, “Doublewrite Buffer”.

重做日志文件占用的磁盘空间由 innodb_redo_log_capacity 变量控制，该变量可以在启动时或运行时设置；例如，要在选项文件中将该变量设置为 8GiB，请添加以下条目：The amount of disk space occupied by redo log files is controlled by the innodb_redo_log_capacity variable, which can be set at startup or runtime; for example, to set the variable to 8GiB in an option file, add the following entry:

[mysqld]
innodb_redo_log_capacity = 8589934592

有关在运行时配置重做日志容量的信息，请参见 配置重做日志容量。For information about configuring redo log capacity at runtime, see Configuring Redo Log Capacity.

innodb_redo_log_capacity 变量取代了 innodb_log_file_size 和 innodb_log_files_in_group 变量，这些变量已被弃用。当定义了 innodb_redo_log_capacity 设置时，innodb_log_file_size 和 innodb_log_files_in_group 设置将被忽略；否则，如果定义了其中一个或两个已弃用的设置，则它们用于计算 Innodb_redo_log_capacity_resized，计算公式为 (innodb_log_files_in_group * innodb_log_file_size)。如果这些变量都未设置，则使用默认的 innodb_redo_log_capacity 值。The innodb_redo_log_capacity variable supersedes the innodb_log_file_size and innodb_log_files_in_group variables, which are deprecated. When the innodb_redo_log_capacity setting is defined, the innodb_log_file_size and innodb_log_files_in_group settings are ignored; otherwise, if one or both of these deprecated settings are defined then they are used to compute Innodb_redo_log_capacity_resized as (innodb_log_files_in_group * innodb_log_file_size). If none of those variables are set, then the default innodb_redo_log_capacity value is used.

InnoDB 尝试维护 32 个重做日志文件，每个文件等于 1/32 * innodb_redo_log_capacity。重做日志文件位于数据目录中的 #innodb_redo 目录下，除非通过 innodb_log_group_home_dir 变量指定了不同的目录。如果定义了 innodb_log_group_home_dir，重做日志文件将位于该目录下的 #innodb_redo 目录中。更多信息，请参见 第 17.6.5 节，“重做日志”。InnoDB attempts to maintain 32 redo log files, with each file equal to 1/32 * innodb_redo_log_capacity. The redo log files reside in the #innodb_redo directory in the data directory unless a different directory was specified by the innodb_log_group_home_dir variable. If innodb_log_group_home_dir was defined, the redo log files reside in the #innodb_redo directory in that directory. For more information, see Section 17.6.5, “Redo Log”.

在初始化 MySQL 服务器实例时，您可以通过配置 innodb_log_files_in_group 和 innodb_log_file_size 变量来定义不同数量的重做日志文件和不同的重做日志文件大小。You can define a different number of redo log files and different redo log file size when initializing the MySQL Server instance by configuring the innodb_log_files_in_group and innodb_log_file_size variables.

innodb_log_files_in_group 定义日志组中的日志文件数量。默认值和推荐值为 2。innodb_log_files_in_group defines the number of log files in the log group. The default and recommended value is 2.

innodb_log_file_size 定义了日志组中每个日志文件的字节大小。组合日志文件大小（innodb_log_file_size * innodb_log_files_in_group）不能超过最大值，该值略小于 512GB。例如，一对 255 GB 的日志文件接近限制但未超过。默认日志文件大小为 48MB。通常，日志文件的总大小应足够大，以便服务器能够平滑处理工作负载活动中的峰值和低谷，这通常意味着有足够的重做日志空间来处理一个小时以上的写入活动。较大的日志文件大小意味着缓冲池中检查点刷新活动减少，从而降低磁盘 I/O。欲了解更多信息，请参见 第 10.5.4 节，“优化 InnoDB 重做日志”。innodb_log_file_size defines the size in bytes of each log file in the log group. The combined log file size (innodb_log_file_size * innodb_log_files_in_group) cannot exceed the maximum value, which is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default log file size is 48MB. Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. A larger log file size means less checkpoint flush activity in the buffer pool, which reduces disk I/O. For additional information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.

innodb_log_group_home_dir 定义了 InnoDB 日志文件的目录路径。您可能会使用此选项将 InnoDB 重做日志文件放置在与 InnoDB 数据文件不同的物理存储位置，以避免潜在的 I/O 资源冲突；例如：The innodb_log_group_home_dir defines the directory path to the InnoDB log files. You might use this option to place InnoDB redo log files in a different physical storage location than InnoDB data files to avoid potential I/O resource conflicts; for example:

[mysqld]
innodb_log_group_home_dir = /dr3/iblogs

回滚日志默认存放在 MySQL 实例初始化时创建的两个回滚表空间中。Undo logs, by default, reside in two undo tablespaces created when the MySQL instance is initialized.

innodb_undo_directory 变量定义了 InnoDB 创建默认回滚表空间的路径。如果该变量未定义，默认回滚表空间将创建在数据目录中。innodb_undo_directory 变量不是动态的。配置它需要重启服务器。The innodb_undo_directory variable defines the path where InnoDB creates default undo tablespaces. If that variable is undefined, default undo tablespaces are created in the data directory. The innodb_undo_directory variable is not dynamic. Configuring it requires restarting the server.

回滚日志的 I/O 模式使回滚表空间成为 SSD 存储的良好候选。The I/O patterns for undo logs make undo tablespaces good candidates for SSD storage.

有关配置附加撤销表空间的信息，请参见第 17.6.3.4 节，“撤销表空间”。For information about configuring additional undo tablespaces, see Section 17.6.3.4, “Undo Tablespaces”.

全局临时表空间存储对用户创建的临时表所做更改的回滚段。The global temporary tablespace stores rollback segments for changes made to user-created temporary tables.

默认情况下，在innodb_data_home_dir目录中有一个名为ibtmp1的自动扩展全局临时表空间数据文件。初始文件大小略大于 12MB。A single auto-extending global temporary tablespace data file named ibtmp1 in the innodb_data_home_dir directory by default. The initial file size is slightly larger than 12MB.

innodb_temp_data_file_path 选项用于指定全局临时表空间数据文件的路径、文件名和文件大小。文件大小可以通过在数值后添加 K、M 或 G 来以 KB、MB 或 GB 指定。文件大小或组合文件大小必须略大于 12MB。The innodb_temp_data_file_path option specifies the path, file name, and file size for global temporary tablespace data files. File size is specified in KB, MB, or GB by appending K, M, or G to the size value. The file size or combined file size must be slightly larger than 12MB.

要为全局临时表空间数据文件指定备用位置，请在启动时配置 innodb_temp_data_file_path 选项。To specify an alternate location for global temporary tablespace data files, configure the innodb_temp_data_file_path option at startup.

在 MySQL 8.4 中，InnoDB 始终用作内部临时表的磁盘存储引擎。In MySQL 8.4, InnoDB is always used as the on-disk storage engine for internal temporary tables.

innodb_temp_tablespaces_dir 变量定义了 InnoDB 创建会话临时表空间的位置。默认位置是在数据目录下的 #innodb_temp 目录。The innodb_temp_tablespaces_dir variable defines the location where InnoDB creates session temporary tablespaces. The default location is the #innodb_temp directory in the data directory.

要为会话临时表空间指定备用位置，请在启动时配置 innodb_temp_tablespaces_dir 变量。允许使用完整路径或相对于数据目录的路径。To specify an alternate location for session temporary tablespaces, configure the innodb_temp_tablespaces_dir variable at startup. A fully qualified path or path relative to the data directory is permitted.

innodb_page_size 选项指定 MySQL 实例中所有 InnoDB 表空间的页面大小。该值在实例创建时设置，之后保持不变。有效值为 64KB、32KB、16KB（默认）、8KB 和 4KB。或者，您可以以字节为单位指定页面大小（65536、32768、16384、8192、4096）。The innodb_page_size option specifies the page size for all InnoDB tablespaces in a MySQL instance. This value is set when the instance is created and remains constant afterward. Valid values are 64KB, 32KB, 16KB (the default), 8KB, and 4KB. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096).

默认的 16KB 页大小适用于各种工作负载，特别是涉及表扫描的查询以及涉及批量更新的 DML 操作。对于涉及许多小写入的 OLTP 工作负载来说，较小的页大小可能更高效，因为当单个页包含许多行时，可能会出现争用问题。对于通常使用小块大小的 SSD 存储设备，较小的页也可能更高效。将 InnoDB 页大小保持接近存储设备块大小，可以最大限度地减少被重写到磁盘的未更改数据量。The default 16KB page size is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when a single page contains many rows. Smaller pages can also be more efficient for SSD storage devices, which typically use small block sizes. Keeping the InnoDB page size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.

MySQL 为各种缓存和缓冲区分配内存，以提高数据库操作的性能。在为 InnoDB 分配内存时，应始终考虑操作系统所需的内存、分配给其他应用程序的内存以及分配给其他 MySQL 缓冲区和缓存的内存。例如，如果您使用 MyISAM 表，请考虑为键缓冲区分配的内存量（key_buffer_size）。有关 MySQL 缓冲区和缓存的概览，请参见 第 10.12.3.1 节，“MySQL 如何使用内存”。MySQL allocates memory to various caches and buffers to improve performance of database operations. When allocating memory for InnoDB, always consider memory required by the operating system, memory allocated to other applications, and memory allocated for other MySQL buffers and caches. For example, if you use MyISAM tables, consider the amount of memory allocated for the key buffer (key_buffer_size). For an overview of MySQL buffers and caches, see Section 10.12.3.1, “How MySQL Uses Memory”.

特定于 InnoDB 的缓冲区可以使用以下参数进行配置：Buffers specific to InnoDB are configured using the following parameters:

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

在 Linux 上，如果内核启用了大页支持，InnoDB 可以使用大页为其缓冲池分配内存。请参阅 第 10.12.3.3 节，“启用大页支持”。On Linux, if the kernel is enabled for large page support, InnoDB can use large pages to allocate memory for its buffer pool. See Section 10.12.3.3, “Enabling Large Page Support”.