Where should I look for my.cnf?

Question

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

• Where is it?
• Could I change this file when I have an Online site?

EDIT

Oops! I was not clear enough, actually I could not find the file (my.cnf)! Where is my.cnf?

Answer 1

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

[mysqld]
innodb_file_per_table

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;

Answer 2

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.