关于云计算:详解MySQL如何进行实时在线备份恢复

42次阅读

共计 3331 个字符,预计需要花费 9 分钟才能阅读完成。

快照和复制技术的联合能够保障咱们失去一个实时的在线 MySQL 备份解决方案 当主库产生误操作时,只须要复原备库上的快照,而后再依据 binlog 执行 point-in-time 的复原即可 上面假设一个场景:主从架构,没有提早,某 DBA 误操作:drop database 接下来咱们依照以上场景进行备份复原模仿测试.

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)

6. 挂载快照

[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

7. 主库某无教训 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)

8. 备份快照


[root@localhost backup]# pwd
/mnt/backup

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

这里做备份的起因有 2 点 其一,低廉的 IO,因为磁头要在快照区和零碎区来回跑 其二,快照区空间有余,因为是 COW 原理

9. 删除快照

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

10. 格式化备库所在分区

[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

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


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

[root@localhost lvm]# ls
lost+found  mysql

12. 启动 MySQL

13. 利用 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

14. 确认数据

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)

福利:豆花同学为大家精心整顿了一份对于 linux 和 python 的学习材料大合集!有须要的小伙伴们,关注豆花集体公众号:python 头条!回复关键词“材料合集”即可收费支付!

正文完
 0