What is the use of ibdata1 file in mysql?

Question

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?

Answer 1

In My Opinion you should use your solution1.

I am not very clear with what you will accomplish with the solution2 and solution3. with innodb_file_per_table OFF because they will never shrink the ibdata size whenever there delete,drop or truncate on an InnoDB table so using these option your ibdata will grow endlessly.

For your information have a look at this way to reduce the size of ibdata in mysql.?

without using innodb_file_per_table, the ibdata file never compresses or shrinks, Not when you delete a row, drop a table, or a database. In no time, 3GB of data can become a 20GB file if you have an active queuing system.

Have a look why to use innodb_file_per_table : Reasons to use innodb_file_per_table.