Backup MySQL Database Regularly using Shell Script

Sometime, we need to backup our database regularly in order to avoid something bad ( paranoid mode) 8-)
Bellow is the simple shell sh script for backing up MySQL database by dumped the database and then put it into gzip file with the date of backup as the name file.

Here is the condition of my server :

  • My MySQL server is located in IP Address 10.1.1.10 with username = USER and password = PASSWORD
  • The name of database which should be dumped regularly is mydatabase
  • My backup server is located in IP 10.1.1.15
  • The path of mysqldump in my backup server is located in /apps/local/mysql/bin/mysqldump
  • I want to put the gzip backup files in folder /backup/BACKUPDB in my backup server
  • My backup script is located in /script folder in my backup server

Here is the simple script :

#! /bin/sh
# filename : backupdb.sh
# Automated database datestamp backup
/apps/local/mysql/bin/mysqldump --opt -Q -h 10.1.1.10 -u USER
-pPASSWORD mydatabase > /backup/BACKUPDB/`date +"%d-%m-%y"`.sql
gzip /backup/BACKUPDB/`date +"%d-%m-%y"`.sql

Then, in order to run automatically that script at specific time, we need to set up cron job by adding the crontab list in backup server.
Here is example crontab for running backupdb.sh every day at 2 am.

0 2 * * * /script/backupdb.sh

Before add the script into cron job, it will be better if you check whether that script is running well or not. Do it at your own risk :wink:

Share and Enjoy at:
  • Facebook
  • Twitter
  • Technorati
  • MySpace
  • LinkedIn
Tags: , , , , June 27th, 2009 Posted in Learn

One Response to “Backup MySQL Database Regularly using Shell Script”

Trackbacks

Leave a Reply

:wink: :-| :-x :twisted: :) 8-O :( :roll: :-P :oops: :-o :mrgreen: :lol: :idea: :-D :evil: :cry: 8) :arrow: :-? :?: :!:

Andhy | Hosting by Qwords | Entries (RSS) and Comments (RSS).

Switch to our mobile site