BIP KB:
Table | Use one file per table with MySQL's INNODB storage
Article By BIP Admin
Introduction to MySQL INNODB engine table storage
InnoDB is a general-purpose storage engine that balances high reliability and high performance. In MySQL 5.6, InnoDB is the default MySQL storage engine. Unless you have configured a different default storage engine, issuing a CREATE TABLE statement without an ENGINE= clause creates an InnoDB table. InnoDB includes all the features that were part of the InnoDB Plugin for MySQL 5.1, plus new features specific to MySQL 5.5 and higher. |
|
Note: The mysql and INFORMATION_SCHEMA databases that implement some of the MySQL internals still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB.
By default MySQL 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 MySQL server ("/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.
Tags: mysql, Engine, INNODB, INNODB Engine
Spin Up A VPS Server In No Time Flat
Simple Setup
Full Root Access
Straightforward Pricing
DEPLOY A SECURE VPS SERVER TODAY!Leave a Reply
Feedbacks
 This work is licensed under a Creative Commons Attribution- NonCommercial-ShareAlike 4.0 International License. |