使用LVM对数据库进行备份还原

基于逻辑备份（使用mysqldump备份之后保存为SQL语句）对数据进行备份、还原应该把二进制日志功能关闭，因为如果逻辑备份中包含所有的数据，如果备份或者是还原开启二进制日志，只会徒增二进制日志的大小，并且增加对磁盘的IO读写。

前提：

1. 数据文件要在逻辑卷上
2. 此逻辑卷组必须有足够的空间使用快照卷
3. 数据文件和事务日志要在同一个逻辑卷上

步骤：

1. 打开会话，施加读锁，锁定所有表

   mysql> flush tables with read lock;
   mysql> flush logs;

2. 通过另一个终端，保存二进制日志文件及相关位置信息

   mysql -uroot -p -e ‘show master status\G’ > /path/to/master.info

3. 创建快照卷

   lvcreate -L 50M -s -p -r -n LV_NAME /path/to/source_lv

4. 释放锁

   mysql > unlock tables;

5. 挂载快照卷、备份

   mount、cp
   

6. 删除快照卷

7. 增量备份二进制日志

实操过程

创建LVM的快照

锁表、滚动二进制日志、

mysql> show master status;

+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000006 | 172313713 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.06 sec)


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

mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000007 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

记录此时的二进制日志节点

[root@miner-k ~]# mysql -e 'show master status' > master-`date +%F`.info

创建LVM快照

[root@miner-k ~]# lvcreate -L 50M -s -p r -n mydata-snap /dev/myvg/mydata 
  Rounding up size to full physical extent 52.00 MiB
  Logical volume "mydata-snap" created.

释放锁

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

数据全量备份

挂载快照

[root@miner-k ~]# mount /dev/myvg/mydata-snap /mnt -o ro
[root@miner-k ~]# ls /mnt/
back  data  employee  lost+found

拷贝文件到指定的存储位置

[root@miner-k ~]# cd /mnt/
[root@miner-k mnt]# cp -a data/ /backupMysql/fullback-2018-07-09/

卸载快照的挂载，并删除挂载

[root@miner-k mnt]# cd 
[root@miner-k ~]# umount /mnt/
[root@miner-k ~]# lvremove --force /dev/myvg/mydata-snap 
  Logical volume "mydata-snap" successfully removed

备份快照之后的二进制日志

查看当前的二进制日志的状态

[root@miner-k ~]# cat master-2018-07-09.info 
File    Position    Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000007    107     
[root@miner-k ~]# mysql -e 'show master status;'
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      349 |              |                  |
+------------------+----------+--------------+------------------+

备份二进制日志

[root@miner-k ~]# mysqlbinlog --start-position=107 /mydata/data/mysql-bin.000007 > /backupMysql/addback/mysql-bin-000007.sql
[root@miner-k ~]# mysqlbinlog  /mydata/data/mysql-bin.000008 > /backupMysql/addback/mysql-bin-000008.sql

或者基于时间来保存

[root@miner-k ~]# mysqlbinlog --start-datetime='2018-07-09  9:26:10' /mydata/data/mysql-bin.000007 /mydata/data/mysql-bin.000008 > /backupMysql/addback/2018-0710.sql

删除数据库中的数据

[root@miner-k ~]# cd /mydata/data/
[root@miner-k data]# rm -rf *

数据恢复

关闭数据库

[root@miner-k data]# service mysqld stop
Shutting down MySQL....                                    [  OK  ]

恢复全部备份

[root@miner-k data]# pwd
/mydata/data

[root@miner-k data]# cp -a /backupMysql/fullback-2018-07-09/data/* .

[root@miner-k data]# service mysqld start
Starting MySQL...                                          [  OK  ]

[root@miner-k data]# mysql < /backupMysql/addback/2018-0710.sql