Remote MySQL database backup

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

2 thoughts on “Remote MySQL database backup

  1. 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.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s