List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:February 11 2011 6:49pm
Subject:Re: Converting INNODB to file-per-table?
View as plain text  
Thanks, Rolando!

It's kind of a scary procedure (dump, drop, reload) that involves significant down-time,
but I guess it's necessary.

On 11 Feb 11, at 10:24, Rolando Edwards wrote:

> I wrote an article in www.stackoverflow.com about how to convert absolutely every
> InnoDB table to .ibd and permanently shrink the ibdata1 file 
> 
>
> http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261
> 
> Enjoy !!!
> 
> Rolando A. Edwards
> MySQL DBA (SCMDBA)
> 
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> 201-660-3221 (Cell)
> AIM & Skype : RolandoLogicWorx
> redwards@stripped
> http://www.linkedin.com/in/rolandoedwards
> 
> 
> -----Original Message-----
> From: Jan Steinman [mailto:Jan@stripped] 
> Sent: Friday, February 11, 2011 12:53 PM
> To: mysql@stripped
> Subject: Converting INNODB to file-per-table?
> 
> Our incremental backups seem to be filling with instances of ib_logfile1,
> ib_logfile2, and ibdata1.
> 
> I know that changing a single byte in a single INNODB table causes these files to be
> "touched."
> 
> I put "innodb_file_per_table" in /etc/my.cnf, but apparently, that only causes new
> databases to be "file per table," and it is older databases that are being touched in a
> minor way daily, causing gigabytes to be backed up needlessly.
> 
> Some time ago, someone posted a way to convert existing INNODB tables to "file per
> table," but I am unable to find that.
> 
> Can someone please post that procedure again?
> 
> (I also welcome any "you shouldn't be doing it that way" comments, as long as they
> show a better way... :-)
> 
> This is for a fairly low-volume server, running on a Mac Mini with two 500GB disks.
> 
> Thanks!
> 
> ----------------
> In summary, the idea is to give all of the information to help others to judge the
> value of your contribution; not just the information that leads to judgement in one
> particular direction or another. -- Richard P. Feynman
> :::: Jan Steinman, EcoReality Co-op ::::
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

----------------
You know you have reached perfection of design not when you have nothing more to add, but
when you have nothing more to take away. -- Antoine de Saint-Exupery
:::: Jan Steinman, EcoReality Co-op ::::

Thread
Converting INNODB to file-per-table?Jan Steinman11 Feb
  • Re: Converting INNODB to file-per-table?Johnny Withers11 Feb
  • RE: Converting INNODB to file-per-table?Rolando Edwards11 Feb
    • Re: Converting INNODB to file-per-table?Jan Steinman11 Feb
      • Re: Converting INNODB to file-per-table?petya11 Feb