I want to add innodb_file_per_table statement to my.cnf file.

Where is my.cnf?

Could I change this file when I have an online site?


Once you add it to my.cnf (wherever it is), make sure you add it as follows:


A mysql restart is required to implement this for all new InnoDB tables. Doing this does not make all the InnoDB tables jump out of ibdata1.

You can cleanup InnoDB so that all tables and their indexes reside in separate tablespace files.

You will also need to tune the InnoDB parameters.

There needs to be some downtime to do this cleanup. Once done, you never have to go about doing it again.

For now, if you want to do it to a live site, you must run this:

mysql> SET GLOBAL innodb_file_per_table = 1;
  • tnx, how should I add innodb_file_per_table on a live site? – ALH Dec 19 '11 at 17:27
  • You can add innodb_file_per_table anytime to my.cnf once you find or create it for the first time. Otherwise, if you do not do that, as @DTest already said, you will lose that setting on the next mysql restart. – RolandoMySQLDBA Dec 19 '11 at 17:31
  • Updated my answer to do it now on a live site. It would be preferable to implement the cleanup instead. – RolandoMySQLDBA Dec 19 '11 at 17:34

I don't know XAMPP that well, but a quick search turned this up:

How to find my.ini and my.cnf

To find these MySQL config files, do this:

  1. Go to Folder Options: in any folder, go to the top horizontal main text menu >> Tools >> Folder Options

  2. Enable 'View Hidden Files and Folders', and 'View Protected System Files', save & exit

  3. Go to C Drive, locate and rename the my.ini file, which will now be visible. Rename it as my.ini.bak

  4. Go to /xampp/msql/bin - locate and rename my.cnf, to my.cnf.bak

  5. If there is no file my.cnf, but there is one called my - then that is the one you want. Rename it to my.cnf.bak

As for whether you can do this with an online site, you can change it online, but unless you update your my.cnf, the change will be lost after mysql restarts the next time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.