How To Use sftp And Automation
By Ian Scott
The other day, an associate of mine was explaining how they were automating the backing up of MySQL databases to a back up server via an automated FTP script. For those not familiar, in order to backup MySQL databases the tables MySQL need to be locked otherwise you risk corrupting the backup version.
The script they were using may be found here: Sitepoint Blog.
I took a look and thought there was a better way than using FTP and having a username and password for the remote server in the backup script.
I also prefer to use sftp, which is a “secure” replacement for ftp. sftp is built around ssh. We’ll discuss ssh later. What sftp does for you is transfer files in an encrypted session rather than plain text. It also has the option to use “keys” so that you do not have to provide a password when you connect with sftp (this is also true for ssh).
For the sake of confusion, let’s call the server from where the data is being transferred ’server 1′ and the backup server, ’server 2′. Here’s what I did:
1. Logged into server 1 as the user that would be running the backup script.
2. Typed the following command: ssh-keygen -t dsa When asked for a password, I simply hit enter both times and did not provide a password. Although this is not as secure as a key with a password, there is no point to a password if you need to use automation.
After running this command, the program ssh-keygen produces two files in your /home/username/.ssh directory called ‘id_dsa’ and ‘id_dsa.pub’. File ‘id_dsa.pub’ now contains your public ssh key which we will transfer to server #2.
3. Using sftp, transfer ‘id_dsa.pub’ to your home directory on server #2.
4. Log into server #2 via ssh.
5. Using the following command, you now will APPEND the contents of ‘id_dsa.pub’ to the ‘/home//.ssh/authorized_keys’ file. If this file does not already exist, don’t worry as the following command will create the file:
cat id_dsa.pub >> .ssh/authorized_keys
After you do this, you may remove the id_dsa.pub file on server #2 as you don’t require it any further.
6. If you are trying to transfer files as the root user, you will need to open up the ‘/etc/ssh/sshd_config’ file. Look for a line that says:
PermitRootLogin
If this is set to ‘yes’, then you should learn a bit more about server hardening. You have three choices as to how to set this particular configuration: ‘yes’, ‘no’, or ‘without-password’.
At first, ‘without-password’ seems confusing - as it would seem to suggest that you could log in to the server as root without having to provide a password. Period. But that is not really what it means. What it means is that root can log in via ssh or sftp without a password as long as the public key in the above ‘authorized_keys’ matches the private key on the system that is attempting to log in.
So, this line in the ’sshd_config’ file should appear exactly like this:
PermitRootLogin without-password
7. Restart the sshd service:
/sbin/service sshd restart
You need to do this in order for the change in the ’sshd_config’ file to take effect.
In summary, what we’ve done is created ssh private and public dsa keys on server #1, transferred the public key to server #2, and configured server #2 to accept a login attempt via ssh or sftp from a user with a private key that matches the public key. Confused yet?
So now that the above is done, we take the above mentioned backup script and we can remove the following lines:
#FTP Username
FTPUSER=”
# FTP Password
FTPPASSWD=”
Then change this line:
ftp -n $FTPHOST <
to this:
sftp $FTPHOST <
and comment out or remove the next two lines like this:
#quote USER $FTPUSER
#quote PASS $FTPPASSWD
We now have a more secure script for automatically backing up your MySQL databases!
You'll also want to make sure you have an entry in your crontab to ensure the script runs at whatever interval you want. For more information on setting up a crontab, please see Brad Illston’s tutorial which is straight forward and should be easy to understand.
Read more in: Computer Security |