MySQL What is a physical backup (lvm-snapshot)

This article brings you an introduction to what MySQL physical backup (lvm-snapshot) is. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

lvm-snapshot (tool backup)

Advantages:

• Almost hot standby (lock the table before taking a snapshot and release it immediately after creation)

• Supports all engines

• Backup speed Fast

• No need to use expensive commercial software (it is OS level)

Disadvantages:

• May require cross-department collaboration (using operating system level commands, DBA generally does not have permission)

• Unable to predict service outage time

• It will be troublesome if the data is distributed on multiple volumes (for storage level)

The principle of logical volume snapshot

Why choose lvm snapshot backup?

Reason: Because the time of the lock table is inconsistent, writing cannot be done at the moment the table is locked. Perform a snapshot backup of it, unlock it immediately after the backup is completed, and then the service can be used normally (writing and other operations), for example When the amount of data is large, a snapshot can be taken at once and then unlocked immediately, without affecting operations such as writing. If you use mysqldump to back up, when the amount of data is large, the table locking time will be long, which will affect efficiency.

Operation process

1、flush table with read locak;   
2、create snapshot  
3、show master status;　show slave status; [可选]
4、unlock tables;
5、Copy files from the snapshot
6、Unmount the snapshot. 
7、Remove snapshot

快照备份：
1.迁移数据到逻辑卷（不是必须，视情况而定）
2.锁表（时间）
3.给数据库所在的逻辑卷拍快照
4.解锁
5.将快照挂载到临时挂载点上
6.将快照上的所有数据拷贝到相应的备份目录里（不同主机）
7.卸载快照并删除

lvm backup example

1. Data migration to the logical volume

Environment: The data file is not on the logical volume, then the data file needs to be migrated to the logical volume

• ##1 , Create a logical volume

[root@Admin ~]# pvcreate /dev/sdb
[root@Admin ~]# vgcreate vg01 /dev/sdb
[root@Admin ~]# lvcreate -n lv_mysql -L 4G vg01
[root@Admin ~]# mkfs.ext4 /dev/mapper/vg01-lv_mysql

• 2. Migrate the current mysql database to the logical volume

1>先停止应用
2>停止mysql服务
[root@Admin ~]# service mysqld stop
3>备份所有的数据文件到指定的地方
[root@Admin ~]# tar -czvf /tmp/backmysql/mysql.tar.gz /data/DB/*
4>挂载逻辑卷到当前mysql的数据目录里
[root@Admin ~]# mount /dev/mapper/vg01-lv_mysql /data/DB/
5>将刚刚备份的数据解压到数据目录里
[root@Admin ~]# tar xf /tmp/backmysql/mysql.tar.gz -C /data/DB/
[root@Admin ~]# mv /data/DB/data/DB/* /data/DB/ && rm -rf /data/DB/data/
6>启动数据库
[root@Admin ~]# service mysqld start
此处启动失败原因/data/DB/数据目录的权限变成了root，
更改权限重新启动
[root@Admin ~]# chown mysql. -R /data/DB/ && service mysqld start

2. Snapshot backup database

• 1. Add read lock to the database

mysql> flush table with read lock;

• 2. Create a snapshot of the logical volume where the mysql database is located

[root@Admin ~]# lvcreate -n lv_mysql_s -L 50M -s /dev/vg01/lv_mysql
[root@Admin ~]# dmsetup --tree ls
vg01-lv_mysql (253:0)
 └─vg01-lv_mysql-real (253:1)
    └─ (8:16)
vg01-lv_mysql_s (253:3)
 ├─vg01-lv_mysql_s-cow (253:2)
 │  └─ (8:16)
 └─vg01-lv_mysql-real (253:1)
    └─ (8:16)

• 3. Unlock the database

[root@Admin ~]# unlock tables

• The above steps 1~3 can be combined into one step

[root@Admin ~]# echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 50M -s /dev/vg01/lv_mysql;unlock tables;" |mysql -p123

• 4. Mount the snapshot to the temporary directory

[root@Admin ~]# mkdir /mnt/mysql && mount /dev/vg01/lv_mysql_s /mnt/mysql/

• 5. Back up data

[root@Admin ~]# ls /mnt/mysql/    # 可以看到新的挂载目录里面的数据
Admin.pid  db01     ib_logfile0  mysql             mysql-bin.000003  mysql-bin.000006  mysql-bin.000009  performance_schema
auto.cnf   db02     ib_logfile1  mysql-bin.000001  mysql-bin.000004  mysql-bin.000007  mysql-bin.000010  test
binlog     ibdata1  login        mysql-bin.000002  mysql-bin.000005  mysql-bin.000008  mysql-bin.index
[root@Admin ~]# mkdir /backup && rsync -av /mnt/mysql /backup

• 6. Uninstall the snapshot and delete it

[root@Admin ~]# umount /mnt/mysql/ && lvremove /dev/vg01/lv_mysql_s

• 7. Test and verify (delete everything in the data directory) and then restore the backed up data directory

    1>我们来点狠的，直接把mysql的数据目录/data/DB/删除。
    [root@Admin ~]# rm -rf /data/DB/* && ls /data/DB/
    2>删除后可以看到重启mysql直接报错了
    [root@Admin ~]# service mysqld restart 
    MySQL server PID file could not be found!                  [失败]
    Starting MySQL...The server quit without updating PID file [失败]/DB/Admin.pid).
    3>根据上面的/backup里面备份的数据进行恢复
    [root@Admin ~]# mv /backup/mysql/* /data/DB/
    [root@Admin ~]# ls /data/DB/
    auto.cnf  db02         ib_logfile1  mysql-bin.000001  mysql-bin.000004  mysql-bin.000007  mysql-bin.000010    test
    binlog    ibdata1      login        mysql-bin.000002  mysql-bin.000005  mysql-bin.000008  mysql-bin.index
    db01      ib_logfile0  mysql        mysql-bin.000003  mysql-bin.000006  mysql-bin.000009  performance_schema
    4>重新启动
    [root@Admin ~]# chown mysql. /data/DB/ -R 
    [root@Admin ~]# service mysqld restart
    这里权限更改了如果启动还是报错的话，查看下是否mysql进程还存在，如果存在，将其kill掉再重启就OK
    [root@Admin ~]# mysql -p123
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | binlog             |
    | db01               |
    | db02               |
    | login              |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
rows in set (0.00 sec)

Organize the above backup into a script Crontab scheduled task to complete the backup regularly

#!/bin/bash
#LVM BackMysql
back_dir=/backup/`date +%F`
[ -d $back_dir ]  mkdir -p $back_dir
echo "flush tables with read lock; system lvcreate -n lv_mysql_s -L 50M -s /dev/vg01/lv_mysql;unlock tables;" |mysql -p123
mount /dev/vg01/lv_mysql_s /mnt/mysql/
rsync -a /mnt/mysql/ $back_dir
if [ $? -eq 0 ];then
    umount /mnt/mysql/ && lvremove -f /dev/vg01/lv_mysql_s
fi

Automate the snapshot backup mylvmbackup

1. Install the corresponding software

2. Two backup methods

1>mylvmbackup xxx terminal backup

2>Modify the configuration file to specify the corresponding parameters

正常安装MySQL：
1. 安装系统
2. 准备LVM，例如 /dev/vg_back/lv-mysql，mount /usr/local/mysql
3. 源码安装MySQL到 /usr/local/mysql

可选操作：　将现在的数据迁移到LVM
1. 准备lvm及文件系统
# lvcreate -L 2G -n lv-mysql vg_back
# mkfs.ext4 /dev/vg_back/lv-mysql
 
2. 将数据迁移到LVM
# service mysqld stop
# mount /dev/vg_back/lv-mysql /u01/ //临时挂载点
# rsync -va /usr/local/mysql/ /u01/ //将MySQL原数据镜像到临时挂载点

# umount /u01/
# mount /dev/vg_back/lv-mysql /usr/local/mysql //加入fstab开机挂载
# df -Th
/dev/mapper/vg_back-lv-mysql ext4 2.0G 274M 1.7G 15% /usr/local/mysql
# service mysqld start

手动基于LVM快照实现备份：
1. 加锁
mysql> flush table with read lock;

2.创建快照
# lvcreate -L 500M -s -n lv-mysql-snap /dev/vg_back/lv-mysql
# mysql -uroot -p123 -e 'show master status' > /backup/`date +%F`_position.txt
或者
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.00003 | 135 | | | |
+-------------------+----------+--------------+------------------+-------------------+

3. 释放锁
mysql> unlock tables;

4. 从快照中备份
# mount -o ro /dev/vg_back/lv-mysql-snap /u01/
# mkdir /backup/`date +%F`
# rsync -a /u01/ /backup/2015-07-02/

5. 移除快照
# umount /u01/
# lvremove -f /dev/vg_back/lv-mysql-snap

脚本 + Cron完成：
#!/bin/bash
#LVM backmysql...
back_dir=/backup/`date +%F`

[ -d $back_dir ] || mkdir -p $back_dir

mysql -uroot -p123 -e 'flush table with read lock'
lvcreate -L 500M -s -n lv-mysql-snap /dev/vg_back/lv-mysql
mysql -uroot -p123 -e 'show master status' |grep mysql > $back_dir/position.txt
mysql -uroot -p123 -e 'flush logs'
mysql -uroot -p123 -e 'unlock tables'

mount -o ro /dev/vg_back/lv-mysql-snap /u01

rsync -a /u01/ $back_dir

if [ $? -eq 0 ];then
umount /u01/
lvremove -f /dev/vg_back/lv-mysql-snap
fi
===============================================================
mylvmbackup

功能：利用LVM快照实现物理备份，即LVM快照备份的自动版

安装perl模块
1. 在线安装
http://www.lenzg.net/mylvmbackup
它依赖于perl 模块，可用以下命令安装
perl -MCPAN -e 'install Config::IniFiles'

2. 离线安装
# rpm -ivh mylvmbackup-0.16-0.noarch.rpm
warning: mylvmbackup-0.16-0.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID b27291f2: NOKEY
error: Failed dependencies:
perl(Config::IniFiles) is needed by mylvmbackup-0.16-0.noarch
perl(Date::Format) is needed by mylvmbackup-0.16-0.noarch
perl(File::Copy::Recursive) is needed by mylvmbackup-0.16-0.noarch

解决：
# yum -y localinstall atrpms-77-1.noarch.rpm perl-File-Copy-Recursive-0.38-1.el6.rfx.noarch.rpm perl-IO-stringy-2.110-1.2.el6.rfx.noarch.rpm perl-Config-IniFiles-2.56-1.el6.rf.noarch.rpm

安装mylvmbackup软件包
# yum -y install mylvmbackup-0.15-0.noarch.rpm 解决依赖关系perl-TimeDate

备份方法一：
# mylvmbackup --user=root --password=123 --host=localhost --mycnf=/etc/my.cnf --vgname=vg_back --lvname=lv-mysql --backuptype=tar --lvsize=100M --backupdir=/backup

# tar xf backup-20140903_000236_mysql.tar.gz
# ls
backup backup-cnf-20150702_000236_mysql
backup-20150702_000236_mysql.tar.gz backup-pos

备份方法二：
# vim /etc/mylvmbackup.conf
[mysql] #连接数据库配置
user=root
password=123456
host=localhost
port=3306
socket=/tmp/mysql.sock
mycnf=/etc/my.cnf
[lvm] #LVM逻辑卷的配置
vgname=vg_server #卷组名称
lvname=lv_mysql #逻辑卷名称
backuplv=mysql_snap #快照卷名称
lvsize=500M
[fs] #文件系统配置
xfs=0
mountdir=/var/tmp/mylvmbackup/mnt/ #挂载目录
backupdir=/backup #备份目录，也可以备份到行程主机
[misc] #定义备份选项
backuptype=tar #定义备份的类型
backupretention=0
prefix=backup #定义备份文件名前缀
suffix=_mysql #定义备份文件名后缀
tararg=cvf #定义tar参数，默认为cvf
tarfilesuffix=.tar.gz #定义备份文件后缀名格式
datefmt=%Y%m%d_%H%M%S #定义备份文件名时间戳格式
keep_snapshot=0 #是否保留snaphot
keep_mount=0 #是否卸载snaphot
quiet=0 #定义记录日志类型
注释：其他配置保持输入即可

然后直接执行mylvmbackup即可

mylvmbackup 参考示例

The above is the detailed content of MySQL What is a physical backup (lvm-snapshot). For more information, please follow other related articles on the PHP Chinese website!