Remote MySQL Tips

Last night I wanted to dump all the data from a MySQL table, edit it in Excel and then reload it back into the table. MySQL provides tools like mysqldump and syntax like SELECT ... INTO OUTFILE ... - however, they assume you have local access to the filesystem. Unfortunately, I didn't have this kind of access.

After a bit of poking around, I came up with the following commands to bulk extract and load remotely.

Remote Table Dump

--
-- Run from a shell (cygwin works) prompt
--
echo 'select <col1>, <col2>, ... from <table>' | \
  mysql  -h<remotehost> -u<user> -p<password> \
  <database> > outfile.txt

Note: This command is run from a shell command prompt, not from inside the database. In this case, outfile.txt will contain a tab separated file with the results of the query that was echo'ed into the mysql command.

Remote Table Restore

--
-- Run at a MySQL command prompt, assuming you're in the right database
--
LOAD DATA LOCAL INFILE 'outfile.txt'  
   INTO TABLE <table> (<col1>,<col2>,...);

This command is not nearly as sexy as the previous one. LOAD DATA is a SQL command MySQL offers. The tricky part is that you need to include the LOCAL keyword.

Did I mention that Postgres handles all this in a much cleaner and more consistent way?