Actually, that will not reduce the size of the ibdata1 file at all. Here is a GUARANTEED WAY to shrink that ibdata1 file, which YOU WILL NOW HAVE TO REPEAT EVER AGAIN !!!
1. Perform mysqldump of all databases, routines and triggers from the mysql server to /root/MyData.sql 2. Drop all databases from the mysql server 3. Add 'innodb_file_per_table' to [mysqld] section of /etc/my.cnf 4. Do 'service mysql stop' 5. Do 'rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_log*' 6. Do 'service mysql start' (ibdata1, ib_logfile 0, and ib_logfile1 are rebuilt) 7. Reload mysql sever from /root/MySQLData.sql The only way ibdata1 will grow is with internal data dictionary information on InnoDB tables. Data and Index Info will all reside in its own tablespace on a per-table basis. When you run OPTIMIZE TABLE on an InnoDB table in its own tablespace, that table will actually shrink. Example (without innodb_file_per_table) CREATE TABLE mydb.mytable (...) ENGINE=InnoDB; Does two things 1. creates mytable.frm in /var/lib/mysql/mydb folder 2. writes table index, and data dictionary info about the table in /var/lib/mysql/ibdata1. 'OPTIMIZE TABLE mydb.mytable;' will actually append the entire mytable table in contiguous blocks or pages to ibdata1, and then perform 'ANALYZE TABLE mydb.mytable;' Example (with innodb_file_per_table) CREATE TABLE mydb.mytable (...) ENGINE=InnoDB; Does three things: 1. creates mytable.frm in /var/lib/mysql/mydb folder 2. creates mytable.ibd in /var/lib/mysql/mydb folder 3. writes data dictionary info about the table in /var/lib/mysql/ibdata1 'OPTIMIZE TABLE mydb.mytable;' Will actually shrink mytable.ibd and perform 'ANALYZE TABLE mydb.mytable;' ibdata1 NEVER GROWS due to mytable table !!! They only way to make ibdata1 grow it to create lots of InnoDB tables, which will just data dictionary info for every InnoDB table. No more data and no more index info. I once did this a job where the company had a 50 GB ibdata1 file, which collapsed quickly to 500 MB (> 0.5 GB) due to wasted space from transactions and old data pages and old index pages. Give it a try and let me know if this worked. I know it will work. -----Original Message----- From: Chandru [mailto:[EMAIL PROTECTED] Sent: Thursday, December 04, 2008 7:10 AM To: Shachi Govil; Jonas Genannt Cc: Ananda Kumar; mysql@lists.mysql.com Subject: Re: Alter Table - InnoDB Hi jones, Innodb does not release the space unless you optimize the tables. To dot that you need to run dummy alter on all tables, by issuing "Alter table <table name> engine=InnoDB" but the space shall not regaing unless you start the table with "innodb_file_per_table" option. Then if you run the alter you shall gain space that was occupied. But in your current scenario, there is space that is occupied that is not shall not get released from OS even you run alter on the table. The space shall be available in the table space "ibdata". If you have a option of reimporting then take a dump, reimport the same with "innodb_file_per_table" option enabled. Regards, Chandru. www.mafiree.com On Thu, Dec 4, 2008 at 4:19 PM, Shachi Govil <[EMAIL PROTECTED]>wrote: > I thought you always have to go to the physical location and delete the tmp > files manually. These are created in tmp folder. > > I am not sure if restarting helps... > > Regards, > Shachi Govil > ----- Original Message ----- From: "Ananda Kumar" <[EMAIL PROTECTED]> > To: "Jonas Genannt" <[EMAIL PROTECTED]> > Cc: <mysql@lists.mysql.com> > Sent: Thursday, December 04, 2008 4:09 PM > Subject: Re: Alter Table - InnoDB > > > > So, now u dont have free space in your file system. >> Is this a production db. >> I think, restarting the db, should not cause any harm. Which version of >> mysql. >> >> regards >> anandkl >> >> >> On 12/4/08, Jonas Genannt <[EMAIL PROTECTED]> wrote: >> >>> >>> Hi Ananda, >>> >>> > Since u have cancled the job, those in-complete temp files can be >>> > deleted from the file system. >>> >>> ok - but I'm using InnoDB. The IBdata file is bumped up. There are no >>> temp files on the database directory. >>> >>> Greets, >>> Jonas >>> >>> >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
