如何使用ZRM for MySQL执行时间点恢复

如何使用ZRM for MySQL执行时间点恢复 ZRM for MySQL是一个强大,灵活和强大的备份和恢复解决方案&nbs ...

如何使用ZRM for MySQL执行时间恢复

ZRM for MySQL是为所有存储引擎的MySQL数据库提供强大,灵活和强大的备份和恢复解决方案。 使用ZRM for MySQL,数据库管理员可以自动将逻辑或原始备份自动化到本地或远程磁盘。 在本方法中,我们尝试解释如何在任何给定时间点从用户错误中恢复。

我们的场景:

大约下午2:30,MovieID表中添加了5个表空间。 下午3点,你会收到一个用户的电话,他正在尝试删除一些未使用的表空间,但最后删除了他刚刚添加的最后5个。 最后一次完成的备份是晚上七点之前的晚上。 在删除最后5个表空间之前,如何恢复正确? 在这种情况下,我们将展示一个时间点的恢复。

注意:

  • 有关ZRM for MySQL的更多信息,请点击此处

  • 要了解有关配置ZRM for MySQL的更多信息,可以查看http://www.zmanda.com/quick-mysql-backup.html

  • 对于这个方法,我们使用ZRM for MySQL 1.1.1版

  • 要执行增量备份,必须打开二进制日志记录选项。 编辑/etc/my.cnf文件,并在[mysqld]部分下添加以下行:

    log-bin=/var/lib/mysql/mysql-bin.log

    您将在此生效之前重新启动您的mysql守护程序。

我们将验证昨天晚上完成的最后一个备份是否成功运行。

-bash-3.1# mysql-zrm-reporter -show restore-info --where backup-set=dailyrun

backup_set backup_date backup_level backup_directory
----------------------------------------------------------------------------------------------------------
dailyrun Wed 18 Oct 2006 07:07:08 PM PDT 0 /var/lib/mysql-zrm/dailyrun/20061018190708

上面我们可以看到,上个赛季的最后一场比赛在晚上7点07分成功运行。 2.所以现在我们将手动运行增量备份来记录上次备份和现在之间的所有更改,方法是键入以下命令:

-bash-3.1# mysql-zrm-scheduler --now --backup-set dailyrun --backup-level 1

接下来,我们将解析在最后一次增量备份中备份的二进制日志。

-bash-3.1# mysql-zrm --action parse-binlogs --source-directory /var/lib/mysql-zrm/dailyrun/20061019151937 --backup-set dailyrun


------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 4 | 06-11-19 14:09:58 | Start: binlog v 4, server v 5.0.22-log created 061019 14:09:58 |
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 98 | 06-11-19 14:34:27 | Query | use movies; INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES ('17786', '1999', 'Sopranos: Season 1 Disc 1');
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 272 | 06-11-19 14:35:46 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES ('17787', '1999', 'Sopranos: Season 1 Disc 2');
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 446 | 06-11-19 14:36:02 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES ('17788', '1999', 'Sopranos: Season 1 Disc 3');
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 620 | 06-11-19 14:36:36 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES ('17789', '1999', 'Sopranos: Season 1 Disc 4');
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 794 | 06-11-19 14:36:53 | Query | INSERT INTO `MovieID` (`MovieID`, `Year`, `MovieTitle`) VALUES ('17790', '1999', 'Sopranos: Season 1 Disc 5');
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 968 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17786 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1096 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17787 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1224 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17788 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1352 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17789 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1480 | 06-11-19 14:56:15 | Query | DELETE FROM `MovieID` WHERE `MovieID`.`MovieID` = 17790 LIMIT 1;
/var/lib/mysql-zrm/dailyrun/20061019151937/mysql-bin.000002 | 1608 | 06-11-19 15:19:37 | Rotate to mysql-bin.000003 pos: 4 |
------------------------------------------------------------
INFO: Removing all of the uncompressed/unencrypted data

所以现在我们将在大约下午2:45将数据库还原到它的样子。 由于这些表在下午2:30被添加,下午3点意外删除。 因为我们希望数据库回到状态,就在删除之前。

-bash-3.1# mysql-zrm --action restore --source-directory /var/lib/mysql-zrm/dailyrun/20061019151937 --backup-set dailyrun --stop-datetime "20061019144500"

INFO: ZRM for MySQL Community Edition - version 1.1
INFO: Mail address: dba@zmanda.com is ok
INFO: Input Parameters Used {
INFO: verbose=1
INFO: retention-policy=10D
INFO: backup-level=1
INFO: mailto=dba@zmanda.com
INFO: databases=movies
INFO: source-directory=/var/lib/mysql-zrm/dailyrun/20061019151937
INFO: html-reports=backup-status-info
INFO: password=******
INFO: backup-mode=logical
INFO: compress-plugin=/usr/bin/gzip
INFO: compress=/usr/bin/gzip
INFO: user=backup-user
INFO: stop-datetime=20061019144500
INFO: Getting mysql variables
INFO: mysqladmin --user=backup-user --password=***** variables
INFO: datadir is /var/lib/mysql/
INFO: mysql_version is 5.0.22-log
INFO: log_bin=ON
INFO: Uncompressing backup
INFO: Command used is 'cat "/var/lib/mysql-zrm/dailyrun/20061019151937/backup-data" | "/usr/bin/gzip" -d | tar --same-owner -xpsC "/var/lib/mysql-zrm/dailyrun/20061019151937" 2>/tmp/HId0KZkvcS'
INFO: Restoring incremental to tmpfile
INFO: mysqlbinlog --user=backup-user --password=***** --stop-datetime=20061019144500 --database=movies -r /tmp/NNqSZFZa8R "/var/lib/mysql-zrm/dailyrun/20061019151937"/mysql-bin.[0-9]*
INFO: restoring using command mysql --user=backup-user --password=***** -e "source /tmp/NNqSZFZa8R;"
INFO: Incremental restore done for database movies
INFO: Shutting down MySQL
INFO: Removing all of the uncompressed/unencrypted data
INFO: Restore done in 2 seconds. MySQL server has been shutdown. Please restart after verification.

5.重新启动MySQL服务后,您会注意到数据库已恢复到下午2:45。 这意味着它有最后5个表空间在下午3点被意外删除。

参考文献:

MySQL的ZRM
ZRM for MySQL Wiki
Zmanda论坛