Did you know? One of the most important practices about networks and computers is backup. Sure it is! Backup can save your ass from catastrophic failures and its implementation costs almost nothing. So why people don't do it? I don't know.
However, another important thing to remember is to store the backup physically far away from the original data, for example uploading it on a remote server. At work I got a very small MySQL database which needed to be replicated on a remote server every 1 minute; to do so you can 1) install & setup the MySQL relication system or 2) use bash scripting!
Option 1) seems a waste of time and resources while the 2nd one seems a lot more suited for the problem and also a lot more funny.
On the origin machine (the computer which owns the original MySQL database) I created a simple script executed every minute by the local cron daemon.
First, we get a local dump of the database. Notice the fancy password 123456 :)
mysqldump -u root -p123456 <database> > /tmp/db_dump.sqlThen, we move the local backup to the remote machine via the secure copy command scp. Scp is the union of SSH and CP. It takes a file and securely transfers it to a remote location.
scp -P 56022 -i <rsakey> /tmp/db.sql user@remotehost:/tmpThe local dump has been moved to the remote /tmp directory. Now we need to import the remote dump on the remote MySQL server. To do so, we should execute the following command on the remote machine!
mysql -u root -p123456 <database> < /tmp/db.sqlWe can''t execute that command because we are not there! So, how to do it? Well, that''s one of the classic situation where SSH comes so handy! With SSH we can securely execute commands as we were in front of the remote keyboard! Nice huh? Just execute the local command:
ssh -p 56022 -i <rsakey> user@remotehost "mysql -u root -p123456 <database> < /tmp/db.sql"
A simple cron rule will execute the scripts every 1 minute, as expected.
Thanks a lot for tihs idea! I already have a MySQL Master/Slave setup, however it already failed me two times. I wanted to set up a third server which is independent and does not rely on the Master/Slave configuration, and this solution seems perfect.