MySQL 오작동 후 데이터 복구 방법에 대한 자세한 설명_MySQL

1. 빈로그를 엽니다.

먼저 binlog가 켜져 있는지 확인

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

값이 OFF이고 활성화가 필요합니다. binlog를 활성화하는 방법은 다음과 같습니다.

#vim /etc/my.cnf

[mysqld]에

추가

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

mysql 서비스 다시 시작

#service mysqld stop
#service mysqld start

2. 시뮬레이션 데이터 작성

데이터베이스 생성

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;

데이터 쓰기

절차 2-1

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

데이터 쿼리

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)

3. 전체 백업

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

참고: 복구 중에 쉽게 작업할 수 있도록 binlog를 새로 고치려면 -F를 추가하세요.

4. 증분 데이터 쓰기 시뮬레이션

2-1번 과정을 진행하세요.

데이터 쿼리

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)

5. 증분 백업

mysql-bin.000002 이상의 binlog를 유지하세요.

6. 오작동 시뮬레이션

delete from number;

7. 증분 데이터 다시 쓰기

실행 프로그램 2-1

Bumber에서 *를 선택하세요.

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

8. 회복

이번에 이전 삭제 작업이 실수로 이루어진 것으로 확인되어 복구 작업이 시급합니다

테이블에 읽기 잠금 추가

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

mysql-bin.000002가 증분 데이터 binlog임을 알 수 있다

전체 백업 가져오기

#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에서 이전 삭제문을 찾아 삭제하세요

mysql -uroot -p <bin.sql

9. 데이터가 복구되었는지 확인

mysql에 로그인

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

복원 완료! 이상이 이 글의 전체 내용입니다. 데이터베이스를 운영할 때 오작동이 발생하지 않도록 더욱 주의하시기 바랍니다. 이 글이 도움이 되기를 바랍니다.