1

We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there were the .frm files. I searched the entire HDD for the .MYD files but the only ones are from the old database. It is on a CentOS 6.6 system running MySQL 5.5.

Where would those files be located? At first I was worried that they might just be held in cache, and that if the server lost power all would be lost, but not so sure now.

Wow, awesome commands Rolando, here are the results which looks like the majority is not InnoDB, but I would have assumed it still would need to store the data in the same place, even if it was in a different format. Our old DB is 1.7GB when I use the du -h in the /var/lib/mysql and the directory I care about is less than 1MB

+----------------+----------------------+----------------------+----------------------+
| Storage Engine | Data Size            | Index Size           | Table Size           |
+----------------+----------------------+----------------------+----------------------+
| MEMORY         |             0.000 GB |             0.000 GB |             0.000 GB |
| InnoDB         |             0.151 GB |             0.059 GB |             0.210 GB |
| MyISAM         |             1.381 GB |             0.227 GB |             1.608 GB |
| Total          |             1.532 GB |             0.286 GB |             1.817 GB |
+----------------+----------------------+----------------------+----------------------+

+-----------------------+------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE         |
+-----------------------+------------------------+
| INNODB_DATA_FILE_PATH | ibdata1:10M:autoextend |
| DATADIR               | /var/lib/mysql/        |
| INNODB_FILE_PER_TABLE | OFF                    |
+-----------------------+------------------------+
Improve this question
6
  • Were all the tables using InnoDB ?
    – RolandoMySQLDBA
    Jan 27, 2015 at 23:31
  • Please go to my post dba.stackexchange.com/questions/8661/…, look for the bold heading Total MySQL Data and Index Usage By Storage Engine and run that query please. Does it report any data ?
    – RolandoMySQLDBA
    Jan 27, 2015 at 23:33
  • 1
    Please run this SELECT variable_name FROM information_schema.global_variables WHERE variable_name IN ('datadir','innodb_file_per_table','innodb_data_file_path','innodb_data_home_dir'); What are the values ?
    – RolandoMySQLDBA
    Jan 27, 2015 at 23:35
  • I am sorry wrong query. Please run this: SELECT * FROM information_schema.global_variables WHERE variable_name IN ('datadir','innodb_file_per_table','innodb_data_file_path','innodb_data_home_dir‌​');
    – RolandoMySQLDBA
    Jan 28, 2015 at 16:04
  • As shown, you have 1.608 GB of MyISAM, 210MB of InnoDB. Therefore, your data is not missing.
    – RolandoMySQLDBA
    Jan 28, 2015 at 16:16

1 Answer 1

Reset to default
0

OK here is your situation: your INNODB_FILE_PER_TABLE is OFF.

The data and index pages for the InnoDB tables are all inside the system tablespace (ibdata1). For those tables, you only see the .frm files. Therefore, everything is OK.

Here is what InnoDB looks like (image courtesy of Percona CTO Vadim Tkachenko)

InnoDB Architecture

These days, many use the default innodb_file_per_table = 1. This used to be 0 for the default back in the days of MySQL 5.1. If you have innodb_file_per_table = 0 in your my.cnf, then MySQL is leaving all InnoDB tables inside ibdata1.

For more info on this, see my posts

You can leave the InnoDB tables inside ibdata1. If you want them stored outside, see the CAVEAT of my post How can I determine which Innodb table is being written?

Improve this answer
3
  • Thank you so much Rolando, I do see that file now. I will read up on those links you posted and see if I really want to make any changes.
    – Alan
    Jan 28, 2015 at 17:47
  • Just out of curiosity, with websites like JSFiddle.com or stackexchange with large databases, wouldn't a single file storage slow down the server if it didn't all fit in RAM? Also it seems like it would be harder to run a cluster as you outgrew a single server.
    – Alan
    Jan 28, 2015 at 17:54
  • InnoDB allows multiple ibdata1 files (See my post dba.stackexchange.com/questions/43503/…)
    – RolandoMySQLDBA
    Jan 28, 2015 at 17:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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