Dumping specific tables in MySQL using mysqldump

Profile image of Mattias Geniar

Mattias Geniar, January 17, 2020

Follow me on Twitter as @mattiasgeniar

Here’s a small little trick when using mysqldump: you don’t have to dump an entire database, you can dump individual tables too.

I’ve been using this a lot lately to pass data around between dev <-> prod for testing on a new project, where it’s important I have representative data that is generated on a different server.

$ mysqldump -uroot yourdatabase -t table1 -t table2 > dump.sql

You can pass multiple -t parameters to the mysqldump command. The dump.sql file now contains only the data from table1 and table2.

$ more dump.sql
[...]
LOCK TABLES `table1` WRITE;
/*!40000 ALTER TABLE `table1` DISABLE KEYS */;
INSERT INTO `table1` VALUES (...);

This is especially useful if your database is huge and you just want to play around with the data of a select set of tables.

Or, alternatively, if you have data that’s changing very often (and it’s not dependent on data of other tables), you could use this to take a back-up of that table more frequently.



Want to subscribe to the cron.weekly newsletter?

I write a weekly-ish newsletter on Linux, open source & webdevelopment called cron.weekly.

It features the latest news, guides & tutorials and new open source projects. You can sign up via email below.

No spam. Just some good, practical Linux & open source content.