Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

what is the purpose of ibdata1,ib_logfile0,ib_logfile1

julianjulian ContributorCurrent User Role Beginner
edited September 2 in Other MySQL® Questions
Hi
I'm new to mysql. In /var/lib/mysql directory I'm creating databases. I'm using InnoDB storage engine. In this directory three files namely ibdata1,ib_logfile0,ib_logfile1 created automatically. What is the use of these files and it contains any information? If I deleted these files what happens?
Any one answer for this doubt.

Comments

  • babuno5babuno5 Entrant Current User Role Beginner
    Dont delete the files. Those are the files where the actual data, indexes and the innodb log are stored for all innodb databases
  • julianjulian Contributor Current User Role Beginner
    If we insert records in the db, will the size of these files increase?.

    Will these files affects the execution speed.

    If databases r deleted will the size of these files decrease?
  • sterinsterin Mentor Inactive User Role Contributor
    edited September 2
    If we insert records in the db, will the size of these files increase?.

    Yes when the ibdata1 file is to small for the amount of data that you are trying store in it. It will grow automatically.
    The ib_logfile files will continue to be the same size always, they do not grow.

    Will these files affects the execution speed.

    The question isn't if the files will affect the execution speed.

    The quesiton is if more _data_ in the database will affect execution speed, and the answer is, yes more data in the database will affect execution speed.
    But if you have proper indexes in your tables you will not notice it that much.
    If databases r deleted will the size of these files decrease?

    No, unfortunately not.
    But generally when you have a database you seldom have the situation that you need to shrink the table space.
    Because often the size of a database is either growing or pretty stable in size.
  • TheCowGodTheCowGod Entrant Inactive User Role Beginner
    If you're using the innodb_file_per_table option, then each InnoDB table gets its own .ibd file, so you can recover freed-up space in a table by rebuilding just that one table. The easiest way to rebuild an InnoDB table is to run the command, "ALTER TABLE tablename ENGINE=InnoDB;". However, this will build the new copy of the table on disk before dropping the old one, so you'll need to have enough free disk space to keep the entire second copy of the table. If you're tight on disk space on the database server (which is probably why you want to free up the space trapped in the InnoDB tablespace in the first place), then you can mysqldump the table to another machine with more free space and then re-import it back into the database server. It's still a pain to have to do that, but it's a lot better than having to dump ALL of your tables.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.