Accueil > 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"
- fi
Y a sans doute moyen de faire mieux, je modifierais au fur et à mesure ...
