The Blog

Copy database or table from one MySQL server to another, clever way. Posted on

This simple bash script can copy database or a table from one remote server to another. Can be used as a backup, but also as a one way sync on a database or table.

ssh -fNg… <- creates a permanent tunnel to the remote server on port 3307. So now from the local server you can access both local and remote MySQL.

export MYSQL_PWD=… <- Helps hide password, as long as you make this script secure, the password it not accessible.

Last one-liner does the rest, if you remove <DB_TABLE_NAME> it will send the entire DB over to the remote, so be carefuller. But you already new that and have a backup, right?

#!/usr/bin/bash

ssh -fNg -L 3307:127.0.0.1:3306 <USERNAME>@<REMORE_HOST> -p 22

export MYSQL_PWD=<DB_PASSWORD>
mysqldump --lock-tables=false -h localhost -u <DB_USERNAME> <DB_NAME> <DB_TABLE_NAME> | mysql -P 3307 -h 127.0.0.1 -u <DB_USERNAME> --compress <DB_NAME>

This entry was posted in Linux, MySQL. Bookmark the permalink.

Please Post Your Comments & Reviews

Your email address will not be published. Required fields are marked *



CAPTCHA
Change the CAPTCHA codeSpeak the CAPTCHA code