查看数据库的信息
mysql> SHOW DATABASES; #查看当前的数据库, 我们的数据库为employees+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> USE employees;
Database changed
mysql> SHOW TABLES; #查看当前库中的表
+---------------------+
| Tables_in_employees |
+---------------------+
| departments |
| dept_emp |
| dept_manager |
| employees |
| salaries |
| titles |
+---------------------+
6 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM employees; #由于篇幅原因, 我们这里只看一下employees的行数为300024
+----------+
| COUNT(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.05 sec)
创建快照卷并备份
mysql> FLUSH TABLES WITH READ LOCK; #锁定所有表Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata #创建快照卷
Logical volume "mydata-snap" created.
mysql> UNLOCK TABLES; #解锁所有表
Query OK, 0 rows affected (0.00 sec)
[root@node1 lvm_data]# mkdir /lvm_snap #创建文件夹
[root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/ #挂载snap
mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only
[root@node1 lvm_data]# cd /lvm_snap/
[root@node1 lvm_snap]# ls
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test
[root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar * #打包文件到/tmp/mysqlback.tar
[root@node1 ~]# umount /lvm_snap/ #卸载snap
[root@node1 ~]# lvremove myvg mydata-snap #删除snap
恢复数据
[root@node1 lvm_snap]# rm -rf /lvm_data/*[root@node1 ~]# service mysqld start #启动MySQL, 如果是编译安装的应该不能启动(需重新初始化), 如果rpm安装则会重新初始化数据库
mysql> SHOW DATABASES; #查看数据库, 数据丢失!
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
[root@node1 ~]# cd /lvm_data/
[root@node1 lvm_data]# rm -rf * #删除所有文件
[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar #解压备份数据库到此文件夹
[root@node1 lvm_data]# ls #查看当前的文件
employees ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.index test
mysql> SHOW DATABASES; #数据恢复了
+--------------------+
| Database |
+--------------------+
| information_schema |
| employees |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
##完成
使用Xtrabackup备份