MySQL help

Not sure if this is the right forum, mods please move if it isn't!

I have a site which uses a mysql backend which I kind of have inherited from someone else. Backups where never really done on a regular basis and I want to start a system of nightly backups of the mysqldb.

I am able to ssh into the webhost and I own a little debian box that is plugged in all the time. So, I would like to run a script that gets the debian machine to ssh into the server, run mysqldump, compress the files and save them to the box.

Most of the above is fine, I've been messing about with bash scripts for the last while and I have something similar going where my laptop runs mysqldump and then rsyncs the files to the box. I have it so that I have the last seven days of backups stored at any given time. Go me!

However, a few things struck me and I'll be honest and say I know very little about proper net security protocols:

1) SSH - the webhost says to ssh in using keys rather then a password. How secure is this? I'm happy enough to use a password but is there is anyway then that the password won't be stored as plaintext in the bash file?

2) MySQL login - kind of the same, is there a way that to have something like

mysqldump -u $UNAME -p $PASSWORD blah.local > blah.remote

where $UNAME and $PASSWORD are stored somewhere so that they aren't lying around in plaintext?

The information isn't commercially sensitive or anything, but I don't want to mess this up! Anyone got any suggestions? Google results seem to have most people having their passwords store in plaintext in teh bash scripts. Must that be done?

ethernet

zaraba wrote: »

1) SSH - the webhost says to ssh in using keys rather then a password. How secure is this? I'm happy enough to use a password but is there is anyway then that the password won't be stored as plaintext in the bash file?

You'd be using public-private key authentication so no passwords involved at all. It's great for automated backups. You can totally disable password-based logins to prevent all those brute-force attacks then. See 'SSH Keys' on this page for a better explanation

2) MySQL login - kind of the same, is there a way that to have something like

mysqldump -u $UNAME -p $PASSWORD blah.local > blah.remote

where $UNAME and $PASSWORD are stored somewhere so that they aren't lying around in plaintext?

Presumably there is. You could keep this command in a file in your home directory with very tight permissions instead so that only you can access (read) it; just like if you're mounting NAS shares that require authentication when you boot your machine. If you're not comfortable with that, you may have the option to create another MySQL user who only has enough privileges for locking your tables before dumping them for a backup, i.e. no delete or equally destructive privileges.