How to selectively delete all innodb tables in mysql database?

Question

I have a database called av2web that contains 130 MyISAM tables and 20 innodb tables. I want to take mysqldump from these 20 innodb tables and export it to another database as MyISAM tables.

Can you tell me a faster way to achieve this?

Thanks to Pedro Alvarez Espinoza.

Answer 1

If it were a one-time operation, I would do:

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

and make 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    |
+-----------+--------+---------+------------+-

into 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 ENGINE=InnoDBwith ENGINE=MyISAMin in DUMP.sql

/, - :

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

countries foo3 foo5 lol people

Answer 2

, . script, mysqldump, ,

script / mysql

SET SESSION group_concat_max_len = 100000000; -- this is very important when you have lots of table to make sure all the tables get included
SET @userName = 'root'; -- the username that you will login with to generate the dump
SET @databaseName = 'my_database_name'; -- the database name to look up the tables from
SET @extraOptions = '--compact --compress'; -- any additional mydqldump options https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
SET @engineName = 'innodb'; -- the engine name to filter down the table by
SET @filename = '"D:/MySQL Backups/my_database_name.sql"'; -- the full path of where to generate the backup too

-- This query will generate the mysqldump command to generate the backup
SELECT
 CASE WHEN tableNames IS NULL 
            THEN 'No tables found. Make sure you set the variables correctly.' 
      ELSE CONCAT_WS(' ','mysqldump -p -u', @userName, @databaseName, tableNames, @extraOptions, '>', @filename)
      END AS command  
FROM (
    SELECT GROUP_CONCAT(table_name SEPARATOR ' ') AS tableNames 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE table_schema= @databaseName AND ENGINE= @engineName
) AS s;

script mysql backup/dump

SET @restoreIntoDatabasename = @databaseName; -- the name of the new database you wish to restore into
SET @restoreFromFile = @filename; -- the full path of the filename you want to restore from
-- This query will generate the command to use to restore the generated backup into mysql
SELECT CONCAT_WS(' ', 'mysql -p -u root', @restoreIntoDatabasename, '<', @restoreFromFile);