Quickly Copy MySQL Database to Remote Host

by damonp on August 31, 2007

in Snippets,SQL

Copy an entire database:

mysqldump LOCAL_DBNAME | ssh USER@REMOTE_HOST mysql -p REMOTE_DBNAME

Copy a single table:

mysqldump LOCAL_DBNAME LOCAL_TABLE | ssh USER@REMOTE_HOST -p REMOTE_DBNAME REMOTE_TABLE

This shortcut only works if you can access the local DB without a password. If you have to login to both local and remote MySQL servers, the MySQL password prompts get mashed together. You could specify the password on the command line like

-pPASSWORD

but your shell may keep the password in its history so that anyone with access to your account could pick through your shell history and retrieve the password. Using the MySQL password prompt doesn’t do this.

Popularity: 3%

Most Popular Posts

Damon Parker is a freelance sysadmin and web developer in Texas. He specializes in server setup, server security and high performance server configurations. Need help setting up a web server or getting a server back online after a crash or hack? Email Damon

{ 1 comment… read it below or add one }

sri May 29, 2011 at 9:11 pm

Great – simple & it works!

Reply

Leave a Comment

Previous post:

Next post: