Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1962
  • Last Modified:

backup mysql database from shell on centos v5.2

hello,
I am running on centos v5.2 and I have about 45 databases on mysql.. I would like to know how can I backup everything and also
how to restore them! thanks
0
XK8ER
Asked:
XK8ER
  • 2
1 Solution
 
fosiul01Commented:
third party soltuion
http://www.zmanda.com/quick-mysql-backup.html

or


http://knowledgelayer.softlayer.com/questions/275/How+do+I+backup+MySQL+in+Linux%3F
or

http://www.backuphowto.info/how-backup-mysql-database-automatically-linux-users

if you go through , couple of these tutorial you will see, concept is same

1. you need mysqldump  command to expert all databases
2. need cron job to make it scheduled

Sorry too many tutorial, just go though couple, see which one is best for you.
if this does not work , then let me   know, tomorrow i will go through again.
0
 
Deepak KosarajuCommented:
Here is the script to backup your mysql database.
1) To backup databases file /backup dir and later pick up by the script

2) You can skip few databases from backup!
MyUSER="YOUR-MYSQL-USER" # mysql username
MyPASS="YOUR-MYSQL-USER-PASSWORD" # mysql password
MyHOST="localhost" # mysql Hostname
Optional setup the name of database which you do not wish to backup:
IGGY="test db2 db3"

Schedule this using cron weekly
place this script to /etc/cron.weekly dir


#!/bin/bash
# Shell script to backup MySql database
# To backup Mysql databases file to /backup dir and later pick up by your
 
MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
MyHOST="localhost"          # Hostname
 
# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"
 
# Backup Dest directory, change this if you have someother location
DEST="/backup"
 
# Main directory where backup will be stored
MBD="$DEST/mysql"
 
# Get hostname
HOST="$(hostname)"
 
# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"
 
# File to store current backup file
FILE=""
# Store list of databases
DBS=""
 
# DO NOT BACKUP these databases
IGGY="test"
 
[ ! -d $MBD ] && mkdir -p $MBD || :
 
# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST
 
# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"
 
for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi
 
    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

Open in new window

0
 
UmeshCommented:
You may want to try this..

A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. Features - Backup mutiple databases - Single backup file or to a seperate file for each DB - Compress backup files - Backup remote servers - E-mail logs - More..


http://sourceforge.net/projects/automysqlbackup/
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now