MyISAM vs InnoDB Swapping from one to the other

While pretty much defunct at this point as innodb_file_per_table is the default setting now, from a flexibility point of view and if the deployment circumstances require you to use InnoDB innodb_file_per_table is the best way to go. This will allow you to move table files around if required. Using a global table space on a per server basis means that all the tables in all the database have metadata stored in /var/lib/mysql/innodb. That file can grow quite large and never EVER shrinks back down again (Linux software is becomming more and more windowsey by the day). Using innodb_file_per_table at least swaps a massive (potentially hundreds of gigabytes) file for smaller individuals and has the already mentioned added advantage that you can move tables and databases around at will! With system table space you have to move or restore the ENTIRE /var/lib/mysql directory or risk the frustrating "table already doesn't exsist but isn't there but is there" issue..

Swapping from system table space to innodb_file_per_table:
First we enable innodb_file_per_table.
emacs /etc/my.cnf
[mysqld]
innodb_file_per_table = 1
Now we dump the ENTIRE database (you really need to do this as root or a super user with root privs):
mysqldump -u USER -p --all-databases > /backup/all-database.sql
Now manually log in and drop ALL the databases one at a time (DON'T drop information_schema or mysql databases eh!!!)! Once you've done that you can stop the server and remove the tablespace data
/etc/init.d/mysql stop || service stop mysql
cd /var/lib/mysql
rm ibdata1
rm ib_logfile0
rm ib_logfile1
Now you are ready to import all the databases back in again.
mysql -u USER -p < /backup/all-database.sql You should now be in a position where you don't have a massive multigigabyte file/files sitting there and instead, you should have a smaller file for each InnoDB enabled table!

Back