MySQL에서 원본 SQL 문을 빠르게 보는 방법

Background

최근 레거시 프로젝트를 조사하는 과정에서 특정 비즈니스 운영을 위해 어떤 데이터베이스 테이블이 수정되었는지 확인하기 위해 binlog에 기록된 SQL 문을 보기로 결정했습니다. 저는 MySQL에 많은 binlog 구문 분석 도구가 있다는 것을 항상 알고 있었지만 이전에는 사용해 본 적이 없습니다. 오늘은 그 과정을 기록해 보겠습니다.

이번에는 공식 구문 분석 도구 외에 타사 오픈 소스 도구도 사용했습니다.

준비

binlog 활성화

먼저 MySQL 서버에 binlog가 활성화되어 있는지 확인하세요. 이는 my.cnf 파일에 구성되어 있습니다.

cat /etc/my.cnf

# 取消log_bin的注释即可，这里可以提供一个具体的路径，否则就使用默认地址
log_bin
# 高版本MySQL需要server-id这个参数，提供一个集群中不重复的id值即可
server-id=1
# 重新启动服务器
service mysqld restart

현재 로그 위치 기록

후속 테스트를 용이하게 하기 위해 먼저 MySQL 로그의 일부 관련 정보를 기록합니다.

-- 로그가 올바르게 활성화되었는지 확인하세요. 여기에서 로그 파일의 특정 경로를 볼 수도 있습니다.

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /var/lib/mysql/mysqld-bin       |
| log_bin_index                   | /var/lib/mysql/mysqld-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
| sql_log_bin                     | ON                              |
+---------------------------------+---------------------------------+

-- 현재 로그의 파일 이름과 오프셋 위치를 기록하여 작업 중에 정확하게 찾을 수 있습니다. 후속 로그 보기

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000001 |     2425 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+

-- 또한 ​​시작 시간을 지정하여 로그를 볼 수 있으므로 현재 시간도 기록합니다

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2018-08-02 09:59:43 |
+---------------------+

DDL을 실행

테스트 라이브러리에서 다음 명령문을 실행하는 것이 우리의 목표입니다. binlog를 확인하여 문의 이 부분을 복원하세요.

-- 建表
create table aaa(id int, title varchar(100));
-- 插入初始化数据
insert into aaa(id, title) values (1, '测试1');
insert into aaa(id, title) values (2, '测试2');
insert into aaa(id, title) values (3, '测试3');
-- 更新/删除
update aaa set title='修改' where id=2;
delete from aaa where id=1;

binlog 보기

공식 도구 mysqlbinlog

제공되는 공식 구문 분석 도구는 사용이 매우 간단하지만 구문 분석된 결과의 가독성이 좋지 않습니다. 알 수 없는 변수 'default-character-set=utf8' 오류를 방지하기 위해 호출 중에 no-defaults 매개변수를 사용했습니다.

mysqlbinlog --no-defaults  /var/lib/mysql/mysqld-bin.000001 --start-position=2425

mysqlbinlog의 표시 결과를 읽을 수 없습니다. 발췌하면 우리가 실행한 DDL 문 외에 다른 상황 정보도 기록되는 것을 볼 수 있습니다.

# at 2425
#180802 10:05:32 server id 1  end_log_pos 2553 CRC32 0x77e80f22 Querythread_id=70exec_time=0error_code=0
use `aaaa`/*!*/;
SET TIMESTAMP=1533175532/*!*/;
SET @@session.pseudo_thread_id=70/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
-- 建表
create table aaa(id int, title varchar(100))
/*!*/;
# at 2553
#180802 10:05:32 server id 1  end_log_pos 2632 CRC32 0x2bcb9bbd Querythread_id=70exec_time=0error_code=0
SET TIMESTAMP=1533175532/*!*/;
BEGIN
/*!*/;
# at 2632
#180802 10:05:32 server id 1  end_log_pos 2779 CRC32 0x8414086d Querythread_id=70exec_time=0error_code=0
SET TIMESTAMP=1533175532/*!*/;
-- 插入初始化数据
insert into aaa(id, title) values (1, '测试1')
/*!*/;

타사 도구 binlog2sql

binlog용 오픈 소스 도구가 많이 있습니다. Google을 검색한 후 binlog2sql을 찾았습니다. 공식 버전보다 더 많은 기능을 제공합니다. 다양한 옵션에 따라 원본 SQL, 롤백 SQL, 기본 키가 제거된 INSERT SQL 등을 얻을 수 있습니다. 이번에는 실행된 DDL을 보는 데만 사용합니다. 더 자세한 사용법은 공식 중국어 문서를 참조하세요.

이 도구는 Python(2.7+, 3.4+)을 기반으로 개발되었으므로 먼저 설명서에 따라 관련 종속성을 설치하십시오.

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

사용 방법은 공식 도구와 유사하며 정확한 위치 지정을 위해 오프셋 위치 또는 시작 시간을 제공합니다. 위치 정보가 제공되지 않으면 처음부터 모든 정보가 표시됩니다.

# 使用偏移位置
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' --start-file='mysqld-bin.000001' --start-pos=2425 -d aaaa
# 同样功能，使用时间戳
python binlog2sql.py -h127.0.0.1 -P3306 -uroot -p'root' --start-file='mysqld-bin.000001' --start-datetime='2018-08-02 10:00:00' -d aaaa

이 도구의 표시 결과는 다음과 같이 매우 간단합니다.

USE aaaa;
-- 建表
create table aaa(id int, title varchar(100));
USE aaaa;
-- 插入初始化数据
insert into aaa(id, title) values (1, '测试1');
USE aaaa;
insert into aaa(id, title) values (2, '测试2');
USE aaaa;
insert into aaa(id, title) values (3, '测试3');
USE aaaa;
-- 更新/删除
update aaa set title='修改' where id=2;
USE aaaa;
delete from aaa where id=1;

권장 학습: "mysql 비디오 튜토리얼"

위 내용은 MySQL에서 원본 SQL 문을 빠르게 보는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!