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 |

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

| mysqldump --set-gtid-purged=OFF appdb person > /tmp/appdb/person.sql |

| mysqldump --set-gtid-purged=OFF mysql engine_cost > /tmp/mysql/engine_cost.sql |

| mysqldump --set-gtid-purged=OFF mysql gtid_executed > /tmp/mysql/gtid_executed.sql |

| mysqldump --set-gtid-purged=OFF mysql help_category > /tmp/mysql/help_category.sql |

| mysqldump --set-gtid-purged=OFF mysql help_keyword > /tmp/mysql/help_keyword.sql |

| mysqldump --set-gtid-purged=OFF mysql help_relation > /tmp/mysql/help_relation.sql |

| mysqldump --set-gtid-purged=OFF mysql help_topic > /tmp/mysql/help_topic.sql |

| mysqldump --set-gtid-purged=OFF mysql innodb_index_stats > /tmp/mysql/innodb_index_stats.sql |

| mysqldump --set-gtid-purged=OFF mysql innodb_table_stats > /tmp/mysql/innodb_table_stats.sql |

| mysqldump --set-gtid-purged=OFF mysql plugin > /tmp/mysql/plugin.sql |

| mysqldump --set-gtid-purged=OFF mysql server_cost > /tmp/mysql/server_cost.sql |

| mysqldump --set-gtid-purged=OFF mysql servers > /tmp/mysql/servers.sql |

| mysqldump --set-gtid-purged=OFF mysql slave_master_info > /tmp/mysql/slave_master_info.sql |

| mysqldump --set-gtid-purged=OFF mysql slave_relay_log_info > /tmp/mysql/slave_relay_log_info.sql |

| mysqldump --set-gtid-purged=OFF mysql slave_worker_info > /tmp/mysql/slave_worker_info.sql |

| mysqldump --set-gtid-purged=OFF mysql time_zone > /tmp/mysql/time_zone.sql |

| mysqldump --set-gtid-purged=OFF mysql time_zone_leap_second > /tmp/mysql/time_zone_leap_second.sql |

| mysqldump --set-gtid-purged=OFF mysql time_zone_name > /tmp/mysql/time_zone_name.sql |

| mysqldump --set-gtid-purged=OFF mysql time_zone_transition > /tmp/mysql/time_zone_transition.sql |

| mysqldump --set-gtid-purged=OFF mysql time_zone_transition_type > /tmp/mysql/time_zone_transition_type.sql |

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

mysql> select concat('source','/tmp/',table_schema,'/',table_name,'.sql ;') assource_cmd-> frominformation_schema.tables-> where engine='innodb' and table_schema not in('information_schema','sys');+---------------------------------------------------+

| source_cmd |

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

| source /tmp/appdb/person.sql ; |

| source /tmp/mysql/engine_cost.sql ; |

| source /tmp/mysql/gtid_executed.sql ; |

| source /tmp/mysql/help_category.sql ; |

| source /tmp/mysql/help_keyword.sql ; |

| source /tmp/mysql/help_relation.sql ; |

| source /tmp/mysql/help_topic.sql ; |

| source /tmp/mysql/innodb_index_stats.sql ; |

| source /tmp/mysql/innodb_table_stats.sql ; |

| source /tmp/mysql/plugin.sql ; |

| source /tmp/mysql/server_cost.sql ; |

| source /tmp/mysql/servers.sql ; |

| source /tmp/mysql/slave_master_info.sql ; |

| source /tmp/mysql/slave_relay_log_info.sql ; |

| source /tmp/mysql/slave_worker_info.sql ; |

| source /tmp/mysql/time_zone.sql ; |

| source /tmp/mysql/time_zone_leap_second.sql ; |

| source /tmp/mysql/time_zone_name.sql ; |

| source /tmp/mysql/time_zone_transition.sql ; |

| source /tmp/mysql/time_zone_transition_type.sql ; |

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

3、执行导出操作

#创建导出后数据要保存到的目录mkdir /tmp/mysql/

mkdir /tmp/appdb/#执行导出操作

mysqldump--set-gtid-purged=OFF appdb person > /tmp/appdb/person.sql

mysqldump--set-gtid-purged=OFF mysql engine_cost > /tmp/mysql/engine_cost.sql

mysqldump--set-gtid-purged=OFF mysql gtid_executed > /tmp/mysql/gtid_executed.sql

mysqldump--set-gtid-purged=OFF mysql help_category > /tmp/mysql/help_category.sql

mysqldump--set-gtid-purged=OFF mysql help_keyword > /tmp/mysql/help_keyword.sql

mysqldump--set-gtid-purged=OFF mysql help_relation > /tmp/mysql/help_relation.sql

mysqldump--set-gtid-purged=OFF mysql help_topic > /tmp/mysql/help_topic.sql

mysqldump--set-gtid-purged=OFF mysql innodb_index_stats > /tmp/mysql/innodb_index_stats.sql

mysqldump--set-gtid-purged=OFF mysql innodb_table_stats > /tmp/mysql/innodb_table_stats.sql

mysqldump--set-gtid-purged=OFF mysql plugin > /tmp/mysql/plugin.sql

mysqldump--set-gtid-purged=OFF mysql server_cost > /tmp/mysql/server_cost.sql

mysqldump--set-gtid-purged=OFF mysql servers > /tmp/mysql/servers.sql

mysqldump--set-gtid-purged=OFF mysql slave_master_info > /tmp/mysql/slave_master_info.sql

mysqldump--set-gtid-purged=OFF mysql slave_relay_log_info > /tmp/mysql/slave_relay_log_info.sql

mysqldump--set-gtid-purged=OFF mysql slave_worker_info > /tmp/mysql/slave_worker_info.sql

mysqldump--set-gtid-purged=OFF mysql time_zone > /tmp/mysql/time_zone.sql

mysqldump--set-gtid-purged=OFF mysql time_zone_leap_second > /tmp/mysql/time_zone_leap_second.sql

mysqldump--set-gtid-purged=OFF mysql time_zone_name > /tmp/mysql/time_zone_name.sql

mysqldump--set-gtid-purged=OFF mysql time_zone_transition > /tmp/mysql/time_zone_transition.sql

mysqldump--set-gtid-purged=OFF mysql time_zone_transition_type > /tmp/mysql/time_zone_transition_type.sql

[root@cstudio 3306]# ll /tmp/mysql

总用量796

-rw-r--r--. 1 root root 2298 9月 20 15:55engine_cost.sql-rw-r--r--. 1 root root 2180 9月 20 15:55gtid_executed.sql-rw-r--r--. 1 root root 3295 9月 20 15:55help_category.sql-rw-r--r--. 1 root root 14278 9月 20 15:55help_keyword.sql-rw-r--r--. 1 root root 15348 9月 20 15:55help_relation.sql-rw-r--r--. 1 root root 710357 9月 20 15:55help_topic.sql-rw-r--r--. 1 root root 3427 9月 20 15:55innodb_index_stats.sql-rw-r--r--. 1 root root 2455 9月 20 15:55innodb_table_stats.sql-rw-r--r--. 1 root root 1892 9月 20 15:55plugin.sql-rw-r--r--. 1 root root 2417 9月 20 15:55server_cost.sql-rw-r--r--. 1 root root 2206 9月 20 15:55servers.sql-rw-r--r--. 1 root root 4443 9月 20 15:55slave_master_info.sql-rw-r--r--. 1 root root 2699 9月 20 15:55slave_relay_log_info.sql-rw-r--r--. 1 root root 2737 9月 20 15:55slave_worker_info.sql-rw-r--r--. 1 root root 2025 9月 20 15:55time_zone_leap_second.sql-rw-r--r--. 1 root root 1939 9月 20 15:55time_zone_name.sql-rw-r--r--. 1 root root 1951 9月 20 15:55time_zone.sql-rw-r--r--. 1 root root 2076 9月 20 15:55time_zone_transition.sql-rw-r--r--. 1 root root 2218 9月 20 15:55time_zone_transition_type.sql

[root@cstudio3306]# ll /tmp/appdb/总用量4

-rw-r--r--. 1 root root 1907 9月 20 15:55 person.sql

4、关闭mysql数据库并删除innodb相关文件：

#关闭mysql服务

systemctl stop mysql

#删除innodb表相关的文件

#1

rm -rf appdb/person.*

rm -rf mysql/engine_cost.*

rm -rf mysql/gtid_executed.*

rm -rf mysql/help_category.*

rm -rf mysql/help_keyword.*

rm -rf mysql/help_relation.*

rm -rf mysql/help_topic.*

rm -rf mysql/innodb_index_stats.*

rm -rf mysql/innodb_table_stats.*

rm -rf mysql/plugin.*

rm -rf mysql/server_cost.*

rm -rf mysql/servers.*

rm -rf mysql/slave_master_info.*

rm -rf mysql/slave_relay_log_info.*

rm -rf mysql/slave_worker_info.*

rm -rf mysql/time_zone.*

rm -rf mysql/time_zone_leap_second.*

rm -rf mysql/time_zone_name.*

rm -rf mysql/time_zone_transition.*

rm -rf mysql/time_zone_transition_type.*#2rm -rf ibdata1rm -rf ib_logfile0rm -rf ib_logfile1

5、修改配置文件中系统表空间中的大小从32M到12M

[mysqld]

innodb_data_file_path=ibdata1:12M

6、启动mysql数据库

systemctl start mysql

7、导入数据

[jianglegege@cstudio mysql]$ mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 5Server version:5.7.19-log MySQL Community Server (GPL)

Copyright (c)2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

mysql>use mysql;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> source /tmp/mysql/engine_cost.sql ;

mysql> source /tmp/mysql/gtid_executed.sql ;

mysql> source /tmp/mysql/help_category.sql ;

mysql> source /tmp/mysql/help_keyword.sql ;

mysql> source /tmp/mysql/help_relation.sql ;

mysql> source /tmp/mysql/help_topic.sql ;

mysql> source /tmp/mysql/innodb_index_stats.sql ;

mysql> source /tmp/mysql/innodb_table_stats.sql ;

mysql> source /tmp/mysql/plugin.sql ;

mysql> source /tmp/mysql/server_cost.sql ;

mysql> source /tmp/mysql/servers.sql ;

mysql> source /tmp/mysql/slave_master_info.sql ;

mysql> source /tmp/mysql/slave_relay_log_info.sql ;

mysql> source /tmp/mysql/slave_worker_info.sql ;

mysql> source /tmp/mysql/time_zone.sql ;

mysql> source /tmp/mysql/time_zone_leap_second.sql ;

mysql> source /tmp/mysql/time_zone_name.sql ;

mysql> source /tmp/mysql/time_zone_transition.sql ;

mysql> source /tmp/mysql/time_zone_transition_type.sql ;

mysql>use appdb;

Database changed

mysql> source /tmp/appdb/person.sql ;

8、检查一下是否都正常

a 查看表空间的大小是否收缩了

[jianglegege@cstudio mysql]$ ll -h /database/mysql/data/3306................-rw-r-----. 1 mysql mysql 12M 9月 20 16:10ibdata1

................

b 查看业务表是否正常

[jianglegege@cstudio mysql]$ mysql

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connectionid is 6Server version:5.7.19-log MySQL Community Server (GPL)

Copyright (c)2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type'help;' or '\h' for help. Type '\c' to clearthe current input statement.

mysql>use appdb;

Reading table informationforcompletion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

mysql> select *from person;+----+--------+

| id | name |

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

| 1 | Jim |

| 2 | welson |

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

2 rows in set (0.00 sec)

从结果上看收缩表空间是成功了！

三、对收缩表空间的思考：

1、上面的dump过程是有问题的，因为上面只dump出了表结构和数据，对于routine,event,procedure,function,view 都数据库对象

并没有导出来。

2、官方给的收缩方案太长了，我觉得不好的地方再于要对系统库进行处理，我个人比较推荐做法是初始化一个新的实例，并把业务

数据数据都导到新的实例上去，再为它建上业务账号。

----