Announcement

Collapse

http://progeeking.com

See more
See less

Бекъп на всички бази - Backup of all databases - Linux

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Бекъп на всички бази - Backup of all databases - Linux

    Това е един от начините как може да се бекъпне Mysql db
    с този скрипт се бекупват всички дб-та, като след това се архивират.

    ### SCRIPT ###
    #!/bin/bash
    unset PATH
    # USER VARIABLES
    MYSQLUSER=root
    MYSQLPWD=qazwsx
    MYSQLHOST=localhost
    MYSQLBACKUPDIR=/var/backups/mysql_daily
    # PATH VARIABLES
    MK=/bin/mkdir;
    RM=/bin/rm;
    TAR=/bin/tar;
    GREP=/bin/grep;
    MYSQL=/usr/bin/mysql;
    MYSQLDUMP=/usr/bin/mysqldump;
    GZIP=/bin/gzip;
    # CREATE MYSQL BACKUP
    # Remove existing backup dir
    $RM -Rf $MYSQLBACKUPDIR
    # Create new backup dir
    $MK $MYSQLBACKUPDIR
    #Dump new files
    for i in $(echo 'SHOW DATABASES;' | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v '^Database$'); do
    $MYSQLDUMP \
    -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST \
    -Q -c -C --add-drop-table --add-locks --quick --lock-tables \
    $i > $MYSQLBACKUPDIR/$i.sql && $GZIP -9 $MYSQLBACKUPDIR/$i.sql;
    done;

    Може да се сложи в:\

    drwxr-xr-x 2 root root 4096 2009-03-07 21:41 cron.daily
    drwxr-xr-x 2 root root 4096 2007-07-30 22:02 cron.hourly
    drwxr-xr-x 2 root root 4096 2008-10-17 03:54 cron.monthly
    -rw-r--r-- 1 root root 934 2008-12-05 22:01 crontab
    drwxr-xr-x 2 root root 4096 2008-02-15 22:49 cron.weekly

    Като е много важно да се добавят права за рънване

    примерно -> $ chmod 755 scriptname.sh
    root@dnsbg:/etc/cron.daily# ls -la mysql_export_daily.sh
    -rwxr-xr-x 1 root root 702 2009-03-07 21:30 mysql_export_daily.sh
    root@dnsbg:/etc/cron.daily#

    Ако искате да имате дневен, седмичен, месечен бекъп, трябва да се промени MYSQLBACKUPDIR=/var/backups/mysql_daily за да може да имате бекупи в различни папки.

  • #2
    Бекуп на Mysql db 3.33, 4.0, 4.1 и 5.0 и RETENTION опция

    #!/usr/bin/env bash
    #
    # Add support for ibbbackup for InnoDB tables
    # Add support for MySQL 4.1 and 5.0
    #
    DATE=`date -I`
    DATADIR="/var/lib/mysql"
    BASE_DIR="/mnt/drive2/backup"
    BACKUP_DIR="$BASE_DIR/current"
    INTERVAL="$1"
    RETENTION=14 # days
    HOST=`hostname -s`
    MYVERSION=`mysql -Bse "SELECT substring_index(version(),'.',2)"`
    ### Uncomment this line to specify the path to and enable ibbackup for hotcopy of InnoDB tables.
    # IBBACKUP="/usr/local/bin/ibbackup"
    if [ "$MYVERSION" = '4.1' ] || [ "$MYVERSION" = '5.0' ] ; then
    PURGELOGS='mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB( NOW(), INTERVAL 1 HOUR )"'
    elif [ $MYVERSION = '3.23' -o "$MYVERSION" = '4.0' ]; then
    PURGELOGS='mysql -e "RESET MASTER"'
    else
    echo "UNSUPPORTED MYSQL VERSION"
    exit 1
    fi
    if [ ! $1 ];
    then
    read -p "Backup Interval? (Hourly|Daily) : " INTERVAL
    fi
    case $INTERVAL in
    hourly | HOURLY | Hourly | 1 )
    echo "Performing HOURLY level backup -- `date`"
    mysql -e "FLUSH LOGS"
    if [ -d $BASE_DIR/$DATE ] && [ "$MYVERSION" = '4.1' -o "$MYVERSION" = '5.0' ] ; then
    rsync -aub $DATADIR/$HOST-bin.?????? $BASE_DIR/$DATE
    elif [ -d $BASE_DIR/$DATE ] && [ "$MYVERSION" = '3.23' -o "$MYVERSION" = '4.0' ] ; then
    rsync -aub $DATADIR/$HOST-bin.??? $BASE_DIR/$DATE
    else
    echo "No destination dir! please run daily backup first." 1>&2
    exit 1
    fi
    sleep 1
    find $BASE_DIR -size 98c -exec rm -rf '{}' \;
    exit 0
    ;;
    daily | DAILY | Daily | 2 )
    echo "Performing DAILY level backup -- `date`"
    if [ ! -d $BACKUP_DIR ];
    then
    echo Creating $BACKUP_DIR
    mkdir -p $BACKUP_DIR
    fi
    if [ ! -z "$IBBACKUP" ] ; then
    $IBBACKUP /etc/my.cnf /etc/my.cnf.ibbackup 2>&1
    $IBBACKUP --apply-log /etc/my.cnf.ibbackup 2>&1
    rm $BACKUP_DIR/ibbackup_logfile
    fi
    mysqlhotcopy --regexp=.* $BACKUP_DIR
    chown -R mysql: $BACKUP_DIR/
    mv $BACKUP_DIR $BASE_DIR/$DATE
    eval $PURGELOGS
    find $BASE_DIR -ctime +$RETENTION -exec rm -rf '{}' \;
    exit 0
    ;;
    * )
    echo "Invalid Selection" 1>&2
    exit 1
    esac

    Comment

    Working...
    X