# Sync MySQL via SSH



## Pushrod (Jan 10, 2011)

I have a production web server which is not part of my home network. I do the development at home, and then sync up to the web server.

I have one site which uses a live database, and I need to be able pull down full copies of it back to the development DB here at home. I'd like to avoid opening TCP and dealing with firewalling and all that on the production DB.

Is there an automated way of doing this over SSH, or should I roll my own scripts?

Using replication is not an option in this case.


----------



## quintessence (Jan 10, 2011)

Hello,

You should use your scripts.

You can set for example cronjob to create dump of your database on production server, then copy to you home station via ssh and import in your mysql home station.

The only important thing is how busy is your production database and what time you can do the dump, depends of this you can play with different mysqldump options.


----------



## phoenix (Jan 11, 2011)

Script something along the lines of:

ssh to remote server, run mysqldump, save to a .sql file
scp .sql file from remote server to local machine
run mysql commands to drop the database, create the database using the .sql file


----------



## mix_room (Jan 11, 2011)

Perhaps something along the lines of 
http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html


----------



## Pushrod (Jan 11, 2011)

mix_room said:
			
		

> Perhaps something along the lines of
> http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html



Thanks, I'll start from there and toss in some Bzip along with further automation.


----------

