如何防止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