Moving the ibdata1 file

[fuzzybr80]

I am using MySQL 5.0 with a number of innodb tables whose ibdata files
are growing quite quickly and filling up the /var partition (file is
/var/mysql/ibdata1). Earlier on I followed instructions in the docs to
create a new ibdata file on a bigger partition
/disk2/var/mysql/ibdata2) and set that to autoextend instead.

However I would like to reclaim the disk space on /var by moving the
existing ibdata1 file to /disk2/var/mysql/ibdata1. The docs don't
mention it and I think its just stopping the server, moving that file
across, changing the my.cnf file and restarting the server. However its
a production environment so I thought it best if someone on the list
can confirm this for me.

Can I also reduce the space used by the ibdata files? I read that they
contain all the past history logs of the innodb tables - can I
reduce/remove this somewhat redundant history information by converting
all my tables to MyISAM and then back to innodb? Or do I need to delete
and recreate the whole schema, reimporting all the data?

Thanks and Best Regards,
CW

[Bill Karwin]

fuzzybr80 wrote:

Can I also reduce the space used by the ibdata files?

I don't think the ibdata file contains logs. There are separate files
for logs. But the ibdata file doesn't shrink as a result of DELETE or
DROP TABLE operations. It just marks the space as unused, and future
data will reuse that space if possible before expanding the file again.

The only way to reduce the size of the ibdata file is to move the data
elsewhere (convert to MyISAM, or back up, etc.), shut down MySQL, and
manually remove the ibdata file. When your restart MySQL, it'll create
a minimally-sized ibdata file.

Regards,
Bill K.

[Chander Ganesan]

You are correct.

You should first stop the server (I'd also perform a backup...), then
move the files to their new location - be sure to preserve information
about the size of files that aren't auto-extending...or MySQL will
complain when you try to restart it. Modify the my.cnf file so that it
lists the new locations of the files, then re-start the server.

Unfortunately, once an IBDATA file grows, it won't/can't be shrunk :-(
.. The best you can do (to minimize downtime) is replicate your data
over to a new server, make sure both systems are "in sync", and then
shut 'em both down and move the replicas ibdata files over to the
original server (remember to move all the iblogfile's , etc. as well).
Alternately, you can just perform a "switchover" to the replica server
(and then re-build the master and switch back). In any case, it's a
pain in the rear...

I would not recommend changing the table types back and forth -
especially in a running system (I don't remember exactly, but I don't
think that constraints will be preserved - they definitely won't be
enforced for the duration of the change - when you switch back and
forth). If anything, you can convert the server to tablespace per
table, rebuild all the tables, export all the tablespaces, stop the
server, remove all of the ibdata/Iblogfile's, restart the server, and
re-import the tablespaces. - also a pain in the rear.

PostgreSQL does a much better job of handling table data storage ...
and if that's a major concern it might be a better fit for your
application.

If you need MySQL (or PostgreSQL) training, we offer a wide range of
both. (http://www.otg-nc.com)

--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC 27560
Phone: [phone]
fuzzybr80 wrote:

I am using MySQL 5.0 with a number of innodb tables whose ibdata files
are growing quite quickly and filling up the /var partition (file is
/var/mysql/ibdata1). Earlier on I followed instructions in the docs to
create a new ibdata file on a bigger partition
/disk2/var/mysql/ibdata2) and set that to autoextend instead.

However I would like to reclaim the disk space on /var by moving the
existing ibdata1 file to /disk2/var/mysql/ibdata1. The docs don't
mention it and I think its just stopping the server, moving that file
across, changing the my.cnf file and restarting the server. However its
a production environment so I thought it best if someone on the list
can confirm this for me.

Can I also reduce the space used by the ibdata files? I read that they
contain all the past history logs of the innodb tables - can I
reduce/remove this somewhat redundant history information by converting
all my tables to MyISAM and then back to innodb? Or do I need to delete
and recreate the whole schema, reimporting all the data?

Thanks and Best Regards,
CW