MySQL 5.6 Reference Manual-14.5 InnoDB Tablespace Management

最新推荐文章于 2021-07-12 00:00:00 发布
最新推荐文章于 2021-07-12 00:00:00 发布
阅读量187 收藏
点赞数
文章标签： 运维 数据库 操作系统
原文链接：http://www.cnblogs.com/paololiu/p/5623222.html
版权

14.5 InnoDB Tablespace Management

 

14.5.1 Resizing the InnoDB System Tablespace

14.5.2 Changing the Number or Size of InnoDB Redo Log Files

14.5.3 Using Raw Disk Partitions for the System Tablespace

14.5.4 InnoDB File-Per-Table Tablespaces

14.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory

14.5.6 Copying File-Per-Table Tablespaces to Another Server

14.5.7 Storing InnoDB Undo Logs in Separate Tablespaces

 

14.5.1 Resizing the InnoDB System Tablespace

 

This section describes how to increase or decrease the size of the InnoDB system tablespace.

这一章节讲述了如何增加减少InnoDB系统表空间的大小。

 

Increasing the Size of the InnoDB System Tablespace

 

The easiest way to increase the size of the InnoDB system tablespace is to configure it from the beginning to be auto-extending. Specify the autoextend attribute for the last data file in the tablespace definition. Then InnoDB increases the size of that file automatically in 8MB increments when it runs out of space. The increment size can be changed by setting the value of the innodb_autoextend_increment system variable, which is measured in megabytes.

增加InnoDB系统表空间大小最简单的方法是在一开始的时候就把它配置成自动扩展的。在表空间的定义中指定最后数据文件自动扩展，当空间满的时候InnoDB会自动为那个文件增加8MB。增长的值可以通过innodb_autoextend_increment系统参数设定，单位是M。

 

You can expand the system tablespace by a defined amount by adding another data file:

你可以通过增加另一个数据文件来扩展系统表空间：

 

1.Shut down the MySQL server.

1.关闭MySQL实例。

 

2.If the previous last data file is defined with the keyword autoextend, change its definition to use a fixed size, based on how large it has actually grown. Check the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

2.如果之前的最后一个文件已经定义自动扩展了，那就根据表空间的实际增长情况修改增长值。检查数据文件的大小， round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify this rounded size explicitly in innodb_data_file_path.

 

3.Add a new data file to the end of innodb_data_file_path, optionally making that file auto-extending. Only the last data file in the innodb_data_file_path can be specified as auto-extending.

3.在innodb_data_file_path的尾部添加一个新的文件，并指定自动扩展。只有innodb_data_file_path里最后一个数据文件可以被指定为自动扩展。

 

4.Start the MySQL server again.

4.再次启动MySQL实例。

 

For example, this tablespace has just one auto-extending data file ibdata1:

例如，这个表空间只有一个自动扩展数据文件ibdata1：

 

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

 

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to use a fixed size and adding a new auto-extending data file:

假设这个数据文件，随着时间已经增长到988MB。这里就配置修改了原始数据使用一个固定的大小并添加一个新的自动扩展的数据文件：

 

innodb_data_home_dir =

innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

 

When you add a new data file to the system tablespace configuration, make sure that the filename does not refer to an existing file. InnoDB creates and initializes the file when you restart the server.

当你要像系统表空间添加一个新的数据文件的时候，要确保文件名不会引用到现有的文件。InnoDB会在你重启实例的时候创建并初始化这个文件。

 

Decreasing the Size of the InnoDB System Tablespace

 

You cannot remove a data file from the system tablespace. To decrease the system tablespace size, use this procedure:

你不能重系统表空间里移除数据文件。要减少系统表空间大小的话可以进行如下的操作：

 

1.Use mysqldump to dump all your InnoDB tables, including InnoDB tables located in the MySQL database. As of 5.6, there are five InnoDB tables included in the MySQL database:

1.使用mysqldump导出你所有的InnoDB表，包括MySQL数据库里的InnoDB表。从5.6开始，包括在MySQL数据库里的有5个InnoDB表：

 

mysql> select table_name from information_schema.tables where table_schema='mysql' and engine='InnoDB';

+----------------------+

| table_name |

+----------------------+

| innodb_index_stats |

| innodb_table_stats |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

+----------------------+

5 rows in set (0.00 sec)

 

 

2.Stop the server.

2.关闭实例。

 

3.Remove all the existing tablespace files (*.ibd), including the ibdata and ib_log files. Do not forget to remove *.ibd files for tables located in the MySQL database.

3.移除所有现存的表空间文件(*.ibd)，包括ibdata 和ib_log文件。还有要不忘记移除MySQL数据库里的*.ibd文件。

 

4.Remove any .frm files for InnoDB tables.

4.移除InnoDB表的.frm文件。

 

5.Configure a new tablespace.

5.配置一个新的表空间。

 

6.Restart the server.

6.重启实例。

 

7.Import the dump files.

7.导入dump文件。

 

Note

 

If your databases only use the InnoDB engine, it may be simpler to dump all databases, stop the server, remove all databases and InnoDB log files, restart the server, and import the dump files.

如果你的数据库只使用了InnoDB引擎，那么最简单的办法是导出所有的数据库，停止实例，移除所有的数据库和InnoDB日志文件，重启实例，再导入dump文件。

 

14.5.2 Changing the Number or Size of InnoDB Redo Log Files

 

To change the number or size of InnoDB redo log files in MySQL 5.6.7 or earlier, perform the following steps:

在MySQL5.6.7或者更早的版本更改InnoDB redo log文件的数量或大小，要执行下面的步骤：

 

1.If innodb_fast_shutdown is set to 2, set innodb_fast_shutdown to 1:

1.如果innodb_fast_shutdown的设置成2，那么改成1：

 

mysql> SET GLOBAL innodb_fast_shutdown = 1;

 

2.After ensuring that innodb_fast_shutdown is not set to 2, stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the log).

2.在确保innodb_fast_shutdown设置的不是2之后，停止MySQL实例并确保没有错误（确保在log里面没有未完成事务的信息）。

 

3.Copy the old log files into a safe place in case something went wrong during the shutdown and you need them to recover the tablespace.

3.把老的log文件复制到一个安全的地方，以防止关闭报错的时候用来recover表空间。

 

4.Delete the old log files from the log file directory.

4.在日志文件目录删除老的日志文件。

 

5.Edit my.cnf to change the log file configuration.

5.编辑my.cnf修改日志文件配置。

 

6.Start the MySQL server again. mysqld sees that no InnoDB log files exist at startup and creates new ones.

6.启动MySQL实例。mysqld的在启动的时候看到没有InnoDB日志文件会创建一个新的。

 

As of MySQL 5.6.8, the innodb_fast_shutdown setting is no longer relevant when changing the number or the size of InnoDB log files. Additionally, you are no longer required remove old log files, although you may still want to copy the old log files to a safe place, as a backup. To change the number or size of InnoDB log files, perform the following steps:

从MySQL5.6.8开始，innodb_fast_shutdown设置已经和修改InnoDB日志文件的数量或大小没有关系了。另外，你也不再需要移除老的日志文件，当然你仍然可以把老的日志文件复制到一个安全的地方作为备份。要修改InnoDB日志文件的数量或大小可以执行以下的步骤：

 

1.Stop the MySQL server and make sure that it shuts down without errors.

1.关闭MySQL实例并确保没有报错。

 

2.Edit my.cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size. To increase the number of log files, configure innodb_log_files_in_group.

2.编译my.cnf文件修改日志文件配置。innodb_log_file_size配置日志文件的大小。innodb_log_files_in_group配置日志文件的数量。

 

3.Start the MySQL server again.

3.启动MySQL实例。

 

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it will write a log checkpoint, close and remove the old log files, create new log files at the requested size, and open the new log files.

如果InnoDB发现innodb_log_file_size的值不同于redo log文件的大小，它会写下日志的checkpoint，关闭并删除老的日志文件，再创建一个符合大小要求的新的日志文件，最后打开新的日志文件。

 

14.5.3 Using Raw Disk Partitions for the System Tablespace

 

You can use raw disk partitions as data files in the InnoDB system tablespace. This technique enables nonbuffered I/O on Windows and on some Linux and Unix systems without file system overhead. Perform tests with and without raw partitions to verify whether this change actually improves performance on your system.

你可以使用raw磁盘分区来作为InnoDB系统表空间的数据文件。这种方式能够使得在Windows以及一些Linux平台上使用没有buffer的I/O，这样就减少了文件系统的损耗。分别对使用或没使用raw分区的情况进行测试，经验证这样的修改对于的你系统是否有实际上的性能提升。

 

When you use a raw disk partition, ensure that the user ID that runs the MySQL server has read and write privileges for that partition. For example, if you run the server as the mysql user, the partition must be readable and writeable by mysql. If you run the server with the --memlock option, the server must be run as root, so the partition must be readable and writeable by root.

当你使用了raw磁盘分区，要确保运行MySQL实例的用户ID有读写分区的权限。例如，你以mysql用户来运行实例的，那么mysql用户对于分区必须要能读能写。如果你想要以--memlock的参数运行的实例，那必须要以root来运行，那么分区对于root也必须是要可读可写的。

 

The procedures described below involve option file modification. For additional information, see Section 4.2.6, "Using Option Files".

下面涉及的配置文件修改的相关信息可以查看Section 4.2.6, "Using Option Files"。

 

Allocating a Raw Disk Partition on Linux and Unix Systems

 

1.When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option. The partition must be at least as large as the size that you specify. Note that 1MB in InnoDB is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

1.当你创建一个新数据文件的时候，在innodb_data_file_path文件大小的后面指定关键字newraw。分区也必须至少达到你指定的大小。还有要注意的是在InnoDB里面1MB等于1024 × 1024 bytes，然而在磁盘规格上1MB通常是1,000,000 bytes。

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

 

2.Restart the server. InnoDB notices the newraw keyword and initializes the new partition. However, do not create or change any InnoDB tables yet. Otherwise, when you next restart the server, InnoDB reinitializes the partition and your changes are lost. (As a safety measure InnoDB prevents users from modifying data when any partition with newraw is specified.)

2.重启实例。InnoDB注意到newraw关键字会初始化这个新的分区。但是，不会创建或修改任何的InnoDB表。要不然，当你再次重启实例的时候，InnoDB会再次初始化分区，你的修改也会丢失。（作为一种安全的方式当任何分区指定了newraw，InnoDB会阻止用户修改数据。）

 

3.After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

3.在InnoDB初始完新的分区之后，停止实例，为其他的raw数据文件指定newraw ：

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

 

4.Restart the server. InnoDB now permits changes to be made.

4.重启实例。现在InnoDB允许可以修改了。

 

Allocating a Raw Disk Partition on Windows

 

On Windows systems, the same steps and accompanying guidelines described for Linux and Unix systems apply except that the innodb_data_file_path setting differs slightly on Windows.

在Windows系统上，除了innodb_data_file_path的配置有些许不同，其他的步骤和Linux平台的一样。

 

1.When you create a new data file, specify the keyword newraw immediately after the data file size for the innodb_data_file_path option:

1.当你创建一个新数据文件的时候，在innodb_data_file_path的数据文件的大小后面指定keyword 关键字：

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Gnewraw

 

The //./ corresponds to the Windows syntax of \\.\ for accessing physical drives. In the example above, D: is the drive letter of the partition.

//./对应Windows的\\.\。在上面的例子里，D: 是分区的驱动字符。

 

2.Restart the server. InnoDB notices the newraw keyword and initializes the new partition.

2.重启实例。InnoDB注意到newraw 关键字会初始化新的分区。

 

3.After InnoDB has initialized the new partition, stop the server, change newraw in the data file specification to raw:

3.在InnoDB初始完新的分区之后，停止实例，为其他的raw数据文件指定newraw：

 

[mysqld]

innodb_data_home_dir=

innodb_data_file_path=//./D::10Graw

 

4.Restart the server. InnoDB now permits changes to be made.

4.重启实例。InnoDB现在允许修改。

 

14.5.4 InnoDB File-Per-Table Tablespaces

 

14.5.4.1 Enabling and Disabling File-Per-Table Tablespaces

 

Historically, all InnoDB tables and indexes were stored in the system tablespace. This monolithic approach was targeted at machines dedicated entirely to database processing, with carefully planned data growth, where any disk storage allocated to MySQL would never be needed for other purposes. InnoDB's file-per-table tablespace feature provides a more flexible alternative, where each InnoDB table and its indexes are stored in a separate .ibd data file. Each such .ibd data file represents an individual tablespace. This feature is controlled by the innodb_file_per_table configuration option, which is enabled by default in MySQL 5.6.6 and higher.

在以前，所有的InnoDB表和索引都是存储在系统表空间里的。这种单片的方法是针对于整个机器都是用于数据库处理的，这样可精心计划数据的增长，MySQL分配的任何磁盘存储也不会用于其他的目的。InnoDB file-per-table 表空间特性提供了一种更灵活的方式，每个InnoDB的表和索引都存储在各自.ibd数据文件里。每个.ibd数据文件都代表了一个单独的表空间。这种特性是由innodb_file_per_table配置参数控制的，在MySQL5.6.6及更高的版本里默认是开启的。

 

Advantages of File-Per-Table Tablespaces

 

  • You can reclaim disk space when truncating or dropping a table stored in a file-per-table tablepace. Truncating or dropping tables stored in the system tablespace creates free space internally in the system tablespace data files (ibdata files) which can only be used for new InnoDB data.
  • 当truncate或者drop存储在file-per-table表空间里的表的时候，能够回收磁盘空间。而truncate或者drop存储在系统表空间里的表时回收的空间只能给新的InnoDB数据使用。

 

  • The TRUNCATE TABLE operation is faster when run on tables stored in file-per-table tablepaces.
  • 表存储在file-per-table表空间上，运行TRUNCATE TABLE操作速度更快。

 

  • You can store specific tables on separate storage devices, for I/O optimization, space management, or backup purposes. In previous releases, you had to move entire database directories to other drives and create symbolic links in the MySQL data directory, as described in Section 8.12.4, "Using Symbolic Links". In MySQL 5.6.6 and higher, you can specify the location of each table using the syntax CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory, as explained in Section 14.5.5, "Creating a File-Per-Table Tablespace Outside the Data Directory".
  • 你可以为I/O优化，空间管理，或者备份目的，把指定的表存储在不同的存储磁盘上。在以前的版本，你则要把整个数据目录移动到其他的磁盘上，在创建一个链接指向到MySQL的数据目录，如Section 8.12.4, "Using Symbolic Links"所讲述的。在MySQL5.6.6及更高的版本里，你可以通过CREATE TABLE ... DATA DIRECTORY = absolute_path_to_directory来指定每个表位置，如Section 14.5.5, "Creating a File-Per-Table Tablespace Outside the Data Directory"锁描述的。

 

  • You can run OPTIMIZE TABLE to compact or recreate a file-per-table tablespace. When you run an OPTIMIZE TABLE, InnoDB creates a new .ibd file with a temporary name, using only the space required to store actual data. When the optimization is complete, InnoDB removes the old .ibd file and replaces it with the new one. If the previous .ibd file grew significantly but the actual data only accounted for a portion of its size, running OPTIMIZE TABLE can reclaim the unused space.
  • 你可以运行OPTIMIZE TABLE来压缩或者重建一个file-per-table表空间。当你运行了OPTIMIZE TABLE，InnoDB会使用一个临时的名字创建一个新的.ibd文件，这个文件仅占用实际数据的大小。当OPTIMIZE TABLE操作完成了，InnoDB会删除旧的.ibd文件并用新的来替换它。如果以前的.ibd文件增长得很大但是实际数据只占用了一部分，那么运行OPTIMIZE TABLE可以回收未使用的空间。

 

  • You can move individual InnoDB tables rather than entire databases.
  • 你可以移动个别表而不需要动整个数据库。

 

  • You can copy individual InnoDB tables from one MySQL instance to another (known as the transportable tablespace feature).
  • 你可以从一个MySQL实例里面复制个别表到另一个实例里面（表空间传输特性the transportable tablespace feature）。

 

  • Tables created in file-per-table tablespaces use the Barracuda file format. The Barracuda file format enables features such as compressed and dynamic row formats. Tables created in the system tablespace cannot use these features. To take advantage of these features for an existing table, enable the innodb_file_per_table setting and run ALTER TABLE t ENGINE=INNODB to place the table in a file-per-table tablespace. Before converting tables, refer to Section 14.6.4, "Converting Tables from MyISAM to InnoDB".
  • 创建在file-per-table表空间里的表使用了Barracuda的文件格式。Barracuda文件格式具有压缩和动态的行格式。而创建在系统表空间里的表是无法使用这些特性的。现有的表要使用这些特性优势，要开始innodb_file_per_table设定并运行ALTER TABLE t ENGINE=INNODB把表放置到filer-per-table表空间里。在转换之前，请先参考Section 14.6.4, "Converting Tables from MyISAM to InnoDB"。

 

  • You can enable more efficient storage for tables with large BLOB or TEXT columns using the dynamic row format.
  • 使用动态行格式(dynamic row format)可以使得有很多BLOB或者TEXT列的表在存储上更有效率。

 

  • File-per-table tablespaces may improve chances for a successful recovery and save time when a corruption occurs, when a server cannot be restarted, or when backup and binary logs are unavailable.
  • 当发生故障实例无法重启的时候，或者备份和二进制日志(binary logs)无法使用的时候，file-per-table表空间能够提升recovery的成功几率并节约一定的时间。

 

  • You can back up or restore individual tables quickly using the MySQL Enterprise Backup product, without interrupting the use of other InnoDB tables. This is beneficial if you have tables that require backup less frequently or on a different backup schedule. See Partial Backup and Restore Options for details.
  • 你可以使用MySQL Enterprise Backup来快速备份或者恢复别个的表，而不会中断其他InnoDB表的使用。这在那种要求备份少量频繁使用或者不同备份策略的表时就显得非常有益了。相关部分可以查看Partial Backup and Restore Options。

 

  • File-per-table tablespaces are convenient for per-table status reporting when copying or backing up tables.
  • 当复制或者备份表的时候，file-per-table表空间为每个表报告状态则更方便。

 

  • You can monitor table size at a file system level, without accessing MySQL.
  • 你可以在文件系统层面监控表的大小，而不需要访问MySQL。

 

  • Common Linux file systems do not permit concurrent writes to a single file when innodb_flush_method is set to O_DIRECT. As a result, there are possible performance improvements when using file-per-table tablespaces in conjunction with innodb_flush_method.
  • 当innodb_flush_method被设置成O_DIRECT的时候普通的Linux文件系统是不允许对单个文件进行并发写的。因此，当同时使用file-per-table表空间以及innodb_flush_method的时候能够提升性能。

 

  • The system tablespace stores the data dictionary and undo logs, and has a 64TB size limit. By comparison, each file-per-table tablespace has a 64TB size limit, which provides you with room for growth. See Section C.10.3, "Limits on Table Size" for related information.
  • 存储数据目录和undo log的系统表空间是有64TB的大小限制的。相比较之下，每个表空间各有64TB的限制，这就为你提供了更多的增长空间。详见Section C.10.3, "Limits on Table Size" for related information。

 

Potential Disadvantages of File-Per-Table Tablespaces

 

  • With file-per-table tablespaces, each table may have unused space, which can only be utilized by rows of the same table. This could lead to wasted space if not properly managed.
  • 使用file-per-table表空间，每个表都可能有未使用的空间，而这部分空间只能被它自己使用。如果管理不当的话就可能导致空间的浪费。

 

  • fsync operations must run on each open table rather than on a single file. Because there is a separate fsync operation for each file, write operations on multiple tables cannot be combined into a single I/O operation. This may require InnoDB to perform a higher total number of fsync operations.
  • fsync操作必须运行在每个打开的表上，而不是一个单独的文件。因为每个文件分别都有一个fsync操作，所以多个表上的写操作就无法合并成一个I/O操作。这可能会使得InnoDB执行更多的fsync操作。

 

  • mysqld must keep one open file handle per table, which may impact performance if you have numerous tables in file-per-table tablespaces.
  • mysqld必须为每个表保持一个open file handle，如果你有很多的file-per-table表空间的表就可能会影响性能。

 

  • More file descriptors are used.
  • 使用更多的file descriptors。

 

  • innodb_file_per_table is enabled by default in MySQL 5.6.6 and higher. You may consider disabling it if backward compatibility with MySQL 5.5 or 5.1 is a concern. Disabling innodb_file_per_table prevents ALTER TABLE from moving an InnoDB table from the system tablespace to an individual .ibd file in cases where ALTER TABLE recreates the table (ALGORITHM=COPY).
  • 在MySQL5.6.6及更高版本里innodb_file_per_table默认是开启的。如果要回退到MySQL5.5或者5.1的话就要考虑关闭它。关闭innodb_file_per_table能够阻止ALTER TABLE把一个InnoDB表从系统表空间移动到个别.ibd文件。这种情况下ALTER TABLE会重新创建表(ALGORITHM=COPY)。

 

For example, when restructuring the clustered index for an InnoDB table, the table is re-created using the current setting for innodb_file_per_table. This behavior does not apply when adding or dropping InnoDB secondary indexes. When a secondary index is created without rebuilding the table, the index is stored in the same file as the table data, regardless of the current innodb_file_per_table setting.

例如，当为一个InnoDB表重建clustered index的时候，使用当前的innodb_file_per_table设定表会重建。但是添加或删除InnoDB secondary index的时候不是这样的。当创建一个secondary index而不重建表的时候，索引会存储在表数据的相同的文件里，而不管innodb_file_per_table的设定是什么。

 

  • If many tables are growing there is potential for more fragmentation which can impede DROP TABLE and table scan performance. However, when fragmentation is managed, having files in their own tablespace can improve performance.
  • 如果很多的表增长得有了很多的碎片，这就会妨碍了DROP TABLE和表扫描的性能。然而，碎片能够有效管理的话，file-per-table表空间还能提升性能的。

 

  • The buffer pool is scanned when dropping a file-per-table tablespace, which can take several seconds for buffer pools that are tens of gigabytes in size. The scan is performed with a broad internal lock, which may delay other operations. Tables in the system tablespace are not affected.
  • 当删除一个file-per-table表空间的时候是要扫描buffer pool的，这对于有几十个G的buffer pool也是要花费不少时间（several seconds）的。这种扫描使用的是一种广泛的内部锁，这会延迟其他的操作。而在系统表空间里的表则不会有这样的影响。

 

  • The innodb_autoextend_increment variable, which defines increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full, does not apply to file-per-table tablespace files, which are auto-extending regardless of the innodb_autoextend_increment setting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.
  • innodb_autoextend_increment变量定义了自动扩展的共享表空间满的时候扩展的大小(in MB)，但这不适用于file-per-table表空间文件。file-per-table表空间的自动扩展与innodb_autoextend_increment的设定无关。最初的扩展是少量的，这之后会增长到4MB。

 

14.5.4.1 Enabling and Disabling File-Per-Table Tablespaces

 

The innodb_file_per_table option is enabled by default as of MySQL 5.6.6.

innodb_file_per_table参数在MySQL5.6.6里面默认是开启的。

 

To set the innodb_file_per_table option at startup, start the server with the --innodb_file_per_table command-line option, or add this line to the [mysqld] section of my.cnf:

要在启动的时候设定innodb_file_per_table参数，可以在启动的命令行里使用--innodb_file_per_table ，或者在my.cnf的 [mysqld]里面添加下面的内容：

 

[mysqld]

innodb_file_per_table=1

 

You can also set innodb_file_per_table dynamically, while the server is running:

你还可以在实例运行的时候动态设定innodb_file_per_table：

 

SET GLOBAL innodb_file_per_table=1;

 

With innodb_file_per_table enabled, you can store InnoDB tables in a tbl_name.ibd file. Unlike the MyISAM storage engine, with its separate tbl_name.MYD and tbl_name.MYI files for indexes and data, InnoDB stores the data and the indexes together in a single .ibd file. The tbl_name.frm file is still created as usual.

在innodb_file_per_table开启的时候，你可以把InnoDB表存储到一个tbl_name.ibd文件里。不像MyISAM存储引擎，分别在tbl_name.MYD and tbl_name.MYI文件里存储索引和数据，InnoDB会把数据和索引都存储在一个.ibd文件里。tbl_name.frm通常情况下也还是会被创建的。

 

If you disable innodb_file_per_table in your startup options and restart the server, or disable it with the SET GLOBAL command, InnoDB creates new tables inside the system tablespace.

如果你在启动和重启的时候关闭了innodb_file_per_table，又或者使用SET GLOBAL动态关闭了它，InnoDB会把新的表创建到系统表空间里。

 

You can always read and write any InnoDB tables, regardless of the file-per-table setting.

你仍然可以读写任何的InnoDB表，而不用去管file-per-table的设定。

 

To move a table from the system tablespace to its own tablespace, change the innodb_file_per_table setting and rebuild the table:

要把一个表从系统表空间移动到它自己的表空间里，可以修改innodb_file_per_table的设定并重建表：

 

SET GLOBAL innodb_file_per_table=1;

ALTER TABLE table_name ENGINE=InnoDB;

 

Note

 

InnoDB always needs the system tablespace because it puts its internal data dictionary and undo logs there. The .ibd files are not sufficient for InnoDB to operate.

InnoDB仍然还是需要系统表空间的，因为里面存放了内部的数据字典以及undo log。单纯的.ibd文件对于InnoDB的操作还是不够的。

 

When a table is moved out of the system tablespace into its own .ibd file, the data files that make up the system tablespace remain the same size. The space formerly occupied by the table can be reused for new InnoDB data, but is not reclaimed for use by the operating system. When moving large InnoDB tables out of the system tablespace, where disk space is limited, you may prefer to enable innodb_file_per_table and recreate the entire instance using the mysqldump command.

当一个表从系统表空间移动到它自己的表空间的时候，系统表空间的大小不会减小。这个表之前占用的空间可以给新的InnoDB数据使用，但不会被操作系统回收使用。当要把很多的表从系统表空间里移除的时候要注意磁盘空间的限制，你最好是开启 innodb_file_per_table在使用mysqldump重建整个实例。

 

14.5.5 Creating a File-Per-Table Tablespace Outside the Data Directory

 

To create a new InnoDB file-per-table tablespace in a specific location outside the MySQL data directory, use the DATA DIRECTORY = absolute_path_to_directory clause of the CREATE TABLE statement.

要在MySQL数据目录之外的地方创建一个新的InnoDB file-per-table表空间，可以在CREATE TABLE 的时候使用DATA DIRECTORY = absolute_path_to_directory子句。

 

Plan the location in advance, because you cannot use the DATA DIRECTORY clause with the ALTER TABLE statement. The directory you specify could be on another storage device with particular performance or capacity characteristics, such as a fast SSD or a high-capacity HDD.

要预先规划数据存放的位置，因为你不能在ALTER TABLE的时候使用DATA DIRECTORY子句。你指定的目录可以是 另一个有特别性能优势或者存储能力的磁盘，例如更快的SSD或者更可靠的HDD。

 

Within the destination directory, MySQL creates a subdirectory corresponding to the database name, and within that a .ibd file for the new table. In the database directory beneath the MySQL DATADIR directory, MySQL creates a table_name.isl file containing the path name for the table. The .isl file is treated by MySQL like a symbolic link. (Using actual symbolic links has never been supported for InnoDB tables.)

在目标目录里，MySQL会创建一个和数据库名相关的子目录，并在里面存放新建表的ibd文件。MySQL DATADIR目录下面的数据库目录里，MySQL会创建一个包含表路径名的 table_name.isl文件。这个.isl文件对MySQL就如同一个symbolic link一样。（InnoDB表从未支持过symbolic links。）

 

The following example demonstrates creating a file-per-table tablespace outside the MySQL data directory. It shows the .ibd created in the specified directory, and the .isl created in the database directory beneath the MySQL data directory.

下面的例子示范了在MySQL数据目录之外创建一个file-per-table表空间。这里显示了.ibd指定的创建目录，以及MySQL数据目录下的数据库目录里面创建的.isl。

 

mysql> USE test;

Database changed

 

mysql> SHOW VARIABLES LIKE 'innodb_file_per_table';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| innodb_file_per_table | ON |

+-----------------------+-------+

1 row in set (0.00 sec)

 

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '/alternative/directory';

Query OK, 0 rows affected (0.03 sec)

 

# MySQL creates a .ibd file for the new table in a subdirectory that corresponding

# to the database name

 

db_user@ubuntu:~/alternative/directory/test$ ls

t1.ibd

 

# MySQL creates a .isl file containing the path name for the table in a directory

# beneath the MySQL data directory

 

db_user@ubuntu:~/mysql/data/test$ ls

db.opt t1.frm t1.isl

 

Usage Notes:

 

  • MySQL initially holds the .ibd file open, preventing you from dismounting the device, but might eventually close the table if the server is busy. Be careful not to accidentally dismount an external device while MySQL is running, or to start MySQL while the device is disconnected. Attempting to access a table when the associated .ibd file is missing causes a serious error that requires a server restart.
  • MySQL最初为保持.ibd文件的打开，已防止你卸载磁盘，但实例繁忙的时候还是会关闭表的。在MySQL正在运行的时候要当心不要卸载外部磁盘，有或者是在未连接磁盘的情况下启动MySQL。尝试去访问那些.idb文件丢失的表会造成严重的错误并导致实例重启。

 

A server restart might fail if the .ibd file is still not at the expected path. In this case, manually remove the table_name.isl file in the database directory, and after restarting perform a DROP TABLE to delete the .frm file and remove the information about the table from the data dictionary.

如果.idb文件不在其应该在的路径的话，实例重启可能会失败。在这种情况下，在数据库目录里面手动删除table_name.isl文件，在重启之后执行DROP TABLE删除.frm文件并重数据目录里面删除这个表的相关信息。

 

  • Do not put MySQL tables on an NFS-mounted volume. NFS uses a message-passing protocol to write to files, which could cause data inconsistency if network messages are lost or received out of order.
  • 不要把MySQL表放在NFS的卷上。NFS使用的是一种消息传递的协议写文件，如果网络消息丢失或者接受的消息不对可能会导致数据不一致。

 

  • If you use an LVM snapshot, file copy, or other file-based mechanism to back up the .ibd file, always use the FLUSH TABLES ... FOR EXPORT statement first to make sure all changes that were buffered in memory are flushed to disk before the backup occurs.
  • 如果你使用了LVM的快照，文件拷贝，或者其他基于文件的原理来备份.ibd文件，记住在这之前要使用FLUSH TABLES ... FOR EXPORT语句首先确保所有内存里缓存的修改都已经刷新的到磁盘上。

 

  • The DATA DIRECTORY clause is a supported alternative to using symbolic links, which has always been problematic and was never supported for individual InnoDB tables.
  • 相对于使用symbolic links，DATA DIRECTORY子句InnoDB是支持的。而symbolic links则一直是个问题，而且对于单个的InnoDB表是从未支持过的。

 

14.5.6 Copying File-Per-Table Tablespaces to Another Server

 

This section describes how to copy file-per-table tablespaces from one database server to another, otherwise known as the Transportable Tablespaces feature.

这一章节讲述了表空间传输(Transportable Tablespaces)的特性，把一个file-per-table表空间从一个数据库实例复制到到另一个。

 

For information about other InnoDB table copying methods, see Section 14.6.2, "Moving or Copying InnoDB Tables to Another Machine".

InnoDB表复制的的其他方法可见Section 14.6.2, "Moving or Copying InnoDB Tables to Another Machine"。

 

There are many reasons why you might copy an InnoDB file-per-table tablespace to a different database server:

下面是讲述的是为什么要把一个InnoDB file-per-table表空间复制到另一个实例上：

 

  • To run reports without putting extra load on a production server.
  • 在生产实例上在不进行额外的数据加载的情况下运行报表。

 

  • To set up identical data for a table on a new slave server.
  • 在一个新的slave实例上建立一份相同的表数据。

 

  • To restore a backed-up version of a table after a problem or mistake.
  • 出现问题或者人为错误之后恢复表的一个备份版本。

 

  • As a faster way of moving data around than importing the results of a mysqldump command. The data is available immediately, rather than having to be re-inserted and the indexes rebuilt.
  • 相比mysqldump的导入这是一个更为快速的数据迁移方式。因为数据是立即可见的，不需要再次insert和建立索引。

 

  • To move a file-per-table tablespace to a server with storage medium that better suits system requirements. For example, you may want to have busy tables on an SSD device, or large tables on a high-capacity HDD device.
  • 通过存储媒介把转移file-per-table表空间更适合系统要求。例如，你想把繁忙的表放到SSD磁盘上，或者把大的表发到大容量的HDD磁盘上。

 

Limitations and Usage Notes

 

  • The tablespace copy procedure is only possible when innodb_file_per_table is set to ON, which is the default setting as of MySQL 5.6.6. Tables residing in the shared system tablespace cannot be quiesced.
  • 只有innodb_file_per_table被设置成ON的时候表空间复制才是可能的。从MySQL5.6.6开始默认都是打开的。在共享的系统表空间里的表是不支持的(cannot be quiesced)。

 

  • When a table is quiesced, only read-only transactions are allowed on the affected table.
  • 当表是静默的(quiesced)，那么这个表值允许只读事务。

 

  • When importing a tablespace, the page size must match the page size of the importing instance.
  • 当导入一个表空间的时候，页的大小必须要和导入的实例匹配。

 

  • DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported. If you run ALTER TABLE ... DISCARD TABLESPACE on a partitioned table, the following error is returned: ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option.
  • 分区表不支持DISCARD TABLESPACE意味着表空间传输也不支持。如果你在一个分区表上运行ALTER TABLE ... DISCARD TABLESPACE，会报下面的错误：ERROR 1031 (HY000): Table storage engine for 'part' doesn't have this option。

 

  • DISCARD TABLESPACE is not supported for tablespaces with a parent-child (primary key-foreign key) relationship when foreign_key_checks is set to 1. Before discarding a tablespace for parent-child tables, set foreign_key_checks=0.
  • 当oreign_key_checks被设置成1的时候有父子(primary key-foreign key)关系的表是不支持DISCARD TABLESPACE的。在discard一个有父子关系的表之前，要设置foreign_key_checks=0。

 

  • ALTER TABLE ... IMPORT TABLESPACE does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time.
  • ALTER TABLE ... IMPORT TABLESPACE不能在导入的数据上实施外键约束。如果表上有外键，那么就必须在同一个时间点一次导出所有的表。

 

  • ALTER TABLE ... IMPORT TABLESPACE does not require a .cfg metadata file to import a tablespace. However, metadata checks are not performed when importing without a .cfg file, and a warning similar to the following will be issued:
  • ALTER TABLE ... IMPORT TABLESPACE不要求把.cfg元数据文件导入到表空间。然而，不导入.cfg文件元数据检查也不会执行，也就会得到下面的警告：

 

Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\

test\t.cfg', will attempt to import without schema verification

1 row in set (0.00 sec)

 

The ability to import without a .cfg file may be more convenient when no schema mismatches are expected. Additionally, the ability to import without a .cfg file could be useful in crash recovery scenarios in which metadata cannot be collected from an .ibd file.

当预期没有schema不匹配的时候，不导入.cfg文件可能会更方便。另外，在崩溃恢复的场景中不导入.cfg会更有用处，因为.idb文件是不会收集元数据的。

 

  • In MySQL 5.6 or later, importing a tablespace file from another server works if both servers have GA (General Availability) status and their versions are within the same series. Otherwise, the file must have been created on the server into which it is imported.
  • 在MySQL5.6及更高的版本里，如果实例都是GA(General Availability)状态，并且版本相同，就可以从另一个实例里面导入表空间文件。否则，被导入的实例则就必须创建文件。

 

  • In replication scenarios, innodb_file_per_table must be set to ON on both the master and slave.
  • 在复制场景中，主从都必须把innodb_file_per_table设置成ON。

 

  • On Windows, InnoDB stores database, tablespace, and table names internally in lowercase. To avoid import problems on case-sensitive operating systems such as Linux and UNIX, create all databases, tablespaces, and tables using lowercase names. A convenient way to accomplish this is to add the following line to the [mysqld] section of your my.cnf or my.ini file before creating databases, tablespaces, or tables:
  • 在Windows平台上，InnoDB的数据库名，表空间名，和表名都是以小写的形式存储的。为了避免在Linux和Unix这种区分大小写平台上的导入问题，最好所有创建的数据库名，表空间名，和表名都用小写。还有一种方便的方式在创建数据库，表空间和表之前可以达到这样的效果，就是是my.cnf或者my.ini配置文件的[mysqld]里加入下面的内容：

 

[mysqld]

lower_case_table_names=1

 

Example: Copying a File-Per-Table Tablespace From One Server To Another

 

This procedure demonstrates how to copy a table stored in a file-per-table tablespace from a running MySQL server instance to another running instance. The same procedure with minor adjustments can be used to perform a full table restore on the same instance.

下面的过程演示了如何从一个正在运行的实例里把一个存储在file-per-table表空间里的表复制到另一个正在运行的实例上。对于在同一个实例上进行全表的恢复只需要对这个过程进行微小的调整。

 

1.On the source server, create a table if one does not already exist:

在源端创建以一个不同名的新表：

 

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

 

2.On the destination server, create a table if one does not exist:

在远端也创建同样的一个表：

 

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

 

3.On the destination server, discard the existing tablespace. (Before a tablespace can be imported, InnoDB must discard the tablespace that is attached to the receiving table.)

在远端，discard现有的表空间。（在表空间导入之前，InnoDB必须discard表空间再附上收到的表。）

 

mysql> ALTER TABLE t DISCARD TABLESPACE;

 

4.On the source server, run FLUSH TABLES ... FOR EXPORT to quiesce the table and create the .cfg metadata file:

在源端，运行FLUSH TABLES ... FOR EXPORT静默(quiesce )表并创建.cfg元数据文件：

 

mysql> use test;

mysql> FLUSH TABLES t FOR EXPORT;

 

The metadata (.cfg) file is created in the InnoDB data directory.

元数据(.cfg)文件创建在InnoDB的数据目录里。

 

Note

 

FLUSH TABLES ... FOR EXPORT is available as of MySQL 5.6.6. The statement ensures that changes to the named tables have been flushed to disk so that binary table copies can be made while the server is running. When FLUSH TABLES ... FOR EXPORT is run, InnoDB produces a .cfg file in the same database directory as the table. The .cfg file contains metadata used for schema verification when importing the tablespace file.

FLUSH TABLES ... FOR EXPORT是从MySQL5.6.6才开始可用的。这个语句会确保表的更新已经被刷新到了磁盘上，这样二进制的表文件才能在实例正在运行的情况下进行复制。当运行FLUSH TABLES ... FOR EXPORT的时候，InnoDB会在表相同的数据库目录下面产生一个.cfg文件。这个.cfg文件包含了在导入表空间文件时用于schema验证的元数据。

 

5.Copy the .ibd file and .cfg metadata file from the source server to the destination server. For example:

从源端复制.ibd文件以及.cfg元数据文件到远端：

 

shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test

 

Note

 

The .ibd file and .cfg file must be copied before releasing the shared locks, as described in the next step.

.idb文件和.cfg文件必须在释放共享锁之前复制，正如下一步所描述的。

 

6.On the source server, use UNLOCK TABLES to release the locks acquired by FLUSH TABLES ... FOR EXPORT:

在源端，使用UNLOCK TABLES释放所有FLUSH TABLES ... FOR EXPORT获取的锁：

 

mysql> use test;

mysql> UNLOCK TABLES;

 

7.On the destination server, import the tablespace:

在远端，导入表空间：

 

mysql> use test;

mysql> ALTER TABLE t IMPORT TABLESPACE;

 

Note

 

The ALTER TABLE ... IMPORT TABLESPACE feature does not enforce foreign key constraints on imported data. If there are foreign key constraints between tables, all tables should be exported at the same (logical) point in time. In this case you would stop updating the tables, commit all transactions, acquire shared locks on the tables, and then perform the export operation.

ALTER TABLE ... IMPORT TABLESPACE特性不会在导入的数据上实施外键约束。如果表上没有外键约束，所有的表必须在同一个时间点上一次都导出。这种情况下你要停止对表的更新，提交所有的事务，在表上施加共享锁，之后才能执行导出操作。

 

Transportable Tablespace Internals

 

The following information describes internals and error log messaging for the transportable tablespaces copy procedure.

下面的内容描述了表空间传输过程中的内部原理和错误日志信息。

 

When ALTER TABLE ... DISCARD TABLESPACE is run on the destination instance:

当在远端实例运行ALTER TABLE ... DISCARD TABLESPACE的时候：

 

  • The table is locked in X mode.
  • 表会加X锁。

 

  • The tablespace is detached from the table.
  • 表空间会从表上分离。

 

When FLUSH TABLES ... FOR EXPORT is run on the source instance:

当在源端运行FLUSH TABLES ... FOR EXPORT的时候：

 

  • The table being flushed for export is locked in shared mode.
  • 要导出的表会被刷新的磁盘上，并加共享锁。

 

  • The purge coordinator thread is stopped.
  • purge协调线程会停止。

 

  • Dirty pages are synchronized to disk.
  • 脏数据页会同步上磁盘上。

 

  • Table metadata is written to the binary .cfg file.
  • 表的元数据会写入到二进制的.cfg文件里。

 

Expected error log messages for this operation:

这个操作在错误日志里预期的信息如下：

 

2013-07-18 14:47:31 34471 [Note] InnoDB: Sync to disk of '"test"."t"' started.

2013-07-18 14:47:31 34471 [Note] InnoDB: Stopping purge

2013-07-18 14:47:31 34471 [Note] InnoDB: Writing table metadata to './test/t.cfg'

2013-07-18 14:47:31 34471 [Note] InnoDB: Table '"test"."t"' flushed to disk

 

When UNLOCK TABLES is run on the source instance:

当在源端运行UNLOCK TABLES的时候：

 

  • The binary .cfg file is deleted.
  • 二进制的.cfg文件被删除。

 

  • The shared lock on the table or tables being imported is released and the purge coordinator thread is restarted.
  • 表上的共享锁被释放，重启purge协调线程。

 

Expected error log messages for this operation:

这个操作在错误日志里预期的信息如下：

 

2013-07-18 15:01:40 34471 [Note] InnoDB: Deleting the meta-data file './test/t.cfg'

2013-07-18 15:01:40 34471 [Note] InnoDB: Resuming purge

 

When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:

当在远端实例运行ALTER TABLE ... IMPORT TABLESPACE的时候，导入算法会为每个导入的表空间执行下面的操作：

 

  • Each tablespace page is checked for corruption.
  • 每个表空间的数据页都会进行损坏检查。

 

  • The space ID and log sequence numbers (LSNs) on each page are updated
  • 每个数据页上的space ID and log sequence numbers (LSNs)都会被更新。

 

  • Flags are validated and LSN updated for the header page.
  • 数据页头的确认标识和LSN会被更新。

 

  • Btree pages are updated.
  • 更新Btree数据页。

 

  • The page state is set to dirty so that it will be written to disk.
  • 数据页的状态被设置成dirty以便可以被写入磁盘。

 

Expected error log messages for this operation:

这个操作在错误日志里预期的信息如下：

 

2013-07-18 15:15:01 34960 [Note] InnoDB: Importing tablespace for table 'test/t' that was exported from host 'ubuntu'

2013-07-18 15:15:01 34960 [Note] InnoDB: Phase I - Update all pages

2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk

2013-07-18 15:15:01 34960 [Note] InnoDB: Sync to disk - done!

2013-07-18 15:15:01 34960 [Note] InnoDB: Phase III - Flush changes to disk

2013-07-18 15:15:01 34960 [Note] InnoDB: Phase IV - Flush complete

 

Note

 

You may also receive a warning that a tablespace is discarded (if you discarded the tablespace for the destination table) and a message stating that statistics could not be calculated due to a missing .ibd file:

表空被分离的时候你也可能会收到一个警告（如果你是为远端的表进行表空间的分离）：由于遗失了.ibd文件无法计算统计信息：

 

2013-07-18 15:14:38 34960 [Warning] InnoDB: Table "test"."t" tablespace is set as discarded.

2013-07-18 15:14:38 7f34d9a37700 InnoDB: cannot calculate statistics for table "test"."t" because the .ibd file is missing. For help, please refer to

http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html

 

14.5.7 Storing InnoDB Undo Logs in Separate Tablespaces

 

As of MySQL 5.6.3, you can store InnoDB undo logs in one or more separate undo tablespaces outside of the system tablespace. This layout is different from the default configuration where the undo log is part of the system tablespace. The I/O patterns for the undo log make these tablespaces good candidates to move to SSD storage, while keeping the system tablespace on hard disk storage. Users cannot drop the separate tablespaces created to hold InnoDB undo logs, or the individual segments inside those tablespaces.

从MySQL5.6.3开始，你可以把InnoDB的undo log存储在一个或者多个分离在系统表空间之外的undo表空间里。这种布局不是默认的配置，默认的是undo log是被放置在系统表空间里的。undo log的I/O模式使得最好能够把这些undo表空间移动到SSD存储上，而把系统表空间放在普通的硬盘上。用户是无法删除用于存放InnoDB undo log的表空间的，又或者是这些表空间里的个别segment。

 

Because these files handle I/O operations formerly done inside the system tablespace, we broaden the definition of system tablespace to include these new files.

因为从前这些文件都是在系统表空间里处理I/O操作的，我们放宽的系统表空间定义也包括这些新的文件。

 

Undo logs are also referred to as rollback segments.

undo log仍然被称为回滚段。

 

This feature involves the following new or renamed configuration options:

新的特性涉及了下面新的或者重命令的配置参数：

 

  • innodb_undo_tablespaces

 

  • innodb_undo_directory

 

  • innodb_rollback_segments becomes innodb_undo_logs. The old name is still available for compatibility.
  • innodb_rollback_segments变成了innodb_undo_logs。因为兼容性问题老的名字仍然可用。

 

Because the InnoDB undo log feature involves setting two non-dynamic startup variables (innodb_undo_tablespaces and innodb_undo_directory), this feature can only be enabled when initializing a MySQL instance.

因为InnoDB undo log的特性涉及了设置两个非动态的启动变量(innodb_undo_tablespaces and innodb_undo_directory)，所以只有在初始化MySQL实例的时候才能启动新的特性。

 

Usage Notes

 

To use this feature, follow these steps:

使用新的特性要跟随下面的步骤：

 

1.Decide on a path to hold the undo logs. You will specify that path as the argument to the innodb_undo_directory option in your MySQL configuration file or startup script. For embedded MySQL installations, an absolute path must be specified.

决定保存undo log的路径。指定这个路径作为MySQL配置文件或者启动脚本里innodb_undo_directory参数的值。对于嵌入式的MySQL，则指定的必须是绝对路径。

 

2.Decide on a starting value for the innodb_undo_logs option. You can start with a relatively low value and increase it over time to examine the effect on performance.

决定innodb_undo_logs参数的起始值。开始的时候你可以设置一个相对较小的值，随着时间测试对性能的影响来逐步增加。

 

3.Decide on a non-zero value for the innodb_undo_tablespaces option. The multiple undo logs specified by the innodb_undo_logs value are divided between this number of separate tablespaces (represented by .ibd files). This value is fixed for the life of the MySQL instance, so if you are uncertain about the optimal value, estimate on the high side.

为innodb_undo_tablespaces参数决定一个非0的值。innodb_undo_logs的值指定了分割多个undo log表空间（代表.ibd文件）。这个值对MySQL实例一开始就是固定好的，所以如果你不确认这个参数的值，那就预估一个相对比较大。

 

4.Create a new MySQL instance, using the values you chose in the configuration file or in your MySQL startup script. Use a realistic workload with data volume similar to your production servers. Alternatively, use the transportable tablespaces feature to copy existing database tables to your newly configured MySQL instance. See Section 14.5.6, "Copying File-Per-Table Tablespaces to Another Server" for more information.

你可以在配置文件或者MySQL的启动脚本里面使用你选择的值来创建一个新的MySQL实例。使用和生产环境相似的数据负载量，或者从现有的数据库里使用表空间传输特性把表复制到新配置的MySQL实例里面，详见Section 14.5.6, "Copying File-Per-Table Tablespaces to Another Server"。

 

5.Benchmark the performance of I/O intensive workloads.

进行密集型I/O负载的性能基准测试。

 

  • 6.Periodically increase the value of innodb_undo_logs and rerun performance tests. Find the value where you stop experiencing gains in I/O performance.
  • 定期增加innodb_undo_logs的值并再次进行性能测试，直到找到最适合你的I/O性能的值。

 

7.Deploy a new production instance using the ideal settings for these options. Set it up as a slave server in a replication configuration, or transfer data from an earlier production instance.

对这些参数使用理想的设定来部署一个新的生产实例。在主从配置中把它设置成slave，或者从之前的生产实例上把数据传输过来。

 

Performance and Scalability Considerations

 

Keeping the undo logs in separate files allows the MySQL team to implement I/O and memory optimizations related to this transactional data. For example, because the undo data is written to disk and then rarely used (only in case of crash recovery), it does not need to be kept in the filesystem memory cache, in turn allowing a higher percentage of system memory to be devoted to the InnoDB buffer pool.

把undo log从系统表空间分离出来能够使得MySQL改进和事务数据相关的I/O和内存的优化。例如，因为undo数据写入到磁盘后就很少使用了（除非是崩溃恢复的情况），这也就不需要保持在文件系统的内存cache里，反过来运行也就允许更多的系统内存可用于InnoDB的buffer pool。

 

The typical SSD best practice of keeping the InnoDB system tablespace on a hard drive and moving the per-table tablespaces to SSD, is assisted by moving the undo information into separate tablespace files.

最佳的实践是把InnoDB的系统表空间放在普通的硬盘上，而把undo的表空间放到SSD的磁盘上。

 

Internals

 

The physical tablespace files are named undoN, where N is the space ID, including leading zeros.

物理的表空间文件命名为undoN，N表示的是space ID，including leading zeros。

 

MySQL instances containing separate undo tablespaces cannot be downgraded to earlier releases such as MySQL 5.5 or 5.1.

包含多个undo表空间的实例是不能降级到早先的版本的，例如MySQL5.5或者5.1。

转载于:https://www.cnblogs.com/paololiu/p/5623222.html

weixin_33975951
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql参考手册5.6_GitHub - souvc/mysql-reference-manual: MySQL 5.6参考手册（https://dev.mysql.com/doc/refman/5...
weixin_42500156的博客
01-20 289
Chapter 1 基本信息1.1 关于本手册1.2 拼写和语法约定1.3 概述的MySQL数据库管理系统1.4 什么是新的MySQL 5.61.5 MySQL的信息来源1.6 如何报告错误或问题1.7 MySQL标准1.8 学分Chapter 2 MySQL的安装和升级2.1一般安装指导2.2安装在Unix / Linux使用通用二进制代码MySQL在微软Windows 2.3安装MySQL2....
MySQL 5.6 Reference Manual 官方参考手册（HTML版）
08-17
Abstract This is the MySQL™ Reference Manual. It documents MySQL 5.6 through 5.6.42, as well as NDB Cluster releases based on versions 7.3 and 7.4 of NDB through 5.6.41-ndb-7.3.23 and 5.6.41-ndb-7.4.22, respectively. It may include documentation of features of MySQL versions that have not yet been released. For information about which versions have been released, see the MySQL 5.6 Release Notes. MySQL 5.6 features. This manual describes features that are not included in every edition of MySQL 5.6; such features may not be included in the edition of MySQL 5.6 licensed to you. If you have any questions about the features included in your edition of MySQL 5.6, refer to your MySQL 5.6 license agreement or contact your Oracle sales representative.
MySQL 5.6 Reference Manual
11-30
Table of Contents Preface, Notes, Licenses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi 1. Licenses for Third-Party Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxi 1.1. FindGTest.cmake License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxiii 1.2. LPeg Library License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxiii 1.3. LuaFileSystem Library License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxiv 1.4. libedit License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxiv 1.5. libevent License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxv 1.6. regex Library License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxxv 1.7. zlib License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvi 1.8. ASM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvi 1.9. Apache Commons FileUpload . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii 1.10. Apache Commons Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii 1.11. Apache License Version 2.0, January 2004 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxvii 1.12. Apache Tomcat . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxxix 1.13. Apache log4j . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xl 1.14. Apache log4j Version 1.2.9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xl 1.15. Boost Library License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli 1.16. Fred Fish's Dbug Library License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xli 1.17. JDOM Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xlii 1.18. Netlib License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xlii 1.19. OpenSSL v0.9.8o and later . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xlii 1.20. Richard A. O'Keefe String Library License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliii 1.21. SHA-1 in C License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliii 1.22. Spring Framework . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliv 1.23. StringTemplate Template Engine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliv 1.24. cURL (libcurl) License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xliv 1.25. jQuery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xlv 1.26. jQuery UI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xlv 1.27. md5 (Message-Digest Algorithm 5) License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xlvi 1.28. nt_servc (Windows NT Service class library) License . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xlvi 1. General Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1. About This Manual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2. Typographical and Syntax Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 1.3. Overview of the MySQL Database Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.3.1. What is MySQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.3.2. History of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.3.3. The Main Features of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.4. MySQL Development History . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 1.5. What Is New in MySQL 5.6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.6. MySQL Information Sources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.6.1. MySQL Mailing Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 1.6.2. MySQL Community Support at the MySQL Forums . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .10 1.6.3. MySQL Community Support on Internet Relay Chat (IRC) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 1.6.4. MySQL Enterprise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 1.7. How to Report Bugs or Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11 1.8. MySQL Standards Compliance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .14 1.8.1. What Standards MySQL Follows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15 1.8.2. Selecting SQL Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15 1.8.3. Running MySQL in ANSI Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15 1.8.4. MySQL Extensions to Standard SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .15 1.8.5. MySQL Differences from Standard SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .18 1.8.6. How MySQL Deals with Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .22 1.9. Credits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24 1.9.1. Contributors to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25 1.9.2. Documenters and translators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28 1.9.3. Packages that support MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30 1.9.4. Tools that were used to create MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30 1.9.5. Supporters of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31 2. Installing and Upgrading MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . v2.1. General Installation Guidance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32 2.1.1. Operating Systems Supported by MySQL Community Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33 2.1.2. Choosing Which MySQL Distribution to Install . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34 2.1.3. How to Get MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37 2.1.4. Verifying Package Integrity Using MD5 Checksums or GnuPG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37 2.1.5. Installation Layouts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40 2.1.6. Compiler-Specific Build Characteristics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41 2.2. Standard MySQL Installation Using a Binary Distribution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41 2.3. Installing MySQL on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .42 2.3.1. Choosing An Installation Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43 2.3.2. Installing MySQL with the Automated Installer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43 2.3.3. Using the MySQL Installation Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43 2.3.4. MySQL Server Instance Configuration Wizard . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46 2.3.5. Installing MySQL from a Noinstall Zip Archive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .58 2.3.6. Extracting the Install Archive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59 2.3.7. Creating an Option File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59 2.3.8. Selecting a MySQL Server Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60 2.3.9. Starting the Server for the First Time . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60 2.3.10. Starting MySQL from the Windows Command Line . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .61 2.3.11. Starting MySQL as a Windows Service . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .62 2.3.12. Testing The MySQL Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64 2.3.13. Troubleshooting a MySQL Installation Under Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64 2.3.14. Upgrading MySQL on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65 2.4. Installing MySQL from RPM Packages on Linux . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66 2.5. Installing MySQL on Mac OS X . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69 2.6. Installing MySQL on Solaris . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71 2.7. Installing MySQL on i5/OS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72 2.8. Installing MySQL on NetWare . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .74 2.9. Installing MySQL from tar.gz Packages on Other Unix-Like Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .76 2.10. MySQL Installation Using a Source Distribution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .78 2.10.1. Source Installation Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .79 2.10.2. Typical configure Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .82 2.10.3. Installing from the Development Source Tree . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .88 2.10.4. Dealing with Problems Compiling MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90 2.10.5. MIT-pthreads Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .92 2.10.6. Installing MySQL from Source on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .93 2.10.7. Compiling MySQL Clients on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97 2.11. Post-Installation Setup and Testing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97 2.11.1. Windows Post-Installation Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .97 2.11.2. Unix Post-Installation Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .99 2.11.3. Securing the Initial MySQL Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 2.12. Upgrading or Downgrading MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 2.12.1. Upgrading MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 2.12.2. Downgrading MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 2.12.3. Checking Whether Tables or Indexes Must Be Rebuilt . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 2.12.4. Rebuilding or Repairing Tables or Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 2.12.5. Copying MySQL Databases to Another Machine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 2.13. Operating System-Specific Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 2.13.1. Linux Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 2.13.2. Mac OS X Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 2.13.3. Solaris Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 2.13.4. BSD Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 2.13.5. Other Unix Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 2.14. Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 2.15. Perl Installation Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 2.15.1. Installing Perl on Unix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 2.15.2. Installing ActiveState Perl on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 2.15.3. Problems Using the Perl DBI/DBD Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144 3. Tutorial . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 3.1. Connecting to and Disconnecting from the Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 3.2. Entering Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 3.3. Creating and Using a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 3.3.1. Creating and Selecting a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 3.3.2. Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 3.3.3. Loading Data into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 MySQL 5.6 Reference Manual vi3.3.4. Retrieving Information from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 3.4. Getting Information About Databases and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 3.5. Using mysql in Batch Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 3.6. Examples of Common Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 3.6.1. The Maximum Value for a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 3.6.2. The Row Holding the Maximum of a Certain Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 3.6.3. Maximum of Column per Group . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 3.6.4. The Rows Holding the Group-wise Maximum of a Certain Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 3.6.5. Using User-Defined Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 3.6.6. Using Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 3.6.7. Searching on Two Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 3.6.8. Calculating Visits Per Day . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 3.6.9. Using AUTO_INCREMENT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 3.7. Queries from the Twin Project . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 3.7.1. Find All Nondistributed Twins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 3.7.2. Show a Table of Twin Pair Status . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 3.8. Using MySQL with Apache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 4. MySQL Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 4.1. Overview of MySQL Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176 4.2. Using MySQL Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 4.2.1. Invoking MySQL Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 4.2.2. Connecting to the MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 4.2.3. Specifying Program Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 4.2.4. Setting Environment Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 4.3. MySQL Server and Server-Startup Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 4.3.1. mysqld— The MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 4.3.2. mysqld_safe— MySQL Server Startup Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 4.3.3. mysql.server— MySQL Server Startup Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 4.3.4. mysqld_multi— Manage Multiple MySQL Servers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 4.4. MySQL Installation-Related Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 4.4.1. comp_err— Compile MySQL Error Message File . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 4.4.2. make_win_bin_dist— Package MySQL Distribution as ZIP Archive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 4.4.3. mysqlbug— Generate Bug Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 4.4.4. mysql_install_db— Initialize MySQL Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206 4.4.5. mysql_secure_installation— Improve MySQL Installation Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 4.4.6. mysql_tzinfo_to_sql— Load the Time Zone Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 4.4.7. mysql_upgrade— Check Tables for MySQL Upgrade . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208 4.5. MySQL Client Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 4.5.1. mysql— The MySQL Command-Line Tool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 4.5.2. mysqladmin— Client for Administering a MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 226 4.5.3. mysqlcheck— A Table Maintenance Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232 4.5.4. mysqldump — A Database Backup Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237 4.5.5. mysqlimport— A Data Import Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251 4.5.6. mysqlshow — Display Database, Table, and Column Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256 4.5.7. mysqlslap — Load Emulation Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 4.6. MySQL Administrative and Utility Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265 4.6.1. innochecksum— Offline InnoDB File Checksum Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265 4.6.2. myisam_ftdump— Display Full-Text Index information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 4.6.3. myisamchk — MyISAM Table-Maintenance Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 4.6.4. myisamlog — Display MyISAM Log File Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 4.6.5. myisampack— Generate Compressed, Read-Only MyISAM Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 4.6.6. mysqlaccess— Client for Checking Access Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 287 4.6.7. mysqlbinlog— Utility for Processing Binary Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 4.6.8. mysqldumpslow— Summarize Slow Query Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 4.6.9. mysqlhotcopy— A Database Backup Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306 4.6.10. mysql_convert_table_format— Convert Tables to Use a Given Storage Engine . . . . . . . . . . . . . . . . 309 4.6.11. mysql_find_rows— Extract SQL Statements from Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310 4.6.12. mysql_fix_extensions— Normalize Table File Name Extensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 4.6.13. mysql_setpermission— Interactively Set Permissions in Grant Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 4.6.14. mysql_waitpid— Kill Process and Wait for Its Termination . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 4.6.15. mysql_zap — Kill Processes That Match a Pattern . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 4.7. MySQL Program Development Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 4.7.1. msql2mysql— Convert mSQL Programs for Use with MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 4.7.2. mysql_config— Get Compile Options for Compiling Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 4.7.3. my_print_defaults— Display Options from Option Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 MySQL 5.6 Reference Manual vii4.7.4. resolve_stack_dump— Resolve Numeric Stack Trace Dump to Symbols . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 4.8. Miscellaneous Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 4.8.1. perror— Explain Error Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 4.8.2. replace — A String-Replacement Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 4.8.3. resolveip — Resolve Host name to IP Address or Vice Versa . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 317 5. MySQL Server Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318 5.1. The MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318 5.1.1. Server Option and Variable Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318 5.1.2. Server Command Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 5.1.3. Server Options for Loading Plugins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 5.1.4. Server System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370 5.1.5. Using System Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451 5.1.6. Server Status Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 5.1.7. Server SQL Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 480 5.1.8. Server-Side Help . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486 5.1.9. Server Response to Signals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 486 5.1.10. The Shutdown Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 5.2. MySQL Server Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 5.2.1. Selecting General Query and Slow Query Log Output Destinations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 488 5.2.2. The Error Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 5.2.3. The General Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 490 5.2.4. The Binary Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 5.2.5. The Slow Query Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501 5.2.6. Server Log Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501 5.3. General Security Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502 5.3.1. General Security Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 502 5.3.2. Password Security in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505 5.3.3. Making MySQL Secure Against Attackers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510 5.3.4. Security-Related mysqld Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511 5.3.5. Security Issues with LOAD DATA LOCAL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513 5.3.6. How to Run MySQL as a Normal User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514 5.4. The MySQL Access Privilege System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514 5.4.1. Privileges Provided by MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515 5.4.2. Privilege System Grant Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519 5.4.3. Specifying Account Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 523 5.4.4. Access Control, Stage 1: Connection Verification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524 5.4.5. Access Control, Stage 2: Request Verification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526 5.4.6. When Privilege Changes Take Effect . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 5.4.7. Causes of Access-Denied Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528 5.5. MySQL User Account Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532 5.5.1. User Names and Passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533 5.5.2. Adding User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 5.5.3. Removing User Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536 5.5.4. Setting Account Resource Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536 5.5.5. Assigning Account Passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 538 5.5.6. Using SSL for Secure Connections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 5.5.7. Connecting to MySQL Remotely from Windows with SSH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547 5.5.8. Auditing MySQL Account Activity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548 5.6. Running Multiple MySQL Servers on the Same Machine . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 549 5.6.1. Running Multiple Servers on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550 5.6.2. Running Multiple Servers on Unix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553 5.6.3. Using Client Programs in a Multiple-Server Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554 5.7. Tracing mysqld Using DTrace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 554 5.7.1. mysqld DTrace Probe Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555 6. Backup and Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571 6.1. Backup and Recovery Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571 6.2. Database Backup Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 574 6.3. Example Backup and Recovery Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575 6.3.1. Backup Policy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576 6.3.2. Using Backups for Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577 6.3.3. Backup Strategy Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578 6.4. Using mysqldump for Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578 6.4.1. Dumping Data in SQL Format with mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579 6.4.2. Reloading SQL-Format Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579 6.4.3. Dumping Data in Delimited-Text Format with mysqldump . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 580 MySQL 5.6 Reference Manual viii6.4.4. Reloading Delimited-Text Format Backups . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581 6.4.5. mysqldump Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 581 6.5. Point-in-Time (Incremental) Recovery Using the Binary Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 583 6.5.1. Point-in-Time Recovery Using Event Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585 6.5.2. Point-in-Time Recovery Using Event Positions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585 6.6. MyISAM Table Maintenance and Crash Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585 6.6.1. Using myisamchk for Crash Recovery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 586 6.6.2. How to Check MyISAM Tables for Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587 6.6.3. How to Repair MyISAM Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 587 6.6.4. MyISAM Table Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589 6.6.5. Setting Up a MyISAM Table Maintenance Schedule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589 7. Optimization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591 7.1. Optimization Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591 7.1.1. MySQL Design Limitations and Tradeoffs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592 7.1.2. Designing Applications for Portability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592 7.1.3. The MySQL Benchmark Suite . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593 7.1.4. Using Your Own Benchmarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594 7.2. Optimizing SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594 7.2.1. Optimizing SELECT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594 7.2.2. Optimizing DML Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 623 7.2.3. Optimizing Database Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626 7.2.4. Optimizing INFORMATION_SCHEMA Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626 7.2.5. Other Optimization Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631 7.3. Optimization and Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633 7.3.1. Column Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633 7.3.2. Multiple-Column Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 634 7.3.3. How MySQL Uses Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635 7.3.4. MyISAM Index Statistics Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 7.4. Understanding the Query Execution Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638 7.4.1. Optimizing Queries with EXPLAIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 638 7.4.2. EXPLAIN Output Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639 7.4.3. Estimating Query Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647 7.5. Controlling the Query Optimizer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647 7.5.1. Controlling Query Plan Evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 647 7.5.2. Controlling Switchable Optimizations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 648 7.6. Buffering and Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649 7.6.1. The InnoDB Buffer Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 649 7.6.2. The MyISAM Key Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651 7.6.3. The MySQL Query Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 655 7.7. Locking Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660 7.7.1. Internal Locking Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 660 7.7.2. Table Locking Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662 7.7.3. Concurrent Inserts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664 7.7.4. Metadata Locking Within Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664 7.7.5. External Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 664 7.8. Optimizing Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 665 7.8.1. Make Your Data as Small as Possible . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 665 7.8.2. How MySQL Opens and Closes Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 667 7.8.3. Disadvantages of Creating Many Tables in the Same Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668 7.8.4. How MySQL Uses Internal Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668 7.9. Optimizing the MySQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 668 7.9.1. How Compiling and Linking Affects the Speed of MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669 7.9.2. System Factors and Startup Parameter Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 669 7.9.3. Tuning Server Pa
MySQL 5.6 Reference Manual 官方参考手册
08-17
Abstract This is the MySQL™ Reference Manual. It documents MySQL 5.6 through 5.6.42, as well as NDB Cluster releases based on versions 7.3 and 7.4 of NDB through 5.6.41-ndb-7.3.23 and 5.6.41-ndb-7.4.22, respectively. It may include documentation of features of MySQL versions that have not yet been released. For information about which versions have been released, see the MySQL 5.6 Release Notes. MySQL 5.6 features. This manual describes features that are not included in every edition of MySQL 5.6; such features may not be included in the edition of MySQL 5.6 licensed to you. If you have any questions about the features included in your edition of MySQL 5.6, refer to your MySQL 5.6 license agreement or contact your Oracle sales representative.
MySQL 5.6 Reference Manual-14.6 InnoDB Table Management
weixin_33728268的博客
07-11 273
14.6 InnoDB Table Management 14.6.1 Creating InnoDB Tables 14.6.2 Moving or Copying InnoDB Tables to Another Machine 14.6.3 Grouping DML Operations with Transactions 14.6.4 Converting Tables f...
mysql 5.6 新特性-innodb
01-22
mysql 5.6 新特性 innodb
mysql-5.6.51-linux-glibc2.12-x86_64.tar.gz
03-12
1. **性能提升**：MySQL 5.6引入了InnoDB存储引擎的并行插入，显著提高了插入大量数据时的性能。同时，查询缓存的改进使得处理重复查询更加高效。 2. **全文搜索**：MySQL 5.6增强了全文索引功能，支持更多的分词器...
mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
12-22
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一，其版本5.6.50是一个稳定且功能丰富的版本。在本讨论中，我们将深入探讨这个特定的MySQL版本以及与其相关的Hadoop技术。 MySQL 5.6引入了许多增强功能，提高...
mysql-5.6.49-winx64.zip
08-11
首先，MySQL 5.6系列引入了InnoDB存储引擎的重大改进，提升了事务处理速度和并发性能。例如，InnoDB Plugin的集成提供了更好的行级锁定，减少了锁竞争，从而提高了多用户环境下的性能。此外，支持更多的自适应哈希...
MySQL 5.6 Reference Manual.pdf
03-01
《MySQL 5.6 参考手册》是MySQL数据库系统5.6版本的官方文档，涵盖了从5.6.0到5.6.23的所有更新和改进，同时包括基于NDB 7.3和7.4版本的MySQL Cluster从5.6.21-ndb-7.3.8到5.6.21-ndb-7.4.3的相关内容。此手册旨在为...
Mysql innodb tablespace 表空间实践
12-12
Mysql innodb tablespace 表空间实践
MySQL删除表时I/O错误的原因分析与解决
12-16
问题现象 最近使用sysbench测试MySQL，由于测试时间较长，写了一个脚本按prepare->run->cleanup的顺序在后台跑着。跑完后察看日志发现一个问题，MySQL服务的错误日志中出现多条类似以下信息的报错： [ERROR] InnoDB: Trying to do I/O to a tablespace which does not exist. I/O type: read, page: [page id: space=32, page number=57890], I/O length: 16384 bytes。 看起来是I/O出现了错误，但MySQL进程并未崩溃
MySQL 5.7.11 超过最大打开文件数crash原因和参数open_files_limit
老叶茶馆
07-12 1340
一、问题重现版本5.7.11，我们首先设置参数：max_connections = 20table_open_cache = 20table_definition_cache=10open...
mysql i o开启_MySQL中I/O出现错误问题原因及解决方案（附优化建议）
weixin_35562134的博客
01-18 756
本文是对MySQL在删除表时I/O错误原因分析，首先要先观察问题现象然后做出相关的问题调查，可以得出结论：主线程获取ibuf的(space，page)的过程与删除操作执行的过程并没有锁保证互斥，只有async I/O完成之后的merge操作与删除操作才有互斥。全部的解释本文有详细的介绍。apache php mysqlbegin!问题现象最近使用sysbench测试MySQL，由于测试时间较长，写...
mysql 删除表数据时报错_MySQL在删除表时I/O错误原因分析
weixin_30003217的博客
01-19 993
欢迎大家前往腾讯云+社区，获取更多腾讯海量技术实践干货哦~问题现象最近使用sysbench测试MySQL，由于测试时间较长，写了一个脚本按prepare->run->cleanup的顺序在后台跑着。跑完后察看日志发现一个问题，MySQL服务的错误日志中出现多条类似以下信息的报错：[ERROR] InnoDB: Trying to do I/O to a tablespace which...
MySQL存储引擎错误码,Mysql'来自存储引擎的错误-1'错误
weixin_33389398的博客
01-20 291
I have a myism table 'test' which holds some out-dated data, now I want to recreate the table, all columns the same except that I changed the storage from myism to innodb. The dumped sql I used to rec...
MySQL在删除表时I/O错误原因分析
weixin_34378767的博客
07-30 113
欢迎大家前往腾讯云+社区，获取更多腾讯海量技术实践干货哦~ 本文由腾讯数据库技术 发表于云+社区专栏 问题现象 最近使用sysbench测试MySQL，由于测试时间较长，写了一个脚本按prepare->run->cleanup的顺序在后台跑着。跑完后察看日志发现一个问题，MySQL服务的错误日志中出现多条类似以下信息的报错： [ERROR] InnoDB: Trying to ...
mysql删除表崩溃_MySQL在删除表时I/O错误原因分析
weixin_31499919的博客
02-02 189
问题现象最近使用sysbench测试MySQL，由于测试时间较长，写了一个脚本按prepare->run->cleanup的顺序在后台跑着。跑完后察看日志发现一个问题，MySQL服务的错误日志中出现多条类似以下信息的报错：[ERROR]InnoDB:TryingtodoI/Otoatablespacewhichdoesnotexist.I/Otype:re...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值