详解MySQL误 *** 作后怎样进行数据恢复

一、开启binlog。

首先查看binlog是否开启

?

1

2

3

4

5

6

7

mysql>show variables like "log_bin"

+---------------+-------+

|Variable_name | Value

+---------------+-------+

| log_bin OFF

+---------------+-------+

1 row in set (0.00 sec)

值为OFF，需开启，开启binlog方式如下：

?

1

#vim /etc/my.cnf

在[mysqld]中加入

?

1

2

log-bin = mysql-bin

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

重启mysql服务

?

1

2

#service mysqld stop

#service mysqld start

二、模拟数据写入

建库

?

1

create database backup

建表

?

1

2

3

4

5

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

写入数据

程序2-1

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

#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):

#time=time.strftime("%Y-%m-%d %H:%M:%S")

sql = 'insert into number(updatetime) values(%s)'

values = [(time.strftime("%Y-%m-%d %H:%M:%S"))]

db1 = connect_mysql()

print db1.executemany(sql,values)

查询数据

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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)

三、全量备份

?

1

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

注：加-F能刷新binlog，方便恢复时 *** 作。

四、模拟写入增量数据

继续执行程序2-1。

查询数据

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

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)

五、增量备份

保留mysql-bin.000002及之后的binlog即可。

六、模拟误 *** 作

?

1

delete from number

七、再次写入增量数据

执行程序2-1

select * from bumber

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

+------+------------------------+

| 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)

八、恢复

此时发现之前的delete *** 作为误 *** 作，急需恢复，恢复过程如下

给该表加上读锁

?

1

lock table number read

将全量备份的数据导入

?

1

2

3

4

#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

刷新日志

?

1

2

3

4

5

6

#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

可确定mysql-bin.000002为增量数据binlog

导入全量备份

?

1

2

3

4

5

#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

在bin.sql找到之前的delete语句，删除

?

1

mysql -uroot -p <bin.sql

九、确认已恢复数据

登录mysql

?

1

2

#mysql -uroot -p'martin' backup

select * from number

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

+----+---------------------+

| 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)

恢复完成

可以使用CHANGE MASTER TO 命令来跳过指定的position，该命令用于更改从服务器的连接参数。例如：

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=123

上面的命令将从服务器的master_log_file设置为mysql-bin.000001，master_log_pos设置为123。

LOCATE(substr,str,pos)

函数返回子串substr在字符串str中的第pos位置后第一次出现的位置。如果substr不在str中返回0。

mysql>SELECT LOCATE('bar', 'foobarbar',5)

->7

这个函数是大小写不敏感的。在 MySQL 3.23 中，这个函数是字母大小写敏感的，当在 MySQL 4.0 中时，如有任一参数是一个二进制字符串，它才是字母大小写敏感的。

---