Scenario

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:

Source

Hostname: perak
Username: nazham
Dump file directory: /home/nazham/data_export/current
Archive directory: /home/nazham/data_export/archive/yyyymmdd

Target

Hostname: selangor
Username: romantika
Target directory: /home/romantika/data_import

Passwordless scp

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:

1. At the source server, generate a pair of public and private keys [1] using RSA.
2. Store the private key in a specific place in the source server.
3. Store the public key in a specific place in the destination server.
4. And that’s it! Now you’ll no longer be prompted for a password when using scp.

One-off steps

# 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.
crontab -e
# 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
mkdir ~/data_export
mkdir ~/data_export/current
mkdir ~/data_export/archive

The run_data_export script

todaysDate=`date +%Y%m%d`
oldDate=`date -d '1 year ago' +%Y%m%d`
exportDir=data_export
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
mkdir ~/$exportDir/archive/$todaysDate
rm ~/$exportDir/current/importdate.txt
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.

Notes

[1] 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.”