In a recent post we talked about using the ever more awesome mysqldump command to dump the schema or structure of a table, but not the data. For example, if you want to create a copy of the database with new data.

I thought I might expand on mysqldump and talk about some other useful things you can do with your data. Let’s start by putting this into perspective. Since we are talking about the mysqldump command, obviously whatever we are doing is going to focus on getting data out of the database. Hence the “dump” portion of the command. This can be to backup the database, to clone it or move it, or in development/test/verification situations where you are taking part of the data and doing something with it.

I am sure that anyone who has been working with MySQL much at all is familiar with mysqldump for dumping the entire schema and contents of the database, this makes a good backup file after all. It makes a file full of SQL commands that can recreate your database in seconds.

But what if you want to refine that data dump a little bit? Well, here are a few ways that our friend mysqldump can help.

Dump just one table

This can be easily down with the mysqldump command by telling it not only the database, but also the table to dump. Then mysqldump will only dump that one table. The format looks like this:

mysqldump -u user -p databasename tablename

So, if you wanted to dump the table balls from the database sportsequip as root, it would look like this:

mysqldump -u root -p sportsequip balls

Naturally you can send it to a file like so:

mysqldump -u root -p sportsequip balls > balls.sql

Dump using a query

Let’s say you have that list of all balls in the sports equipment database, but what you really want is all the information on soccer balls, not all balls in general. Yes, you can parse the text file with awk or PERL or something similar, but that takes time to write and get right and all that. So, in that case the easiest thing would be to dump only the data you want by adding some SQL statements to the mysqldump command. The format looks like this:

mysqldump -u user -p databasename tablename --where "sql=whatever"

So, using the same example above, you could do something like this:

mysqldump -u root -p sportsequip balls --where "type=soccer"

That would only pull the rows from the balls table where the type descriptor equals soccer. In other words, it would only list the soccer balls. You can send it to a text file the same way as above.

This is a quick touch on just a couple of the powerful features of the mysqldump command. I might go more in depth later, but this is a good start. I hope this helps you in your daily data management duties!