Aller aujourd’hui un petit script pour sauvegarder nos bases de données à chaud sans utiliser l’utilitaire mysqldump (malheureusement gourmand en ressources sur des bases de données MySQL conséquentes).
#!/bin/bash
## VARIABLES ##
USER="usermysql"
PASSWORD="mypassword"
TMPMOUNTPOINT="/snapshot"
VGNAME="vg02"
LVNAME="mysql"
SNAPNAME="snapmysql"
## Check if /backup/mysql.tar.gz exists on the remote server ##
ssh root@remote-server "if [ -e /backup/mysql.tar.gz ] ; then rm -Rf /backup/mysql.tar.gz ; fi"
## Check if temp mount point not used ##
[ `mount | grep "$TMPMOUNTPOINT" | wc -l` -ne 0 ] && exit 1
## FUNCTIONS ##
function log() {
logger -p local7.info "$1"
}
## BACKUP PROCESS ##
log "Locking databases"
mysql -u$USER -p$PASSWORD << EOF
FLUSH TABLES WITH READ LOCK;
system lvcreate -l +100%FREE --snapshot -n $SNAPNAME /dev/$VGNAME/$LVNAME;
UNLOCK TABLES;
quit
EOF
log "Databases unlocked"
## TAR AND SEND THROUGH SSH ##
log "Backing up databases"
mount /dev/$VGNAME/$SNAPNAME $TMPMOUNTPOINT
cd $TMPMOUNTPOINT
tar cfvz - --exclude=debian-5.1.flag --exclude=mysql_upgrade_info --exclude=mysql --exclude=phpmyadmin * | ssh root@remote-server "cat > /backup/mysql.tar.gz"
cd
umount $TMPMOUNTPOINT
lvremove -f /dev/$VGNAME/$SNAPNAME
log "Databases backed up"
## VARIABLES ##
USER="usermysql"
PASSWORD="mypassword"
TMPMOUNTPOINT="/snapshot"
VGNAME="vg02"
LVNAME="mysql"
SNAPNAME="snapmysql"
## Check if /backup/mysql.tar.gz exists on the remote server ##
ssh root@remote-server "if [ -e /backup/mysql.tar.gz ] ; then rm -Rf /backup/mysql.tar.gz ; fi"
## Check if temp mount point not used ##
[ `mount | grep "$TMPMOUNTPOINT" | wc -l` -ne 0 ] && exit 1
## FUNCTIONS ##
function log() {
logger -p local7.info "$1"
}
## BACKUP PROCESS ##
log "Locking databases"
mysql -u$USER -p$PASSWORD << EOF
FLUSH TABLES WITH READ LOCK;
system lvcreate -l +100%FREE --snapshot -n $SNAPNAME /dev/$VGNAME/$LVNAME;
UNLOCK TABLES;
quit
EOF
log "Databases unlocked"
## TAR AND SEND THROUGH SSH ##
log "Backing up databases"
mount /dev/$VGNAME/$SNAPNAME $TMPMOUNTPOINT
cd $TMPMOUNTPOINT
tar cfvz - --exclude=debian-5.1.flag --exclude=mysql_upgrade_info --exclude=mysql --exclude=phpmyadmin * | ssh root@remote-server "cat > /backup/mysql.tar.gz"
cd
umount $TMPMOUNTPOINT
lvremove -f /dev/$VGNAME/$SNAPNAME
log "Databases backed up"
Les différentes étapes sont les suivantes :
– On vérifie que la sauvegarde n’existe pas déjà sur le serveur distant
– On verrouille les tables MySQL en lecture seule
– On créé un snapshot LVM
– On lève les verrous des tables
– On monte le snapshot sur le dossier /snapshot
– On fait une archive (avec tar) qu’on transfert directement via SSH au serveur distant
– On démonte le point de montage du snapshot
– On supprime le snapshot LVM
Cette astuce est utilisable sur tout système Linux ayant LVM et MySQL installé.
(No Ratings Yet)
Loading...