Solved

backup mysql database from shell on centos v5.2

Posted on 2008-10-27
4
1,950 Views
Last Modified: 2011-10-03
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
Comment
Question by:XK8ER
  • 2
4 Comments
 
LVL 29

Expert Comment

by:fosiul01
ID: 22818027
0
 
LVL 29

Expert Comment

by:fosiul01
ID: 22818061
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
 
LVL 14

Accepted Solution

by:
Deepak Kosaraju earned 500 total points
ID: 22818472
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
 
LVL 26

Expert Comment

by:ushastry
ID: 22819331
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question