My ibdata1 file continues to grow regardless of how much data is deleted or tables/databases dropped. I know this is the norm. When my database was first established, the innodb-file-per-table setting was set to off. I want to activate it so that InnoDB databases are stored in separate .ibd files instead of within ibdata1.
Everything I’ve read says the process to accomplish this is as follows:
- Dump all databases (or at least all InnoDB databases)
- Shut down mysql
- Add “innodb-file-per-table=1” to my.cnf (if it hasn’t already been added)
- Delete ibdata1, ib_logfile0, and ib_logfile1
- Restart mysql
- Import dumped file
Here’s a site that lays out these steps:
http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
Ok. I get how that’s done, but I prefer to take another (more complicated) route, if it’s viable.
Another site I visited says another way is to convert InnoDB tables to MyISAM, as shown here in the “Changing Table Engines” technique:
http://vdachev.net/2007/02/22/mysql-reducing-ibdata1/
The way it works is:
- Convert all InnoDB tables to MyISAM using an ‘ALTER TABLE’ statement.
- Foreign keys/constrains will be lost, so they’ll need to be recreated later.
- Shut down mysql
- Add “innodb-file-per-table=1” to my.cnf (if it hasn’t already been added)
- Delete ibdata1, ib_logfile0, and ib_logfile1
- Restart mysql
- Convert the previously altered tables back from MyISAM to InnoDB
- Restore foreign keys/constraints sandwiched between the following statement
SET FOREIGN_KEY_CHECKS=0;
-- put restore foreign key/constraint code here
SET FOREIGN_KEY_CHECKS=1;
I should be able to get all my foreign key/constrains by dumping the database/table “structure” using phpMyAdmin. So I’ll just copy and paste them afterward.
The site referenced above is the only onc I’ve seen reference this approach. My question is, is it viable or am I overlooking something?