MySQL 오작동 후 데이터를 신속하게 복구하는 방법

Mysql 관계형 데이터베이스 관리 시스템

MySQL은 스웨덴 MySQL AB 회사에서 개발한 오픈 소스 소규모 관계형 데이터베이스 관리 시스템입니다. MySQL은 인터넷상의 중소규모 웹사이트에서 널리 사용되고 있습니다. 작은 크기, 빠른 속도, 낮은 총 소유 비용, 특히 오픈 소스의 특성으로 인해 많은 중소 웹 사이트에서는 웹 사이트 총 소유 비용을 줄이기 위해 MySQL을 웹 사이트 데이터베이스로 선택합니다.

이 글에서는 주로 MySQL 오작동 후 데이터를 빠르게 복구하는 방법을 소개합니다. 필요한 친구들이 참고할 수 있습니다

요약: binlog Flash를 사용하세요. 다시 오작동 데이터.
기본적으로 데이터베이스 작업을 하는 모든 프로그래머(물론 동료일 수도 있음)는 문제에 직면하게 됩니다. 오작동 후 MySQL을 신속하게 롤백하는 방법은 무엇입니까? 예를 들어 테이블을 삭제하고 제한 사항을 추가하는 것을 잊은 경우 전체 테이블이 손실됩니다. 이것이 여전히 온라인 환경의 핵심 비즈니스 데이터라면 이 문제는 큰 문제가 될 것입니다. 오작동 후 데이터를 신속하게 롤백하는 것은 매우 중요합니다.

기존 솔루션

전체 백업으로 인스턴스를 재구축한 후 증분 binlog 백업을 사용하여 오작동 이전 상태로 복원합니다. 그런 다음 SQL 오작동을 건너뛰고 binlog를 계속 사용합니다. 이 방법은 시간이 많이 걸리고 노동 집약적이므로 권장할 가치가 없습니다.

binlog2sql을 사용하여 빠른 플래시백

먼저 MySQL 서버에 binlog가 활성화되어 있는지 확인하고 다음 매개변수를 설정합니다.

[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
max_binlog_size = 100M
binlog-format = row

binlog가 활성화되지 않으면 사전 생성된 응답 SQL을 롤백하면 실제로 빠르게 롤백할 수 없습니다. 중요한 비즈니스 데이터를 저장하는 MySQL의 경우 binlog를 활성화하는 것이 좋습니다.

그런 다음 오픈 소스 도구인 binlog2sql을 설치합니다. binlog2sql은 간단하고 사용하기 쉬운 binlog 구문 분석 도구입니다. 그 기능 중 하나는 롤백 SQL을 생성하는 것입니다.

shell> git clone https://github.com/danfengcao/binlog2sql.git
shell> pip install -r requirements.txt

그런 다음 롤백 SQL을 생성할 수 있습니다.

배경: Xiao Ming이 20시경 테스트 라이브러리의 전체 tbl 테이블 데이터를 실수로 삭제하여 긴급 롤백이 필요했습니다.

test库tbl表原有数据
mysql> select * from tbl;
+----+--------+---------------------+
| id | name  | addtime       |
+----+--------+---------------------+
| 1 | 小赵  | 2016-12-10 00:04:33 |
| 2 | 小钱  | 2016-12-10 00:04:48 |
| 3 | 小孙  | 2016-12-13 20:25:00 |
| 4 | 小李  | 2016-12-12 00:00:00 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

mysql> delete from tbl;
Query OK, 4 rows affected (0.00 sec)

20:28时，tbl表误操作被清空
mysql> select * from tbl;
Empty set (0.00 sec)

데이터 복원 단계:

1. mysql에 로그인하여 현재 binlog 파일을 확인합니다.

mysql> show master status;
+------------------+-----------+
| Log_name     | File_size |
+------------------+-----------+
| mysql-bin.000051 |    967 |
| mysql-bin.000052 |    965 |
+------------------+-----------+

2. 최신 binlog 파일은 mysql-bin.000052입니다. 다시 찾아보겠습니다. SQL binlog 위치가 잘못 작동되었습니다. 실수를 한 사람은 대략적인 오조작 시간만 알 수 있으며, 우리는 대략적인 시간을 기준으로 데이터를 필터링합니다.

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-datetime='2016-12-13 20:25:00'
 --stop-datetime='2016-12-13 20:30:00'
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 4 end 290 time 2016-12-13 20:25:46
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:26:00', 4, '小李'); #start 317 end 487 time 2016-12-13 20:26:26
UPDATE `test`.`tbl` SET `addtime`='2016-12-12 00:00:00', `id`=4, `name`='小李' WHERE `addtime`='2016-12-13 20:26:00' AND `id`=4 AND `name`='小李' LIMIT 1; 
#start 514 end 701 time 2016-12-13 20:27:07
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:33' AND `id`=1 AND `name`='小赵' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-10 00:04:48' AND `id`=2 AND `name`='小钱' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-13 20:25:00' AND `id`=3 AND `name`='小孙' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05
DELETE FROM `test`.`tbl` WHERE `addtime`='2016-12-12 00:00:00' AND `id`=4 AND `name`='小李' LIMIT 1; #start 728 end 938 time 2016-12-13 20:28:05

3. 728~938 사이의 오작동 SQL의 정확한 위치를 파악하고, 해당 위치를 기준으로 추가 필터링한 후, 플래시백 모드를 사용하여 롤백 SQL을 생성하고, 롤백 SQL이 올바른지 확인했습니다

shell> python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uadmin -p'admin' -dtest -ttbl --start-file='mysql-bin.000052' --start-pos=3346 --end-pos=3556 -B
输出：
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-12 00:00:00', 4, '小李'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-13 20:25:00', 3, '小孙'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:48', 2, '小钱'); #start 728 end 938 time 2016-12-13 20:28:05
INSERT INTO `test`.`tbl`(`addtime`, `id`, `name`) VALUES ('2016-12-10 00:04:33', 1, '小赵'); #start 728 end 938 time 2016-12-13 20:28:05

롤백 SQL이 올바른지 확인하고 롤백문을 실행해 보세요. mysql에 로그인하여 데이터 롤백이 성공했는지 확인합니다.

아아아아

이제부터는 더 이상 해고될 걱정이 없습니다.

자주 묻는 질문

실수로 DDL 작업을 빠르게 롤백하려면 어떻게 해야 합니까? 예를 들어, 큰 테이블이 삭제되었습니다.

어려운 일이에요. 행 모드에서도 DDL 작업은 각 데이터 행의 변경 사항을 binlog에 기록하지 않으므로 binlog를 통해 DDL을 롤백할 수 없습니다. DDL 롤백을 구현하려면 DDL을 실행하기 전에 오래된 데이터를 백업해야 합니다. 실제로 누군가 mysql 서버 소스 코드를 수정하여 DDL의 빠른 롤백을 달성했습니다. 저는 Alibaba의 xiaobin lin을 찾아 패치를 제출했습니다. 하지만 제가 아는 한, 국내 인터넷 업체 중 이 기능을 적용한 업체는 거의 없습니다. 그 이유는 내가 너무 게을러서 이 저주파 기능을 사용할 필요가 없기 때문이라고 생각합니다. 두 번째 이유는 추가 저장 공간이 추가된다는 것입니다.

따라서 DDL 오작동은 일반적으로 백업을 통해서만 복구할 수 있습니다. 회사에서 더 이상 백업을 사용할 수 없다면 비행기 표를 구입하는 것이 좋습니다. 무엇을 해야 할까요? 실행

mysql binlog2sql 외에 다른 롤백 도구가 있나요?

물론이죠. Ali Peng Lixun은 mysql의 최초 플래시백 기능이어야 할 mysqlbinlog에 플래시백 기능을 추가했습니다. Peng은 DML 롤백 문제를 해결하고 DML 플래시백에 binlog를 사용하는 설계 아이디어를 설명했습니다. DDL 롤백 기능도 Alibaba 팀에서 제안하고 구현했습니다. 이 두 가지 기능은 혁신적이며, 이후 등장한 플래시백 도구는 기본적으로 위 두 가지를 모방한 것입니다. 또한 Qunar의 오픈 소스 Inception은 MySQL의 자동화된 운영 및 유지 관리 도구 세트입니다. 이것은 binlog가 아닌 DML 롤백을 지원하지만 데이터를 롤백할 수 없습니다. ~ 슬레이브 지연 백업이라는 또 다른 방법이 있는데, 이는 비즈니스 트래픽을 추가하지 않는 슬레이브를 설정하고 의도적으로 일정 시간 동안 지연시키는 것입니다. 이는 실제로 기존 방법에 따른 인스턴스 복구 단계를 제거합니다. 이 방법은 추가 시스템을 사용하므로 권장되지 않습니다.

mysql 롤백과 관련된 우수한 도구에 대한 훌륭한 기사 중 누락된 기사가 있으면 알려주시기 바랍니다.

위 내용은 MySQL 오작동 후 데이터를 빠르게 복구하는 방법에 대한 내용입니다. 자세한 내용은 PHP 중국어 홈페이지(www.php.cn)를 참고해주세요!