Some helpful hints when using mysqldump

I always have to go to the mysql reference manual for this, so I'm going to blog about it so that I'm fewer clicks away and I don't have to re-read the mysql manual every 3 days.

First, mysqldump is useful for a variety of reasons, primarily backup purposes, but it can also be very useful for dumping a very small subset of data. We do this commonly to dump live data to test databases.

Here is an example:

Say you have a database called 'webmail' with a table called 'address' and you want to dump all of the address data for a particular user ('owner') from the live system to the test system to debug a problem. You would run mysqldump in the following manner:

mysqldump --skip-opt --quick --extended-insert --no-create-info --user=testing -p --where="owner = 'kevin@domain.com'" webmail address > tmp.txt

mysqldump by default uses the parameter "--opt" which means:
--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

Most likely you don't want most of those in this scenario, so that's what all of the options are about.

And to import the data run:

mysql --user=testuser -p webmail < tmp.txt

The most important tip is to make sure mysqldump doesn't add the "DROP TABLE" commands!