mysql binlog를 사용하여 데이터를 복원하는 방법

데이터 복구를 위해 mysql binlog를 사용하기 전에 먼저 binlog를 활성화해야 합니다. mysql binlog를 사용하여 데이터베이스를 복원하는 방법을 알아보려면 여기에서 테스트 환경을 설정하세요. 원리는 비교적 간단합니다. Binlog는 변경된 데이터를 mysql에 저장합니다. 예를 들어, 데이터베이스를 만들고 일부 데이터를 쓰면 이러한 내용이 mysql의 binlog에 저장됩니다.

회복이 필요할 때는 시작 위치와 끝 위치라는 두 가지 위치를 찾으세요. 종료 위치의 절반은 데이터가 파기되거나 삭제되기 전의 위치입니다. Mysql 8은 기본적으로 binlog가 활성화되어 있습니다

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

log_bin이 활성화되어 있는 것을 볼 수 있고, 저장 위치가 /daba/mysql 디렉토리에 있고 접두사가 ​​binlog

ls /data/mysql/binlog.*
/data/mysql/binlog.000143  /data/mysql/binlog.000144  /data/mysql/binlog.000145  /data/mysql/binlog.000146  /data/mysql/binlog.index

인 것을 볼 수 있습니다. binlog 로그 파일이 여러 개 있다는 것은 테스트 편의를 위해 테스트 데이터베이스(기록 정보 및 이전 데이터가 포함되어 쓸모가 없음)를 직접 재설정하기 때문입니다. binlog 파일을 모두 삭제할 때는 주의하세요. 프로덕션 환경이라면 데이터의 가치는 헤아릴 수 없을 정도로 큽니다.

show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000143 |       156 | No        |
| binlog.000144 |       200 | No        |
| binlog.000145 |       156 | No        |
| binlog.000146 |       156 | No        |
+---------------+-----------+-----------+
4 rows in set (0.01 sec)

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       156 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)


ls /data/mysql/binlog.*
/data/mysql/binlog.000001  /data/mysql/binlog.index

리셋(reset master)을 실행하면 이전 binlog 파일이 삭제되고 새로운 binlog 파일이 생성되는 것을 확인할 수 있습니다.

이 파일의 내용을 확인할 수 있습니다

$mysqlbinlog binlog.000001
# The proper term is pseudo_replica_mode, but we use this compatibility alias
# to make the statement usable on server versions 8.0.24 and older.
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
...

이번에는 일부 데이터를 추가합니다

mysql <<EOT
create database test_liuhaolin_com;
select now();
EOT

mysql <<EOT
use test_liuhaolin_com;

create table if not exists test(
	\`id\` int unsigned not null auto_increment primary key,
	\`key\` varchar(100),
	\`val\` varchar(255)
) engine=myisam charset=utf8mb4;
EOT

mysql <<EOT
use test_liuhaolin_com;
insert into test  values (&#39;1&#39;,&#39;website&#39;, &#39;https://www.liuhaolin.com&#39;);
EOT

이번에는 실수로 test_liuhaolin_com 데이터베이스를 삭제했습니다

mysql> drop database test_liuhaolin_com;
Query OK, 1 row affected (0.09 sec)

이제 실제 문제를 해결해야 합니다. 이를 복원하는 방법 Database

먼저 간섭을 방지하려면 플러시 로그를 실행하여 새로운 binlog 파일을 생성하세요.

mysql> show master logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |      1594 | No        |
+---------------+-----------+-----------+
1 row in set (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000001 |     1594 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 |      156 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

데이터를 복구하려면 먼저 데이터가 삭제된 위치를 찾아야 합니다.

mysqlbinlog binlog.000001 | grep -n  "drop database"
113:drop database test_liuhaolin_com

113번째 줄에 삭제문이 있는 것을 볼 수 있습니다. 마지막으로 두 곳을 찾을 수 있습니다

1. 데이터를 복원해야 할 시작 위치

2. 데이터를 복원해야 하는 끝 위치

여기의 시작 위치는 데이터베이스를 생성한 위치이며, 종료 위치는 데이터베이스 위치를 삭제한 위치입니다.

mysqlbinlog --set-charset=utf8  binlog.000001 > tmp.sql

tmp.sql 파일에서 시작 위치와 끝 위치를 알 수 있습니다

binlog

시작 위치가 233

binlog

끝 위치가 233임을 알 수 있습니다. 1371이므로 데이터 복구 정보를 실행하십시오.

mysqlbinlog -v binlog.000001 --start-position=233 --stop-position=1371 | mysql

검증으로는 실행 전 데이터베이스가 존재하는지 확인할 수 있습니다.

아아아아

위 내용은 mysql binlog를 사용하여 데이터를 복원하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!