调整InnoDB系统表空间大小

本文永久地址:
http://fuxkdb.com/2017/08/11/%E8%B0%83%E6%95%B4InnoDB%E7%B3%BB%E7%BB%9F%E8%A1%A8%E7%A9%BA%E9%97%B4%E5%A4%A7%E5%B0%8F/

调整InnoDB系统表空间大小

本文介绍如何增大或缩小InnoDB system tablespace

增大InnoDB system tablespace

最简单的增大InnoDB system tablespace大小的方法是在一开始配置的时候就指定为自动扩展. 为innodb_data_file_path参数中的最后一个数据文件指定autoextend选项. InnoDB在空间不足时以64MB为单位自动增加该文件的大小. 可以通过设置innodb_autoextend_increment系统变量的值（以兆字节为单位）来更改增量大小.

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

1.关闭MySQL
2.如果上一个数据文件是使用关键字autoextend定义的,则根据实际增长的大小将其定义更改为使用固定大小. 检查数据文件的大小,将其舍入到1024×1024字节（= 1MB）的最接近的倍数,并在innodb_data_file_path中显式指定舍入后的大小.
3.将新的数据文件添加到innodb_data_file_path的末尾,可以指定该文件为自动扩展. 注意,只能将innodb_data_file_path中的最后一个数据文件指定为自动扩展.
4.启动MySQL

实际例子:

初始只有一个ibdata1,现在我们想增加一个数据文件

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设ibdata1此时已经增长到988M,那么修改配置为

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

启动MySQL后,ibdata2会被初始化

2017-08-11T10:27:06.014446+08:00 0 [Note] InnoDB: Need to create a new innodb_system data file 'ibdata2'.
2017-08-11T10:27:06.014567+08:00 0 [Note] InnoDB: Setting file './ibdata2' size to 50 MB. Physically writing the file full; Please wait ...
2017-08-11T10:27:06.182464+08:00 0 [Note] InnoDB: File './ibdata2' size is now 50 MB.

缩小InnoDB system tablespace

您不能从系统表空间中删除数据文件. 要减少系统表空间大小,请使用以下过程：

1.使用mysqldump来转储所有的InnoDB表,包括位于MySQL数据库中的InnoDB表.

mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+---------------------------+
| TABLE_NAME                |
+---------------------------+
| engine_cost               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+

2.关闭MySQL
3.删除所有现有的表空间文件（* .ibd）,包括ibdata和ib_log文件. 不要忘记删除位于MySQL数据库中的表的* .ibd文件.
4.删除InnoDB表的任何.frm文件.
5.配置新的表空间.
6.重启MySQL
7.导入dump文件

Note
如果您的数据库仅使用InnoDB引擎,可能会更容易地转储所有数据库,停止服务器,删除所有数据库和InnoDB日志文件,重新启动服务器以及导入转储文件.