Linux_MySQL에서 binlog 파일을 통해 mysql 데이터베이스를 복원하는 세부 단계

1. binlog 소개

서버의 바이너리 로그는 데이터베이스의 모든 추가, 삭제 및 수정을 기록하며(자체 서버에서 binlog가 활성화된 경우) 이러한 작업의 실행 시간도 포함합니다. 이러한 바이너리 내용을 표시하려면 mysqlbinlog 명령을 사용하여 볼 수 있습니다.

목적 1: 마스터-슬레이브 동기화

목적 2: 데이터베이스 복원(데이터베이스 파일이 온라인에서 손실된 후에야 이 사실을 알게 되었습니다.)

Mysqlbinlog 명령 사용법: shell> mysqlbinlog [옵션] log_file ...

1) mysqlbinlog 옵션 예

일반적인 옵션은 다음과 같습니다.

--시작-날짜/시간

바이너리 로그에서 로컬 시스템 시간과 같거나 그 이후의 지정된 타임스탬프를 읽습니다. 다음과 같은 값:="1470733768" 또는="2016-08-09 5:09:28"

예:

[root@hcloud ~]# mysqlbinlog --start-datetime="2016-08-09 5:05:27" /var/lib/mysql/mysql-bin.000001 
--stop-datetime

타임스탬프보다 작거나 로컬 컴퓨터와 동일한 바이너리 로그에서 지정된 시간을 읽어옵니다.

--시작 위치

바이너리 로그에서 지정된 위치 이벤트 위치를 시작으로 읽어옵니다. 값:="2698"

예:

[root@hcloud ~]# mysqlbinlog --start-position="2698" /var/lib/mysql/mysql-bin.000001 
--stop-position

이벤트 종료로 바이너리 로그에서 지정된 위치 이벤트 위치를 읽어옵니다. 값:="2698"

2. 환경 준비 및 백업 및 복구

　1) mysql 설치 후 binlog 활성화 체크

mysql> 바이너리 로그 표시;

오류 1381(HY000): 바이너리 로깅을 사용하고 있지 않습니다. 위 프롬프트는 binlog를 활성화할 서버가 없음을 나타냅니다.

/etc/my.cnf 수정

mysqld 옵션에 다음과 같이 한 줄을 추가합니다.

로그-bin=mysql-bin

기본적으로 값이 지정되지 않으면 log-bin은 mysqld-bin을 인덱스로 사용하고 mysqld-bin.00001 등을 생성합니다.

mysqld를 다시 시작하세요.

　2) 빈로그 확인

mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 106 |
+------------------+-----------+
1 row in set (0.00 sec)

　3) 먼저 원시 데이터를 생성합니다.

mysql> create database Test_DB;
Query OK, 1 row affected (0.00 sec)
mysql> use Test_DB;
Database changed
mysql> CREATE TABLE OneTb(id INT(10) NOT NULL,name varchar(20),age INT(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into OneTb values (1,'user1',18);
mysql> insert into OneTb values (2,'user2',19);
insert into OneTb values (3,'user3',20);

데이터 확인:

mysql> select * from OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
+----+-------+------+
3 rows in set (0.00 sec)

　4) 백업 및 복원(전체 백업 및 복원)

여기에서는 일일 전체 데이터베이스 백업 작업을 시뮬레이션합니다.

[root@hcloud ~]# mysqldump -uroot -p Test_DB > /data/mysqlbackup/Test_DB_0809-16:50.sql
Enter password:

시뮬레이션 중 연산 오류가 발생하여 데이터가 잘못 수정되었습니다.

mysql> update OneTb set age = 15;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql> select * from OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 15 |
| 2 | user2 | 15 |
| 3 | user3 | 15 |
+----+-------+------+
3 rows in set (0.00 sec)

이제 우리는 전통적인 방법을 사용하여 복원합니다.

[root@hcloud ~]# mysql -uroot -p Test_DB < /data/mysqlbackup/Test_DB_0809-16\:50.sql 

다시 확인:

mysql> select * from Test_DB.OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
+----+-------+------+
3 rows in set (0.00 sec)

데이터가 복원된 것을 확인할 수 있습니다.

5) binlog를 사용하여 복원 시뮬레이션

원본 테이블을 기반으로 여러 데이터 조각을 만듭니다.

mysql> insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from Test_DB.OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
| 4 | user4 | 21 |
| 5 | user5 | 22 |
| 6 | user6 | 23 |
+----+-------+------+
6 rows in set (0.00 sec)

이때 실수로 데이터를 수정하거나 라이브러리를 삭제하여 모든 데이터가 손실된 경우, 이때 최신 백업 파일인 Test_DB_0809-16:50.sql을 사용하여 데이터를 복원하면 이후에 Data가 새로 삽입됩니다. 백업이 손실됩니다.

참고: 정말 최신 백업 파일을 사용한다면 최후의 수단이 되어야 합니다(예를 들어 binlog가 삭제되거나 하드 디스크 전체가 정지되는 등... 생각만 해도 끔찍합니다...)

오작동을 시뮬레이션하고 사용자 이름을 일괄 변경합니다.

mysql> update Test_DB.OneTb set name='user10';
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

아니요, 이전 단계는 충분히 무자비하지 않았습니다. 여기서는 더 무자비하게 모든 테이블을 삭제해 보겠습니다.

mysql> drop table Test_DB.OneTb;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 3
Current database: *** NONE ***
Query OK, 0 rows affected (0.00 sec)

처음에 binlog 옵션을 켜두었기 때문에 binlog를 사용하여 데이터베이스를 복원했습니다. binlog부터 시작해 보겠습니다. 먼저 binlog 파일을 확인합니다. 현재 mysql 서비스는 binlog가 켜진 이후 두 번 다시 시작되었으므로 binlog 파일이 2개 있습니다(다시 시작할 때마다 binlog 파일이 재생성됩니다.) FLUSH LOGS 명령을 실행하면 다시 빌드됩니다.

mysql-bin.index 파일에 기록되는 내용은 log-bin 옵션이 활성화된 이후 기록된 모든 바이너리 로그 목록입니다.

참고: 실제 프로덕션 환경에서 데이터베이스를 복원해야 하는 상황이 발생하면 새로운 데이터가 삽입되는 것을 피하기 위해 사용자가 데이터베이스에 접근하는 것을 허용하지 말고, 마스터-슬레이브 환경에서는 데이터베이스를 종료하세요. 주인-노예.

mysqlbinlog 명령어를 이용하여 binlog 파일을 살펴보겠습니다. mysql-bin.00002

마지막에 삭제 작업이 있는 것을 볼 수 있습니다. 하지만 아직 마지막에 삭제 작업이 남아 있기 때문에 완전히 복원할 수는 없습니다.

　　现在我的思路就是，先将第一个binlog 和第二个binlog 文件导出来à利用指定的position位置的方式（过滤掉删除表操作和update Test_DB.OneTb set name='user10';这条语句 ），导出2个sql 语句，最后我们将2个sql 合成一个sql，导入到数据库中即可。

　　我们先用mysqlbinlog命令找到update 那条语句的位置，然后指定position 将mysql-bin.00001 导出来。

[root@hcloud ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
….
#160809 5:09:28 server id 1 end_log_pos 2698 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1470733768/*!*/;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
insert into Test_DB.OneTb values(4,'user4',21),(5,'user5',22),(6,'user6',23)
/*!*/;
# at 2698
#160809 5:19:49 server id 1 end_log_pos 2795 Query thread_id=17 exec_time=0 error_code=0
SET TIMESTAMP=1470734389/*!*/;
update Test_DB.OneTb set name='user10'
/*!*/;
# at 2795
#160809 5:30:38 server id 1 end_log_pos 2814 Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

　　从上面可以看到我们在做插入正常数据后的position 是2698，那么使用下面的命令导出sql

[root@hcloud ~]# mysqlbinlog --stop-position="2698" /var/lib/mysql/mysql-bin.000001 > Backup_1.sql 

　　然后导出mysql-bin.00002的sql 语句（注：由于演示操作，该文件只有一个drop 表操作，所以不做处理，但是在实际环境中，由于中途可能会有重启数据库操作，那时就需要检测最新的binlog有没有业务需要的语句。）

　　sql 语句已经导出来了。我们可以利用该语句直接恢复所有正常的数据。

　　注：本次恢复没有利用到之前的完整备份，因为我是开启binlog后，然后才做的所有建库建表操作，第一个binlog文件里已经记录了所有的数据库操作，所以不需要使用之前的完整备份（另外：实际的生产环境，还是需要利用到完整备份的，因为线上环境可能会有N多个binlog文件，所以需要利用到完整备份和最新的binlog文件来结合恢复）

　　开始恢复前，我们将原有的Test_DB数据库也给干掉吧。毕竟我们的binlog中有创建操作

mysql> DROP DATABASE Test_DB;
Query OK, 0 rows affected (0.03 sec)

　　恢复数据库时还可以利用在登陆mysql 后，用source 命令导入sql语句，这里暂不介绍

[root@hcloud ~]# mysql -uroot -p < Backup_1.sql 

Enter password:

　　恢复完成后，我们检查下表的数据是否完整

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| Test_DB |
| mysql |
+--------------------+
3 rows in set (0.00 sec)
mysql> select * from Test_DB.OneTb;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | user1 | 18 |
| 2 | user2 | 19 |
| 3 | user3 | 20 |
| 4 | user4 | 21 |
| 5 | user5 | 22 |
| 6 | user6 | 23 |
+----+-------+------+
6 rows in set (0.00 sec)

　　Ok完整的都恢复过来了。

三、总结

　　1) 恢复方式

　　　　a) 利用最新一次的完整备份加binlog 指定事件起始时间和终止时间或者position恢复数据库

　　　　b) 利用所有binlog指定事件起始位置和终止时间来合并sql文件恢复数据库（此方法要确保binlog文件的完整）

　　　　c) 利用mysqldump 使用完整恢复。（在确保最新一次的完整备份后的数据不重要，允许丢掉的情况下，直接恢复。该方法最简单、效率最高）

　　2) 附:官方建议的备份原则（为了能睡个好觉….嗯，是的）

　　　　a) 在mysql安装好并运行时，就始终开启 log-bin选项，该日志文件位于datadir目录下，也要确保该目录所在存储介质是安全的。

　　　　b) 定期做完整的mysql 备份。

　　　　c) 定期使用 FlUSH LOGS 或者 mysqladmin flush-logs ，该操作会关闭当前的二进制日志文件，并新建一个binlog日志文件。(和重启mysql后新建的binlog操作一样)。以备份binlog日志，利用binlog日志也可以做增量备份。

以上所述是小编给大家介绍的Linux上通过binlog文件恢复mysql数据库详细步骤，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对网站的支持！