生活就是被生下来,然后自己活下去!
分类: Mysql/postgreSQL
2015-06-26 17:55:12
mysql> select count(*) from sdb_b2c_orders; +----------+ | count(*) | +----------+ | 35630 | +----------+ 1 row in set (0.01 sec) mysql>delete from sdb_b2c_orders |
/opt/mysql-5.1.57/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -start-date='2015-06-24 10:10:00' --stop-date='2015-06-24 11:10:00' /data/mysql/data_3310/mysqld2-binlog.000023 > /tmp/recovery1.log [root@localhost ~]# vi /tmp/recovery1.log #150624 11:18:57 server id 1 end_log_pos 308613577 Query thread_id=979 exec_time=0 error_code=0 SET TIMESTAMP=1435115937/*!*/; /*!40000 ALTER TABLE `sdb_b2c_orders` ENABLE KEYS */ /*!*/; # at 308613577 #150624 11:18:57 server id 1 end_log_pos 308613604 Xid = 17217 COMMIT/*!*/; # at 308613604 #150624 11:21:00 server id 1 end_log_pos 308613674 Query thread_id=981 exec_time=5 error_code=0 SET TIMESTAMP=1435116060/*!*/; SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/; SET @@session.sql_mode=0/*!*/; BEGIN /*!*/; # at 308613674 #150624 11:21:00 server id 1 end_log_pos 308613765 Query thread_id=981 exec_time=5 error_code=0 SET TIMESTAMP=1435116060/*!*/; delete from sdb_b2c_orders /*!*/; # at 308613765 #150624 11:21:00 server id 1 end_log_pos 308613792 Xid = 17238 COMMIT/*!*/; # at 308613792 #150624 11:23:55 server id 1 end_log_pos 308613894 Query thread_id=982 exec_time=0 error_code=0 SET TIMESTAMP=1435116235/*!*/; SET @@session.foreign_key_checks=0, @@session.unique_checks=0/*!*/; SET @@session.sql_mode=524288/*!*/; DROP TABLE IF EXISTS `sdb_b2c_orders` /*!*/; # at 308613894 #150624 11:23:55 server id 1 end_log_pos 308617447 Query thread_id=982 exec_time=0 error_code=0 SET TIMESTAMP=1435116235/*!*/; CREATE TABLE `sdb_b2c_orders` ( `order_id` bigint(20) unsigned NOT NULL DEFAULT '0', `order_bn` varchar(50) DEFAULT '0', 在文本中输入/delete进行查找 |
/opt/mysql-5.1.57/bin/mysqlbinlog --no-defaults --base64-output=decode-rows --stop-date='2015-06-24 10:20:59' /data/mysql/data_3310/mysqld2-binlog.000023 > /tmp/recovery1.log |
cat /tmp/recovery1.log |grep --ignore-case -E 'insert|update|delete' -A2 |grep -E 'sdb_b2c_orders|;' > /tmp/test1.sql [root@localhost ~]# tail /tmp/test1.sql……………… 'false','local',NULL,'pc',0,NULL) /*!*/; update sdb_b2c_orders set status='finish' where createtime>unix_timestamp('2015-05-27') and status='active' /*!*/; delete from sdb_b2c_orders where createtime<unix_timestamp('2015-05-27') and createtime>unix_timestamp('2015-05-26') /*!*/; |
+----------+ | count(*) | +----------+ | 35630 | +----------+ 1 row in set (0.01 sec) |