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:


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.

Filed under: Code