Solved

backup mysql database from shell on centos v5.2

Posted on 2008-10-27
4
1,948 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now