Dumping specific tables in MySQL using mysqldump

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 table1 table2 > dump.sql

You list the tables you want right after the database name, space-separated. The dump.sql file now contains only table1 and table2.

If you want just the data and no CREATE TABLE statements (like the output below), add -t (short for --no-create-info):

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

$ 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.