View Single Post
Old 10-04-2017, 03:51 PM  
porn-update
Confirmed User
 
porn-update's Avatar
 
Industry Role:
Join Date: Apr 2014
Posts: 380
I found this script, almost perfect for what I want to do

Code:
#!/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/blogger/2005/01/mysql-backup-script.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
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------
 
MyUSER=username     # USERNAME
MyPASS=password   # PASSWORD
MyHOST=hostname        # 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="/var/backup"
 
# Main directory where backup will be stored
MBD="$DEST/mysql"

#elimino vecchi backup
rm -f $MBD/*
 
# 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="information_schema cond_instances mysql performance_schema phpmyadmin"
 
[ ! -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"
        #no gzip, comprimo dopo tutta la cartella
        FILE="$MBD/$db.$HOST.$NOW.sql"

        # 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

        #no gzip, comprimo dopo tutta la cartella
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db > $FILE
    fi
done

#comprimo tutto
zip -r $DEST/mysql_backup.$HOST.zip $MBD/

#tar -zcvf $DEST/mysql_backup.$HOST.tar.gz $MBD
I added this to delete last week's backups
Code:
#elimino vecchi backup
rm -f $MBD/*
I added the system databases among those excluded from the process
Code:
# DO NOT BACKUP these databases
IGGY="information_schema cond_instances mysql performance_schema phpmyadmin"
Removed the compression on mysqldump
Code:
#FILE="$MBD/$db.$HOST.$NOW.gz"
        #no gzip, comprimo dopo tutta la cartella
        FILE="$MBD/$db.$HOST.$NOW.sql"

        # 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

        #no gzip, comprimo dopo tutta la cartella
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db > $FILE
Added to the end compression of the entire /mysql folder
Code:
#comprimo tutto
zip -r $DEST/mysql_backup.$HOST.zip $MBD/

#tar -zcvf $DEST/mysql_backup.$HOST.tar.gz $MBD
From all this comes a single zipped file, which in theory, I should be able to download with SCP on my raspberry

Crontab? Only this code?
Code:
0 6 * * 4 /var/backup/mysql_backup
Will I receive some output via mail? Maybe the time elapsed to run the script?

I hope I didn't make any big errors... I added, edited, deleted lines, but I practically have no idea what the language with which the file is written... looks like PHP, it seems to work again... it's the best I can say... it would be nice if you would you warn me if I made some horrendous error

One strange thing I saw is that in the compressed file I find the folder structure /var/backup/mysql, while I was expecting only /mysql, not a big problem, but strange...

Now I try to bring everything on my raspberry via SPC, hopefully everything works.

The next and last step will be to understand when to start all to not create problems to the server
porn-update is offline   Share thread on Digg Share thread on Twitter Share thread on Reddit Share thread on Facebook Reply With Quote