Mysql基于快照卷做备份

模拟基于快照的备份后恢复方案
环境：
模拟：在mydb库中，首先插入一行数据，然后模拟不小心删除了整个mydb库，
要求恢复数据库到删除数据库之前的时刻
于快照卷做备份
1. 首先对数据库施加读锁
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
2. 记录二进制文件的文件名和事件位置
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000011 |      326 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
3. 创建快照卷
[root@localhost ~]# lvcreate -L 200M -s -p r -n allenbackup /dev/myvg/mydata
  Logical volume "allenbackup" created
-p r 权限 只读卷
4. 解锁数据库
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
5. 挂在快照卷，复制数据文件
[root@localhost ~]# mkdir /allenbackup
[root@localhost ~]# mount -o ro /dev/myvg/allenbackup /allenbackup/
[root@localhost ~]# cd /allenbackup/
[root@localhost allenbackup]# ls
bak  data  lost+found
[root@localhost allenbackup]# cd data/
[root@localhost data]# ll
total 28804
-rw-rw---- 1 mysql mysql 18874368 Mar 29 23:18 ibdata1
-rw-rw---- 1 mysql mysql  5242880 Mar 29 23:18 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 Mar 29 11:05 ib_logfile1
-rw-r----- 1 mysql root      4989 Mar 29 23:16 localhost.err
-rw-rw---- 1 mysql mysql        5 Mar 29 23:16 localhost.pid
drwx------ 2 mysql mysql     4096 Mar 29 14:10 mydb
drwx------ 2 mysql root      4096 Mar 29 10:59 mysql
-rw-rw---- 1 mysql mysql      126 Mar 29 11:49 mysql-bin.000001
-rw-rw---- 1 mysql mysql      348 Mar 29 11:58 mysql-bin.000002
-rw-rw---- 1 mysql mysql      126 Mar 29 11:58 mysql-bin.000003
-rw-rw---- 1 mysql mysql      126 Mar 29 11:59 mysql-bin.000004
-rw-rw---- 1 mysql mysql      126 Mar 29 12:07 mysql-bin.000005
-rw-rw---- 1 mysql mysql      126 Mar 29 12:53 mysql-bin.000006
-rw-rw---- 1 mysql mysql     1889 Mar 29 13:16 mysql-bin.000007
-rw-rw---- 1 mysql mysql     5616 Mar 29 13:38 mysql-bin.000008
-rw-rw---- 1 mysql mysql     3437 Mar 29 14:13 mysql-bin.000009
-rw-rw---- 1 mysql mysql      182 Mar 29 22:26 mysql-bin.000010
-rw-rw---- 1 mysql mysql      326 Mar 29 23:18 mysql-bin.000011
-rw-rw---- 1 mysql mysql      209 Mar 29 23:16 mysql-bin.index
-rw-r----- 1 mysql root     45481 Mar 29 13:00 Mysql.err
-rw-rw---- 1 mysql mysql        5 Mar 29 13:00 Mysql.pid
drwx------ 2 mysql mysql     4096 Mar 29 10:59 performance_schema
drwx------ 2 mysql root      4096 Mar 29 10:58 test
# tar jcf /root/data_back.`date +%F`.tar.bz2 data/
或者
# tar jcf /root/data_back.`date +%F-%H-%M-%S`.tar.bz2 data/
引号用的ESC 下面的键
date 当前的日期
%F 时
%M 分
%S 秒
6. 删除快照卷
[root@localhost /]# umount /allenbackup/
[root@localhost /]# lvremove --force /dev/myvg/allenbackup
  Logical volume "allenbackup" successfully removed
    
7. 模拟现在对数据库mydb库某表插入数据，然后在数据mydb库
mysql> insert into allentuns values(12,"su","hui","ZhengZhou","HeNan");
Query OK, 1 row affected (0.01 sec)
mysql> drop database mydb;
Query OK, 1 row affected (0.01 sec)
8. 然后基于二进制日志对数据进行恢复
[root@localhost data]# service mysqld stop  #首先停掉服务，做灾难性恢复
[root@localhost data]# mysqlbinlog --start-position=326  mysql-bin.000011
/*!*/;
# at 394
#140330  0:51:18 server id 1  end_log_pos 520   Query   thread_id=6 exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1396155078/*!*/;
insert into allentuns values(12,"su","hui","ZhengZhou","HeNan")
/*!*/;
# at 520
#140330  0:51:18 server id 1  end_log_pos 547   Xid = 59
COMMIT/*!*/;
# at 547
#140330  0:51:36 server id 1  end_log_pos 628   Query   thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1396155096/*!*/;
drop database mydb
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost data]# mysqlbinlog --start-position=326 --stop-position=547  mysql-bin.000011 > /root/a.sql
[root@localhost data]# ll /root/
total 380
-rw-------. 1 root root    945 Mar  6 21:05 anaconda-ks.cfg
-rw-r--r--  1 root root   1572 Mar 30 00:58 a.sql
drwxr-xr-x  2 root root   4096 Mar 30 00:57 bak
-rw-r--r--. 1 root root   8785 Mar  6 21:05 install.log
-rw-r--r--. 1 root root   3161 Mar  6 21:03 install.log.syslog
-rw-r--r--  1 root root 356823 Mar 30 00:41 mydb_data.2014-03-30.tar.bz2
[root@localhost data]# cp mysql-bin.000011 /tmp/   #我们很有必要吧当时的二进制文件进行备份 已被不时只需
[root@localhost data]# ls /tmp/
mysql-bin.000010  mysql-bin.000011  mysql.sock  yum.log
[root@localhost mydata]# pwd
/mydata
[root@localhost mydata]# rm -rf data
[root@localhost mydata]# ll
total 20
drwxr-xr-x 4 root root  4096 Mar 29 23:15 bak
drwx------ 2 root root 16384 Mar 29 10:45 lost+found
[root@localhost mydata]# tar xf /root/mydb_data.2014-03-30.tar.bz2 -C .
[root@localhost mydata]# ll
total 24
drwxr-xr-x 4 root  root   4096 Mar 29 23:15 bak
drwxr-xr-x 6 mysql mysql  4096 Mar 29 23:16 data
drwx------ 2 root  root  16384 Mar 29 10:45 lost+found
[root@localhost mydata]# service mysqld start
Starting MySQL SUCCESS!
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mydb;
Database changed
mysql> select * from allentuns;
+------+----------+-----------+---------+------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+---------+------+
|    1 | zheng    | sheng     | HaiDian | BJ   |
|    2 | qin      | hen       | HaiDian | BJ   |
|    3 | wang     | yang      | HaiDian | BJ   |
|    4 | zhao     | meng      | HaiDian | BJ   |
|    6 | sun      | sheng     | HaiDian | BJ   |
|    7 | qi       | hen       | HaiDian | BJ   |
|    8 | zhu      | yang      | HaiDian | BJ   |
|    9 | guo      | meng      | HaiDian | BJ   |
|   10 | wang     | wei       | HaiDian | BJ   |
|   11 | li       | qiang     | HaiDian | Bj   |
+------+----------+-----------+---------+------+
10 rows in set (0.00 sec)
由此可看新出入的那条记录不存在的
在这里我们利用二进制日志 对其进行恢复
mysql> source /root/a.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from allentuns;
+------+----------+-----------+-----------+-------+
| Id_P | LastName | FirstName | Address   | City  |
+------+----------+-----------+-----------+-------+
|    1 | zheng    | sheng     | HaiDian   | BJ    |
|    2 | qin      | hen       | HaiDian   | BJ    |
|    3 | wang     | yang      | HaiDian   | BJ    |
|    4 | zhao     | meng      | HaiDian   | BJ    |
|    6 | sun      | sheng     | HaiDian   | BJ    |
|    7 | qi       | hen       | HaiDian   | BJ    |
|    8 | zhu      | yang      | HaiDian   | BJ    |
|    9 | guo      | meng      | HaiDian   | BJ    |
|   10 | wang     | wei       | HaiDian   | BJ    |
|   11 | li       | qiang     | HaiDian   | Bj    |
|   12 | su       | hui       | ZhengZhou | HeNan |
+------+----------+-----------+-----------+-------+
11 rows in set (0.00 sec)
可以看到最后插入的第12条记录已经存在了
到此基于快照的备份 恢复已经完成！

本文出自 “郑彦生” 博客，请务必保留此出处http://467754239.blog.51cto.com/4878013/1386780

Mysql基于快照卷做备份,古老的榕树,5-wow.com