MySQL数据库通过LVM快照实现数据备份

数据库备份温馨提醒：常见数据库备份方式有“热备”、“温备”、“冷备”。

 

1）冷备：停库、停服务来备份
即当数据库进行备份时, 数据库不能进行读写操作, 即数据库要下线

2）温备：不停库、不停服务来备份，会(锁表)阻止用户的写入 
即当数据库进行备份时, 数据库的读操作可以执行, 但是不能执行写操作 

3）热备（建议）：不停库、不停服务来备份，也不会(锁表)阻止用户的写入
即当数据库进行备份时, 数据库的读写操作均不是受影响

LVM数据库备份优缺点：

优点
    几乎是热备 (创建快照前把表上锁，创建完后立即释放)
    支持所有存储引擎
    备份速度快
    无需使用昂贵的商业软件(它是操作系统级别的)
缺点：
    需要系统管理员权限，一般的DBA是没有的这个权限的。
    无法预计停机时间
    如果数据不是存在LVM上，则需要停机迁移数据到LVM上

 

LVM数据库备份过程：

 1）给加全局读锁
 2）LVM快照
 3）释放锁
 4)挂载快照卷
 5)拷贝快照卷数据
 6)卸载被删除快照卷

创建LVM逻辑卷架构

添加一块磁盘

#磁盘热刷新或者重启

[root@mysql1 ~]# alias scandisk='echo - - - > /sys/class/scsi_host/host0/scan;echo - - - > /sys/class/scsi_host/host1/scan;echo - - - > /sys/class/scsi_host/host2/scan'

[root@mysql1 ~]# scandisk 

#查看磁盘信息
[root@mysql1 ~]# lsblk 
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   30G  0 disk 
├─sda1            8:1    0    2M  0 part 
├─sda2            8:2    0    1G  0 part /boot
└─sda3            8:3    0   29G  0 part 
  ├─centos-root 253:0    0   26G  0 lvm  /
  └─centos-swap 253:1    0    3G  0 lvm  [SWAP]
sdb               8:16   0   20G  0 disk 
sr0              11:0    1  4.4G  0 rom  
[root@mysql1 ~]#  fdisk /dev/sdb 
命令(输入 m 获取帮助)：n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): 
Using default response p
分区号 (1-4，默认 1)：
起始 扇区 (2048-41943039，默认为 2048)：
将使用默认值 2048
Last 扇区, +扇区 or +size{K,M,G} (2048-41943039，默认为 41943039)：
将使用默认值 41943039
分区 1 已设置为 Linux 类型，大小设为 20 GiB

命令(输入 m 获取帮助)：w
The partition table has been altered!

Calling ioctl() to re-read partition table.
正在同步磁盘。
[root@mysql1 ~]# lsblk 
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   30G  0 disk 
├─sda1            8:1    0    2M  0 part 
├─sda2            8:2    0    1G  0 part /boot
└─sda3            8:3    0   29G  0 part 
  ├─centos-root 253:0    0   26G  0 lvm  /
  └─centos-swap 253:1    0    3G  0 lvm  [SWAP]
sdb               8:16   0   20G  0 disk 
└─sdb1            8:17   0   20G  0 part 
sr0              11:0    1  4.4G  0 rom  

创建LVM逻辑卷

#创建pv物理卷、vg卷组

[root@mysql1 ~]# vgcreate vg01 /dev/sdb1
  Physical volume "/dev/sdb1" successfully created.
  Volume group "vg01" successfully created

#创建LVM逻辑卷
[root@mysql1 ~]# lvcreate -n lv01 -L 5G vg01 
  Logical volume "lv01" created.

#查看逻辑卷
[root@mysql1 ~]# lvs
  LV   VG     Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  root centos -wi-ao---- <26.00g                                                    
  swap centos -wi-ao----   3.00g                                                    
  lv01 vg01   -wi-a-----   5.00g          

#查看卷组                                         
[root@mysql1 ~]# vgs
  VG     #PV #LV #SN Attr   VSize   VFree  
  centos   1   2   0 wz--n- <29.00g      0 
  vg01     1   1   0 wz--n- <20.00g <15.00g

#创建挂载点
[root@mysql1 ~]# mkdir /data

#格式化文件系统
[root@mysql1 ~]# mkfs.xfs /dev/vg01/lv01 
meta-data=/dev/vg01/lv01         isize=512    agcount=4, agsize=327680 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=1310720, imaxpct=25
         =                       sunit=0      swidth=0 blks
naming   =version 2              bsize=4096   ascii-ci=0 ftype=1
log      =internal log           bsize=4096   blocks=2560, version=2
         =                       sectsz=512   sunit=0 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0

#逻辑卷挂载
[root@mysql1 ~]# mount /dev/vg01/lv01 /data/
[root@mysql1 ~]# lsblk 
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   30G  0 disk 
├─sda1            8:1    0    2M  0 part 
├─sda2            8:2    0    1G  0 part /boot
└─sda3            8:3    0   29G  0 part 
  ├─centos-root 253:0    0   26G  0 lvm  /
  └─centos-swap 253:1    0    3G  0 lvm  [SWAP]
sdb               8:16   0   20G  0 disk 
└─sdb1            8:17   0   20G  0 part 
  └─vg01-lv01   253:2    0    5G  0 lvm  /data
sr0              11:0    1  4.4G  0 rom  

MySQL数据迁移

#将 /var/lib/mysql 所有文件目录临时备份到 挂载点/data下

mount  /dev/vg01/lv01   /data

# 设置自动挂载

#将/dev/vg01/lv01  挂载到/var/lib/mysql 

[root@mysql1 data]# cat /etc/fstab 

#
# /etc/fstab
# Created by anaconda on Fri Dec  1 08:48:15 2023
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/vg01/lv01 /var/lib/mysql    xfs    defaults    0 0
 

#查看挂载情况

[root@mysql1 data]# mount -a
[root@mysql1 data]# lsblk 
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   30G  0 disk 
├─sda1            8:1    0    2M  0 part 
├─sda2            8:2    0    1G  0 part /boot
└─sda3            8:3    0   29G  0 part 
  ├─centos-root 253:0    0   26G  0 lvm  /
  └─centos-swap 253:1    0    3G  0 lvm  [SWAP]
sdb               8:16   0   20G  0 disk 
└─sdb1            8:17   0   20G  0 part 
  └─vg01-lv01   253:2    0    5G  0 lvm  /var/lib/mysql
sr0              11:0    1  4.4G  0 rom  
 

#查看数据

#挂载需要授权，否则启动mysql服务会报错

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql   ##修改权限
[root@localhost ~]# systemctl start mysqld    #启动数据库

MySQL快照备份

#开启读锁（保证数据备份完整，以免备份时有数据写入）

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

 

#测试数据库读锁是否开启成功

mysql> select * from books;
+------+--------+----------+----------------+------+
| id   | title  | author   | year_published | isbn |
+------+--------+----------+----------------+------+
| NULL | Book 2 | Author 4 | 2021           | NULL |
| NULL | Book 3 | Author 3 | 2022           | NULL |
+------+--------+----------+----------------+------+
2 rows in set (0.00 sec)

mysql> insert into books values(3,'Book 4','Author 5','2023');
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
 

演示全局锁的概念

• 注意开启binlog日志：vim /etc/my.cnf

• log-bin=mysql-binlog
server-id=1

参数说明：

-L 指定逻辑空间大小1G
-s 代表是snapshot快照
-n 指定快照名称

创建快照时不要将锁终端关闭

lvcreate -L 1G -s -n lv-mysql-snap01 /dev/vg01/lv01   ##创建快照
[root@localhost ~]# mkdir /backup
[root@localhost backup]# mysql -uroot -p123456 -e 'show master status'>/backup/`date +%F`_position.txt
mysql: [Warning] Using a password on the command line interface can be insecure.

[root@localhost backup]# ls   /backup
2021-11-23_position.txt

 

#如果我们分开写了，当我开始加锁了之后，命令就要跳出数据库中，等于锁又自动解开了，因此是没有意义的，所以建议使用如下的办法：

[root@localhost ~]# echo "FLUSH TABLES WITH READ LOCK; SYSTEM lvcreate -L 1G -s -n mysql-snap /dev/vg01/lv01;" |mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
File descriptor 3 (socket:[44391]) leaked on lvcreate invocation. Parent PID 13783: mysql
  Logical volume "mysql-snap" created. 

#将新建的快照以只读挂载起来

[root@localhost ~]# mount -o ro /dev/vg01/mysql-snap /data
[root@localhost ~]# cd /data/
[root@localhost data]# ls
auto.cnf    client-cert.pem  ib_buffer_pool  ib_logfile1  mysql-binlog.000001  mysql.sock.lock     public_key.pem   sys
ca-key.pem  client-key.pem   ibdata1         ibtmp1       mysql-binlog.index   performance_schema  server-cert.pem
ca.pem      HA               ib_logfile0     mysql        mysql.sock           private_key.pem     server-key.pem

 

#备份快照中的数据

[root@localhost ~]# mkdir /beifen
[root@localhost data]# tar cvf /beifen/mysql_snap_`date +%F`.tar.gz ./*

 

#取消挂载

umount        /data

 

#移除快照

[root@localhost /]# lvremove -f /dev/vg01/mysql-snap 
  Logical volume "mysql-snap" successfully remove

[root@localhost ~]# tar xf mysql_snap_2021-11-24_mysql.tar.gz   ##解压
[root@localhost ~]# cd backup/
[root@localhost backup]# ls
2021-11-24
[root@localhost backup]# cd 2021-11-24/
[root@localhost 2021-11-24]# ls
auto.cnf    ca.pem           client-key.pem  ib_buffer_pool  ib_logfile0  ibtmp1      mysql            performance_schema  public_key.pem   server-key.pem
ca-key.pem  client-cert.pem  HA              ibdata1         ib_logfile1  lost+found  mysql.sock.lock  private_key.pem     server-cert.pem  sys
[root@localhost 2021-11-24]# rm -rf /var/lib/mysql/*    ###模拟数据丢失
[root@localhost 2021-11-24]# cp -a * /var/lib/mysql
[root@localhost 2021-11-24]# chown -R mysql.mysql /var/lib/mysql    ##修改权限
[root@localhost 2021-11-24]# systemctl restart mysqld      ##重启服务
[root@localhost 2021-11-24]# mysql -uroot -p123456      ##测试登录

模拟恢复数据并进行登录测试：

[root@mysql1 mysql]# mysql -uroot -p123456

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.44 MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

 

#登录成功则表示数据备份快照有效