今天就跟大家聊聊有关MySQL中怎么实现实时在线备份恢复，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

1.主库准备测试数据

mysql>createdatabasecnfol;QueryOK,1rowaffected(0.00sec)mysql>createtablecnfol.t(idintprimarykey);QueryOK,0rowsaffected(0.02sec)mysql>insertintocnfol.tselect1;QueryOK,1rowaffected(0.01sec)Records:1Duplicates:0Warnings:0mysql>insertintocnfol.tselect2;QueryOK,1rowaffected(0.00sec)Records:1Duplicates:0Warnings:0

到备库确认：

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||cnfol||mysql||test|+--------------------+4rowsinset(0.00sec)mysql>select*fromcnfol.t;+----+|id|+----+|1||2|+----+2rowsinset(0.00sec)

2.加个全局读锁 在备库

mysql>flushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)

3.为备库所在分区创建快照

[root@localhost~]#lvcreate--size1G--snapshot--namebackup_mysql/dev/vg/mysqlLogicalvolume"backup_mysql"created[root@localhost~]#lvsLVVGAttrLSizeOriginsnap%MoveLogCopy%Convertbackup_mysqlvgswi-a-1.00Gmysql0.00mysqlvgowi-ao2.00G

4.获取二进制日志坐标

在备库：

mysql>showmasterstatus;+------------------+----------+--------------+------------------+|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|+------------------+----------+--------------+------------------+|mysql-bin.000003|727|||+------------------+----------+--------------+------------------+1rowinset(0.00sec)

解锁 在备库：

mysql>unlocktables;QueryOK,0rowsaffected(0.00sec)

5.挂载快照

[root@localhost~]#mount/dev/vg/backup_mysql/mnt/backup[root@localhost~]#cd/mnt/backup/mysql/data/cnfol/&&ls-alh总计32Kdrwx------2mysqldba4.0K10-1409:57.drwx------5mysqldba4.0K10-1409:57..-rw-rw----1mysqldba6110-1409:57db.opt-rw-rw----1mysqldba8.4K10-1409:57t.frm-rw-rw----1mysqldba1410-1409:57t.MYD-rw-rw----1mysqldba2.0K10-1410:06t.MYI

6.主库某无经验DBA误操作

mysql>dropdatabasecnfol;QueryOK,1rowaffected(0.05sec)

记录下此时时间：2013-10-14 10:17:10

备库确认是否存在库cnfol:

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||mysql||test|+--------------------+3rowsinset(0.01sec)

7.备份快照

[root@localhostbackup]#pwd/mnt/backup[root@localhostbackup]#tar-jcv-f/mnt/snapshot/mysql.tar.bz2*

这里做备份的原因有2点 * 其一，昂贵的IO，因为磁头要在快照区和系统区来回跑 * 其二，快照区空间不足，因为是COW原理

8.删除快照

[root@localhost~]#umount/mnt/backup[root@localhost~]#lvremove--force/dev/vg/backup_mysqlLogicalvolume"backup_mysql"successfullyremoved

9.格式化备库所在分区

[mysql@localhost~]$mysqladmin-uroot-poracleshutdown13101410:32:40mysqld_safemysqldfrompidfile/mnt/lvm/mysql/data/localhost.localdomain.pidended[1]+Donemysqld_safe[root@localhost~]#umount/mnt/lvm[root@localhost~]#mkfs-text3/dev/vg/mysql[root@localhost~]#mount/dev/vg/mysql/mnt/lvm[root@localhost~]#lvsLVVGAttrLSizeOriginsnap%MoveLogCopy%Convertmysqlvg-wi-ao2.00G[root@localhost~]#vgsVG#PV#LV#SNAttrVSizeVFreevg410wz--n-3.81G1.81G

10.解压缩快照到备库所在分区

#tar-jxv-f/mnt/snapshot/mysql.tar.bz2-C/mnt/lvm/[root@localhostlvm]#pwd/mnt/lvm[root@localhostlvm]#lslost+foundmysql

11.启动MySQL

12.利用binlog执行point-in-time恢复

[mysql@localhost~]$mysqlbinlog--stop-datetime="2013-10-1410:17:10"/mnt/lvm/mysql/data/mysql-bin.000003|mysql-uroot-poracle

13.确认数据

mysql>showdatabases;+--------------------+|Database|+--------------------+|information_schema||cnfol||mysql||test|+--------------------+4rowsinset(0.00sec)mysql>select*fromcnfol.t;+----+|id|+----+|1||2|+----+2rowsinset(0.00sec)

看完上述内容，你们对MySQL中怎么实现实时在线备份恢复有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。