MySQL数据库备份&恢复（备份恢复）【备份策略五：lvm快照备份数据库（物理角度实现的完全备份）】

前提

• 数据文件要在逻辑卷上；
• 此逻辑卷所在卷组必须有足够空间使用快照卷；
• 数据文件和事务日志要在同一个逻辑卷上；
• MySQL数据lv和将要创建的快照要在同一vg内（vg要有足够的空间存储）；

优点

1. 几乎是热备（创建快照前把表上锁，创建完毕后立即释放）；
2. 支持所有的存储引擎；
3. 备份速度快；
4. 无需使用昂贵的商业软件（操作系统级别的）；

缺点

1. 可能需要部门协调（使用操作系统级别的命令，DBA一般没有权限）；
2. 无法预计服务停止时间；
3. 数据如果分布在多个卷上比较麻烦；

流程

1. 创建LVM逻辑卷并将数据库迁移到逻辑卷中

[root@localhost ~]# lsblk  		//查看sdb硬盘大小（判断是否满足备份使用）
NAME            MAJ:MIN RM  SIZE RO TYPE MOUNTPOINT
sda               8:0    0   60G  0 disk 
├─sda1            8:1    0    1G  0 part /boot
└─sda2            8:2    0   59G  0 part 
  ├─centos-root 253:0    0 38.3G  0 lvm  /
  ├─centos-swap 253:1    0    2G  0 lvm  [SWAP]
  └─centos-home 253:3    0 18.7G  0 lvm  /home
sdb               8:16   0    6G  0 disk 
sr0              11:0    1  973M  0 rom  

[root@localhost ~]# pvcreate /dev/sdb		//创建pv卷
[root@localhost ~]# vgcreate vg1 /dev/sdb		//创建vg卷（vg1）
[root@localhost ~]# lvcreate -n lv_mysql -L 4G vg1		//创建lv卷（lv_mysql 4G）
[root@localhost ~]# mkfs.xfs /dev/vg1/lv_mysql 		//格式化为xfs文件系统格式
meta-data=/dev/vg1/lv_mysql      isize=512    agcount=4, agsize=262144 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=0, sparse=0
data     =                       bsize=4096   blocks=1048576, 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@localhost ~]# systemctl stop mysqld		//关闭MySQL服务

[root@localhost ~]# cd /var/lib/mysql		//切换至数据库数据目录
[root@localhost mysql]# tar -czvf /backup/MySql/mysql.tar.gz *		//将数据目录下的所有数据压缩到/backup/MySql/mysql.tar.gz目录（暂时备份，后面的逻辑卷挂载操作会清空数据库数据目录）
[root@localhost mysql]# ll /backup/MySql/mysql.tar.gz		//查看压缩结果 
-rw-r--r-- 1 root root 739007 Apr 30 15:36 /backup/MySql/mysql.tar.gz

[root@localhost mysql]# mount /dev/vg1/lv_mysql /var/lib/mysql/		//挂载逻辑卷
[root@localhost mysql]# ll /var/lib/mysql/		//可以看到挂载之后数据库数据目录清空
total 0

[root@localhost mysql]# tar xf /backup/MySql/mysql.tar.gz -C /var/lib/mysql/	//解压数据（恢复数据目录原数据）
[root@localhost mysql]# ll /var/lib/mysql		//查看恢复结果（可以看到数据已恢复成功）
total 110680
-rw-r----- 1 mysql mysql       56 Apr 30 00:20 auto.cnf
-rw------- 1 mysql mysql     1680 Apr 30 00:20 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 client-cert.pem
-rw------- 1 mysql mysql     1676 Apr 30 00:20 client-key.pem
drwxr-x--- 2 mysql mysql       48 Apr 30 00:19 db1
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 homework1
-rw-r----- 1 mysql mysql      426 Apr 30 13:51 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 30 13:51 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 30 13:51 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 30 00:20 ib_logfile1
drwxr-x--- 2 mysql mysql       58 Apr 30 00:19 it
drwxr-x--- 2 mysql mysql     4096 Apr 30 00:19 mysql
-rw-r----- 1 mysql mysql      177 Apr 30 00:33 mysql-bin.000001
-rw-r----- 1 mysql mysql      177 Apr 30 13:51 mysql-bin.000002
-rw-r----- 1 mysql mysql       38 Apr 30 13:44 mysql-bin.index
drwxr-x--- 2 mysql mysql     8192 Apr 30 00:19 performance_schema
-rw------- 1 mysql mysql     1676 Apr 30 00:20 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Apr 30 00:20 public_key.pem
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 school
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 server-cert.pem
-rw------- 1 mysql mysql     1680 Apr 30 00:20 server-key.pem
drwxr-x--- 2 mysql mysql       56 Apr 30 00:19 student
drwxr-x--- 2 mysql mysql     8192 Apr 30 00:19 sys
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 test
drwxr-x--- 2 mysql mysql      138 Apr 30 00:19 test1
drwxr-x--- 2 mysql mysql       58 Apr 30 00:19 test2
-rw-r----- 1 mysql mysql       21 Apr 30 00:19 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      659 Apr 30 00:19 xtrabackup_info

[root@localhost mysql]# chown -R mysql:mysql /var/lib/mysql
//修改数据目录所属组和所属用户，刚刚执行的操作导致所属组和所属用户均变为root（不更改后面MySQL服务无法启动）

[root@localhost ~]# systemctl stop mysqld		//启动MySQL服务

2. 锁表（避免快照创建时lv卷出现新的变动）【lv卷挂载在数据目录】

#给数据库加读锁
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)

3. 创建快照（snapshot）

[root@localhost mysql]# lvcreate -n lv_mysql_snap -L 500M -s /dev/vg1/lv_mysql		
//创建500M的快照卷（lv_mysql_snap）
  Logical volume "lv_mysql_snap" created.

[root@localhost mysql]# lvs		//查看lv卷情况
  LV            VG     Attr       LSize   Pool Origin   Data%  Meta%  Move Log Cpy%Sync Convert
  home          centos -wi-ao---- <18.70g                                                      
  root          centos -wi-ao---- <38.30g                                                      
  swap          centos -wi-ao----   2.00g                                                      
  lv_mysql      vg1    owi-aos---   4.00g                                                      
  lv_mysql_snap vg1    swi-a-s--- 500.00m      lv_mysql 0.00                                   

4. 解表

#解锁数据库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

5. 挂载快照（挂载到临时目录，临时目录自行创建）【相当于备份了数据】

[root@localhost mysql]# mkdir /mnt/mysql		//创建临时目录
[root@localhost mysql]# ll /mnt/mysql/		//临时目录为空
total 0

[root@localhost mysql]# mount -o nouuid /dev/vg1/lv_mysql_snap /mnt/mysql/		//挂载快照
//注：必须添加参数“-o nouuid”，否则挂载报错（快照卷uuid与lv卷uuid一致，系统会认为一致，需要去除uuid）
[root@localhost mysql]# ll /mnt/mysql/		//再次查看临时目录
total 110684
-rw-r----- 1 mysql mysql       56 Apr 30 00:20 auto.cnf
-rw------- 1 mysql mysql     1680 Apr 30 00:20 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 client-cert.pem
-rw------- 1 mysql mysql     1676 Apr 30 00:20 client-key.pem
drwxr-x--- 2 mysql mysql       48 Apr 30 00:19 db1
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 homework1
-rw-r----- 1 mysql mysql      426 Apr 30 13:51 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 30 13:51 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 30 13:51 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 30 00:20 ib_logfile1
drwxr-x--- 2 mysql mysql       58 Apr 30 00:19 it
drwxr-x--- 2 mysql mysql     4096 Apr 30 00:19 mysql
-rw-r----- 1 mysql mysql      177 Apr 30 00:33 mysql-bin.000001
-rw-r----- 1 mysql mysql      177 Apr 30 13:51 mysql-bin.000002
-rw-r----- 1 mysql mysql       38 Apr 30 13:44 mysql-bin.index
srwxrwxrwx 1 mysql mysql        0 Apr 30 15:45 mysql.sock
-rw------- 1 mysql mysql        5 Apr 30 15:45 mysql.sock.lock
drwxr-x--- 2 mysql mysql     8192 Apr 30 00:19 performance_schema
-rw------- 1 mysql mysql     1676 Apr 30 00:20 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Apr 30 00:20 public_key.pem
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 school
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 server-cert.pem
-rw------- 1 mysql mysql     1680 Apr 30 00:20 server-key.pem
drwxr-x--- 2 mysql mysql       56 Apr 30 00:19 student
drwxr-x--- 2 mysql mysql     8192 Apr 30 00:19 sys
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 test
drwxr-x--- 2 mysql mysql      138 Apr 30 00:19 test1
drwxr-x--- 2 mysql mysql       58 Apr 30 00:19 test2
-rw-r----- 1 mysql mysql       21 Apr 30 00:19 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      659 Apr 30 00:19 xtrabackup_info
//可看出临时目录已拥有全部数据目录中的数据

6. 拷贝快照备份数据至数据目录【备份整个数据库之前关闭mysql服务（保护ibdata1文件）】

[root@localhost mysql]# systemctl stop mysqld		//关闭MySQL服务

[root@localhost mysql]# cp -a /mnt/mysql/\* /backup/mysql_snap_bak/		
//将临时目录中所有文件复制到备份目录下
//注：上面命令的“*”前面的“\”表示转译“*”，不然CSDN会理解为注释，复制到Linux中记得把“\”去掉
[root@localhost mysql]# ll /backup/mysql_snap_bak/		//查看备份目录
total 110684
-rw-r----- 1 mysql mysql       56 Apr 30 00:20 auto.cnf
-rw------- 1 mysql mysql     1680 Apr 30 00:20 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 client-cert.pem
-rw------- 1 mysql mysql     1676 Apr 30 00:20 client-key.pem
drwxr-x--- 2 mysql mysql       48 Apr 30 00:19 db1
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 homework1
-rw-r----- 1 mysql mysql      426 Apr 30 13:51 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 30 13:51 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 30 13:51 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 30 00:20 ib_logfile1
drwxr-x--- 2 mysql mysql       58 Apr 30 00:19 it
drwxr-x--- 2 mysql mysql     4096 Apr 30 00:19 mysql
-rw-r----- 1 mysql mysql      177 Apr 30 00:33 mysql-bin.000001
-rw-r----- 1 mysql mysql      177 Apr 30 13:51 mysql-bin.000002
-rw-r----- 1 mysql mysql       38 Apr 30 13:44 mysql-bin.index
srwxrwxrwx 1 mysql mysql        0 Apr 30 15:45 mysql.sock
-rw------- 1 mysql mysql        5 Apr 30 15:45 mysql.sock.lock
drwxr-x--- 2 mysql mysql     8192 Apr 30 00:19 performance_schema
-rw------- 1 mysql mysql     1676 Apr 30 00:20 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Apr 30 00:20 public_key.pem
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 school
-rw-r--r-- 1 mysql mysql     1112 Apr 30 00:20 server-cert.pem
-rw------- 1 mysql mysql     1680 Apr 30 00:20 server-key.pem
drwxr-x--- 2 mysql mysql       56 Apr 30 00:19 student
drwxr-x--- 2 mysql mysql     8192 Apr 30 00:19 sys
drwxr-x--- 2 mysql mysql       92 Apr 30 00:19 test
drwxr-x--- 2 mysql mysql      138 Apr 30 00:19 test1
drwxr-x--- 2 mysql mysql       58 Apr 30 00:19 test2
-rw-r----- 1 mysql mysql       21 Apr 30 00:19 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql      659 Apr 30 00:19 xtrabackup_info
//可以看到备份目录已拥有所有备份数据

[root@localhost mysql]# chown -R mysql:mysql /backup/mysql_snap_bak/
//修改备份目录所属组和所属用户，刚刚执行的操作导致所属组和所属用户均变为root（不更改后面MySQL服务无法启动）
[root@localhost mysql]# ll /backup/		//查看所属组和所属用户修改结果
total 20
drwxr-xr-x 11 mysql mysql 4096 Apr 20 09:49 2022-04-20
drwxr-xr-x 13 root  root  4096 Apr 30 00:19 full
drwxr-x---  4 root  root    60 Apr 30 00:13 incremental
drwxr-x--- 13 root  root  4096 Apr 30 00:18 incremental1
drwxr-x--- 13 root  root  4096 Apr 30 00:19 incremental2
drwxr-xr-x  3 root  root   200 Apr 30 15:36 MySql
drwxr-xr-x 13 mysql mysql 4096 Apr 30 15:58 mysql_snap_bak

7. 卸载快照卷同时移除快照

[root@localhost mysql]# umount /mnt/mysql 	//卸载快照卷
[root@localhost mysql]# lvremove /dev/vg1/lv_mysql_snap 	//移除快照卷
[root@localhost mysql]# lvs		//查看结果
  LV       VG     Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  home     centos -wi-ao---- <18.70g                                                    
  root     centos -wi-ao---- <38.30g                                                    
  swap     centos -wi-ao----   2.00g                                                    
  lv_mysql vg1    -wi-ao----   4.00g                

8. 测试

[root@Admin ~]# vim /etc/my.cnf
//将配置文件的数据目录路径定位修改为快照备份目录（建议将旧的进行注释而不是删除）
datadir=/backup/mysql_snap_bak

[root@Admin ~]# systemctl start mysqld		//启动MySQL服务

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| homework1          |
| it                 |
| mysql              |
| performance_schema |
| school             |
| student            |
| sys                |
| test               |
| test1              |
| test2              |
+--------------------+
12 rows in set (0.00 sec)
#可以看到全部数据库均存在（此处偷懒，不往后检查了，不过肯定没问题）

shell脚本 + Crontab演示

#!/bin/bash
    back_dir=/backup/`date +%F`		#创建时间戳目录会更好（容易区分备份时间）
    [ -d $back_dir ]||  mkdir -p $back_dir
    echo "flush tables with read lock; system lvcreate -n lv_mysql_snap -L 500M -s /dev/vg1/lv_mysql;unlock tables;" | mysql -uroot -p123 &>/dev/null
    [ -d /mnt/mysql/ ] || mkdir -p /mnt/mysql/
    mount -o nouuid /dev/vg1/lv_mysql_snap /mnt/mysql/
    cp -a /mnt/mysql/ $back_dir
	chown -R mysql:mysql $back_dir
    if [ $? -eq 0 ];then
        umount /mnt/mysql/ && lvremove -f /dev/vg1/lv_mysql_snap &>/dev/null
    fi

Crontab很简单就不演示了（有点想偷懒）