MySQL - Old ibdata file shall I delete safely

Question

On a production server I have changed "innodb_file_per_table=ON" and I can see separate tablespace files for each table on my server. I have used parititioning for tables so for each partition there is a separate .ibd file .

But I can see previous "ibdata1" file of size 10 Gb. I am bit curious whether to delete it or not ?

1. Does MySQL still uses old shared table space "ibdata" file?

2. If I delete shared tablespace file "ibdata1" will there be any impact on server ? Will it cause any problem ?

Answer 1

Great question, and a very common issue.

When you changed the server configuration, to innodb_file_per_table = 1, the tables still lived on the common tablespace. You still had to recreate those using ALTER TABLE ENGINE=InnoDB (or any other operation that forced a tablespace recreation).

Once you have done that, why does MySQL still have a huge ib_data1? The truth is that InnoDB cannot really shrink (defragment) its tablespace files. That is not a huge issue in most cases, because the free space inside every file can be used by other data, index, etc. pages, and once you start using innodb_file_per_table you can always recreate each individual tablespace individually in order to free filesystem space.

However, the common tablespace is special. Even if it didn't contain any table or index information, it is always compulsory as it stores common data like the data dictionary, change buffer, etc. So if you delete it, you would end up with a fully unusable mysql installation. Answering your questions: 1) Yes it is being still used and 2) you won't be able to restart mysql again if you delete it. But, obviously, most of the ibdata1 file will be empty, it is just that it is very difficult to move page addresses around.

Here is a Pictorial Representation of InnoDB Architeture so you can see the contents of ibdata1

So, do you have to live with the 10GB file forever? Well, it is certainly a possibility, but there are ways to get rid of it, depending on the server version and your need for availability:

• One approach, if availability is not a problem, and the database is small, would be to dump the whole database into a .sql or .txt with tools like mysqldump or mydumper,stop the server, delete all the files (or at least, all the innodb files), start mysql again and reload the data making sure that innodb_file_per_table is on. Very slow, but very easy.
• If you do not want to suffer such a long process, there are ways to do it fully online, using a slave- you just set it up right from the beginning, and then switch nodes. If you do not have a second box, you can setup a temporary "slave" on the same machine (with the problem of extra resources needed)
• If you use MySQL >= 5.6, you can move tablespaces around with IMPORT TABLESPACE. That would be a way to perform a quick backup, stop and wipe, and restore in binary format (way faster than with .sqls). Some tools like Percona XtraBackup may be useful for that.

This is a very common operation I've been asked about and performed a MySQL DBA. Hope this helps.