MySQL使用LVM快照实现备份
新建一个lvm磁盘，这里我建的lv为mydatalv，挂载到了/data下
[root@localhost~]#lvs LVVGAttrLSizePoolOriginData%Meta%MoveLogCpy%SyncConvert mydatalvmydata-wi-ao----1.00g [root@localhost~]#df-h 文件系统容量已用可用已用%挂载点 /dev/mapper/mydata-mydatalv976M2.6M907M1%/data
将原数据库文件复制到/data目录下
[root@localhost~]#cp-a/var/lib/mysql/data/
修改配置文件，将mysql数据库文件放在lvm盘中，二进制文件放在非lvm盘的/var/lib/mysql/目录下
[root@ns1~]#vim/etc/my.cnf [mysqld] log_bin=/var/lib/mysql/mysql-bin datadir=/data/mysql [root@localhost~]#servicemariadbrestart [root@localhost~]#ls/data/mysql aria_log.00000001ibdata1ib_logfile1mysql-bin.000001mysql-bin.000003performance_schema aria_log_controlib_logfile0mysqlmysql-bin.000002mysql-bin.indextest
可以看到重启后数据库文件已存放在了/data/mysql目录中了
对mysql进行锁表备份
[root@localhost~]#mysql-e'flushtableswithreadlock;'锁表 [root@localhost~]#mysql-e'flushlogs;'对日志进行滚动， [root@localhost~]#mysql-e'showmasterstatus;'>/root/back.$(date+%F+%T) [root@localhost~]#ls back.2016-07-13+10:14:29
对lv创建快照
[root@localhost~]#lvcreate-L1G-nmysqlback-pr-s/dev/mydata/mydatalv
释放锁
[root@localhost~]#mysql-e'unlocktables;'
在别的磁盘上创建备份目录，只读挂载快照后备份至备份目录
[root@localhost~]#mkdir/myback [root@localhost~]#mount-r/dev/mydata/mysqlback/mnt [root@localhost~]#cp-a/mnt/mysql/myback
修改表内容，然后删除掉数据库文件内容即/data/mysql中的内容
[root@localhost~]#mysql
MariaDB[hellodb]>usehellodb;
MariaDB[hellodb]>insertintoclasses(class,numofstu)values('xxoo',39);
[root@localhost~]#rm-rf/data/*
修改配置文件中二进制日志和数据库文件的位置
[root@localhost~]#vim/etc/my.cnf [mysqld] log_bin=/data/mysql/mysql-bin datadir=/data/mysql
利用/myback/中的内容还原
[root@localhost~]#cp-a/myback/*/data/ [root@localhost~]#servicemariadbrestart
利用二进制日志还原快照后的操作，由下面这个文件来查看快照执行时二进制日志的位置
[root@localhost~]#catback.2016-07-13+10\:14\:29 FilePositionBinlog_Do_DBBinlog_Ignore_DB mysql-bin.000014245
将000014中245之后的操作做成sql文件，进行还原
[root@localhost~]#mysqlbinlog--start-position=245/var/lib/mysql/mysql-bin.000014>binlog.sql [root@localhost~]#mysql</root/binlog.sql
查看恢复情况
[root@localhost~]#mysql MariaDB[(none)]>usehellodb; MariaDB[hellodb]>select*fromclasses; +---------+----------------+----------+ |ClassID|Class|NumOfStu| +---------+----------------+----------+ |1|ShaolinPai|10| |2|EmeiPai|7| |3|QingChengPai|11| |4|WudangPai|12| |5|RiyueShenjiao|31| |6|LianshanPai|27| |7|MingJiao|27| |8|XiaoyaoPai|15| |9|xxoo|39| +---------+----------------+----------+ 9rowsinset(0.00sec)