I needed to copy a database and the idea of backing it up just to re-import1 seemed like double the work. Here’s a snippet to pipe a mysqldump into a remote database. Keep an eye on the user names and passwords – you’ll need 3 sets; one for the database your copying, one to get into your remote server, and one for the remote, target database.
mysqldump -v -uUSER -pPASSWORD --opt --compress DATABASE_NAME | ssh REMOTE_SERVER_USER@REMOTE_HOSTNAME mysql -uREMOTE-MYSQL-USER -pREMOTE_MYSQL_PASSWORD REMOTE_DATABASE_NAME
1. Backing up is a good thing. Why aren’t you doing it? Here’s a script for that.
mysqldump -h HOSTNAME DATABASE_NAME | gzip -9 > BACKUP_DIR/DATABASE_NAME.sql.gz
I’d said more : don’t use password on command line or erase history after…
I’m trying to the reverse of this command but the remote server is not registering the password.
mysql -u USER -pPASSWORD DATABASE_NAME | ssh USER@REMOTE_HOSTNAME mysqldump -v -u USER -pPASSWORD -h HOST --opt --compress DATABASE_NAME
I am prompted for my ssh password. After entering it and pressing return, nothing happens. If I hit return again, it tells my my password is incorrect, when I know it’s not.
If I break the command up it works as expected.
ssh USER@ REMOTE_HOSTNAME mysqldump -v -u USER -pPASSWORD -h HOST --opt --compress DATABASE_NAME > db.sql
Any idea’s what’s causing this hang up?
Thanks
It may be a bit late but you could try:
ssh REMOTE_USER@host mysqldump -uREMOTE_DB_USER -pPASS OLD_DB | mysql -uDB_USER -pPASS NEW_DB
This way your are feeding the remote DB dump into the local mysql command.
GREAT!
Thanks a lot man.