Hi Friends,
I have set innodb_file_per_table option in my.cnf file for one database.
Now there is huge load on this servers.
Is it possible for me to move all these files to a new db on a new machine.
If you can you please let know how it can be done or guide to any url, it
would be of great help.

regards
anandkl

In news:829b199c0703280335p6147599bt9188439964f46d51@ mail.gmail.com,
"Ananda Kumar" <anandkl@gmail.com> wrote:

> Is it possible for me to move all these files to a new db on a new
> machine. If you can you please let know how it can be done or guide
> to any url, it would be of great help.

You cannot do this with copying .ibd files alone, but in most cases you can
move to a different server all the InnoDB information (.idb files, shared
tablespace(s), log files, database parameters regarding InnoDB log files and
tablespaces (count, sizes, filenames)), with appropriate .frm files. This
would of course require the new server not to contain any earlier InnoDB
data or it will be lost.

I don't know if there are any guides covering this particular operation.
Perhaps some InnoDB backup&resotre guides will be helpful as this is
virtually what you want to do.

Maciek

Hi Maciej,
Thanks a lot for this info, so the best may to move tables from one db to
another db on a different machine is to use mysqldump, right?

regards
anandkl


On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
>
> In news:829b199c0703280335p6147599bt9188439964f46d51@ mail.gmail.com,
> "Ananda Kumar" <anandkl@gmail.com> wrote:
>
> > Is it possible for me to move all these files to a new db on a new
> > machine. If you can you please let know how it can be done or guide
> > to any url, it would be of great help.
>
> You cannot do this with copying .ibd files alone, but in most cases you
> can
> move to a different server all the InnoDB information (.idb files, shared
> tablespace(s), log files, database parameters regarding InnoDB log files
> and
> tablespaces (count, sizes, filenames)), with appropriate .frm files. This
> would of course require the new server not to contain any earlier InnoDB
> data or it will be lost.
>
> I don't know if there are any guides covering this particular operation.
> Perhaps some InnoDB backup&resotre guides will be helpful as this is
> virtually what you want to do.
>
> Maciek
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

On Wed, 28 Mar 2007, Ananda Kumar wrote:

> Hi Maciej,
>
> Thanks a lot for this info, so the best may to move tables from one db to
> another db on a different machine is to use mysqldump, right?
>

Not necessarily, especially if you are moving to a fresh, empty server.
Each solution has its advantages. For example, copying binary InnoDB data
files may be much faster if your database is large as export to and import
from text files may take a very long time. Importing from text files
however results in optimized data which can be highly fragmented in the
old server's datafiles, and the import is not affected by any
(incompatible) file format changes which can happen from time to time
between two different versions of MySQL. etc.

You have to decide what is good for your data, what you can and can't do.
I'd say using dump&import method is the simplest and the safest way to
go.

Maciek


> On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
>>
>> In news:829b199c0703280335p6147599bt9188439964f46d51@ mail.gmail.com,
>> "Ananda Kumar" <anandkl@gmail.com> wrote:
>>
>> > Is it possible for me to move all these files to a new db on a new
>> > machine. If you can you please let know how it can be done or guide
>> > to any url, it would be of great help.
>>
>> You cannot do this with copying .ibd files alone, but in most cases you
>> can
>> move to a different server all the InnoDB information (.idb files, shared
>> tablespace(s), log files, database parameters regarding InnoDB log files
>> and
>> tablespaces (count, sizes, filenames)), with appropriate .frm files. This
>> would of course require the new server not to contain any earlier InnoDB
>> data or it will be lost.
>>
>> I don't know if there are any guides covering this particular operation.
>> Perhaps some InnoDB backup&resotre guides will be helpful as this is
>> virtually what you want to do.
>>
>> Maciek
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>>
>>
>

Hi Maciej,
When you say copying binary Innodb data.
Can you please elobrate on this.

regards
anandkl


On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
>
> On Wed, 28 Mar 2007, Ananda Kumar wrote:
>
> > Hi Maciej,
> >
> > Thanks a lot for this info, so the best may to move tables from one db
> to
> > another db on a different machine is to use mysqldump, right?
> >
>
> Not necessarily, especially if you are moving to a fresh, empty server.
> Each solution has its advantages. For example, copying binary InnoDB data
> files may be much faster if your database is large as export to and import
> from text files may take a very long time. Importing from text files
> however results in optimized data which can be highly fragmented in the
> old server's datafiles, and the import is not affected by any
> (incompatible) file format changes which can happen from time to time
> between two different versions of MySQL. etc.
>
> You have to decide what is good for your data, what you can and can't do.
> I'd say using dump&import method is the simplest and the safest way to
> go.
>
> Maciek
>
>
> > On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
> >>
> >> In news:829b199c0703280335p6147599bt9188439964f46d51@ mail.gmail.com,
> >> "Ananda Kumar" <anandkl@gmail.com> wrote:
> >>
> >> > Is it possible for me to move all these files to a new db on a new
> >> > machine. If you can you please let know how it can be done or guide
> >> > to any url, it would be of great help.
> >>
> >> You cannot do this with copying .ibd files alone, but in most cases
> you
> >> can
> >> move to a different server all the InnoDB information (.idb files,
> shared
> >> tablespace(s), log files, database parameters regarding InnoDB log
> files
> >> and
> >> tablespaces (count, sizes, filenames)), with appropriate .frm files.
> This
> >> would of course require the new server not to contain any earlier
> InnoDB
> >> data or it will be lost.
> >>
> >> I don't know if there are any guides covering this particular
> operation.
> >> Perhaps some InnoDB backup&resotre guides will be helpful as this is
> >> virtually what you want to do.
> >>
> >> Maciek
> >>
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
> >>
> >>
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

On Wed, 28 Mar 2007, Ananda Kumar wrote:

> Hi Maciej,
> When you say copying binary Innodb data.
> Can you please elobrate on this.

I meant the .ibd files, shared tablespace files, and so on.

Maciek

Okie, but in this case, if the shared tablespace has other database, then i
will be moving that also, which i may not require.

regards
anandkl


On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
>
> On Wed, 28 Mar 2007, Ananda Kumar wrote:
>
> > Hi Maciej,
> > When you say copying binary Innodb data.
> > Can you please elobrate on this.
>
> I meant the .ibd files, shared tablespace files, and so on.
>
> Maciek
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>

Hi Maciej,
What i meant to say was. Say if i have two database A & B, and if i want to
move only database A, then the shared tablespace will also have info about
database B, and when i try to open the database A in the new machine, will
it not throw any error, saying that database B is not present.

regards
anandkl


On 3/28/07, Ananda Kumar <anandkl@gmail.com> wrote:
>
> Okie, but in this case, if the shared tablespace has other database, then
> i will be moving that also, which i may not require.
>
> regards
> anandkl
>
>
> On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
> >
> > On Wed, 28 Mar 2007, Ananda Kumar wrote:
> >
> > > Hi Maciej,
> > > When you say copying binary Innodb data.
> > > Can you please elobrate on this.
> >
> > I meant the .ibd files, shared tablespace files, and so on.
> >
> > Maciek
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
> >
> >
>

In news:829b199c0703280610q41e49ba2v2870bd641cb511bc@ mail.gmail.com,
"Ananda Kumar" <anandkl@gmail.com> wrote:

> What i meant to say was. Say if i have two database A & B, and if i
> want to move only database A, then the shared tablespace will also
> have info about database B, and when i try to open the database A in
> the new machine, will it not throw any error, saying that database B
> is not present.

Of course you cannot move only a part of the InnoDB data (e.g. single
database). Although you can still achieve the desired result. What you can
do is to migrate all the InnoDB data + .frm (containing all the databases,
that is A and B), start both servers and then drop database A on the old
server and drop database B on the new server.

Maciek

sounds like a good solution.

regards
anandkl


On 3/28/07, Maciej Dobrzanski <reg.mysql@posterus.com> wrote:
>
> In news:829b199c0703280610q41e49ba2v2870bd641cb511bc@ mail.gmail.com,
> "Ananda Kumar" <anandkl@gmail.com> wrote:
>
> > What i meant to say was. Say if i have two database A & B, and if i
> > want to move only database A, then the shared tablespace will also
> > have info about database B, and when i try to open the database A in
> > the new machine, will it not throw any error, saying that database B
> > is not present.
>
> Of course you cannot move only a part of the InnoDB data (e.g. single
> database). Although you can still achieve the desired result. What you can
> do is to migrate all the InnoDB data + .frm (containing all the databases,
> that is A and B), start both servers and then drop database A on the old
> server and drop database B on the new server.
>
> Maciek
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=anandkl@gmail.com
>
>