MySQL中怎么实现实时在线备份恢复

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

1.主库准备测试数据

mysql> create database cnfol; Query OK, 1 row affected (0.00 sec)  mysql> create table cnfol.t (id int primary key); Query OK, 0 rows affected (0.02 sec)  mysql> insert into cnfol.t select 1; Query OK, 1 row affected (0.01 sec) Records: 1  Duplicates: 0  Warnings: 0  mysql> insert into cnfol.t select 2; Query OK, 1 row affected (0.00 sec) Records: 1  Duplicates: 0  Warnings: 0

到备库确认：

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | cnfol              | | mysql              | | test               | +--------------------+ 4 rows in set (0.00 sec)  mysql> select * from cnfol.t; +----+ | id | +----+ |  1 | |  2 | +----+ 2 rows in set (0.00 sec)

2.加个全局读锁 在备库

mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)

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

[root@localhost ~]# lvcreate --size 1G --snapshot --name backup_mysql /dev/vg/mysql   Logical volume "backup_mysql" created  [root@localhost ~]# lvs   LV           VG   Attr   LSize Origin snap%  Move Log Copy%  Convert   backup_mysql vg   swi-a- 1.00G mysql    0.00                           mysql        vg   owi-ao 2.00G

4.获取二进制日志坐标

在备库：

mysql> show master status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 |      727 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)

解锁 在备库：

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

5.挂载快照

[root@localhost ~]# mount /dev/vg/backup_mysql  /mnt/backup [root@localhost ~]# cd /mnt/backup/mysql/data/cnfol/ && ls -alh 总计 32K drwx------ 2 mysql dba 4.0K 10-14 09:57 . drwx------ 5 mysql dba 4.0K 10-14 09:57 .. -rw-rw---- 1 mysql dba   61 10-14 09:57 db.opt -rw-rw---- 1 mysql dba 8.4K 10-14 09:57 t.frm -rw-rw---- 1 mysql dba   14 10-14 09:57 t.MYD -rw-rw---- 1 mysql dba 2.0K 10-14 10:06 t.MYI

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

mysql> drop database cnfol; Query OK, 1 row affected (0.05 sec)

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

备库确认是否存在库cnfol:

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | test               | +--------------------+ 3 rows in set (0.01 sec)

7.备份快照

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

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

8.删除快照

[root@localhost ~]# umount /mnt/backup [root@localhost ~]# lvremove --force /dev/vg/backup_mysql    Logical volume "backup_mysql" successfully removed

9.格式化备库所在分区

[mysql@localhost ~]$ mysqladmin -uroot -poracle shutdown 131014 10:32:40 mysqld_safe mysqld from pid file /mnt/lvm/mysql/data/localhost.localdomain.pid ended [1]+  Done                    mysqld_safe  [root@localhost ~]# umount /mnt/lvm [root@localhost ~]# mkfs -t ext3 /dev/vg/mysql   [root@localhost ~]# mount /dev/vg/mysql  /mnt/lvm [root@localhost ~]# lvs   LV    VG   Attr   LSize Origin snap%  Move Log Copy%  Convert   mysql vg   -wi-ao 2.00G                                        [root@localhost ~]# vgs   VG   #PV #LV #SN Attr   VSize VFree   vg     4   1   0 wz--n- 3.81G 1.81G

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

# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm/ [root@localhost lvm]# pwd /mnt/lvm  [root@localhost lvm]# ls lost+found  mysql

11.启动MySQL

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

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

13.确认数据

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | cnfol              | | mysql              | | test               | +--------------------+ 4 rows in set (0.00 sec)  mysql> select * from cnfol.t; +----+ | id | +----+ |  1 | |  2 | +----+ 2 rows in set (0.00 sec)

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