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
select table_schema,table_name
-> from information_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') as mysqldump_cmd
-> from information_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 ;') as source_cmd
-> from information_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 ]# ll /tmp/mysql
总用量
-rw-r--r--. root root 9月 : engine_cost.sql
-rw-r--r--. root root 9月 : gtid_executed.sql
-rw-r--r--. root root 9月 : help_category.sql
-rw-r--r--. root root 9月 : help_keyword.sql
-rw-r--r--. root root 9月 : help_relation.sql
-rw-r--r--. root root 9月 : help_topic.sql
-rw-r--r--. root root 9月 : innodb_index_stats.sql
-rw-r--r--. root root 9月 : innodb_table_stats.sql
-rw-r--r--. root root 9月 : plugin.sql
-rw-r--r--. root root 9月 : server_cost.sql
-rw-r--r--. root root 9月 : servers.sql
-rw-r--r--. root root 9月 : slave_master_info.sql
-rw-r--r--. root root 9月 : slave_relay_log_info.sql
-rw-r--r--. root root 9月 : slave_worker_info.sql
-rw-r--r--. root root 9月 : time_zone_leap_second.sql
-rw-r--r--. root root 9月 : time_zone_name.sql
-rw-r--r--. root root 9月 : time_zone.sql
-rw-r--r--. root root 9月 : time_zone_transition.sql
-rw-r--r--. root root 9月 : time_zone_transition_type.sql
[root@cstudio ]# ll /tmp/appdb/
总用量
-rw-r--r--. root root 9月 : person.sql
4、关闭mysql数据库并删除innodb相关文件：
#关闭mysql服务
systemctl stop mysql #删除innodb表相关的文件 #
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.* #2
rm -rf ibdata1
rm -rf ib_logfile0
rm -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 connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , 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 clear the current input statement. mysql> use mysql;
Reading table information for completion 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/
................
-rw-r-----. mysql mysql 12M 9月 : ibdata1
................
b 查看业务表是否正常
[jianglegege@cstudio mysql]$ mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is
Server version: 5.7.-log MySQL Community Server (GPL) Copyright (c) , , 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 clear the current input statement. mysql> use appdb;
Reading table information for completion 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 |
+----+--------+
| | Jim |
| | welson |
+----+--------+
rows in set (0.00 sec)
从结果上看收缩表空间是成功了！
三、对收缩表空间的思考：
1、上面的dump过程是有问题的，因为上面只dump出了表结构和数据，对于routine,event,procedure,function,view 都数据库对象
并没有导出来。
2、官方给的收缩方案太长了，我觉得不好的地方再于要对系统库进行处理，我个人比较推荐做法是初始化一个新的实例，并把业务
数据数据都导到新的实例上去，再为它建上业务账号。
----