오프라인 binlog 기반 MySQL의 빠른 '플래시백'에 대한 자세한 소개

어제 갑자기 한 고객이 실수로 많은 양의 데이터를 삭제했다고 말했습니다. CTO가 저를 직접 토론 그룹으로 끌어들여 데이터 복구를 도와주겠다고 했습니다. 자체적으로 구멍을 파고 업무 로그를 바탕으로 개발 측에서 복원하도록 할 계획이었는데, 기본키 삭제 등의 정보만 기록되고 물리적 삭제는 불가능하다는 얘기였습니다.

서버에 기록된 로그를 살펴보니 그 중 몇몇이 실수로 기록 출력을 삭제한 것을 발견했습니다. 알리바바 RDS는 인스턴스를 복제해 삭제 전 시점으로 복원할 수 있지만, 흩어져 있는 수만 개의 ID를 찾기도 어렵고, 여러 테이블에 연결된 데이터도 복원해야 하는 번거로움이 있다.

MySQL의 플래시백 솔루션을 생각해 보세요. 예전에 여러 관련 글을 읽어본 적도 있고, python을 이용해 직접 binlog를 파싱하고, 역으로 롤백 sql을 구하는 일도 거의 잦았는데 시간이 없어서 급하게 써먹어야 겠습니다. . 나는 온라인에서 "기성 솔루션"을 빠르게 찾았습니다.

텍스트 시작

---

MySQL(Alibaba RDS 포함) 빠른 플래시백은 데이터베이스 오작동에 대한 해독제라고 할 수 있습니다. 플래시백 기능은 데이터베이스를 오작동 이전으로 되돌릴 수 있습니다. 그러나 Oracle 데이터베이스도 짧은 시간 내에만 플래시백을 지원합니다.

인터넷의 기존 오픈소스 MySQL 플래시백 구현은 binlog를 구문 분석하고 역방향 sql을 생성하는 원리를 사용합니다. (행 모드여야 함)

1. 삭제 작업의 경우, 삽입 생성(DELETE_ROWS_EVENT)

2. 업데이트 작업의 경우 binlog(UPDATE_ROWS_EVENT)의 값 순서를 교환합니다.

3. insert 연산, 역방향 생성 삭제( WRITE_ROWS_EVENT)

4. 여러 이벤트의 경우 역방향 sql 생성

위의 두 가지 구현 방법은 python- mysql-replication 패키지를 사용하여 원본 라이브러리의 슬레이브 라이브러리를 시뮬레이션한 다음 show binary logs을 사용하여 binlog를 얻고, binlog 동기화 요청을 시작한 다음 EVENT를 구문 분석합니다. 그러나 Alibaba Cloud RDS의 binlog가 슬레이브 데이터베이스에 동기화된 후 빠르게 삭제되었습니다. 데이터 의 어제 부분을 복원하려는 경우 두 옵션 모두 binlog를 가져올 수 없습니다. 즉, 플래시백 시간이 제한되어 있습니다.

binlog-rollback.pl과 같이 binlog 실제 파일을 구문 분석하고 롤백을 구현하는 비교적 간단한 구현도 있지만 속도가 너무 느립니다.

속도에 영향을 주지 않고 보다 성숙한 플래시백 솔루션을 사용하려면 다음과 같이 할 수 있습니다.

1. 자체 구축된 mysqld 인스턴스를 사용하여 purge binlog를 인스턴스 디렉토리에 복사

2. 자체 구축된 인스턴스에서는 도구 연결이 필요하기 때문에 복원해야 할 테이블(구조)을 미리 생성합니다. information_schema.columns에서 메타데이터를 얻으려면

3. 정보를 복사할 때 mysql 인스턴스의 binlog 파일 이름을 교체하여 연속적으로 유지할 수 있습니다.

4. 파일 이름이 mysqld mysql-bin.index

5. mysql 인스턴스

   를 다시 시작하고 목록에 있는지 확인하려면 show binary logs을 수정해야 할 수도 있습니다

6. 그런 다음 위 도구 중 하나를 사용하고, 슬레이브 라이브러리를 시뮬레이션하고, binlog 파일을 지정하고, 시작 시간, 종료 시간을 지정하고, 롤백 SQL을 가져올 수 있습니다

7. 그런 다음 비즈니스 로직

을 기반으로 필요한 sql
을 필터링합니다. 즉, 다른 mysql을 사용하여 binlog 이벤트를 전송합니다. 알림:

1. 두 인스턴스 사이에 너무 큰 버전 차이를 두지 마세요.

2. 파일 권한에 주의하세요

3. 원본 라이브러리에서 gtid가 활성화된 경우 이 자체 구축 인스턴스도 gtid를 활성화해야 합니다.

예:

python mysqlbinlog_back.py --host="localhost" --username="ecuser" --password="ecuser" --port=3306 \
--schema=dbname --tables="t_xx1,t_xx2,t_xx3" -S "mysql-bin.000019" -E "2017-03-02 13:00:00" -N "2017-03-02 14:09:00" -I -U

===log will also  write to .//mysqlbinlog_flashback.log===
parameter={'start_binlog_file': 'mysql-bin.000019', 'stream': None, 'keep_data': True,
 'file': {'data_create': None, 'flashback': None, 'data': None}, 'add_schema_name': False, 'start_time': None, 
 'keep_current_data': False, 'start_to_timestamp': 1488430800,
 'mysql_setting': {'passwd': 'ecuser', 'host': 'localhost', 'charset': 'utf8', 'port': 3306, 'user': 'ecuser'},
 'table_name': 't_xx1,t_xx2,t_xx3', 'skip_delete': False, 'schema': 'dbname', 'stat': {'flash_sql': {}},
 'table_name_array': ['t_xx1', 't_xx2', 't_xx3'],
 'one_binlog_file': False, 'output_file_path': './log', 'start_position': 4, 'skip_update': True,
 'dump_event': False, 'end_to_timestamp': 1488434940, 'skip_insert': True, 'schema_array': ['dbname']
}
scan 10000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:14
scan 20000 events ....from binlogfile=mysql-bin.000019,timestamp=2017-03-02T11:42:29
...

팁:
binlog는 dml의 영향을 받는 ROW 형식입니다. 각 행은 Table_map 및 Row_log라는 두 가지 이벤트를 기록합니다. table_map의 table_id는 적용되는 인스턴스에 영향을 주지 않습니다. 이 ID는 테이블 구조 버전을 기록하는 논리적 메커니즘으로 간주될 수 있습니다. table_definition_cache에서 테이블 정의를 찾지 못하면 ID가 1씩 증가하고 다음에 할당됩니다. binlog 테이블에 기록합니다.

mysqlbinlog_back.py 사용 경험 :

• 라이브러리 이름, 표시, 시작 binlog 파일 이름, 시작 시간 및 종료를 반드시 지정하세요. 시간. 스캔 속도를 높일 수 있습니다.

• 복구 요구 사항에 따라 -I, -U, -D를 선택하여 롤백할 작업 유형을 지정합니다.

• 테이블의 일부 데이터만 복원(미완료 플래시백)하는 경우 해당 테이블을 제대로 복원할 수 없습니다. 예를 들어, 삭제된 데이터를 복원해야 하는데, 삭제로 인해 다른 테이블업데이트를 발생시킨 업무상 데이터를 완전히 플래시백하지 않으면 복원이 불가능합니다.

• t_xx3의 f_do_type 필드와 같은 enum 유형의 테이블 필드를 지원하지 않습니다. 자체 빌드된 인스턴스의 열거형 정의를 int로 변경할 수 있습니다.

위 내용은 오프라인 binlog 기반 MySQL의 빠른 '플래시백'에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!