safe mysql backup mode: snapshot & dump

This is an easy to use mysql backup script. Optionally you can choose whether you want to dump or only copy the database files which are created with an lvm snapshot in a safe way.

#!/bin/bash

VG="vg0"
LV="data"
MOUNT_POINT="/mnt/snapshot"
SNAPSHOT_SIZE="1000MB"
MYSQL_DATA="$MOUNT_POINT/mysql"
MYSQL_DUMP_DIR="/data/backup/mysql"

create_lv(){
    /sbin/lvremove -f /dev/$VG/$LV-backup 2> /dev/null
    echo "FLUSH TABLES WITH READ LOCK;" | /usr/bin/mysql
    echo "flush logs;"| /usr/bin/mysql
    /sbin/lvcreate -L"${SNAPSHOT_SIZE}" -s -n $LV-backup /dev/$VG/$LV
}

mount_lv(){
    mount /dev/$VG/$LV-backup $MOUNT_POINT
    touch $MOUNT_POINT/.fs_is_mounted
}

umount_lv(){
    umount -l $MOUNT_POINT
}

remove_lv(){
    /sbin/lvremove -f /dev/$VG/$LV-backup
}


mysql_start(){
    /usr/sbin/mysqld --basedir=/usr --datadir="${MYSQL_DATA}" --user=mysql --pid-file=/var/run/mysqld/mysqld-backup.pid --socket=/var/run/mysqld/mysqld-backup.sock --skip-networking &
}

mysql_dump(){
    if [ ! -f $MOUNT_POINT/.fs_is_mounted ];then
        echo "Backup volume is not mounted!!!"
        exit 1
    fi

    for database in `mysql -S /var/run/mysqld/mysqld-backup.sock -e "show databases"|egrep -wv '(Database|information_schema)'`;
        do /usr/bin/mysqldump -S /var/run/mysqld/mysqld-backup.sock $database > $MYSQL_DUMP_DIR/$database.mysql
    done
}

mysql_stop(){
    PID=`pgrep -f /var/run/mysqld/mysqld-backup.sock`
    kill $PID
    kill -9 $PID
    pgrep -f /var/run/mysqld/mysqld-backup.sock && "mysql backup instance is not killable!!!"
}

usage(){
    echo "Usage: mylvmsnapshot lv-start|lv-stop"
    echo "       mylvmsnapshot my-start|my-dump|my-stop"
}

case $1 in
    lv-start)
        create_lv;
        mount_lv;
    ;;

    my-start)
        mysql_start;
    ;;

    my-dump)
        mysql_dump;
    ;;

    my-stop)
        mysql_stop;
    ;;

    lv-stop)
        umount_lv;
        remove_lv;
    ;;

    *)
        usage;
        exit 1;
    ;;

esac
avatar

About tompos

Hungary, Budapest
This entry was posted in IT. Bookmark the permalink.

Leave a Reply