ibdata1 file needs to be truncated

[Suresh Samuel]

hi,


I am basically in a peculiar situation with respect to the increase in filesize of ibdata1.

to put things in clear my problem is :

1.) I cannot take a mysqldump of the database as their is not enough space available.

2.) Is there any approach that I can take to reduce this file .

The versions that I use are as follows:

Zabbix 1.8.5
mysql mysql Ver 14.12 Distrib 5.0.77, for redhat-linux-gnu (x86_64) using readline 5.1

What I have done ?

I basically tried to use the housekeeper ( Administration==> General)option to not keep the actions and events older than 3 days.


This seems to have reduced the number of rows but does not seem to reduce the size (which I understand is not possible to be retrieved )

Any options for me to retrieve the space ?

Else the mysqldump option suggested by literally all the links/forums (http://dev.mysql.com/doc/refman/5.0/...iguration.html) is a tough option in my case due to the limitation in space.


Thanks,

Suresh

[Suresh Samuel]

Can I use the following sql command to alter the table :

ALTER TABLE HISTORY ENGINE=INNODB

and will this free the space on the file system.


Thanks,

Suresh

[MrKen]

I seriously doubt that doing that will reduce the size of the ibdata1 file.

Why not mount a larger partition for the database.
Shutdown Zabbix and Mysql.
Copy the database across to the new partition (be sure to change the path in my.cnf)
Restart Mysql. Check the logs to make sure everything is working.
Restart Zabbix.

Once you've got the database onto the new partition, then you do the dump to reclaim the space.

MrKen

[Suresh Samuel]

Hi,


The alter command had to be aborted as it started to increase the ibdata1 file :-(

Your suggestion might be an option to try.

Thanks,

Suresh