1

I am using this command to export all database.

mysqldump -u root -p --all-databases > alldb.sql

But it export all tables including MyISAM, However I want to only export and import innodb tables of all databases.

Dewlance
  • 441
  • 4
  • 7
  • Since since is a rather unusual requirement, mysqldump or similar tools do not provide such a feature out of the box. You can (= "have to") list the tables you [want to export](https://dba.stackexchange.com/q/9306) or that you [want to skip](https://stackoverflow.com/q/425158) yourself. The first link contains a way to generate that list automatically (by filtering for, in your case, the engine-column of [information_schema.tables](https://dev.mysql.com/doc/refman/8.0/en/tables-table.html)). – Solarflare Sep 27 '19 at 07:30
  • Easy fix: Make all your tables InnoDB! (except for `mysql.*` tables of course). – Bill Karwin Sep 27 '19 at 14:39
  • Use a `SELECT ... FROM information_schema.TABLES ...` to find the InnoDB tables and build the desired dump(s). – Rick James Oct 08 '19 at 01:13

2 Answers2

0

You have to be careful exporting and importing data from InnoDB tables. InnoDB tables "might" contain foreign keys (child and parent table). If the script executed the child table first, then there's a possibility MySQL might produce an error because the Parent table has not exist yet. You can do well MyISAM since they are not strict, doesn't care about foreign keys.

dodzb
  • 379
  • 2
  • 5
0

You can follow the below steps

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

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

+-----------+--------+---------+------------+-
| Name      | Engine | Version | Row_format |
+-----------+--------+---------+------------+-
| db1       | InnoDB |      10 | Compact    |
| db2       | InnoDB |      10 | Compact    |
| db3       | InnoDB |      10 | Compact    |  |
+-----------+--------+---------+------------+-

to a text editor and convert it to a command

mysqldump -u username --databases db1 db2 db3 > DUMP.sql
Techie
  • 44,706
  • 42
  • 157
  • 243
  • Thank you but Its showing error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'name where engine=innodb' at line 1 – Dewlance Oct 01 '19 at 05:50
  • This statement will give you the same result: ``` select table_name, Engine, Version, Row_format FROM information_schema.tables WHERE Engine='InnoDB' AND table_schema = 'yourdbname'; ``` – gabriel capparelli Aug 03 '21 at 11:09