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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Converting INNODB to file-per-table?

--0016364d1ca5206bf0049c05668d
Content-Type: text/plain; charset=ISO-8859-1

Dump the entire DB, drop the DB, restore the DB.

On Fri, Feb 11, 2011 at 11:53 AM, Jan Steinman wrote:

> 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=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016364d1ca5206bf0049c05668d--

RE: Converting INNODB to file-per-table?

I wrote an article in www.stackoverflow.com about how to convert absolutely=
every InnoDB table to .ibd and permanently shrink the ibdata1 file=20

http://stackoverflow.com/questions/3927690/howto-clean-a-mys ql-innodb-stora=
ge-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@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-----Original Message-----
From: Jan Steinman [mailto:Jan@Bytesmiths.com]=20
Sent: Friday, February 11, 2011 12:53 PM
To: mysql@lists.mysql.com
Subject: Converting INNODB to file-per-table?

Our incremental backups seem to be filling with instances of ib_logfile1, i=
b_logfile2, and ibdata1.

I know that changing a single byte in a single INNODB table causes these fi=
les to be "touched."

I put "innodb_file_per_table" in /etc/my.cnf, but apparently, that only cau=
ses new databases to be "file per table," and it is older databases that ar=
e being touched in a minor way daily, causing gigabytes to be backed up nee=
dlessly.

Some time ago, someone posted a way to convert existing INNODB tables to "f=
ile 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 500G=
B disks.

Thanks!

----------------
In summary, the idea is to give all of the information to help others to ju=
dge 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 ::::


--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks=
..net


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Converting INNODB to file-per-table?

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=20
>=20
> =
http://stackoverflow.com/questions/3927690/howto-clean-a-mys ql-innodb-stor=
age-engine/4056261#4056261
>=20
> Enjoy !!!
>=20
> Rolando A. Edwards
> MySQL DBA (SCMDBA)
>=20
> 155 Avenue of the Americas, Fifth Floor
> New York, NY 10013
> 212-625-5307 (Work)
> 201-660-3221 (Cell)
> AIM & Skype : RolandoLogicWorx
> redwards@logicworks.net
> http://www.linkedin.com/in/rolandoedwards
>=20
>=20
> -----Original Message-----
> From: Jan Steinman [mailto:Jan@Bytesmiths.com]=20
> Sent: Friday, February 11, 2011 12:53 PM
> To: mysql@lists.mysql.com
> Subject: Converting INNODB to file-per-table?
>=20
> Our incremental backups seem to be filling with instances of =
ib_logfile1, ib_logfile2, and ibdata1.
>=20
> I know that changing a single byte in a single INNODB table causes =
these files to be "touched."
>=20
> 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.
>=20
> Some time ago, someone posted a way to convert existing INNODB tables =
to "file per table," but I am unable to find that.
>=20
> Can someone please post that procedure again?
>=20
> (I also welcome any "you shouldn't be doing it that way" comments, as =
long as they show a better way... :-)
>=20
> This is for a fairly low-volume server, running on a Mac Mini with two =
500GB disks.
>=20
> Thanks!
>=20
> ----------------
> 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 ::::
>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dredwards@logicworks.net
>=20

----------------
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 ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Converting INNODB to file-per-table?

Hi,

You can convert the tables themselves semi-online. Just do
set global innodb_file_per_table=1;
and no a no-operation alter on each table with alter table tablename
engine=innodb;

Note that the global variable is just a default, the currently connectd
threads will use the shared tablespace for table data.

You data will be in .ibd files this way, but you can't reclaim space
used by ibdata1 unless you dump and reload your database.

If you don't do the alter just set innodb_file_per_table on the fly,
your new data will be in .ibd files.

These are your options, and the best is indeed dump and reload, there is
no other way to reclaim space from ibdata1, although, there is a way to
convert your tables to use .ibd files.

Peter Boros

On 02/11/2011 06:49 PM, Jan Steinman wrote:
> 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-mys ql-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@logicworks.net
>> http://www.linkedin.com/in/rolandoedwards
>>
>>
>> -----Original Message-----
>> From: Jan Steinman [mailto:Jan@Bytesmiths.com]
>> Sent: Friday, February 11, 2011 12:53 PM
>> To: mysql@lists.mysql.com
>> 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=redwards@logicworks.net
>>
>
> ----------------
> 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 ::::
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org