如何防止 MySQL 的 ibdata1 文件过大

在 MySQL 5.6.6 版本以前， MySQL 默认会把所有的 innodb 的表都放在同一个文件中(ibdata1)，当该文件过大的时候， MySQL 容易出错，维护性能差。一个简单的办法是修改 MySQL 配置文件 /etc/my.cnf 后重启 [mysqld]

innodb_file_per_table=1

但那些在修改之前已经创建了 innodb 的表怎么办？ 一种办法是用 mysqldump 备份所有数据库后重建所有数据库，步骤如下

1. mysqldump -u user -p password --all-databases > backup.sql 导出所有数据
2. 删除所有数据库 drop database database_N
3. 停止 MySQL 服务 service mysql stop 如果是 Mariadb 就是 service mariadb stop
4. 删除文件 ibdata1, ib_logfile0, ib_logfile1
5. 重启 MySQL 服务 service mysql start 如果是 Mariadb 就是 service mariadb start
6. 导入备份数据 mysql -u user -p password --all-databases < backup.sql

另一种办法是把已经储存在 ibdata1 里面的 innodb 表拿出来放到它自己的路径上 mysql -sN -u user -p -e "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb'" | awk -F" " '{print "alter table"$1"."$2" engine innodb;"}' | tee prepare.sql

mysql -u user -p < prepare.sql

如果要排除 MySQL 自带的 table ，可以修改上面脚本中的 where 部分的语句。下面是在 Plesk 中使用的一个例子 MYSQL_PWD=cat /etc/psa/.psa.shadow mysql -sN -u admin -e "SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = 'innodb' and table_schema != 'apsc' and table_schema != 'mysql' and table_schema != 'psa' and table_schema != 'information_schema' and table_schema != 'horde' and table_schema != 'performance_schema' and table_schema != 'roundcubemail' and table_schema not like 'phpmyadmin_%' " | awk -F" " '{print "alter table "$1"."$2" engine innodb;"}' | tee prepare.sql

mysql -u user -p < prepare.sql

Reference:

1. How to Shrink MySQL ibdata1 Size using innodb_file_per_table
2. InnoDB File-Per-Table Tablespaces