mysql5 - What is the use of ibdata1 file in mysql

24
2014-04
  • loganathan

    I have application which has the backend db as Mysql(InnoDb engine), and currently my client wants to decrease the size of the ibdata file to some fixed size. I found the three below solutions.

    Solution 1: innodb_file_per_table

    Solution 2: innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

    Solution 3: innodb_data_file_path=ibdata1:10M:autoextend:max:50M

    And I really dont have knowledge about the ibadata1 file, can anybody help me on this and I want to know whether is it safe to fix the ibdata size(solution 3) and what does the ibdata exactly for?

  • Answers
  • syneticon-dj

    You really should take a thorough look at the documentation when dealing with a DBMS of this complexity. http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html states:

    Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you specify no InnoDB configuration options, MySQL creates an auto-extending 10MB data file named ibdata1 and two 5MB log files named ib_logfile0 and ib_logfile1 in the MySQL data directory.

    Basically, the ibdata1 file contains the table data of your InnoDB tables. Specifying more than a single file would make sense in cases where you are using a different storage subsystem to store the second file for performance reasons or if you ran out of space for ibdata1 on its original partition and would want to have a simple add-on storage with a different storage file. Fixing the file size obviously has the effect of limiting the total amount of data which can be stored in InnoDB tables.

    The ibdata1 file is probably auto-growing in your case, so it will inflate as more data is put into InnoDB tables. After records are deleted from InnoDB tables, the file will contain pages marked as "free" which could be used for future data, but the file itself is unshrinkable. If you have deleted larger amounts of data and need to shrink ibdata1, the only supported way goes through backing up the tables using mysqldump and re-initializing your database - see this StackOverflow answer for details.


  • Related Question

    Mysql ibdata1 file
  • run

    What happens if I delete the ibdata1 file and restart the server, my database is MyISAM, I used InnoDB before but now all tables are MyISAM. Will be a big problem if I delete this file. As far as I know that will be recreated when I restart the server, but I don't understand what exactly is that file!


  • Related Answers
  • RolandoMySQLDBA

    You should do the following:

    Add this to /etc/my.cnf

    [mysqld]
    skip-innodb

    Then perform the following:

    1. service mysql stop
    2. rm -f /var/lib/mysql/ib*
    3. service mysql start

    As to what the file is : ibdata1 stores the internal metadata for each InnoDB table that exists. Make sure all InnoDB tables are converted to MyISAM or mysqldump'd out before deleting ibdata1. There are also two other files: ib_logfile0 and ib_logfile1. Those are InnoDB log files for holding running transactions not fully commited to disk as well as MVCC (Multiversioning Concurrency Control) data.

    Once you restart mysql from those three(3) steps, none of the InnoDB files should reappear. In fact, you should get a much faster mysql startup.

    Don't forget to scale up the key_buffer_size

  • HTTP500

    InnoDB stores the tablespace for all tables with ENGINE=InnoDB in that file whereas in MyISAM the tablespace is stored in individual .MYD and .MYI files.

    Cheers

  • thinice

    Yes, you can remove the ibdata1 file. However, as long as you have the InnoDB engine enabled, the file will be recreated at startup. I wouldn't delete this file while the mysqld service is running.