Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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;
share|improve this answer
    
tnx, how should I add innodb_file_per_table on a live site? –  john.locke 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
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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