You would like to automate the export of SQL dump files from one Linux server to another using scp. These dump files are to be generated and exported once every two weeks. You also wish to archive these dump files on the source server.
So let’s say the servers and directories are as follows:
Dump file directory:
First you need to set it up so that you don’t need to enter a password for scp. The overall picture is as follows:
- At the source server, generate a pair of public and private keys  using RSA.
- Store the private key in a specific place in the source server.
- Store the public key in a specific place in the destination server.
- And that’s it! Now you’ll no longer be prompted for a password when using scp.
# login to perak with username nazham
ssh-keygen -t rsa
# choose default location, no passphrase - just press enter at all prompts
scp ~/.ssh/id_rsa.pub romantika@selangor:/home/romantika/.ssh/authorized_keys
# I'm assuming the file authorized_keys does not exist!
# otherwise, you need to append the contents of id_rsa.pub to it.
# add the following:
# 1 0 1,15 * * nazham /home/nazham/run_data_export >> /dev/null 2>&1
# which means, run at 12:01 am every 1st and 15th of the month
The run_data_export script
oldDate=`date -d '1 year ago' +%Y%m%d`
mysqldump -u username -ppassword -r ~/$exportDir/current/filename dbname tablenames
echo $todaysDate > ~/$exportDir/current/importdate.txt
scp ~/$exportDir/current/* romantika@selangor:/home/romantika/data_import
mv ~/$exportDir/current/* ~/$exportDir/archive/$todaysDate
if [ -d ~/$exportDir/archive/$oldDate ]; then rm -r ~/$exportDir/archive/$oldDate; fi
The Linux date command
I’m very impressed by the Linux date command. This might be old news to some, but the fact that I’m able to say
date -d '1 year ago' or
date -d '30 days ago' completely blows the mind.
 From Pass on Passwords with scp: “If you’re not familiar with public key cryptography, here’s the 15-second explanation. In public key cryptography, you generate a pair of mathematically related keys, one public and one private. You then give your public key to anyone and everyone in the world, but you never ever give out your private key. The magic is in the mathematical makeup of the keys; anyone with your public key can use it to encrypt a message, but only you can decrypt it with your private key.”
This method can also be used using rsync if you’re backing up multiple files.
For example, I have this as a scheduled task on my Windows box (Cygwin connecting to a Debian box) to backup my emails:
bash.exe –login -i -c “echo `date` > /cygdrive/d/Logs/rsync.log; cd /cygdrive/d/Thunderbird; rsync -avzt –delete –exclude=parent.lock . email@example.com:/home/personal/Thunderbird >> /cygdrive/d/Logs/rsync.log” 2>&1
Yeah our servers in the office are set-up with this (SSH passwordless on local + rsync to backup)
I’m not a Linux expert so it took me a while to set up all this 🙁