Dumping specific tables in MySQL using mysqldump

Oh Dear monitors your entire site, not just the homepage. We crawl and search for broken pages and mixed content, send alerts when your site is down and notify you on expiring SSL certificates.

Start your free 10 day trial! »

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.