我们从关键点开始进行；
话外：这里我们选择ext4文件格式，xfs文件系统lv、快照不可以同时挂载；
快照备份的优缺点：
优点：可以进行在线备份，备份速度快；
缺点：锁表时间不可控，快照空间一定要大于需备份数据大小；
1.锁定Mysql数据库
mysql> flush tables with read lock; Query OK, 0 rows affected (0.01 sec)
2.创建lv快照
[root@logan ~]# lvcreate -L 500M -s -n /dev/vg0/lv_sql /dev/vg0/lv0 Logical volume "lv_sql" created.
3.挂载lv快照
[root@logan ~]# mount /dev/vg0/lv_sql /opt/
4.到这里我们就可以解锁数据库了
mysql> unlock tables; Query OK, 0 rows affected (0.37 sec)
5.打包压缩备份好的数据
[root@logan ~]# tar -cjvf /backup/mysql.tar.bz2 /opt/* [root@logan backup]# ls all.sql master.000001 master.index mysql.tar.bz2
6.备份好数据后，我们就可以把快照删除了
[root@logan backup]# lvremove /dev/vg0/lv_sql Do you really want to remove active logical volume vg0/lv_sql? [y/n]: y Logical volume "lv_sql" successfully removed
到此为止我们的lv快照备份数据就完成了
============================================================================================================================================================
现在我们开始模拟数据丢失
[root@logan mysql]# cd data/ [root@logan data]# ls auto.cnf ibdata1 ibtmp1 mysql upup file ib_logfile0 logan.liunx.com.err performance_schema ib_buffer_pool ib_logfile1 logan.liunx.com.pid sys [root@logan data]# rm -rf ./*
1.进行恢复
停掉Mysql服务
[root@logan data]# killall mysqld
我们需要对压缩包进行解包
[root@logan backup]# tar -xvf mysql.tar.bz2 -C /tmp/ [root@logan opt]# cp -r ./* /usr/local/mysql/data/
2.我们cp过来的文件需要更改属主、属组
[root@logan mysql]# chown -R mysql.mysql data/ [root@logan mysql]# ll data/ 总用量 110640 -rw-r----- 1 mysql mysql 56 7月 4 22:49 auto.cnf drwxr-x--- 2 mysql mysql 4096 7月 4 22:49 file -rw-r----- 1 mysql mysql 433 7月 4 22:51 ib_buffer_pool -rw-r----- 1 mysql mysql 12582912 7月 4 22:49 ibdata1 -rw-r----- 1 mysql mysql 50331648 7月 4 22:49 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 7月 4 22:49 ib_logfile1 -rw-r----- 1 mysql mysql 11042 7月 4 22:49 logan.liunx.com.err drwxr-x--- 2 mysql mysql 4096 7月 4 22:49 mysql drwxr-x--- 2 mysql mysql 4096 7月 4 22:49 performance_schema drwxr-x--- 2 mysql mysql 12288 7月 4 22:49 sys drwxr-x--- 2 mysql mysql 4096 7月 4 22:49 upup
3.重启Mysql服务
[root@logan mysql]# systemctl restart mysqldd
4.登录数据库查看数据
[root@logan mysql]# systemctl restart mysqldd [root@logan mysql]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.25-log Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. 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>
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | file | | mysql | | performance_schema | | sys | | upup | +--------------------+ 6 rows in set (0.00 sec) mysql>
mysql> use upup; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_upup | +----------------+ | home | | t1 | +----------------+ 2 rows in set (0.00 sec) mysql> select * from t1; +------+-------+------+ | id | name | home | +------+-------+------+ | 1 | robin | 100 | | 2 | sdaa | 200 | | 3 | dfdaa | 100 | +------+-------+------+ 3 rows in set (0.00 sec) mysql>
数据恢复完成
原文：https://www.cnblogs.com/loganSxb/p/11135468.html