Mysql: Is it OK to delete ibdata1 under /var/lib/mysql ?

Kunsheng · 09-24-2009, 11:59 AM

The database has been running for couple months and I noticed the file is growing large to 16GB now.

The information is my database is no more than 1GB. I am wondering can I delete that file directly ?

Any idea is well appreciated.

Thanks,

-Kun

BrianK · 09-24-2009, 04:24 PM

You cannot delete that file on a running database.

You can delete it if you backup the db, drop all the tables, stop the db, rm the file, restart the db, then restore all the data.

In the future, you can prevent it from getting so large with your db configuration. See: http://dev.mysql.com/doc/refman/5.0/...iguration.html

chrism01 · 09-24-2009, 07:00 PM

You'll need to drop the DB (ie schema) itself as well, or the data dictionary will get confused.

Smartpatrol · 09-24-2009, 11:36 PM

Kunsheng · 09-25-2009, 08:30 PM

I am trying to backup one database belong to myself in Mysql.

There are other two databases 'mysql' and 'information_schema' created by Mysql when installed.

And 'information_schema' denied my access when I trying to delete that. Is it OK just leave this two database and go ahead to delete that ibdata file ?

Thanks,

-Kun

Smartpatrol · 09-25-2009, 11:21 PM

Kunsheng · 09-26-2009, 01:35 PM

Thanks, smartpatrol, mysql command works to restore the data.

But the database I created took no more than 1GB (according to the script created by mysqldump), and there is no other databases besides 'mysql' and 'information_schema' there, so how could that ibdata take 16GB ? It sounds pretty weird.

Kunsheng · 09-26-2009, 01:36 PM

Thanks, smartpatrol, mysql command works to restore the data.

But the database I created took no more than 1GB (according to the script created by mysqldump), and there is no other databases besides 'mysql' and 'information_schema' there, so how could that ibdata take 16GB ? It sounds pretty weird.

Smartpatrol · 09-26-2009, 09:28 PM