Le blog de Shnoulle
Bricolage en informatique et autres joyeusetées
Accueil du site > Logiciels libres > GNU/Linux > Un ptit script de backup mysql
Un ptit script de backup mysql
mardi 9 novembre 2010, par
Ce script est une note de rappel personnel et ne vise pas à être implémenté tel que ....
Ce script est (très) fortement inspiré des scripts suivant :
#!/bin/bash
# Linux bin paths, change this if it can not be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
# Backup Dest directory, change this if you have someother location
DEST="/home/Shnoulle/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")"
DATEREM=`date --date '10 days ago' "+%d-%m-%Y"`
# File to store current backup file
FILE=""
# Store list of databases
DBS=""
# DO NOT BACKUP these databases
SKIP=""
[ ! -d $MBD ] && mkdir -p $MBD || :
[ ! -d "$MBD/$NOW" ] && mkdir -p "$MBD/$NOW" || :
# Get all database list first
DBS="$($MYSQL -Bse 'show databases')"
for db in $DBS
do
skipdb=-1
if [ "$SKIP" != "" ];
then
for i in $SKIP
do
[ "$db" == "$i" ] && skipdb=1 || :
done
fi
if [ "$skipdb" == "-1" ] ; then
for table in `$MYSQL -N $db -e "show tables"`
do
ENGINE=$($MYSQL $db -e "show table status like '$table'\G" | awk '/Engine/ {print $2}')
if [ "$ENGINE" == "MyISAM" ]
then
SIZE=$($MYSQL $db -e "show table status like '$table'\G" | awk '/Data_length/ {print $2}')
FREE=$($MYSQL $db -e "show table status like '$table'\G" | awk '/Data_free/ {print $2}')
ROWS=$($MYSQL $db -e "show table status like '$table'\G" | awk '/Rows/ {print $2}')
if [ $FREE -gt 0 ] && [ $SIZE -gt 0 ] && [ $ROWS -gt 0 ]
then
FRAGMENTATION=$(printf '%i' $(echo "scale=0; $FREE/($SIZE/100)"|bc))
echo "$FRAGMENTATION% $db.$table Data_length=$SIZE Data_free=$FREE"
if [ $FRAGMENTATION -ge 5 ]
then
echo "OPTIMIZE REQUESTED"
$MYSQL $db -e "use '$db';OPTIMIZE TABLE $table\G"
fi
fi
fi
done
FILE="$MBD/$NOW/$db.$HOST.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 $db | $GZIP -9 > $FILE
fi
done
# On supprime les repertoire de il y a 10 jours
if [ -d "$MBD/$DATEREM" ]; then
rm -rF "$MBD/$DATEREM"
fiY a sans doute moyen de faire mieux, je modifierais au fur et à mesure ...
RSS 2.0