innodb 系统表空间是一个逻辑上的概念,它的物理表现就是innodb系统表空间文件;在讲扩展系统表空间时我们说到
可以用增加文件,增加autoextend标记 这两种方式来解决;但是问题到了收缩表空间时就变的不一般了,如果直接删掉
系统表空间文件就意味着数据的丢失,innodb系统表空间文件也没有提供收缩的标记可以打,也没有提供可以收缩表空间
的SQL语句。难道就没有能收缩系统表空间的办法了吗?办法是有的只是比较暴力,这个办法就是重建一个新实例。
一、官方文档中给出的收缩表空间的方法&步骤:
1、用mysqldump 备份出所有的innodb表,这里要特别注意mysql这个系统库中有部分表也是innodb的,所以这些表
也是要备份出来的。
2、关闭mysql数据库服务。
3、删除所有innodb表的*idb,*frm文件 ;删除系统表空间文件ibdata*.idb ,删除innodb的日志文件ib_log 文件。
4、修改配置文件中系统表空间的配置到想要的大小。
5、启动mysql服务
6、导入第一.1目中导出的数据
二、实战(对一个系统表空间为32M的实例进行表空间收缩,收缩到12M)
1、确定实例中有哪些表的引擎是innodb
selecttable_schema,table_name-> frominformation_schema.tables-> where engine='innodb' and table_schema != 'information_schema';+--------------+---------------------------+
| table_schema | table_name |
+--------------+---------------------------+
| appdb | person |
| mysql | engine_cost |
| mysql | gtid_executed |
| mysql | help_category |
| mysql | help_keyword |
| mysql | help_relation |
| mysql | help_topic |
| mysql | innodb_index_stats |
| mysql | innodb_table_stats |
| mysql | plugin |
| mysql | server_cost |
| mysql | servers |
| mysql | slave_master_info |
| mysql | slave_relay_log_info |
| mysql | slave_worker_info |
| mysql | time_zone |
| mysql | time_zone_leap_second |
| mysql | time_zone_name |
| mysql | time_zone_transition |
| mysql | time_zone_transition_type |
| sys | sys_config |
+--------------+---------------------------+
这里是一个非常尴尬的地方 a、官方文档上明确指出了要dump出所有innodb表的数据,但是呢它自己的操作中只是查看了mysql这一个
系统库中的innodb表;事实上对于mysql5.7来说sys库和informations-schema都能查出它们有innodb表。
b、不管sys还是information_schema都它们的表都是虚的,也就是说这两个库不会有数据文件被保存在磁盘中;在下面操作中我只会
dump出mysql库和appdb 这个库中的innodb表;这样的话我操作的表的个数就相对少一些,也算是测试一下只dump mysql库和业务
库能不能完成收缩的任务
2、拼接出导出时dump的语句和导入时的source语句:
mysql> select concat('mysqldump --set-gtid-purged=OFF',table_schema,' ',table_name,-> '> /tmp/',table_schema,'/',table_name,'.sql') asmysqldump_cmd-> frominformation_schema.tables-> where engine='innodb' and table_schema not in('information_schema','sys');+------------------------------------------------------------------------------------------------------------+
| mysqldump_cmd |
+------------------------------------------------------------------------------------------------------------+