shell script pentru backup la bazele de date mysql

Discutii/Probleme/Intrebari/Raspunsuri cu si despre Linux
Post Reply
TiTex
Administrator
Administrator
Posts: 2690
Joined: Sat May 19, 2007 00:00
Nume Real: Alex
Location: C:\
Contact:

shell script pentru backup la bazele de date mysql

Post by TiTex » Thu Nov 22, 2007 19:07

acest script se poate folosi pentru a face backup periodic la toate bazele de date pe care le aveti in mysql

Code: Select all

#!/bin/bash
# Shell script to backup MySql database 
# To backup Nysql databases file to /backup dir and later pick up by your 
# script. You can skip few databases from backup too.
# For more info please see (Installation info):
# http://www.cyberciti.biz/nixcraft/vivek ... cript.html 
# Last updated: Aug - 2005
# --------------------------------------------------------------------
# This is a free shell script under GNU GPL version 2.0 or above
# Copyright (C) 2004, 2005 nixCraft project
# Feedback/comment/suggestions : http://cyberciti.biz/fb/
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------

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

Selecatati tot si salvati ca  mysqlbackup.sh , completati campurile de mai sus cu datele pentru a accesa serverul mysql
Il puteti rula periodic prin intermediul cron-ului
Bafta!
Academician, profesor, doctor, inginer, honoris cauza si premiul Nobel

Reguli pe Scurt | Arata Continutul
1.Inainte de a deschide un topic nou, folosete "cautarea" sa vezi daca ce vrei sa postezi nu s-a discutat deja
2.Cand deschideti un topic nou asigurati-va ca postati unde trebuie!
3.Folositi titluri descriptive pentru topic! NU puneti "Sal , Click Aici, Intrati va rog" sau alte titluri monosilabice fara sens
4.Nu faceti spam... adica sa postati in toate topicele doar sa va aflati in treaba!
5.Fara prea mult "OFF TOPIC"!
6.Nu jigniti alti utilizatori prin posturile pe care le faceti!
7.Folositi un limbaj decent, cat mai corect gramatical ca lumea sa poata sa inteleaga ce vreti
8.Nu faceti pe moderatorii daca nu sunteti!

Utile & Informatii | Arata Continutul


Nu imi trimiteti mesaj privat doar daca aveti ceva personal de discutat cu mine!

Upload Imagini: ImageHosting | ImageShack
Upload Fisiere: MediaFire | MegaUpload

Cititi regulile forumului!!! | Reguli Counter-Strike | Anunturi Importante | Cum folosim BBcode (butoanele) de pe forum

RadioPlay

Re: shell script pentru backup la bazele de date mysql

Post by RadioPlay » Thu Nov 20, 2008 07:26

Salutare,

Uite ... dupa mesajul primit am zis ca ar fi bine sa dau si eu o raita pe forum si sa ma axez pe un subiect anume si am nimerit aici :). M-ar interesa aceeasi metoda dar sa faca un backup la pgsql si un eventual script care la un anumit intervat sa faca upload la scripturile respective pe un FTP. Un ftp extern nu unul inter.

Daca ma poti ajuta astept un reply.

Meris :)

fpz0r
Administrator
Administrator
Posts: 740
Joined: Sat May 19, 2007 00:00
Nume Real: Flo
Location: aici

Re: shell script pentru backup la bazele de date mysql

Post by fpz0r » Thu Nov 20, 2008 09:15

Check this:

Code: Select all

#!/bin/sh
#
# Postgresql backup script
#
# Author
#  |
#  +-- speedboy (speedboy_420 at hotmail dot com)
#  +-- spiderr (spiderr at bitweaver dot org)
#
# Last modified
#  |
#  +-- 16-10-2001
#  +-- 16-12-2007
#
# Version
#  |
#  +-- 1.1.2
#
# Description
#  |
#  +-- A bourne shell script that automates the backup, vacuum and
#      analyze of databases running on a postgresql server.
#
# Tested on
#  |
#  +-- Postgresql
#  |    |
#  |    +-- 8.1.9
#  |    +-- 7.1.3
#  |    +-- 7.1.2
#  |    +-- 7.1
#  |    +-- 7.0
#  |
#  +-- Operating systems
#  |    |
#  |    +-- CentOS 5 (RHEL 5)
#  |    +-- Linux Redhat 6.2
#  |    +-- Linux Mandrake 7.2
#  |    +-- FreeBSD 4.3
#  |    +-- Cygwin (omit the column ":" because NT doesn't quite digest it)
#  |
#  +-- Shells:
#       |
#       +-- sh
#       +-- bash
#       +-- ksh
#
# Requirements
#  |
#  +-- grep, awk, sed, echo, bzip2, chmod, touch, mkdir, date, psql,
#      test, expr, dirname, find, tail, du, pg_dump, vacuum_db
#
# Installation
#  |
#  +-- Set the path and shell you wish to use for this script on the
#  |   first line of this file. Keep in mind this script has only been
#  |   tested on the shells above in the "Tested on" section.
#  |
#  +-- Set the configuration variables below in the configuration
#  |   section to appropriate values.
#  |
#  +-- Remove the line at the end of the configuration section so that
#  |   the script will run.
#  |
#  +-- Now save the script and perform the following command:
#  |
#  |   chmod +x ./pg_backup.sh
#  |
#  +-- Now run the configuration test:
#  |
#  |   ./pg_backup.sh configtest
#  |
#  |   This will test the configuration details.
#  |
#  +-- Once you have done that add similiar entries given as examples
#      below to the crontab (`crontab -e`) without the the _first_ #
#      characters.
#
#      # Run a backup of the remote database host 'foodb', likely on a private network
#      00 03 * * * export PGHOST=foodb; export PGBACKUPDIR=/bak/backups ; nice /server/postgres/pg_backup.sh b 2  >> /var/log/pgsql/pg_backup.log 2>&1
#      # Run a backup of the local database host 'foodb' to a custom backup directory
#      00 04 * * * export PGBACKUPDIR=/bak/backups ; nice /usr/bin/pg_backup b 2  >> /var/log/pgsql/pg_backup.log 2>&1
#
# Restoration
#  |
#  +-- Restoration can be performed by using psql or pg_restore.
#  |   Here are two examples:
#  |
#  |   a) If the backup is plain text:
#  |
#  |   Firstly bunzip2 your backup file (if it was bzip2ped).
#  |
#  |   nunzip2 backup_file.bz2
#  |   psql -U postgres database < backup_file
#  |
#  |   b) If the backup is not plain text:
#  |
#  |   Firstly bunzip2 your backup file (if it was bzip2ed).
#  |
#  |   bunzip2 backup_file
#  |   pg_restore -d database -F {c|t} backup_file
#  |
#  |   Note: {c|t} is the format the database was backed up as.
#  |
#  |   pg_restore -d database -F t backup_file_tar
#  |
#  +-- Refer to the following url for more pg_restore help:
#
#      http://www.postgresql.org/idocs/index.php?app-pgrestore.html
#
# To Do List
#              1. db_connectivity() is BROKEN, if the script can not create a
#                 connection to postmaster it should die and write to the logfile
#                 that it could not connect.
#              2. make configtest check for every required binary
#
########################################################################
#                          Start configuration                         #
########################################################################
#
##################
# Authentication #
##################
#
# Postgresql hostname to connect to.
if [ $PGHOST ]; then
        PARAM_PGHOST="-h $PGHOST"
else
        PGHOST="localhost"
fi
 
# Postgresql username to perform backups under.
if [ -z $PGUSER ]; then
        PGUSER="postgres"
fi
 
# Postgresql password for the Postgresql username (if required).
# postgresql_password="password"
 
##################
# Locations      #
##################
#
# Location to place backups.
if [ -z $PGBACKUPDIR ]; then
        PGBACKUPDIR="/var/lib/pgsql/backups"
fi
subdir=`date +%Y-%m`
 
# Location to place the pg_backup.sh logfile.
if [ -z $PGLOGDIR ]; then
        PGLOGDIR="/var/log/pgsql/pg_backup.log"
fi
 
# Location of the psql binaries.
if [ -z $PGBINDIR ]; then
        PGBINDIR="/usr/bin"
fi
 
##################
# Permissions    #
##################
#
# Permissions for the backup location.
permissions_backup_dir="0755"
 
# Permissions for the backup files.
permissions_backup_file="0644"
 
# Permissions for the backup logfile.
permissions_backup_log="0644"
 
##################
# Other options  #
##################
#
# Databases to exclude from the backup process (separated by a space)
        exclusions="template"
 
        # Backup format
        #  |
        #  +-- p = plain text : psql database < backup_file
        #  +-- t = tar        : pg_restore -F t -d database backup_file
        #  +-- c = custom     : pg_restore -F c -d database backup_file
        #
        backup_format="t"
 
        # Backup large objects
        backup_large_objects="yes"
 
        # bzip2 the backups
        backup_bzip2="yes"
 
        # Date format for the backup
        #  |
        #  +-- %d-%m-%Y       = DD-MM-YYYY
        #  +-- %Y-%m-%d       = YYYY-MM-DD
        #  +-- %A-%b-%Y       = Tuesday-Sep-2001
        #  +-- %A-%Y-%d-%m-%Y = Tuesday-2001-18-09-2001
        #  |
        #  +-- For more date formats type:
        #
        #      date --help
        #
        backup_date_format="%Y-%m-%d"
 
        # You must comment out the line below before using this script
        #echo "You must set all values in the configuration section in this file then run ./pg_backup.sh configtest before using this script" && exit 1
        ########################################################################
        #                          End configuration                           #
        ########################################################################
        #
        #################
        # Variables     #
        #################
        #
        version="1.1.2"
        current_time=`date +%H%M`
        date_info=`date +$backup_date_format`
        PGPASSWORD="$postgresql_password"
        PATH="$PATH:/bin:/usr/bin"
 
        # Export the variables
        export PGUSER PGPASSWORD PATH
 
        #################
        # Checking      #
        #################
        #
        # Check the backup format
        if [ "$backup_format" = "p" ]; then
                backup_type="Plain text SQL"
                backup_args="-F $backup_format"
 
        elif [ "$backup_format" = "t" ]; then
                backup_type="Tar"
                if [ "$backup_large_objects" = "yes" ]; then
                        backup_args="-b -F $backup_format"
                else
                        backup_args="-F $backup_format"
                fi
        elif [ "$backup_format" = "c" ]; then
                backup_type="Custom"
                if [ "$backup_large_objects" = "yes" ]; then
                        backup_args="-b -F $backup_format"
                else
                        backup_args="-F $backup_format"
                fi
        else
                backup_format="c"
                backup_args="-F $backup_format"
                backup_type="Custom"
        fi
 
        #################
        # Functions     #
        #################
        #
        # Obtain a list of available databases with reference to the user
        # defined exclusions
db_connectivity() {
        tmp=`echo "($exclusions)" | sed 's/\ /\|/g'`
        if [ "$exclusions" = "" ]; then
                databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
        else
                databases=`$PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "\l" template1 | sed -n 4,/\eof/p | grep -v rows\) | grep -Ev $tmp | awk {'print $1'} || echo "Database connection could not be established at $timeinfo" >> $PGLOGDIR`
        fi
}
 
# Setup the permissions according to the Permissions section
set_permissions() {
        # Make the backup directories and secure them.
        mkdir -m $permissions_backup_dir -p "$PGBACKUPDIR/$subdir"
 
        # Touch the log file
        touch "$PGLOGDIR"
 
        # Make the backup tree
        chmod -f $permissions_backup_log "$PGLOGDIR"
        chmod -f $permissions_backup_dir "$PGBACKUPDIR"
        chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir"
#       chmod -f $permissions_backup_dir "$PGBACKUPDIR/$subdir/$date_info"
}
 
# Run backup
run_b() {
        for i in $databases; do
                start_time=`date '+%s'`
                timeinfo=`date '+%T %x'`
 
                "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                if [ "$backup_bzip2" = "yes" ]; then
                        bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                        chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
                else
                        chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                fi
                finish_time=`date '+%s'`
                duration=`expr $finish_time - $start_time`
                echo "Backup complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
        done
        exit 1
}
 
# Run backup and vacuum
run_bv() {
        for i in $databases; do
                start_time=`date '+%s'`
                timeinfo=`date '+%T %x'`
 
                "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
                "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                if [ "$backup_bzip2" = "yes" ]; then
                        bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                        chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
                else
                        chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                fi
                finish_time=`date '+%s'`
                duration=`expr $finish_time - $start_time`
                echo "Backup and Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
        done
        exit 1
}
 
# Run backup, vacuum and analyze
run_bva() {
        for i in $databases; do
                start_time=`date '+%s'`
                timeinfo=`date '+%T %x'`
 
                "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
                "$PGBINDIR/pg_dump" -i $backup_args $PARAM_PGHOST $i > "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                if [ "$backup_bzip2" = "yes" ]; then
                        bzip2 "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                        chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time.bz2"
                else
                        chmod $permissions_backup_file "$PGBACKUPDIR/$subdir/pg_db-$PGHOST-$i-backup-$date_info-$current_time"
                fi
                finish_time=`date '+%s'`
                duration=`expr $finish_time - $start_time`
                echo "Backup, Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i, format: $backup_type" >> $PGLOGDIR
        done
        exit 1
}
 
# Run vacuum
run_v() {
        for i in $databases; do
                start_time=`date '+%s'`
                timeinfo=`date '+%T %x'`
 
                "$PGBINDIR/vacuumdb" $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
                finish_time=`date '+%s'`
                duration=`expr $finish_time - $start_time`
                echo "Vacuum complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
        done
        exit 1
}
 
# Run vacuum and analyze
run_va() {
        for i in $databases; do
                start_time=`date '+%s'`
                timeinfo=`date '+%T %x'`
 
                "$PGBINDIR/vacuumdb" -z $PARAM_PGHOST -U $PGUSER $i >/dev/null 2>&1
                finish_time=`date '+%s'`
                duration=`expr $finish_time - $start_time`
                echo "Vacuum and Analyze complete (duration $duration seconds) at $timeinfo for schedule $current_time on database: $i " >> $PGLOGDIR
        done
        exit 1
}
 
# Print information regarding available backups
print_info() {
        echo "Postgresql backup script version $version"
        echo ""
        echo "Available backups:"
        echo ""
        if [ ! -d $PGBACKUPDIR ] ; then
                echo "There are currently no available backups"
                echo ""
                exit 0
        else
        for i in `find "$PGBACKUPDIR" -type d -maxdepth 2`; do
                echo "$i `du -h \"$i\" | tail -n 1 | awk {'print $1'}`"
                #for j in `ls "$PGBACKUPDIR/$i"`; do
                #       echo "    $j `du -h \"$PGBACKUPDIR/$i/$j\" | tail -n 1 | awk {'print $1'}`"
                #done
        done
        echo ""
        fi
        exit 1
}
 
# Print configuration test
print_configtest() {
        echo "Postgresql backup script version $version"
        echo ""
        echo "Configuration test..."
        echo ""
 
        # Check database connectivity information
        echo -n "Database hostname                    : "
        echo "$PGHOST"
        echo -n "Database username                    : "
        echo "$PGUSER"
        echo -n "Database connectivity                : "
        $PGBINDIR/psql $PARAM_PGHOST -U $PGUSER -q -c "select now()" template1 > /dev/null 2>&1 && echo "Yes" || echo "Connection could not be established..."
 
        # Backup information
        echo ""
        echo "Backup information:"
        echo ""
        echo -n "Backup format                        : "
        if [ "$backup_format" = "p" ]; then
                echo "Plain text SQL"
        elif [ "$backup_format" = "t" ]; then
                echo "Tar"
        else
                echo "Custom"
        fi
 
        echo -n "Backup large objects                 : "
        if [ "$backup_large_objects" = "yes" ]; then
                echo "Yes"
        else
                echo "No"
        fi
        echo -n "bzip2 backups                         : "
        if [ "$backup_bzip2" = "yes" ]; then
                echo "Yes"
        else
                echo "No"
        fi
        echo -n "Backup date format                   : $date_info"
        echo ""
 
        # File locations
        echo -n "Backup directory                     : "
        echo "$PGBACKUPDIR"
        echo -n "Backup logfile                       : "
        echo "$PGLOGDIR"
        echo -n "Postgresql binaries                  : "
        echo "$PGBINDIR"
 
        # Backup file permissions
 
        echo -n "Backup directory permissions         : "
        echo "$permissions_backup_dir"
        echo -n "Backup file permissions              : "
        echo "$permissions_backup_file"
        echo -n "Backup log permissions               : "
        echo "$permissions_backup_log"
 
        # Databases that will be backed up
        echo -n "Databases that will be backed up     : "
        echo ""
        for i in $databases; do
                echo "                                       $i"
        done
 
        # Databases that will not be backed up
        echo -n "Databases that will not be backed up :"
        echo ""
        if [ "$exclusions" = "" ]; then
                echo "                                       none"
        else
                for i in $exclusions; do
                        echo "                                       $i"
                done
                echo ""
        fi
 
        # Check if the backups location is writable
        echo "Checking permissions:"
        echo ""
        echo -n "Write access                         : $PGBACKUPDIR: "
        # Needed to create/write to the dump location"
        test -w "$PGBACKUPDIR" && echo "Yes" || echo "No"
 
        # Check if the logfile location is writable
        echo -n "Write access                         : $PGLOGDIR: "
        # Needed to create/write to this scripts logfile"
        if [ ! -x $PGLOGDIR ] ; then
                test -w `dirname "$PGLOGDIR"` && echo "Yes" || echo "No"
        else
                test -w "$PGLOGDIR" && echo "Yes" || echo "No"
        fi
 
        # Check if the binaries are executable
        echo -n "Execute access                       : $PGBINDIR/psql: "
        test -x $PGBINDIR/psql && echo "Yes" || echo "No"
 
        echo -n "Execute access                       : $PGBINDIR/pg_dump: "
        test -x $PGBINDIR/pg_dump && echo "Yes" || echo "No"
 
        echo -n "Execute access                       : $PGBINDIR/vacuumdb: "
        test -x $PGBINDIR/vacuumdb && echo "Yes" || echo "No"
 
        echo ""
        exit 1
}
 
# Print help
print_help() {
        echo "Postgresql backup script version $version"
        echo ""
        echo "Usage: $0 [options]"
        echo ""
        echo "Options:"
        echo "  b,          Backup ALL databases"
        echo "  bv,         Backup and Vacuum ALL databases"
        echo "  bva,        Backup, Vacuum and Analyze ALL databases"
        echo "  v,          Vacuum ALL databases"
        echo "  va,         Vacuum and Analyze ALL databases"
        echo "  info,       Information regarding all available backups"
        echo "  configtest, Configuration test"
        echo "  help,       This message"
        echo ""
        echo "Report bugs to <speedboy_420 at hotmail dot com >."
        exit 1
}
 
case "$1" in
        # Run backup
        b)
                db_connectivity
                set_permissions
                run_b
                exit 1
                ;;
        # Run backup and vacuum
        bv)
                db_connectivity
                set_permissions
                run_bv
                exit 1
                ;;
 
        # Run backup, vacuum and analyze
        bva)
                db_connectivity
                set_permissions
                run_bva
                exit 1
                ;;
 
        # Run vacuum
        v)
                db_connectivity
                set_permissions
                run_v
                exit 1
                ;;
 
        # Run vacuum and analyze
        va)
                db_connectivity
                set_permissions
                run_va
                exit 1
                ;;
 
        # Print info
        info)
                set_permissions
                print_info
                exit 1
                ;;
 
        # Print configtest
        configtest)
                db_connectivity
                set_permissions
                print_configtest
                exit 1
                ;;
 
        # Default
        *)
                print_help
                exit 1
                ;;
esac
more info HERE
Image

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest