MySQL data files and log files

In Data folder I can see a folder named with my database name. Inside it I can see few .FRM files. The largest size of a .FRM file is 11 KB. In the Data folder itself, I can see some files whose name are like this:

ibLogFile0    -  10 MB
ibLogFile1    -  10 MB
ibData1        -  18 MB
impserver.pid  - 1 KB

I want to know, what is the maximum size of the .FRM file in Windows XP SP2 (FAT32 and NTFS). I also want to know why log files have reached to 10 MB when the database size (total of .FRM files) is 132 KB.

For what these 'ib...' files are used?
LVL 8
rpkhareAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ChrisedeboCommented:
The maximum size of a .frm file is 64kb and is related to the number of columns you have in a table. see here for more information http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html

The log files will grow fairly quickly as they will store all the transactions performed on the database. You can shrink the files with a Flush command. see here for more information http://dev.mysql.com/doc/refman/5.1/en/log-files.html
0
rpkhareAuthor Commented:
What is the name of my data file which is storing real data? Is it "ibData1"? Are the ".frm" files the structure information of a table?
0
ChrisedeboCommented:
Yes, thats correct. All data will be stored in the ibData1 file, unless you or the MySQL db create additional datafiles later on.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

ChrisedeboCommented:
if you are using the innodb engine the files will be approximatley twice the size of the data they contain.
0
rpkhareAuthor Commented:
In case I am planning to create a Data-warehouse where in I want the "ibData1" file to grow freely without limits, how I can do that? Does MySQL 5.1 community edition support this?
0
UmeshMySQL Principle Technical Support EngineerCommented:
>>>In Data folder I can see a folder named with my database name. Inside it I can see few .FRM files. The largest size of a .FRM file is 11 KB. In the Data folder itself, I can see some files whose name are like this:

Regardless of the storage engine you choose, every MySQL table you create is represented, on disk, by a .frm file, which describes the table's format (i.e. the table definition). The file bears the same name as the table, with a .frm extension. The .frm format is the same on all platforms but in the description of the .frm format that follows, the examples come from tables created under the Linux operating system.


>>ibLogFile0    -  10 MB - InnoDB log files (primary)
>>ibLogFile1    -  10 MB - InnoDB log files (Secondary)
>>ibData1        -  18 MB - InnoDB data file (tablespace)
>>impserver.pid  - 1 KB  - Process identification number associated with a service/daemon




>>I want to know, what is the maximum size of the .FRM file in Windows XP SP2 (FAT32 and NTFS). I also want to know why log files have reached to 10 MB when the database size (total of .FRM files) is 132 KB.


Can you post InnoDB specific settings from my.cnf here? which solve your above query

0
rpkhareAuthor Commented:
I searched for my.cnf file but I couldn't find it.
0
UmeshMySQL Principle Technical Support EngineerCommented:
Are you on Windows box then pls look for my.ini
0
UmeshMySQL Principle Technical Support EngineerCommented:
You may notice below lines..in my.ini/my.cnf

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = "E:/MySQLMaster/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "E:/MySQLMaster/data"
innodb_log_arch_dir = "E:/MySQLMaster/data"
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


***Data file

innodb_data_file_path = ibdata1:10M:autoextend  

This setting configures a single 10MB data file named ibdata1 that is auto-extending. No location for the file is given, so by default, InnoDB  creates it in the MySQL data directory.

0
rpkhareAuthor Commented:
Ya. Found it. Please see the attached document.
my.txt
0
rpkhareAuthor Commented:
I cannot see that "auto-extend" line in "my.ini" file. See the attached file above.
0
UmeshMySQL Principle Technical Support EngineerCommented:
I see these settings for InnoDB in your my.ini

#*** INNODB Specific options ***


innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=15M
innodb_log_file_size=10M
innodb_thread_concurrency=8


To see rest of the InnoDB specific details.. pls run this from mysql prompt.

mysql>show global variables;

or even this

mysql>show variables like 'innodb%';

Pls take a look at the parameters.. you will see all the details
0
rpkhareAuthor Commented:
To backup manually, I need to take backup of:

ibLogFile0
ibLogFile1
ibData1

Database folder containing ".FRM" files and the "my.ini " file. Is it correct?
0
UmeshMySQL Principle Technical Support EngineerCommented:

No need to copy my.ini every time if you haven't done significant changes.

   1. Shut down your MySQL server and make sure that it shuts down without errors.
   2.Copy all your data files (ibdata files and .ibd files) into a safe place.
   3.Copy all your ib_logfile files to a safe place.
   4.Copy your my.ini configuration file or files to a safe place.
   5.Copy all the .frm files for your InnoDB tables to a safe place.

I assume per table data file is not enabled for InnoDB
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rpkhareAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.