减少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 (c) 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 (c) 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日志文件，重新启动服务器，并导入转储文件。