Scenario: A database named db1 is hosted on host1. It needs to be backed up on a remote machine named host2 and be restored on the local MySQL instance. An overnight cronjob running the following script (or more accurate the command in sequence) gets the job done. The script runs mysqldump for backing up the database on host1. The generated .sql file is copied over ssh on the remote host host2. On host2, the /root/script.sql file is invoked to drop the existing db1 database and re-creates an empty db1 database to be ready to store the data:
#!/bin/bash mysqldump -u root -ppassword db1 > /root/db1.mysql scp /root/db1.mysql host1:/root/db1.mysql ssh host2 "mysql -u root -ppassword < /root/script.sql" ssh host2 "mysql -u root -ppassword db1 < /root/db1.mysql"
It may not be the most appropriate way, but it works. For any other ways of doing it please comment (apart from rsync’ing the /var/lib/mysql/db1 directories between the two hosts ;)).
: | : vortex of false deceit : | : 
I think there’s supposed to be more “proper” way of doing this by using database replication.
You can connect 2 different mysql databases in a master and slave configuration and replication is done automatically. The basic advantage of this is of course high availability but it also serves for disaster recovery.
Unfortunately I haven’t investigated into the matter further and I cannot be more specific.
P.S. Funny how linux, databases, photography, diving ( and maybe bikes ? ) are connected.
You’re right Pantelis. Quick googling reveals this: http://www.howtoforge.com/mysql_database_replication I didn’t try it but should sooner or later.
Replace bikes with bicycle 😉 They are all labeled under “interests and passions” 😛