3

I made a mistake.

I have a huge database of MyISAM tables that I was playing with converting [a copy of] to InnoDB. I didn't set innodb_file_per_table=1 before doing so.

I know that safely shrinking ibdata1 requires all databases to be nuked then reloaded from an SQL dump.

But, if I don't care about my InnoDB tables any more, can I remove the ibdata1 file without breaking the original database that only contains MyISAM tables? Dropping it and reloading it from an SQL dump is kind of possible, but since the database is approaching 250GB in size, it will take a lot longer than I'd like.

$ mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (i686) using readline 5.1
$ cat /etc/issue
Red Hat Enterprise Linux Server release 6.5 (Santiago)

migrated from stackoverflow.com Jun 21 '16 at 17:07

This question came from our site for professional and enthusiast programmers.

  • If you have the original myd and myi files for the myisam tables (so the conversion to innodb was done on a copy basis), then you can drop the innodb data files without affecting the myisam data. – Shadow Jun 20 '16 at 16:05
  • The docs do suggest that the "remove everything" trick actually need only be performed on InnoDB tables, but it's not as explicit as I'd like. Having done so, will I be able to create new InnoDB databases in this MySQL instance in the future? It will literally only be the data I've dropped that is affected in any way? – Lightness Races with Monica Jun 20 '16 at 16:06
  • Yes, you can create new innodb tables (not databases) if you drop the existing files. The procedure you linked describes the steps. But be careful not to remove the frm files of the myisam tables, otherwise you will loose your myisam data. – Shadow Jun 20 '16 at 16:20
3
+400

Since you are using MySQL 5.1.73, you can blow away the ibdata1 file without worries.

First, let's look inside ibdata1 (picture created by Percona CTO Vadim Tkachenko)

InnoDB Plumbing

STEP 01 : Make sure there is no InnoDB

Run the following

SELECT
    table_schema,table_name,
    (data_length+index_length)/POWER(1024,3) table_sizegb
FROM information_schema.tables
WHERE
    engine='InnoDB' AND
    table_schema NOT IN ('information_schema','performance_schema','mysql');

If you get nothing, you can proceed.

If you do get something, either drop those tables, convert them to MyISAM, or mysqldump them to reload later.

Assuming datadir is /var/lib/mysql

STEP 02 : Configure innodb_file_per_table

You should add this to `my.cnf

[mysqld]
innodb_file_per_table=ON

This will allow future for CREATE TABLEs where you want .ibd files

STEP 03 : Flush everything from InnoDB

SET GLOBAL innodb_fast_shutdown = 0;

STEP 04 : Shutdown mysql

service mysql stop

STEP 05 : Delete ibdata and the redo logs

cd /var/lib/mysql
rm -rf ibdata1
rm -rf ib_logfile0
rm -rf ib_logfile1

STEP 06 : Start mysql

service mysql start

This will regenerate ibdata1, ib_logfile0, and ib_logfile1

NOTE: MyISAM tables are completely unaffected. Why ?

Suppose you have a table mytable in the mydb database.

The files for that one table are the following:

  • /var/lib/mysql/mydb/mytable.frm
  • /var/lib/mysql/mydb/mytable.MYD
  • /var/lib/mysql/mydb/mytable.MYI

The InnoDB file ibdata1 is usually located in /var/lib/mysql. Therefore, all MyISAM tables are safely tucked away in their respect database folders.

CAVEAT for MyISAM only users

If you know for a certainty that you will never use InnoDB, you could add this parameter to my.cnf

[mysqld]
skip-innodb

That way, when you start up mysqld in STEP 06, the process will not regenerate ibdata1, ib_logfile0, and ib_logfile1.

CAVEAT FOR MySQL 5.6/5.7 users

In MySQL 5.6, there are 5 tables in the mysql schema (/var/lib/mysql/mysql) that are InnoDB. MySQL 5.7 has 19.

Just run this query to see what those tables are

SELECT table_name FROM information_schema.tables
WHERE engine='InnoDB' and table_schema='mysql';

See my old post InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 and the MySQL Documentation.

In this instance, you would mysqldump the mysql schema, blow away the InnoDB files, and manually delete the .ibd file in /var/lib/mysql/mysql. Then, reload the mysql schema from that dump.

1

MyISAM tables have many problems and limitations, but one very convenient aspect to them in cases like this, is that the underlying .frm (table metadata) and MYI/MYD index and data files can be easily copied around since there are no transaction logs to worry about.

If you don't care about the InnoDB tables at all, you could avoid the need to shrink the ibdata file by shutting down your DB (or at least do a flush tables with read lock), copy the MyISAM-based files to some other safe location, and blow away the database containing the large ibdata once you've confirmed that you have copied the data successfully.

Once you place the MyISAM files back into the schema directory for your database (make sure to set the same permissions), they will appear when you do SHOW TABLES.

  • Copying 250GB is too slow. They are backed up nightly but this takes like 10 hours on our current hardware (I know -.-). I can't take the DB down for that long. I was hoping to be able to simply destroy the InnoDB store without needing to do anything to the MyISAM tables at all. Shadow suggested in the comments that this would be possible. – Lightness Races with Monica Jun 23 '16 at 9:48
  • The problem is that "destroy the InnoDB store" is not something that you can do, officially. What I'm suggesting and shadow's comment are basically the same - if you don't want to copy, you could also just move the files to another part of the filesystem and move them back once you've recreated the datadir. This avoids the need to go through the procedure here to reclaim the space from the system tablespace. – atomic77 Jun 23 '16 at 12:19
  • I guess what I'm wondering is why it's necessary then to move the MyISAM files at all. Why not just shut down the service, remove the target database's directory and the ibdata1 file, then start up the service? I'm trying to understand why it's necessary to move/copy the MyISAM files at any stage of this process. – Lightness Races with Monica Jun 23 '16 at 13:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.