473,231 Members | 1,477 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,231 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 38824
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: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...

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.