如何有选择地转储mysql数据库中的所有innodb表? [英] How to selectively dump all innodb tables in a mysql database?

问题描述

我有一个名为av2web的数据库，其中包含130个MyISAM表和20个innodb表.我想使用这20个innodb表的mysqldump，并将其作为MyISAM表导出到另一个数据库.

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.

推荐答案

如果这是一次性操作，我会这样做:

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

，然后在DUMP.sql中将ENGINE=InnoDB的所有实例替换为ENGINE=MyISAM后导入

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';

这将返回countries foo3 foo5 lol people

这篇关于如何有选择地转储mysql数据库中的所有innodb表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！