Solved

backup mysql database from shell on centos v5.2

Posted on 2008-10-27
4
1,946 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
Comment Utility
0
 
LVL 29

Expert Comment

by:fosiul01
Comment Utility
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
Comment Utility
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
Comment Utility
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.

Join & Write a Comment

SSH (Secure Shell) - Tips and Tricks As you all know SSH(Secure Shell) is a network protocol, which we use to access/transfer files securely between two networked devices. SSH was actually designed as a replacement for insecure protocols that sen…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
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…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

8 Experts available now in Live!

Get 1:1 Help Now