Backing up MySQL database

Losing data on your business application's database would be a real disaster. Here's a way simple script to back up your MySQL database.

Scenario

We have a remote Debian server with the database, and an Ubuntu server (Intel Xeon) in our office right beneath my table. :)

We wanted to have our database backed up daily. So here we go:

1-st step. Save SQL script of the database in the remote server.

#!/bin/bash
export d=$(date +'%Y-%m-%d')
mkdir -p /root/backup/$d
mysqldump --add-drop-table --allow-keywords -q -a -c -u root -p[password_for_mysql_root] [databasename] > /root/backup/$d/[databasename].sql

Save it as /root/mysqlbackup.sh

2-nd step. Automize it with crontab -e to run daily at 05:30.

30 05 * * * /root/mysqlbackup.sh

3-rd step. Fetch the SQL from the local server and save.

#!/bin/bash
export d=$(date +'%Y-%m-%d')
mkdir -p /root/backup/$d
pscp -sftp -l root -pw [password_for_remote_server_root] root@[server_ip]:/root/backup/$d/[databasename].sql /root/backup/$d
exit 0