Script Shell per eseguire Backup di tutti db Mysql in remoto via FTP

ott 22 2008

Script Shell per eseguire Backup di tutti db Mysql in remoto via FTP

Published by Dev at 14:15 under Opensource, Sicurezza, Tutorial

Questo script permette di fare il backup di ciascuna tabella di ogni database (un file per tabella),, compattando il tutto alla fine per poi inviarlo via FTP sul server di backup.



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
#!/bin/sh
# System + MySQL backup script
# Copyright (c) 2008 Marchost
# This script is licensed under GNU GPL version 2.0 or above
# ---------------------------------------------------------------------
 
#########################
######TO BE MODIFIED#####
 
### System Setup ###
BACKUP=YOUR_LOCAL_BACKUP_DIR
 
### MySQL Setup ###
MUSER="MYSQL_USER"
MPASS="MYSQL_USER_PASSWORD"
MHOST="localhost"
 
### FTP server Setup ###
FTPD="YOUR_FTP_BACKUP_DIR"
FTPU="YOUR_FTP_USER"
FTPP="YOUR_FTP_USER_PASSWORD"
FTPS="YOUR_FTP_SERVER_ADDRESS"
 
######DO NOT MAKE MODIFICATION BELOW#####
#########################################
 
### Binaries ###
TAR="$(which tar)"
GZIP="$(which gzip)"
FTP="$(which ftp)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
 
### Today + hour in 24h format ###
NOW=$(date +"%d%H")
 
### Create hourly dir ###
 
mkdir $BACKUP/$NOW
 
### Get all databases name ###
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 
### Create dir for each databases, backup tables in individual files ###
mkdir $BACKUP/$NOW/$db
 
for i in `echo "show tables" | $MYSQL -u $MUSER -h $MHOST -p$MPASS $db|grep -v Tables_in_`;
do
FILE=$BACKUP/$NOW/$db/$i.sql.gz
echo $i; $MYSQLDUMP --add-drop-table --allow-keywords -q -c -u $MUSER -h $MHOST -p$MPASS $db $i | $GZIP -9 &gt; $FILE
done
done
 
### Compress all tables in one nice file to upload ###
 
ARCHIVE=$BACKUP/$NOW.tar.gz
ARCHIVED=$BACKUP/$NOW
 
$TAR -cvf $ARCHIVE $ARCHIVED
 
### Dump backup using FTP ###
cd $BACKUP
DUMPFILE=$NOW.tar.gz
$FTP -n $FTPS &lt;&lt;END_SCRIPT
quote USER $FTPU
quote PASS $FTPP
cd $FTPD
mput $DUMPFILE
quit
END_SCRIPT
 
### Delete the backup dir and keep archive ###
 
rm -rf $ARCHIVED