Skip to content

Instantly share code, notes, and snippets.

@Godefroy
Created August 21, 2015 09:19
Show Gist options
  • Save Godefroy/0f1d18d0a00179dadcaa to your computer and use it in GitHub Desktop.
Save Godefroy/0f1d18d0a00179dadcaa to your computer and use it in GitHub Desktop.
Instant backup of a MySQL database with LVM snapshot
#! /bin/bash
## Instant backup of a MySQL database with LVM snapshot
## Author: Godefroy de Compreignac (@Godefroy)
## License: Beerware
# Instructions:
# - MySQL Data on DB server must be an logical LVM partition
# - Backup server must have ssh root access to DB server
# - Master status is saved at the exact moment of snapshot to allow restoration of master-slave replication
BACKUP_DIR=/home/backup
BACKUP_MYSQL_DIR=$BACKUP_DIR/mysql
MYSQL_SERVER=db.example.com
MYSQL_PASSWORD=trololo
MYSQL_DATA_PART=/dev/vhd1/mysql-data
# Create backup dir
mkdir -p $BACKUP_MYSQL_DIR/data
# Lock DB, Create Snapshot, Unlock DB
ssh $MYSQL_SERVER "mysql -u root -p$MYSQL_PASSWORD -Bse \"
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS\G;
system lvcreate -L 5G -s -n backup $MYSQL_DATA_PART;
UNLOCK TABLES;
\"" > $BACKUP_MYSQL_DIR/master_status
# Mount snapshot
ssh $MYSQL_SERVER "mkdir -p /backup && mount -o nouuid $MYSQL_DATA_PART /backup"
# Backup Snapshot
rsync -a --partial --delete --exclude=/*.info $MYSQL_SERVER:/backup/ $BACKUP_MYSQL_DIR/data/
# Remove Snapshot
ssh $MYSQL_SERVER "umount /backup && rm -r /backup && dmsetup remove $MYSQL_DATA_PART && lvremove -f $MYSQL_DATA_PART"
@s5unty
Copy link

s5unty commented Jul 9, 2022

🍺
It seems to me that there is a problem with line 30,
Correct: /dev/vhd1/backup
Wrong: /dev/vhd1/mysql-data

--- ng.sh	2022-07-09 10:37:39.437867674 +0800
+++ ok.sh	2022-07-09 10:37:58.762305314 +0800
@@ -27,7 +27,7 @@
 \"" > $BACKUP_MYSQL_DIR/master_status
 
 # Mount snapshot
-ssh $MYSQL_SERVER "mkdir -p /backup && mount -o nouuid $MYSQL_DATA_PART /backup"
+ssh $MYSQL_SERVER "mkdir -p /backup && mount -o nouuid ${MYSQL_DATA_PART/mysql-data/backup} /backup"
 
 # Backup Snapshot
 rsync -a --partial --delete --exclude=/*.info $MYSQL_SERVER:/backup/ $BACKUP_MYSQL_DIR/data/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment