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 ;)).