MySQL – Old ibdata file shall I delete safely

MySQL

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 ?

Does MySQL still uses old shared table space "ibdata" file?
If I delete shared tablespace file "ibdata1" will there be any impact on server ?
Will it cause any problem ?

Best Answer

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

InnoDB Architecture

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.

Related Solutions

MySQL Bug required remedy : [Warning] Invalid (old?) table or database name ‘#sql-‘

There is the presence of a bug in this case for two reasons:

111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9' 
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'

REASON #1 : Any table starting with #sql is a temp table. If these tables are still present in any database folder and the datetime stamp, simply delete them.

REASON #2 : Look carefully at the suffix of each table. #P#p? resembles a partition tag. This would indicate an attempt to create a temp table using partitiions. That's sounds insane. There was bug report on this back in Feb 16, 2006 for MySQL 5.1.7-beta (closed Mar 15, 2006). The bug report is based on trying to do this manually. Is mysql attempting to do this internally ?

IMHO I would upgrade mysql away from MySQL 5.1 up to MySQL 5.5

Mysql – Partitioning and freeing disk space

The status of the bug is labeled as Won't fix and I can understand why in this context.

The right way would have been to run ALTER TABLE tbl_name DROP PARTITION; because the MySQL Documentation on ALTER TABLE Partition Operations says:

DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded.

ALTER TABLE t1 DROP PARTITION p0, p1;

Once you ran ALTER TABLE tbl_name DISCARD TABLESPACE;, the data dictionary should no longer be aware of the physical presence of the orphaned .ibd files. However, since the bug does not reclaim the diskspace, perhaps the data dictionary entries for your tables is intact. Let's find out.

WARNING : You should run service mysql stop and do a physical backup of /var/lib/mysql

First do this: SHOW CREATE TABLE tbl_name\G

Then, run SELECT * FROM tbl_name WHERE (stuff from partition #x) LIMIT 10;

to test the table's usability of each partition.

Once you are confortable with the table usability, you can any of the following:

REMOVE ANY PARTITION YOU WANT

ALTER TABLE tbl_name DROP PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name DROP PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
ALTER TABLE tbl_name2 DROP PARTITION partition-name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

DROP ALL DATA FROM PARTITION BUT KEEP PARTITION

ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

CREATE A BLANK TABLE

CREATE TABLE tbl_name2 LIKE tbl_name;
DROP TABLE tbl_name;
ALTER TABLE tbl_name2 RENAME tbl_name;

Give it a Try !!!

UPDATE 2013-05-20 18:41 EDT

I am glad you were able to drop the partition.

I have some distressing news: It is not possible to shrink ibdata1 without dumping all the data, deleting ibdata1, restarting mysql, and reloading.

Here are my past posts on how to do that safely:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?