[MySQL] MySQL误操作后的数据恢复

mysql> show variables like "log_bin";
+---------------+-------+
|Variable_name | Value 
+---------------+-------+
| log_bin   OFF   
+---------------+-------+
1 row in set (0.00 sec)

log-bin         = mysql-bin
log-bin         = /usr/local/mysql/log/mysql-bin.log

#service mysqld stop
#service mysqld start

create database backup;

CREATE TABLE `number` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `updatetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#coding:utf8
#python2.7
import MySQLdb
import time
def connect_mysql(db_host="192.168.11.169",user="martin",passwd="martin",db="backup",charset="utf8"):
  conn = MySQLdb.connect(host=db_host,user=user,passwd=passwd,db=db,charset=charset)
  conn.autocommit(True)
return conn.cursor()
#数据插入
for i in range(0,10):
  sql = 'insert into number(updatetime) values(%s)'
  values = [(time.strftime("%Y-%m-%d %H:%M:%S"))]
  db1 = connect_mysql()
  print db1.executemany(sql,values)

mysql> select * from number;
+-------+------------------------+
| id  | updatetime      
+--------------------------------+
|  1 | 2016-06-29 23:27:15 |
|  2 | 2016-06-29 23:27:15 |
|  3 | 2016-06-29 23:27:15 |
|  4 | 2016-06-29 23:27:15 |
|  5 | 2016-06-29 23:27:15 |
|  6 | 2016-06-29 23:27:15 |
|  7 | 2016-06-29 23:27:15 |
|  8 | 2016-06-29 23:27:15 |
|  9 | 2016-06-29 23:27:15 |
|  10 | 2016-06-29 23:27:15 |
+-------+------------------------+
10 rows in set (0.00 sec)

mysqldump -uroot -p -F --master-data=2  backup |gzip> /martin/data/backup_$(date +%F).sql.gz

mysql> select * from number;
+----+---------------------------+
| id  | updatetime      |
+----+---------------------------+
|  1  | 2016-06-29 23:27:15 |
|  2  | 2016-06-29 23:27:15 |
|  3  | 2016-06-29 23:27:15 |
|  4  | 2016-06-29 23:27:15 |
|  5  | 2016-06-29 23:27:15 |
|  6  | 2016-06-29 23:27:15 |
|  7  | 2016-06-29 23:27:15 |
|  8  | 2016-06-29 23:27:15 |
|  9  | 2016-06-29 23:27:15 |
|  10  | 2016-06-29 23:27:15 |
|  11  | 2016-06-29 23:31:03 |
|  12  | 2016-06-29 23:31:03 |
|  13  | 2016-06-29 23:31:03 |
|  14  | 2016-06-29 23:31:03 |
|  15  | 2016-06-29 23:31:03 |
|  16  | 2016-06-29 23:31:03 |
|  17  | 2016-06-29 23:31:03 |
|  18  | 2016-06-29 23:31:03 |
|  19  | 2016-06-29 23:31:03 |
|  20  | 2016-06-29 23:31:03 |
+-------+---------------------+
20 rows in set (0.00 sec)

+------+------------------------+
| id | updatetime      |
+------+------------------------+
| 21 | 2016-06-29 23:41:06 |
| 22 | 2016-06-29 23:41:06 |
| 23 | 2016-06-29 23:41:06 |
| 24 | 2016-06-29 23:41:06 |
| 25 | 2016-06-29 23:41:06 |
| 26 | 2016-06-29 23:41:06 |
| 27 | 2016-06-29 23:41:06 |
| 28 | 2016-06-29 23:41:06 |
| 29 | 2016-06-29 23:41:06 |
| 30 | 2016-06-29 23:41:06 |
+------+------------------------+
10 rows in set (0.00 sec)

lock table number read;

#cd /martin/data/
#gzip -d number_2016-06-29.sql.gz
#grep -i "change" *.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=107;

#mysqladmin -uroot -p'martin' flush-logs
#cd /usr/local/mysql/log
#ls|grep mysql-bin|grep -v index
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003

#cd /martin/data/
#mysql -uroot -p backup < number_2016-06-29.sql
#cp /usr/local/mysql/log/mysql-bin.000002 /martin/data/
#mysqlbinlog  mysql-bin.000002 >bin.sql
#vim bin.sql

mysql -uroot -p <bin.sql

#mysql -uroot -p'martin' backup
select * from number;

+----+---------------------+
| id | updatetime      |
+----+---------------------+
| 1  | 2016-06-29 23:27:15 |
| 2  | 2016-06-29 23:27:15 |
| 3  | 2016-06-29 23:27:15 |
| 4  | 2016-06-29 23:27:15 |
| 5  | 2016-06-29 23:27:15 |
| 6  | 2016-06-29 23:27:15 |
| 7  | 2016-06-29 23:27:15 |
| 8  | 2016-06-29 23:27:15 |
| 9  | 2016-06-29 23:27:15 |
| 10  | 2016-06-29 23:27:15 |
| 11  | 2016-06-29 23:31:03 |
| 12  | 2016-06-29 23:31:03 |
| 13  | 2016-06-29 23:31:03 |
| 14  | 2016-06-29 23:31:03 |
| 15  | 2016-06-29 23:31:03 |
| 16  | 2016-06-29 23:31:03 |
| 17  | 2016-06-29 23:31:03 |
| 18  | 2016-06-29 23:31:03 |
| 19  | 2016-06-29 23:31:03 |
| 20  | 2016-06-29 23:31:03 |
| 21  | 2016-06-29 23:41:06 |
| 22  | 2016-06-29 23:41:06 |
| 23  | 2016-06-29 23:41:06 |
| 24  | 2016-06-29 23:41:06 |
| 25  | 2016-06-29 23:41:06 |
| 26  | 2016-06-29 23:41:06 |
| 27  | 2016-06-29 23:41:06 |
| 28  | 2016-06-29 23:41:06 |
| 29  | 2016-06-29 23:41:06 |
| 30  | 2016-06-29 23:41:06 |
+----+---------------------+
30 rows in set (0.00 sec)