[ale] MySQL array based snapshot

[Derek Atkins]

Chris,

On Tue, November 22, 2016 11:40 am, Chris Fowler wrote:
>> From: "Jim Kinney" <jim.kinney at gmail.com>
>> To: "Atlanta Linux Enthusiasts - Yes! We run Linux!" <ale at ale.org>
>> Sent: Tuesday, November 22, 2016 10:09:36 AM
>> Subject: Re: [ale] MySQL array based snapshot
>
>> That's even easier. From inside vm, sync and lock db, vm snapshot,
>> unlock. Copy
>> snapshot away. Flatten vm snapshot. Vmware and Ovirt/KVM will do this.
> I've received even more Greek when I asked if they were planning on doing
> snapshots in their storage
>
> "Snaps through VAAI. Our storage is VASA aware. It is the disk STUN I am
> worried about related to running DB."
>
> The good news is that the transactions to the DB are not that high. It
> could be if they grow significantly and start monitoring SNMP. I store
> traps in the DB. They could lock the database and that would cause all
> processes to "pause" till it was unlocked. I know this because I've
> received complaints when I've done mysqldumps. Those locks are not lock
> enough to cause timeouts in the apps. They just block until the lock is
> released.

Basically, what you need to do is tell mysql to flush its tables, lock its
tables, and then, while it's all locked, create your snapshot.  Then it
can unlock itself and continue and you can back up the snapshot.

Here's some scripts I use that do this (which I use with rdiff-backup, but
you can use it with something else too).  Hopefully these scripts will
help you.

-derek

> Chris

cat > /usr/local/bin/snapshot_mysql_backup.sh <<EOF
#!/bin/bash
#
# snapshot_mysql_backup.sh -- run a snapshot backup instead of off the
running
#                             filesystem.  Requires LVM.  This is for use
#                             when using mysql.
#
# change /root/.ssh/authorized_keys command to this script
#
# Make sure to set VG_NAME, LV_NAME, and LV_SIZE appropriately
#
# And create the appropriate mysql user:
# GRANT RELOAD, LOCK TABLES ON *.* TO flusher identified by 'flusher';
# FLUSH PRIVILEGES;
#

export PATH=/usr/bin:/bin:/usr/sbin:/sbin
VG_NAME=my_vg
LV_NAME=my_lv
LV_SIZE=8G

(
    # RPM Dump
    rfile=/tmp/rpmdump.txt.$$
    dfile=/root/rpmdump.txt
    rpm -qa | sort > $rfile
    cmp -s $rfile $dfile || cp -p $rfile $dfile
    rm -f $rfile

    [ -d /mnt/snap ] || mkdir -p /mnt/snap

    echo "FLUSH TABLES WITH READ LOCK; SYSTEM
/usr/local/bin/snapshot_mysql_start.sh $VG_NAME $LV_NAME $LV_SIZE;
UNLOCK TABLES;" | mysql -u flusher --password=flusher

    if [ `mount | grep -c /mnt/snap` -eq 0 ] ; then
	lvremove -f /dev/$VG_NAME/snap
	exit 1
    fi
) 1>&2 </dev/null || exit 1

rdiff-backup --server --restrict-read-only /mnt/snap

(
    umount /mnt/snap
    lvdisplay /dev/$VG_NAME/snap
    lvremove -f /dev/$VG_NAME/snap
) 1>&2 </dev/null
EOF

cat >/usr/local/bin/snapshot_mysql_start.sh <<EOF
#!/bin/bash
#
# snapshot_mysql_start.sh -- Create the LVM Snapshot from inside the MySQL
run
#
# Make sure to set VG_NAME, LV_NAME, and LV_SIZE appropriately
#
# echo "FLUSH TABLES WITH READ LOCK; SYSTEM /path/to/helper/snap-start.sh
$VG_NAME $LV_NAME $LV_SIZE ; UNLOCK TABLES;" | mysql -u <user>
--password=<password>
#
# And create the appropriate mysql user:
# GRANT RELOAD, LOCK TABLES ON *.* TO flusher identified by 'flusher';
# FLUSH PRIVILEGES;
#

export PATH=/usr/bin:/bin:/usr/sbin:/sbin
VG_NAME="$1"
LV_NAME="$2"
LV_SIZE="$3"

(
    if ! lvcreate --size $LV_SIZE --snapshot --name snap
/dev/$VG_NAME/$LV_NAME; then
	exit 1
    fi

    # add ,nouuid for XFS
    if ! mount -o ro,nouuid /dev/$VG_NAME/snap /mnt/snap; then
	lvremove -f /dev/$VG_NAME/snap
	exit 1
    fi
) 1>&2 </dev/null || exit 1
EOF
-- 
       Derek Atkins                 617-623-3745
       derek at ihtfp.com             www.ihtfp.com
       Computer and Internet Security Consultant