473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Moving the ibdata1 file

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

Jun 4 '06 #1
2 38858
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.
Jun 4 '06 #2
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: 877-258-8987/919-463-0999
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


Jun 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Pyung Yoon | last post by:
Hi, I have a strange problem with innodb tables. I have about 10 innodb tables, a few of which can have tens of thousands of entries. These huge tables are clean up regularly. But the size of...
4
by: Dave | last post by:
Hello all, The scheme shown below to move a text file's contents into a std::string works with one exception: it drops the carriage return and line feed characters. How may I, in a...
1
by: Terry On Windigo | last post by:
I want to do one of two things and I not sure which until I know the difficulty level (knowing a little asp but not alot) of each. I am helping in the office and we will have several small access...
5
by: Dylan Parry | last post by:
Hi, I'm trying to upload some files via a form, and I have managed so far to save the files on the server in a temporary directory using: HttpPostedFile.SaveAs(string filename); This is a...
1
by: Ale | last post by:
hi, i've a problem, i've a web services where i define a function that start an executable on the server. this Executable must get information from some file then put the data in a DB. after that...
0
by: John Yale | last post by:
I am using an XmlDocument to save some data. Periodically I add new data and save the XmlDocument to a file: Me.fXmlDoc.Save(Me.fFilename) When I have added all the data I need, I want to move...
0
by: Arjen | last post by:
Hi, I want to move a file. Here is my code: files.MoveTo(dirs.ToString()); Variable files represents a file in the current directory (of the executable). Variable dirs represents a...
1
by: rush81 | last post by:
i'm trying to create an ssis package for renaming and moving the file. It gives error at the file system task: Error: An error occurred with the following error message: "Could not find file...
2
by: K Viltersten | last post by:
I've found a couple of examples for moving a file on a FTP server to another location but all of them required something like this. char command = "PASV\r\n".ToCharArray(); Byte cmd =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.