1、首先对数据库施加读锁
2、记录二进制日志文件的文件名和事件位置
3、创建快照卷
4、解锁数据库
5、挂载快照卷,复制数据文件
6、删除快照卷
[root@station58 ~]#mysql
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.02 sec)
mysql> show master status;
+------------------+----------++--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)
#创建快照卷
[root@station58 ~]# lvcreate -L 150M -s -p r -n mybackup /dev/myvg/mydata #-p 带权限 r 只读方式 -n 快照卷的名字
Rounding up size to full physical extent 152.00 MB
Logical volume "mybackup" created
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
[root@station58 ~]# mkdir /backup
[root@station58 ~]# mount -o ro /dev/myvg/mybackup /backup/
[root@station58 ~]# cd /backup
[root@station58 backup]# tar jcf /root/data_back-`date +%F`.tar.bz2 data/
[root@station58 backup]#cd
[root@station58 ~]# ll -h
[root@station58 ~]# tar xf data_back-2012-02-04.tar.bz2 -C /tmp
[root@station58 ~]# cd /tmp
[root@station58 tmp]# ls
data keyring-rzxDba orbit-root
gconfd-root mapping-root ssh-lviWWn3623
keyring-QKdZbd mysql.sock virtual-root.THhkv1
[root@station58 data]# cd ..
[root@station58 tmp]# rm -rf data/
[root@station58 tmp]# cd
[root@station58 ~]# umount /backup
[root@station58 ~]# lvremove --force /dev/myvg/mybackup
Logical volume "mybackup" successfully removed
但如果备份完成后我们对数据库做了误操作我们又如何恢复呢
mysql> create database www;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| www |
+--------------------+
5 rows in set (0.03 sec)
mysql> drop database www;
Query OK, 0 rows affected (0.08 sec)
mysql> \q;
[root@station58 data]# mysqlbinlog --start-position=107 mysql-bin.000001
[root@station58 data]# mysqlbinlog --start-position=107 --stop-position 188 mysql-bin.000001 >/root/a.sql
[root@station58 data]# cp mysql-bin.000001 /tmp
[root@station58 data]# cd /mydata
[root@station58 mydata]# rm -rf data
[root@station58 mydata]# cd
[root@station58 ~]# tar vxf data_back-2012-02-04.tar.bz2 -C /mydata
[root@station58 ~]# cd /mydata/data
[root@station58 data]# ls
ibdata1 mysql performance_schema test
ib_logfile0 mysql-bin.000001 station58.example.com.err
ib_logfile1 mysql-bin.index station58.example.com.pid
[root@station58 data]# service mysqld start
Starting MySQL [ OK ]
#重新连接mysql数据库
[root@station58 data]# mysql
#查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.01 sec)
mysql> source /root/a.sql
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
| www |
+--------------------+
5 rows in set (0.00 sec)