LinuxQuestions.org - Using cronjobs to mysql dump and scp backup to another box

- Linux - General (https://www.linuxquestions.org/questions/linux-general-1/)

- - Using cronjobs to mysql dump and scp backup to another box (https://www.linuxquestions.org/questions/linux-general-1/using-cronjobs-to-mysql-dump-and-scp-backup-to-another-box-500301/)

Using cronjobs to mysql dump and scp backup to another box

I need to run cronjobs to backup some of my databases daily and scp them over to another box.

I'm debating with my brain if I should just use one filename and have it overwrite the file every day or use multiple names (like a date) and manually delete them when I get a chance.

How would I do this? I know how to back up the databases using the shell, but I don't know how to instruct the command what the database password is or what the pass of the other box is.

Also, I would like a daily email informing me that the backup process was successful, if possible.

Can anyone help me out?

Hi Daiver. With respect to the naming convention of the dumpfile, perhaps you could use the weekday as part of the filename. This way you'll keep a week's worth of backups, but not have to manually delete files as they will be over-written after a week.

As for the general process. I would write a script to determine the backup filename, make the backup file and then scp to the remote host, then call this script from cron.

The script might look something like this:

Code:

#!/bin/bash

cd /my/local/backup/dir

backup_filename="database_backup_$(date +%a).dat"

mysql -u username -pmypassword database_name < /path/to/backup_script.sql > "$backup_filename"

scp "$backup_filename" remoteuser@remotehost:backup_dir

...where backup_script.sql contains the SQL commands which make the backup. The ugly part of this is that the password is in the script which is potentially a security risk, and is also something to bear in mind when deciding on maintenance (if you end up with lots of these scripts, you'll need to update them all when you change your database connection details).

In a production environment, it would probably make sense to check that the backup was successful (i.e. mysql and scp didn't return an error, and perhaps that the size of the backup file is not too small etc.), and perhaps send an email alert on failure. If there's one thing more horrible than not having a backup when you need one, it's having a backup which you can't restore because the backup process wasn't working properly...

I've done a similar setup at work. Here is how it can be done:

On MySQL host create a cronjob that makes a backup of your desired tables. Check out

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

for possible options. The one your want for password is ... --password="your pass" or short -p"pass"

Pass the output of mysqldump to a file which you can late transfer with scp. (pack it into an archive for smaller files)

We are creating a directory with the name of the date to hold the databases. We also create one .sql file for each database. But you can also create a file for each table in a database. Just check out the options of mysqldump. It's a great tool by the way.

So now that we have our sqldumps we can take care of copying it over to the other box.

Easiest way for this is first to have an scp login which does not query for a password.

Check out :

http://www.bluegum.com/Software/ssh-auth.html

to acomplish this.

After automatic ssh login is possible we only have to use scp to shove the files over to the other box.

Task accomplished.

Considering the mail when it comes to failure just use the $? shell var that tells you the return code of the last programm.

I can give you some extract from our scripts when I'm at work on Monday. If still required then.

Regards zhjim

Hi guys. Thanks for your prompt replies.

Matthew's method sounds very easy to do if I can figure out how to do a script. Do I just copy and edit that in a file and call it from cron? I especially liked his idea of naming the file after the weekday to have 7 backups at all times. Would you know the necessary commands for the email part? Also, exactly how public will the passwords be? If I lock the file so only one user, which nobody has access to, can read or execute it, I can live with that.

Zhjim's method also sounds very good, however, I'm skeptical about leaving a user that does not require a password in a box. I don't have the expertise to secure that user so that if anyone gains access to it, he won't be able to do anything else but delete those backups. I'm worried, though, that the intruder might be able to tamper with them, making these backups useless. I've never been successful with working with keys. I'm sort of a newbie, but I guess that if I really try hard, I can get it to work. However, I thought that the key was an accessory to the regular user/pass method, sort of like added security. Also, I don't wish to backup by table, but just backup the whole thing. I'm dealing mostly with WordPress and vBulletin databases, the biggest one being around 30 MB. Eventually, I will need to pack the backup from that specific big database to save bandwidth.

Quote:

Originally Posted by daiver

Zhjim's suggestion about using mysqldump is probably better than using regular SQL statements - certainly for performance, and probably for reliability and completeness too. Since there is documentation on the mysql site on how to do it, that's probably going to be easiest to figure out as well.

If the script file is only readable by the one user, and access to that user is properly controlled, it's probably not an issue. Be aware that some corporate security policies may not allow having unencrypted passwords in program files (especially if they have to comply with that Sarbanes Oxley regulatory bill which has been brought in to some sectors recently).

There's several ways to do email from a script. The one I use most often is with the "mailx" program. Note that you have to have your machine configured to forward email. You can always test it from the command line. Here's the syntax:

Code:

echo "Hello me, I'm a test" | mailx -s "subject here" user1@foo.com user2@foo.com

As you can see, it's possible to send to multiple email address just by specifiying more than one. You don't have to though.

If you have a longer message, you might want to use the heredoc style quoting:

Code:

cat <<EOD |mailx -s "database backup status" admin@foo.com

Dear database backup admin,



In the daily database backup script we've detected a

serious problem.  You better log in and check it out.

You'd probably be best to get some coffee first, and

put some soothing music on.



Good luck

your database backup script

EOD

One thing to no when using this heredoc quoting... the EOD has to be at the start of the line - no indenting!