Bash Scripting FTW
I'm feeling nerdish today… Here's a script to run mysqldump for a few tables on a remote server and load it into your local mysql db on the fly:
#!/bin/bash DB=$1 shift TABLES=$* ssh -C user@remoteserver \ "mysqldump -u username --password='pw' --skip-lock-tables $DB $TABLES > /tmp/load.sql &&; gzip -c /tmp/load.sql" \ > /tmp/load.sql.gz \ && gunzip -c /tmp/load.sql.gz | mysql -u user $DB
$DB is the first parameter specified on the command-line. $TABLES is everything else, which should either be a list of the tables you want, or leave it blank to dump the whole db. Then we cat the output into /tmp/load.sql. You could actually dump it to STDOUT and save a step, but putting it into a file removes MySQL from the equation. If you have a lot of data, MySQL will block while it's being sent to your computer. A smarter person than I probably has another way of taking care of this problem.
"gzip -c /tmp/load.sql" compresses the file and dumps it to STDOUT, sending it down the pipe to your computer.
Meanwhile, back on your local computer…
The output is stored in /tmp/load.sql.gz. This is also technically not needed, you could be clever and put it straight into MySQL, but I find it handy to keep a copy of the SQL script around in case I want to run it more than once. "gunzip -c /tmp/load.sql.gz" decompresses the SQL file and prints it to STDOUT.
"mysql -u user $DB" loads it into the MySQL database named '$DB' And that's it!
Code on its own page here.