利用LVM快照备份mysql数据库

      mysql数据库32位【支持win10/win7】 5.7.17 官方最新版

      类型：国产软件大小：337.2M语言：中文时间：16-12-13评分：10.0

      mysql数据库64位【支持win7/win10】 5.7.17.0 官方中文最新版

      类型：国产软件大小：350.9M语言：中文时间：16-12-13评分：5.0

      一、步骤
      1、首先对数据库施加读锁
      2、记录二进制日志文件的文件名和事件位置
      3、创建快照卷
      4、解锁数据库
      5、挂载快照卷，复制数据文件
      6、删除快照卷

      二、准备工作

      首先，要对LVM的快照有所了解（http://svenman.blog.51cto.com/6867097/1357510这篇博客写了LVM的快照），并将数据文件放在LVM上。

      其次，在数据库中新建一张测试用的表

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      mysql> select * from data.info;
      +-----+-------+------+
      | sid | name  | age  |
      +-----+-------+------+
      |   1 | tom   |   25 |
      |   2 | jerry |   26 |
      |   3 | jim   |   40 |
      |   4 | ccr   |   34 |
      |   5 | dss   |   21 |
      |   6 | safe  |   44 |
      +-----+-------+------+
      6 rows in set (0.00 sec)

      三、开始备份

      1. 首先对数据库施加读锁

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

      2. 记录二进制日志文件的文件名和事件位置

      1
      2
      3
      4
      5
      6
      7
      mysql> show master status;
      +------------------+----------+--------------+------------------+-------------------+
      | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
      +------------------+----------+--------------+------------------+-------------------+
      | mysql-bin.000002 |      964 |              |                  |                   |
      +------------------+----------+--------------+------------------+-------------------+
      1 row in set (0.00 sec)

      3. 创建快照卷

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      [root@mysql ~]# cat /etc/my.cnf
      [client]
      user=root
      password=mysql
      [mysqld]
      datadir=/mysql/data
      socket=/tmp/mysql.sock
      user=mysql
      [root@mysql ~]# df -Th
      /dev/mapper/myvg-mydata
      ext4    689M  128M  527M  20% /mysql
      #可以看到mysql的数据放在myvg下的mydata这个逻辑卷上

      1
      2
      3
      [root@mysql ~]# lvcreate -n snap-data -L 100M -p r -s /dev/myvg/mydata
      Logical volume "snap-data" created
      #快照完成

      4. 解锁数据库

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

      5. 挂载快照卷，复制数据文件

      1
      2
      3
      4
      5
      6
      7
      8
      [root@mysql mnt]# mount -o ro /dev/myvg/snap-data /mnt/
      [root@mysql mnt]# cd /mnt/
      [root@mysql mnt]# ls
      data  lost+found
      [root@mysql mnt]# tar -jcvf /root/mysql-backup-`date +%F`.tar.bz2 data/
      [root@mysql mnt]# cd ~
      [root@mysql ~]# ls
      anaconda-ks.cfg  install.log  install.log.syslog  mysql-backup-2014-02-22.tar.bz2

      6. 删除快照卷

      1
      2
      3
      4
      [root@mysql ~]# umount /mnt/
      [root@mysql ~]# lvremove /dev/myvg/snap-data
      Do you really want to remove active logical volume snap-data? [y/n]: y
      Logical volume "snap-data" successfully removed

      这样就备份完成了。

      三、恢复测试

      1. 在新建的info表中再插入2行数据

      1
      2
      3
      mysql> insert into info(name,age) values('seven',30),('eight',49);
      Query OK, 2 rows affected (0.01 sec)
      Records: 2  Duplicates: 0  Warnings: 0

      2. 备份上次lvm快照开始的position号以后的二进制文件

      1
      [root@mysql ~]# mysqlbinlog --start-position=964 /mysql/data/mysql-bin.000002 > /root/bak.sql

      mysqlbinlog的用法：

      A.指定恢复时间     --start-date和--stop-date选项，格式“2014-2-22 16:33:04”

      B.指定恢复位置     --start-position和--stop-position选项

      3. 删除所有文件

      1
      2
      [root@mysql ~]# service mysqld stop
      [root@mysql ~]# rm /mysql/* -fr

      4. 开始恢复（LVM完全备份的恢复）

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      [root@mysql ~]# tar -jxvf mysql-backup-2014-02-22.tar.bz2 -C /mysql/
      [root@mysql ~]# service mysqld start
      Starting MySQL                                             [  OK  ]
      [root@mysql ~]# mysql
      mysql> select * from data.info;
      +-----+-------+------+
      | sid | name  | age  |
      +-----+-------+------+
      |   1 | tom   |   25 |
      |   2 | jerry |   26 |
      |   3 | jim   |   40 |
      |   4 | ccr   |   34 |
      |   5 | dss   |   21 |
      |   6 | safe  |   44 |
      +-----+-------+------+
      6 rows in set (0.00 sec)

      进行LVM快照后，我们新插入的数据没有恢复过来

      5. 利用二进制文件基于时间点恢复（增量备份的恢复）

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      mysql> source /root/bak.sql
      #这是第2步从二进制文件中导出的脚本
      mysql> select * from data.info;
      +-----+-------+------+
      | sid | name  | age  |
      +-----+-------+------+
      |   1 | tom   |   25 |
      |   2 | jerry |   26 |
      |   3 | jim   |   40 |
      |   4 | ccr   |   34 |
      |   5 | dss   |   21 |
      |   6 | safe  |   44 |
      |   7 | seven |   30 |
      |   8 | eight |   49 |
      +-----+-------+------+
      8 rows in set (0.00 sec)
      #新插入的2行数据也恢复了

      这样，完全备份+增量备份的备份和恢复就完成了。

      附：

      逻辑备份工具mysqldump介绍

      -u                        #指定用户名
      -p                        #指定用户密码
      -h                        #指定主机地址
      -A|--all-databases        #备份所有数据库
      --databases               #备份指定数据库
      --single-transcation      #基于此项可以实现对InnoDB表做热备份
      --lock-a--flush-logs      #备份之前刷新日志
      --lock-all-tables         #执行备份时为所有表请求加锁
      -E|--events               #备份事件调度器代码
      -R|--routines             #备份存储过程和存储函数
      --triggers                #备份触发器
      --master-data=2           #该选项将会记录binlog的日志位置与文件名并追加到文件中，如果为1将会输出CHANGE

      1
      2
      3
      4
      5
      6
      7
      #导出全库
      mysqldump  -u root  -p  --all-databases > dump.sql
      #导出单个库，或者单个库中的某个表
      mysqldump --u root -p data > dump.sql
      mysqldump --u root -p data info > dump.sql
      #导出多个数据库
      mysqldump --u root -p  --databases data mysql > dump.sql