How to selectively dump all innodb tables in a mysql database?

Question

Hi,

I have a database called av2web, which contains 130 MyISAM tables and 20 innodb tables. I wanna take mysqldump of these 20 innodb tables, and export it to another database as MyISAM tables.

Can you tell me a quicker way to achieve this?

Thanks Pedro Alvarez Espinoza.

Answer 1

If this was an one-off operation I'd do:

use DB;
show table status name where engine='innodb';

and do a rectangular copy/paste from the Name column:

+-----------+--------+---------+------------+-
| Name      | Engine | Version | Row_format |
+-----------+--------+---------+------------+-
| countries | InnoDB |      10 | Compact    |
| foo3      | InnoDB |      10 | Compact    |
| foo5      | InnoDB |      10 | Compact    |
| lol       | InnoDB |      10 | Compact    |
| people    | InnoDB |      10 | Compact    |
+-----------+--------+---------+------------+-

to a text editor and convert it to a command

mysqldump -u USER DB countries foo3 foo5 lol people > DUMP.sql

and then import after replacing all instances of ENGINE=InnoDB with ENGINE=MyISAM in DUMP.sql

If you want to avoid the rectangular copy/paste magic you can do something like:

use information_schema;
select group_concat(table_name separator ' ') from tables 
    where table_schema='DB' and engine='innodb';

which will return countries foo3 foo5 lol people