MySQL 减少InnoDB系统表空间的大小

减少InnoDB系统表空间的大小
不能从系统表空间中删除数据文件。若要减少系统表空间大小，请使用此过程:
1.使用mysqldump将所有的包括MySQL数据库中的InnoDB表dump出来，在5.6当前版本中，包括5张InnoDB表:

[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution
Copyright © 2000, 2019, 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> show variables like ‘innodb_data%’;
±----------------------±-----------------------------------+
| Variable_name | Value |
±----------------------±-----------------------------------+
| innodb_data_file_path | ibdata1:76M;ibdata2:50M:autoextend |
| innodb_data_home_dir | |
±----------------------±-----------------------------------+
2 rows in set (0.01 sec)
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 |
±--------------------------+
19 rows in set (0.00 sec)
[mysql@localhost ~]$ mysqldump -uroot -pxxzx7817600 --all-databases > all_db_dump_20220223.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
2.停止MySQL服务器

[mysql@localhost ~]$ mysqladmin -uroot -pxxzx7817600 shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
3.删除所有已经存在的表空间文件(.ibd),包括ibdata和ib_log文件。不要忘记删除MySQL数据库中的.ibd文件。

[mysql@localhost mysql]$ find ./mysql -name “.ibd"
./mysql/plugin.ibd
./mysql/servers.ibd
./mysql/help_topic.ibd
./mysql/help_category.ibd
./mysql/help_relation.ibd
./mysql/help_keyword.ibd
./mysql/time_zone_name.ibd
./mysql/time_zone.ibd
./mysql/time_zone_transition.ibd
./mysql/time_zone_transition_type.ibd
./mysql/time_zone_leap_second.ibd
./mysql/innodb_table_stats.ibd
./mysql/innodb_index_stats.ibd
./mysql/slave_relay_log_info.ibd
./mysql/slave_master_info.ibd
./mysql/slave_worker_info.ibd
./mysql/gtid_executed.ibd
./mysql/server_cost.ibd
./mysql/engine_cost.ibd
[mysql@localhost mysql]$ find ./mysql -name ".ibd” | xargs -n 1 rm -f
[mysql@localhost mysql]$ rm -rf ibdata*
[mysql@localhost mysql]$ rm -rf ib_log*
[mysql@localhost mysql]$ ls -lrt ibdata*
ls: 无法访问ibdata*: 没有那个文件或目录
[mysql@localhost mysql]$ ls -lrt ib_log*
ls: 无法访问ib_log*: 没有那个文件或目录
4.删除InnoDB表的.frm文件

[mysql@localhost mysql]$ rm -rf mysql/plugin…frm
[mysql@localhost mysql]$ rm -rf mysql/servers…frm
[mysql@localhost mysql]$ rm -rf mysql/help_topic…frm
[mysql@localhost mysql]$ rm -rf mysql/help_category…frm
[mysql@localhost mysql]$ rm -rf mysql/help_relation.frm
[mysql@localhost mysql]$ rm -rf mysql/help_keyword.frm
[mysql@localhost mysql]$ rm -rf mysql/time_zone_name.frm
[mysql@localhost mysql]$ rm -rf mysql/time_zone.frm
[mysql@localhost mysql]$ rm -rf mysql/time_zone_transition.frm
[mysql@localhost mysql]$ rm -rf mysql/time_zone_transition_type.frm
[mysql@localhost mysql]$ rm -rf mysql/time_zone_leap_second.frm
[mysql@localhost mysql]$ rm -rf mysql/innodb_table_stats.frm
[mysql@localhost mysql]$ rm -rf mysql/innodb_index_stats.frm
[mysql@localhost mysql]$ rm -rf mysql/slave_relay_log_info.frm
[mysql@localhost mysql]$ rm -rf mysql/slave_master_info.frm
[mysql@localhost mysql]$ rm -rf mysql/slave_worker_info.frm
[mysql@localhost mysql]$ rm -rf mysql/gtid_executed.frm
[mysql@localhost mysql]$ rm -rf mysql/server_cost.frm
[mysql@localhost mysql]$ rm -rf mysql/engine_cost.frm
5.配置一个新表空间

[mysql@localhost mysql]$ vi my.cnf
…
innodb_data_file_path = ibdata1:10M:autoextend
6.重启MySQL服务器

[root@localhost ~]# service mysqld start
Starting MySQL… SUCCESS!
7.导入dump文件

[mysql@localhost ~]$ mysql -uroot -pxxzx7817600 < all_db_dump_20220223.sql mysql: [Warning] Using a password on the command line interface can be insecure. [mysql@localhost ~]$ mysql -uroot -pxxzx7817600 mysql mysql: [Warning] Using a password on the command line interface can be insecure. Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.26-log Source distribution Copyright © 2000, 2019, 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> show variables like ‘innodb_data%’;
±----------------------±-----------------------+
| Variable_name | Value |
±----------------------±-----------------------+
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
±----------------------±-----------------------+
2 rows in set (0.01 sec)
InnoDB系统表空间的文件变回原来的ibdata1了

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 |
±--------------------------+
19 rows in set (0.01 sec)
注意:如果您的数据库只使用InnoDB引擎，那么可能更简单的方法是转储所有数据库，停止服务器，删除所有数据库和InnoDB日志文件，重新启动服务器，并导入转储文件。