Archives par mot-clé : Backup Mysql

Backup MySQL avancé

#!/bin/sh

date=`date +%Y%m%d`
day=`date +%u`
week=`date +%Y%U`
user=root
pass=password
dest=/var/backup_mysql
daily=$dest/data-day-$date

bases="mysql base1 base2"

if [ ! -d $daily ]
then
       mkdir -p $daily
fi

cd $daily
for base in $bases
do
	mysqldump -l --user $user --password=$pass --opt --databases $base > $base.sql
	tar -czf $base.tgz $base.sql
	rm $base.sql
done

mysqladmin -p$pass flush-tables

if [ $day == 1 ]
then
	weekly=$dest/data-week-$week
	if [ ! -d $weekly ]
	then
		mkdir $weekly
	fi
	cp $daily/*.tgz $weekly
fi

# On laisse 7 jours de backup
date=`date -d '7 days ago' +%Y%m%d`
rm -rf $dest/data-day-$date

# On laisse 6 mois de backup hebdo
date=`date -d '6 month ago' +%Y%U`
rm -rf $dest/data-week-$date

Backup MySQL Simple

#!/bin/sh

## Options de backup
opts="--skip-opt --event --triggers --routines --quick --extended-insert --create-options --set-charset --disable-keys"

# pour chaque base sauf performance_schema

for DBNAME in `mysql -u root -ppassword -B -e "show databases;" | sed 1d | grep -v performance_schema`
do
# on fait le backup et on compresse
        /usr/bin/mysqldump $opts -u root -ppassword $DBNAME | bzip2 > /mnt/mysql_backup/$DBNAME-$(date +%d%m%Y).sql.bz2
done