By default MySQL's INNODB engine puts all the data for all the INNODB tables for all databases into the same file. This file will grow as it is used but will never shrink, even if you drop a very large INNODB table. This post shows how instead to have a data file per table in the database's directory which is the same as the way the MyISAM engine works.
Disclaimer
Use with caution and ensure you test on a non-production system first.
Change INNODB to use one file per table
Stop the MySQL server (usually "/etc/init.d/mysql stop" on Linux).
Edit the my.cnf file (located at somewhere like /etc/my.cnf or /etc/mysql/my.cnf)
Add "innodb_file_per_table" on a single line by itself into the my.cnf file.
Start the MySQLserver ("/etc/init.d/mysql start").
Any new INNODB tables that are created will now be created in the database's directory with a single file per database table. Doing this does not affect any existing INNODB tables; they will still use the common data file.
Reclaiming space
The only way to reclaim the space used by the common file and existing tables is to drop all the INNODB tables, stop MySQL, delete the common INNODB data files, start MySQL and re-import the tables into the appropriate databases. Alternatively you can go the whole hog and make a complete database backup including all triggers, stored procedures etc, drop all databases and so on. When MySQL is restarted after deleting the common INNODB tables it re-creates them.
How to do this is covered here on the MySQL mailing lists. Again, please ensure if you are going to do this that you use it with extreme caution and test first on a non-production environment. Also it may take some time to a) backup your databases/tables and b) re-import them.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
0 comments:
Post a Comment