mysql-5.7 收缩系统表空间详解
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 3306]# ll /tmp/mysql 总用量 796 -rw-r--r--. 1 root root 2298 9月 20 15:55 engine_cost.sql -rw-r--r--. 1 root root 2180 9月 20 15:55 gtid_executed.sql -rw-r--r--. 1 root root 3295 9月 20 15:55 help_category.sql -rw-r--r--. 1 root root 14278 9月 20 15:55 help_keyword.sql -rw-r--r--. 1 root root 15348 9月 20 15:55 help_relation.sql -rw-r--r--. 1 root root 710357 9月 20 15:55 help_topic.sql -rw-r--r--. 1 root root 3427 9月 20 15:55 innodb_index_stats.sql -rw-r--r--. 1 root root 2455 9月 20 15:55 innodb_table_stats.sql -rw-r--r--. 1 root root 1892 9月 20 15:55 plugin.sql -rw-r--r--. 1 root root 2417 9月 20 15:55 server_cost.sql -rw-r--r--. 1 root root 2206 9月 20 15:55 servers.sql -rw-r--r--. 1 root root 4443 9月 20 15:55 slave_master_info.sql -rw-r--r--. 1 root root 2699 9月 20 15:55 slave_relay_log_info.sql -rw-r--r--. 1 root root 2737 9月 20 15:55 slave_worker_info.sql -rw-r--r--. 1 root root 2025 9月 20 15:55 time_zone_leap_second.sql -rw-r--r--. 1 root root 1939 9月 20 15:55 time_zone_name.sql -rw-r--r--. 1 root root 1951 9月 20 15:55 time_zone.sql -rw-r--r--. 1 root root 2076 9月 20 15:55 time_zone_transition.sql -rw-r--r--. 1 root root 2218 9月 20 15:55 time_zone_transition_type.sql [root@cstudio 3306]# 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.* #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 5 Server 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 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/3306 ................ -rw-r-----. 1 mysql mysql 12M 9月 20 16:10 ibdata1 ................
b 查看业务表是否正常
[jianglegege@cstudio mysql]$ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server 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 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 | +----+--------+ | 1 | Jim | | 2 | welson | +----+--------+ 2 rows in set (0.00 sec)
从结果上看收缩表空间是成功了！
三、对收缩表空间的思考：
1、上面的dump过程是有问题的，因为上面只dump出了表结构和数据，对于routine,event,procedure,function,view 都数据库对象
并没有导出来。
2、官方给的收缩方案太长了，我觉得不好的地方再于要对系统库进行处理，我个人比较推荐做法是初始化一个新的实例，并把业务
数据数据都导到新的实例上去，再为它建上业务账号。
----
