SQL Backup

The following requires two files depending on your need. If you're only putting the SQL backups on a secondary harddrive, then you just need step one. However, if you want to put the SQL backups on a remote server for storage you'll need to use the second step (expect-based).

#/bin/sh

# Dates the new tarballs of current builds.

DATE=`date +%m_%d_%Y`

# create /backup/databases/ as a directory or edit below.

# make sure to edit the [password] to your SQL root password.

mysqldump -u root -p[password] --all-databases > /backup/databases/dump.$DATE.sql

The above will backup all of your SQL databases to the /backup/databases/ directory. If you want it to continue and upload the data to a remote location, add the following line to the above script:

expect /backup/sqlbackupremote.exp

Now, create a file called /backup/sqlbackupremote.exp and edit the username, password, and remote path for uploading your files on a remote server.

#!/usr/bin/expect -f

set DATE [timestamp -format %m_%d_%Y]

set filename "/backup/databases/remote-dump.sql"

set timeout -1

# replace username@remotehost.tld with the username and address to the remote server.

spawn scp /backup/databases/dump.$DATE.sql username@remotehost.tld:remote-dump.sql

# replace password with the password to log into the remote server's account above.

set pass "password"

expect {

password: {send "$pass\r" ; exp_continue}

eof exit

}

The above will have your SQL safe with backups.

Remember to create a CRON JOB to ensure it happens as frequently as you wish!