hi all,
I have backup all the database files(innodb engine).
Now I want to recover it as myisam engine offline.
Any suggestions?
--
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
At 07:53 AM 7/15/2009, you wrote:
>hi all,
>
>I have backup all the database files(innodb engine).
>Now I want to recover it as myisam engine offline.
>
>Any suggestions?
Why can't you copy the InnoDb files to another machine and open the
database with another MySQL server? (You could do it on your current server
but it will be CPU & disk intensive).
Then do a:
create table MyTable1 engine=MYISAM select * from InnoTable1;
If these tables are large, then you could use:
create table MyTable1 engine=MYISAM select * from InnoTable1 limit 0;
insert into MyTable1 select * from InnoTable1 limit 0,100000;
insert into MyTable1 select * from InnoTable1 limit 100000,100000;
to import just 100k rows at a time.
Now build the indexes for MyTable1.
Mike
--
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
Or you could just do an export with
mysqldump --single-transaction > backup.sql
Then replace all the ENGINE=3DInnoDB with ENGINE=3DMyIsam in the .sql file.
-----Original Message-----
From: mos [mailto:mos99@fastmail.fm]
Sent: Wednesday, July 15, 2009 11:58 AM
To: mysql@lists.mysql.com
Subject: Re: Any tool convert ibdata1/ib_logfile0/ib_logfile1 files to myis=
am data offline
At 07:53 AM 7/15/2009, you wrote:
>hi all,
>
>I have backup all the database files(innodb engine).
>Now I want to recover it as myisam engine offline.
>
>Any suggestions?
Why can't you copy the InnoDb files to another machine and open the
database with another MySQL server? (You could do it on your current server
but it will be CPU & disk intensive).
Then do a:
create table MyTable1 engine=3DMYISAM select * from InnoTable1;
If these tables are large, then you could use:
create table MyTable1 engine=3DMYISAM select * from InnoTable1 limit 0;
insert into MyTable1 select * from InnoTable1 limit 0,100000;
insert into MyTable1 select * from InnoTable1 limit 100000,100000;
to import just 100k rows at a time.
Now build the indexes for MyTable1.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com
The information contained in this transmission may contain privileged and c=
onfidential information. It is intended only for the use of the person(s) n=
amed above. If you are not the intended recipient, you are hereby notified =
that any review, dissemination, distribution or duplication of this communi=
cation is strictly prohibited. If you are not the intended recipient, pleas=
e contact the sender by reply email and destroy all copies of the original =
message.
--
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
On Wed, Jul 15, 2009 at 11:58 AM, mos
>> I have backup all the database files(innodb engine).
>> Now I want to recover it as myisam engine offline.
>> Any suggestions?
>
> Why can't you copy the InnoDb files to another machine and open the datab=
ase
> with another MySQL server? (You could do it on your current server but it
> will be CPU & disk intensive).
>
> =A0Then do a:
> create table MyTable1 engine=3DMYISAM select * from InnoTable1;
>
> If these tables are large, then you could use:
> create table MyTable1 engine=3DMYISAM select * from InnoTable1 limit 0;
> insert into MyTable1 select * from InnoTable1 limit 0,100000;
> insert into MyTable1 select * from InnoTable1 limit 100000,100000;
> to import just 100k rows at a time.
> Now build the indexes for MyTable1.
Could do it easier I would think:
ALTER TABLE t1 ENGINE =3D MyISAM
Repeat for each table. =A0Script it if it's more than a few tables.
Note that foreign keys will break if your InnoDB table uses them.
--
Regards... =A0 =A0 =A0Todd
--
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
At 04:52 PM 7/16/2009, Todd Lyons wrote:
>On Wed, Jul 15, 2009 at 11:58 AM, mos
> >> I have backup all the database files(innodb engine).
> >> Now I want to recover it as myisam engine offline.
> >> Any suggestions?
> >
> > Why can't you copy the InnoDb files to another machine and open the
> database
> > with another MySQL server? (You could do it on your current server but it
> > will be CPU & disk intensive).
> >
> > Then do a:
> > create table MyTable1 engine=MYISAM select * from InnoTable1;
> >
> > If these tables are large, then you could use:
> > create table MyTable1 engine=MYISAM select * from InnoTable1 limit 0;
> > insert into MyTable1 select * from InnoTable1 limit 0,100000;
> > insert into MyTable1 select * from InnoTable1 limit 100000,100000;
> > to import just 100k rows at a time.
> > Now build the indexes for MyTable1.
>
>Could do it easier I would think:
>ALTER TABLE t1 ENGINE = MyISAM
Yes, I had thought of that but I find it is safer if he copies the data
from an InnoDb table to a MyISAM table. That way he can abort the operation
without any fear of having his InnoDb table corrupted. With the Limit
clause he can also do it in sections and it gives him more control of the
operation. At least that's the way I'd do it.
Mike
>Repeat for each table. Script it if it's more than a few tables.
>Note that foreign keys will break if your InnoDB table uses them.
>
>--
>Regards... Todd
--
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