Mysql dump table by table in .sql files

Question

Is there a way via console to dump all tables of a database one by one? I want only to import some of them in staging area and some local but not export most of the tables twice (I need once 70% and once 80% of the database).

Write a script to get all the table names from INFORMATION_SCHEMA.TABLES, then dump each to its own file. — Barmar
– Barmar, Commented Mar 13, 2018 at 19:53
I have done it where I use a LIKE to get a list of table, then dump them all into one .sql file. — Napoli
– Napoli, Commented Mar 13, 2018 at 20:06

Bill Karwin · Accepted Answer · 2018-03-13 20:52:59Z

1

mydumper does this by default. It writes two files for every table dumped:

database.table.sql(.gz)
database.table-schema.sql(.gz)

Where database and table are the names of your database and each table.

If you want to do this with mysqldump, it's awkward. Possible solutions:

Use mysqldump with the --tables option to specify a subset of tables.
Use the --tab option to dump CSV files, one per table.
Use sed or perl or some other text filtering tool to separate the tables you want from your single dump file.

answered Mar 13, 2018 at 20:52

Bill Karwin

567k87 gold badges710 silver badges870 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Isengo Over a year ago

Too bad - our hoster will not allow non-ubuntu core repos. Maybe a nice touch for local setups. Export of 1.6GB DB in 7s and import in 5min. Pretty fast.

Collectives™ on Stack Overflow

Mysql dump table by table in .sql files

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related