MySQL复制+快照恢复误删除操作实验测试_MySQL
bitsCN.com
MySQL复制+快照恢复误删除操作实验测试
下面假定2个场景:
场景1:主从架构,没有延迟,某DBA误操作:drop database 【复制+快照:在线备份】
场景2:存在不确定性或者风险性较大的操作,如升级测试,大表变更【啥事都在快照上折腾,不行大不了就将之删除】
场景2比较简单,随便在新建的一个快照上折腾,搞砸就把快照删除,再新建一个,2个字:随便玩
下面我们对场景1进行模拟
恢复方法:
① 恢复备库上的快照
② 根据binlog执行point-in-time恢复
先为备库创建快照
[root@localhost ~]# vgs VG #PV #LV #SN Attr VSize VFree vg 4 1 0 wz--n- 3.81G 1.81G[root@localhost ~]# lvs LV VG Attr LSize Origin Snap% Move Log Copy% Convert mysql vg -wi-ao 2.00G [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 [root@localhost ~]# mount /dev/vg/backup_mysql /mnt/backup[root@localhost ~]# cd /mnt/backup/[root@localhost backup]# lslost+found mysql[root@localhost backup]# tar -jcv -f /mnt/snapshot/mysql.tar.bz2 *[root@localhost ~]# lvremove --force /dev/vg/backup_mysql Logical volume "backup_mysql" successfully removed
这里为什么要先备份快照再还原呢?
其一,昂贵的IO,因为磁头要在快照区和系统区来回跑
其二,快照区空间不足,因为是COW原理
在 2013-10-12 9:57 某位无经验DBA错误地执行了drop database snapshots:
在备库上确认查看:
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || test |+--------------------+
可见库已经被删除了
同时在主库或者备库查看当前的二进制日志坐标并记录下来
[mysql@localhost mysql]$ mysqladmin -uroot -poracle shutdown131012 09:59:36 mysqld_safe mysqld from pid file /mnt/lvm/mysql/data/localhost.localdomain.pid ended[1]+ Done mysqld_safe[root@localhost ~]# umount /mnt/backup[root@localhost ~]# lvremove --force /dev/vg/backup_mysql Logical volume "backup_mysql" successfully removed[root@localhost ~]# umount /mnt/lvm [root@localhost ~]# mkfs -t ext3 /dev/vg/mysql[root@localhost ~]# mount /dev/vg/mysql /mnt/lvm[root@localhost ~]# tar -jxv -f /mnt/snapshot/mysql.tar.bz2 -C /mnt/lvm[mysql@localhost ~]$ mysqld_safe &
通过binlog执行point-in-time恢复
[mysql@localhost ~]$ mysqlbinlog --stop-datetime="2013-10-12 10:9:56" /mnt/lvm/mysql/data/mysql-bin.000008 | mysql -uroot -poracle
确认数据是否恢复:
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || snapshots || test |+--------------------+4 rows in set (0.00 sec)mysql> use snapshots;Database changedmysql> show tables;+---------------------+| Tables_in_snapshots |+---------------------+| t |+---------------------+1 row in set (0.00 sec)mysql> select * from t;+----+| id |+----+| 1 |+----+1 row in set (0.00 sec)
到此,整个简单的测试就算完成了
这种复制+快照的备份架构可以实现在线实时的备份,个人感觉是个不错的备份解决方案
bitsCN.com
Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How to use Laravel to develop an online restaurant reservation system In recent years, with the rapid development of the Internet and mobile Internet, online reservations have become an indispensable part of modern people's lives. The catering industry is no exception. More and more restaurants are beginning to provide online reservation services to improve user experience and expand market share. This article will introduce how to use the Laravel framework to develop a simple but fully functional online restaurant reservation system, and provide specific code examples to facilitate readers to learn and practice. Environment setup First, we need

How to use JavaWebsocket to implement online audio and video calls? In today's digital age, real-time communication is becoming more and more common. Whether it is remote collaboration at work or remote communication with relatives and friends at home, real-time audio and video calls have become an indispensable part of people. This article will introduce how to use JavaWebsocket to implement online audio and video calls, and provide specific code examples. 1. Understand WebsocketWebsocket is a new technology in HTML5

With the popularity of e-commerce, more and more businesses are adopting online invoice management systems to manage sales and invoices. When developing an online invoice management system, it is important to choose the right tools and technology. This article will introduce how to use PHP language to develop an online invoice management system and provide some useful development guidelines. Determine system requirements Before starting development, the specific requirements for an online invoice management system need to be determined. This may include the following aspects: Invoice management: saving, querying and editing invoice information; Customer information management: saving, querying and editing customers

How to use PHP to implement a simple online music player. With the advent of the digital age, more and more people are beginning to enjoy music through the Internet, and online music players have become an important tool. In this article, we will implement a simple online music player through the PHP programming language and provide specific code examples. Preparation work: Before starting, we need to prepare the following aspects: a machine running a web server (such as Apache). PHP running environment. Music files, music files can be

How to use Laravel to develop an online customer service system Introduction: Online customer service systems play an important role in modern enterprises. It helps businesses communicate with customers in real time, answer questions, provide support, and enhance user experience. This article will introduce how to use the Laravel framework to develop a simple and practical online customer service system. 1. Design the database The online customer service system needs to store users and conversation records, so it is first necessary to design a suitable database model. In Laravel we can use the migration tool

How to use PHP to implement a simple online event registration system. With the rapid development of the Internet, more and more activities are beginning to manage the registration process through online registration systems, eliminating the trouble of traditional paper registration forms and manual processing. This article will introduce how to use PHP language to implement a simple online event registration system, and use specific code examples to help readers understand and practice. System Requirements Analysis Before developing a system, it is first necessary to clarify the requirements and functions of the system. According to the characteristics of the event registration system, we can determine the following

How to use PHP to implement a simple online order management system 1. Introduction Online order management system is a common e-commerce application. It can help merchants effectively manage the order process, speed up order processing, and improve customer satisfaction. This article will introduce how to use PHP to implement a simple online order management system, including the creation, modification, query and deletion of orders. This article assumes that readers already have some basic knowledge of PHP. 2. System Requirements The online order management system needs to meet the following basic requirements: Login function

Use JavaScript to build an online electronic signature tool. With the advent of the digital age, electronic signatures have become a fast, convenient, and secure way of business communication. When developing online electronic signature tools, JavaScript is undoubtedly a powerful language choice. This article will introduce how to use JavaScript to build a simple yet powerful online electronic signature tool, with code examples. Before we begin, we need to understand a few concepts. There are usually two types of electronic signatures: image-based signatures and vector-based signatures.
