MySQL Backup

install utilities:
- pigz - multithreaded gzip implementation of gzip
- pv - progressbar
yum install pigz pv -y
Backup
mysqldump --extended-insert --single-transaction mydb | pigz > mydb-backup.sql.gz
Restore
# default
gunzip  < mydb-backup.sql.gz | mysql mydb
# or multithreaded
unpigz < mydb-backup.sql.gz | mysql mydb

# with progressbar
pv mydb-backup.sql.gz | unpigz | mysql mydb

LVM based backup
# volume group should have free space
vgs

# if none - shrink it (in example we have 30GB used of 50GB disk = shrink to 40GB, 10GB for snapshot)
lsblk
df -h
umount /dev/cl/data
e2fsck -fy /dev/cl/data
resize2fs /dev/cl/data 39G
lvreduce -L 40G /dev/cl/data
resize2fs /dev/cl/data
e2fsck -fy /dev/cl/data
mount /dev/cl/data /data

# check free space again
vgs

# read lock db
mysql
> FLUSH TABLES WITH READ LOCK;

# create snapshot
lvcreate -l100%FREE -s -p r -n dbbackup /dev/cl/data
lvcreate -l100%FREE --snapshot --permission r --name dbbackup /dev/cl/data

# unlock mysql
> UNLOCK TABLES;

# save position
mysql -e "SHOW MASTER STATUS;"

# mount
mount -o ro /dev/cl/dbbackup /mnt/backup

# control used space in snapshot
lvs

# ===== mysql dump backup from snapshot =======================

# starting second instance from snapshot (--innodb-log-file-size must be identical to the primary MySQL instance)
mysqld_safe --no-defaults --port=3307 --socket=/tmp/mysqld-snapshot.sock --datadir=/mnt/dbbackup/mysql --innodb-log-file-size=268435456

# make mysqldump from snapshot
mysqldump -S /tmp/mysql-snapshot.sock --extended-insert --single-transaction --set-gtid-purged=OFF | gzip > /path/to/mysql/backup.sql.gz

# shutdown mysql
mysqladmin -S /tmp/mysqld-snapshot.sock shutdown

# ====== gzip backup from snapshot ============================
tar -czpf /backup/mysql-backup.tar.gz /mnt/backup/mysql

# ====== unmount and del snapshot =============================
lvscan
umount /mnt/backup
lvremove -f /dev/cl/dbbackup
lvscan