Mysql基于二进制日志进行恢复的简单案列
环境搭建模拟测试 假如:全备的是在FILE=mysql-bin.000008;Position=5573 但是没有做增量备份 采用二进制日志恢复 首先删除一张表,然后删除整个库 要求:数据恢复到删除整个数据库前的时刻??? 1. 创建库 表插入数据 [root@Mysql data]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.33-log MySQL Community Server (GPL) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> create database mydb; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.01 sec) mysql> use mydb; Database changed mysql> CREATE TABLE allentuns -> ( -> Id_P int, -> LastName varchar(255), -> FirstName varchar(255), -> Address varchar(255), -> City varchar(255) -> ); Query OK, 0 rows affected (0.01 sec) mysql> insert into allentuns values (1,"zheng","sheng","HaiDian","BJ"); allentuns values (9,"guo","meng","HaiDian","BJ"); insert into allentuns values (10,"wang","wei","HaiDian","BJ");Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (2,"qin","hen","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (3,"wang","yang","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (4,"zhao","meng","HaiDian","BJ"); Query OK, 1 row affected (0.00 sec) mysql> insert into allentuns values (5,"liang","wei","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (6,"sun","sheng","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (7,"qi","hen","HaiDian","BJ"); Query OK, 1 row affected (0.00 sec) mysql> insert into allentuns values (8,"zhu","yang","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (9,"guo","meng","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> insert into allentuns values (10,"wang","wei","HaiDian","BJ"); Query OK, 1 row affected (0.01 sec) mysql> select * from allentuns -> ; +------+----------+-----------+---------+------+ | Id_P | LastName | FirstName | Address | City | +------+----------+-----------+---------+------+ | 1 | zheng | sheng | HaiDian | BJ | | 2 | qin | hen | HaiDian | BJ | | 3 | wang | yang | HaiDian | BJ | | 4 | zhao | meng | HaiDian | BJ | | 5 | liang | wei | HaiDian | BJ | | 6 | sun | sheng | HaiDian | BJ | | 7 | qi | hen | HaiDian | BJ | | 8 | zhu | yang | HaiDian | BJ | | 9 | guo | meng | HaiDian | BJ | | 10 | wang | wei | HaiDian | BJ | +------+----------+-----------+---------+------+ 10 rows in set (0.00 sec) mysql> select count(*) from allentuns; +----------+ | count(*) | +----------+ | 10 | +----------+ 1 row in set (0.04 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000008 | 5573 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> show master status\g +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000008 | 5573 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> show table status\G *************************** 1. row *************************** Name: allentuns Engine: InnoDB Version: 10 Row_format: Compact Rows: 10 Avg_row_length: 1638 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 9437184 Auto_increment: NULL Create_time: 2014-03-29 13:29:47 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> 2. 全备mydb库 查看备份信息 [root@Mysql ~]# mysqldump --events --routines --master-data=2 --single-transaction --flush-logs --databases mydb > ./mydb.sql [root@Mysql ~]# ll total 28 -rw-------. 1 root root 945 Mar 6 21:05 anaconda-ks.cfg -rw-r--r--. 1 root root 8785 Mar 6 21:05 install.log -rw-r--r--. 1 root root 3161 Mar 6 21:03 install.log.syslog -rw-r--r-- 1 root root 2488 Mar 29 13:16 mydb1.sql -rw-r--r-- 1 root root 2680 Mar 29 13:38 mydb.sql [root@Mysql ~]# less mydb.sql -- MySQL dump 10.13 Distrib 5.5.33, for linux2.6 (x86_64) -- -- Host: localhost Database: mydb -- ------------------------------------------------------ -- Server version 5.5.33-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE=‘+00:00‘ */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=‘NO_AUTO_VALUE_ON_ZERO‘ */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000009‘, MASTER_LOG_POS=107; -- -- Current Database: `mydb` -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */; mydb.sql 3. 模拟测试 首先删除allentuns表 然后在删除mydb库 mysql> select * from allentuns; +------+----------+-----------+---------+------+ | Id_P | LastName | FirstName | Address | City | +------+----------+-----------+---------+------+ | 1 | zheng | sheng | HaiDian | BJ | | 2 | qin | hen | HaiDian | BJ | | 3 | wang | yang | HaiDian | BJ | | 4 | zhao | meng | HaiDian | BJ | | 5 | liang | wei | HaiDian | BJ | | 6 | sun | sheng | HaiDian | BJ | | 7 | qi | hen | HaiDian | BJ | | 8 | zhu | yang | HaiDian | BJ | | 9 | guo | meng | HaiDian | BJ | | 10 | wang | wei | HaiDian | BJ | +------+----------+-----------+---------+------+ 10 rows in set (0.00 sec) mysql> delete from allentuns where Id_P="5"; Query OK, 1 row affected (0.01 sec) mysql> select * from allentuns; +------+----------+-----------+---------+------+ | Id_P | LastName | FirstName | Address | City | +------+----------+-----------+---------+------+ | 1 | zheng | sheng | HaiDian | BJ | | 2 | qin | hen | HaiDian | BJ | | 3 | wang | yang | HaiDian | BJ | | 4 | zhao | meng | HaiDian | BJ | | 6 | sun | sheng | HaiDian | BJ | | 7 | qi | hen | HaiDian | BJ | | 8 | zhu | yang | HaiDian | BJ | | 9 | guo | meng | HaiDian | BJ | | 10 | wang | wei | HaiDian | BJ | +------+----------+-----------+---------+------+ 9 rows in set (0.00 sec) mysql> drop database mydb; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> 4. 恢复mydb库 [root@Mysql ~]# mysql < mydb.sql mysql> select * from mydb.allentuns; 很明显数据全在 不是我们要的结果 第五条记录不存在才对 +------+----------+-----------+---------+------+ | Id_P | LastName | FirstName | Address | City | +------+----------+-----------+---------+------+ | 1 | zheng | sheng | HaiDian | BJ | | 2 | qin | hen | HaiDian | BJ | | 3 | wang | yang | HaiDian | BJ | | 4 | zhao | meng | HaiDian | BJ | | 5 | liang | wei | HaiDian | BJ | | 6 | sun | sheng | HaiDian | BJ | | 7 | qi | hen | HaiDian | BJ | | 8 | zhu | yang | HaiDian | BJ | | 9 | guo | meng | HaiDian | BJ | | 10 | wang | wei | HaiDian | BJ | +------+----------+-----------+---------+------+ 10 rows in set (0.01 sec) mysql> 5. 查看二进制日志 首先查看mysql-bin.000008 [root@Mysql data]# mysqlbinlog mysql-bin.000008 # at 5204 #140329 13:29:55 server id 1 end_log_pos 5327 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1396114195/*!*/; insert into allentuns values (9,"guo","meng","HaiDian","BJ") /*!*/; # at 5327 #140329 13:29:55 server id 1 end_log_pos 5354 Xid = 236 COMMIT/*!*/; # at 5354 #140329 13:29:56 server id 1 end_log_pos 5422 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1396114196/*!*/; BEGIN /*!*/; # at 5422 #140329 13:29:56 server id 1 end_log_pos 5546 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1396114196/*!*/; insert into allentuns values (10,"wang","wei","HaiDian","BJ") /*!*/; # at 5546 #140329 13:29:56 server id 1 end_log_pos 5573 Xid = 237 COMMIT/*!*/; # at 5573 #140329 13:38:20 server id 1 end_log_pos 5616 Rotate to mysql-bin.000009 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; 很明显这个二进制日志并没有记录我们delete 和 drop语句 这个事因为我们在进行数据库备份的时候 执行flush logs 滚动日志 因此日志切换到了下一个二进制日志中 那我们来查看下一个二进制日志 [root@Mysql data]# mysqlbinlog mysql-bin.000009 |less DAU3Uw8BAAAAZwAAAGsAAAABAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== ‘/*!*/; # at 107 #140329 13:42:44 server id 1 end_log_pos 175 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1396114964/*!*/; SET @@session.pseudo_thread_id=12/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=0/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 175 #140329 13:42:44 server id 1 end_log_pos 275 Query thread_id=12 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1396114964/*!*/; delete from allentuns where Id_P="5" #delete /*!*/; # at 275 #140329 13:42:44 server id 1 end_log_pos 302 Xid = 282 COMMIT/*!*/; # at 302 #140329 13:43:01 server id 1 end_log_pos 383 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1396114981/*!*/; drop database mydb #drop /*!*/; # at 383 #140329 13:47:44 server id 1 end_log_pos 534 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1396115264/*!*/; SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/; SET @@session.sql_mode=524288/*!*/; CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mydb` /*!40100 DEFAULT CHARACTER SET latin1 */ /*!*/; # at 534 #140329 13:47:44 server id 1 end_log_pos 655 Query thread_id=14 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1396115264/*!*/; DROP TABLE IF EXISTS `allentuns` /* generated by server */ /*!*/; # at 655 #140329 13:47:44 server id 1 end_log_pos 969 Query thread_id=14 exec_time=0 error_code=0 SET TIMESTAMP=1396115264/*!*/; 我们可以利用一下信息进行数据恢复 -- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000009‘, MASTER_LOG_POS=107; #140329 13:42:44 server id 1 end_log_pos 275 Query thread_id=12 exec_time=0 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1396114964/*!*/; delete from allentuns where Id_P="5" #delete #140329 13:43:01 server id 1 end_log_pos 383 Query thread_id=12 exec_time=0 error_code=0 SET TIMESTAMP=1396114981/*!*/; drop database mydb #drop 6. 利用二进制日志恢复数据 [root@Mysql data]# mysqlbinlog --start-position=107 --stop-position=275 mysql-bin.000009 |mysql mysql> select * from mydb.allentuns; +------+----------+-----------+---------+------+ | Id_P | LastName | FirstName | Address | City | +------+----------+-----------+---------+------+ | 1 | zheng | sheng | HaiDian | BJ | | 2 | qin | hen | HaiDian | BJ | | 3 | wang | yang | HaiDian | BJ | | 4 | zhao | meng | HaiDian | BJ | | 6 | sun | sheng | HaiDian | BJ | | 7 | qi | hen | HaiDian | BJ | | 8 | zhu | yang | HaiDian | BJ | | 9 | guo | meng | HaiDian | BJ | | 10 | wang | wei | HaiDian | BJ | +------+----------+-----------+---------+------+ 9 rows in set (0.00 sec) mysql> 完成了 第五条记录不存在了
本文出自 “郑彦生” 博客,请务必保留此出处http://467754239.blog.51cto.com/4878013/1386503
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。