데이터 복구를 위해 binlog 로그를 사용하기 위한 MySQL binlog 로그 및 샘플 코드 공유
- 黄舟원래의
- 2017-03-27 13:42:531291검색
다음 편집기에서는 mysql binlog 로그에 대한 설명과 binlog 로그를 사용하여 데이터를 복구하는 방법을 제공합니다. 에디터가 꽤 좋다고 생각해서 지금 공유해서 참고용으로 올려보겠습니다. 편집기를 따라 살펴보겠습니다
우리 모두 알고 있듯이 binlog 로그는 mysql 데이터베이스에 매우 중요합니다. 데이터가 손실된 긴급 상황에서 우리는 데이터 복구를 위해 binlog 로그 기능(예약된 전체 백업 + binlog 로그 복구 증분 데이터 부분)을 사용하여 하루를 절약하는 것을 종종 생각합니다!
더 이상 고민하지 않고 binlog 로그 작업에 대한 설명은 다음과 같습니다.
1. binlog에 대한 사전 이해
MySQL의 바이너리 로그 binlog는 MySQL의 가장 중요한 로그라고 할 수 있습니다. 모든 DDL 및 DML 문(데이터 쿼리 문 select 제외)을 이벤트 형태로 기록합니다. 또한 명령문 실행에 소요된 시간도 포함됩니다. MySQL의 바이너리 로그는 트랜잭션에 안전합니다.
---------------------------------- --- ---------------------------------- --- -------------------
DDL
----데이터 정의어 데이터베이스 정의어
주요 명령어는 CREATE, ALTER, DROP 등이 있습니다. DDL은 주로 테이블(TABLE)의 구조, 데이터 유형, 데이터 유형을 정의하거나 변경하는 데 사용됩니다. 테이블 간의 링크 및 제약 조건 등의 초기화 작업을 위해 테이블을 생성할 때 주로 사용됩니다.
DML
----데이터 조작 언어 데이터 조작 언어
주요 명령은 SELECT, UPDATE, INSERT, DELETE라는 이름처럼 이 네 가지 명령을 사용하는 언어입니다. 데이터베이스의 데이터
------------------------------- -- ------------------------------------------------ -- ------------------------------------------------ -- ---
Mysqlbinlog 공통 옵션은 다음과 같습니다.
--start-datetime: 바이너리 로그에서 지정된 타임스탬프 또는 로컬 컴퓨터보다 이후의 시간을 읽습니다. time
--stop-datetime: 바이너리 로그에서 타임스탬프보다 작거나 로컬 컴퓨터와 동일한 지정된 시간 값을 읽고 값이 동일합니다. 위와 같습니다
--start-position: 바이너리 로그에서 지정된 위치 이벤트 위치를 시작으로 읽어옵니다.
--stop-position: 바이너리 로그에서 지정된 위치 이벤트 위치를
***** 현재 이벤트로 읽습니다. ************************************************** *** ************
일반적으로 binlog를 켜면 약 1% 정도의 성능 손실이 발생합니다.
binlog 로그에는 두 가지 가장 중요한 사용 시나리오가 있습니다.
1) MySQL 마스터-슬레이브 복제 : MySQL 복제 마스터 측에서 binlog를 활성화하면 마스터가 바이너리 로그를 슬레이브에 전달하여
마스터-슬레이브 데이터 일관성을 달성합니다.
2) 당연히 데이터 복구, mysqlbinlog 도구를 사용하여 데이터를 복구합니다.
Binlog 로그에는 두 가지 유형의 파일이 포함됩니다.
1) 바이너리 로그 인덱스 파일(파일 이름 접미사는 .index)을 사용하여 모든 내용을 기록합니다. 바이너리 파일
2) 바이너리 로그 파일( 파일 이름 접미사는 .00000*)은 데이터베이스의 모든 DDL 및 DML(데이터 쿼리 문 선택 제외) 문 이벤트를 기록합니다. .
2. binlog 활성화:
1) mysql 구성 파일 /etc/을 편집하고 엽니다. mys .cnf
[root@vm-002 ~]# vim /etc/my.cnf
[mysqld] 블록에
log-bin=mysql-bin을 추가하고 열려 있는지 상태 확인하세요( mysql-bin은 로그의 기본 이름 또는 접두사 이름입니다.
2) mysqld 서비스를 다시 시작하여 구성을 적용합니다.
[root@vm-002 ~]# /etc/init.d/mysqld stop [root@vm-002 ~]# /etc/init.d/mysqld restart Stopping mysqld: [ OK ] Starting mysqld: [ OK ]
3) binlog 로그가 켜져 있습니다
mysql> show variables like 'log_%'; +---------------------------------+---------------------+ | Variable_name | Value | +---------------------------------+---------------------+ | log_bin | ON | | log_bin_trust_function_creators | OFF | | log_bin_trust_routine_creators | OFF | | log_error | /var/log/mysqld.log | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_slave_updates | OFF | | log_slow_queries | OFF | | log_warnings | 1 | +---------------------------------+---------------------+ 9 rows in set (0.00 sec)
3. 자주 사용하는 binlog 로그 작업 명령
1) binlog 전체 보기 로그 목록
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000 001 | 149 | | mysql-bin.000002 | 4102 | +------------------+-----------+ 2 rows in set (0.00 sec)
2 ) 마스터 상태, 즉 마지막(최신) binlog 로그의 번호명과 마지막 작업 이벤트의 pos 끝점(Position) 값을 확인합니다
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000002 | 4102 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
3) flush 로그 로그 새로 고침, 이 순간부터 새로운 번호의 binlog 로그 파일
mysql> flush logs; Query OK, 0 rows affected (0.13 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 149 | | mysql-bin.000002 | 4145 | | mysql-bin.000003 | 106 | +------------------+-----------+ 3 rows in set (0.00 sec)
참고:
mysqld 서비스가 다시 시작될 때마다 이 명령이 자동으로 실행되어 binlog 로그를 새로 고칩니다. mysqldump로 데이터를 백업할 때 -F 옵션을 추가하면 binlog 로그도 새로 고쳐집니다. >
4) 모든 binlog 로그 재설정(삭제)mysql> reset master; Query OK, 0 rows affected (0.12 sec) mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 106 | +------------------+-----------+ 1 row in set (0.00 sec)
四、查看binlog日志内容，常用有两种方式：
1）使用mysqlbinlog自带查看命令法：
注意：
-->binlog是二进制文件，普通文件查看器cat、more、vim等都无法打开，必须使用自带的mysqlbinlog命令查看
-->binlog日志与数据库文件在同目录中
-->在MySQL5.5以下版本使用mysqlbinlog命令时如果报错，就加上 “--no-defaults”选项
查看mysql的数据存放目录，从下面结果可知是/var/lib//mysql
[root@vm-002 ~]# ps -ef|grep mysql root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql mysql 9896 9791 0 21:18 pts/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx root 9919 9715 0 21:23 pts/1 00:00:00 grep --color mysql [root@vm-002 ~]# cd /var/lib/mysql/ [root@vm-002 mysql]# ls ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock ops test
使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析：
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000002 .............. # at 624 #160925 21:29:53 server id 1 end _log_pos 796 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1474810193/*!*/; insert into member(`name`,`sex`,`age`,` class id`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #执行的sql语句 /*!*/; # at 796 #160925 21:29:53 server id 1 end_log_pos 823 Xid = 17 #执行的时间 .............
解释：
server id 1 ： 数据库主机的服务号；
end_log_pos 796： sql结束时的pos节点
thread_id=11： 线程号
2）上面这种办法读取出binlog日志的全文内容比较多，不容易分辨查看到pos点信息
下面介绍一种更为方便的查询命令：
命令格式：
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
参数解释：
IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] ：偏移量(不指定就是0)
row_count ：查询总条数(不指定就是所有行)
mysql> show master logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 125 | | mysql-bin.000002 | 823 | +------------------+-----------+ 2 rows in set (0.00 sec) mysql> show binlog events in 'mysql-bin.000002'\G; *************************** 1. row *************************** Log_name: mysql-bin.000002 Pos: 4 Event_type: Format_desc Server_id: 1 End_log_pos: 106 Info: Server ver: 5.1.73-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysql-bin.000002 Pos: 106 Event_type: Query Server_id: 1 End_log_pos: 188 Info: use `ops`; drop table customers *************************** 3. row *************************** Log_name: mysql-bin.000002 Pos: 188 Event_type: Query Server_id: 1 End_log_pos: 529 Info: use `ops`; CREATE TABLE IF NOT EXISTS `member` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(16) NOT NULL, `sex` enum('m','w') NOT NULL DEFAULT 'm', `age` tinyint(3) unsigned NOT NULL, `classid` char(6) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 *************************** 4. row *************************** Log_name: mysql-bin.000002 Pos: 529 Event_type: Query Server_id: 1 End_log_pos: 596 Info: BEGIN *************************** 5. row *************************** Log_name: mysql-bin.000002 Pos: 596 Event_type: Intvar Server_id: 1 End_log_pos: 624 Info: INSERT_ID=1 *************************** 6. row *************************** Log_name: mysql-bin.000002 Pos: 624 Event_type: Query Server_id: 1 End_log_pos: 796 Info: use `ops`; insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') *************************** 7. row *************************** Log_name: mysql-bin.000002 Pos: 796 Event_type: Xid Server_id: 1 End_log_pos: 823 Info: COMMIT /* xid=17 */ 7 rows in set (0.00 sec) ERROR: No query specified mysql>
上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！
如下操作示例：
a）查询第一个(最早)的binlog日志：
mysql> show binlog events\G;
b）指定查询 mysql-bin.000002这个文件：
mysql> show binlog events in 'mysql-bin.000002'\G;
c）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起：
mysql> show binlog events in 'mysql-bin.000002' from 624\G;
d）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;
e）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;
五、利用binlog日志恢复mysql数据
以下对ops库的member表进行操作
mysql> use ops； mysql> CREATE TABLE IF NOT EXISTS `member` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `name` varchar(16) NOT NULL, -> `sex` enum('m','w') NOT NULL DEFAULT 'm', -> `age` tinyint(3) unsigned NOT NULL, -> `classid` char(6) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.10 sec) mysql> show tables; +---------------+ | Tables_in_ops | +---------------+ | member | +---------------+ 1 row in set (0.00 sec) mysql> desc member; +---------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(16) | NO | | NULL | | | sex | enum('m','w') | NO | | m | | | age | tinyint(3) unsigned | NO | | NULL | | | classid | char(6) | YES | | NULL | | +---------+---------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
事先插入两条数据
mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2'); Query OK, 2 rows affected (0.08 sec) Rec ord s: 2 Duplicates: 0 Warnings: 0 mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | +----+-----------+-----+-----+---------+ 2 rows in set (0.00 sec)
下面开始进行场景模拟：
1）
ops库会在每天凌晨4点进行一次完全备份的定时计划任务，如下：
[root@vm-002 ~]# crontab -l 0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|g zip >/opt/backup/ops_$(date +%F).sql.gz
这里手动执行下，将ops数据库备份到/opt/backup/ops_$(date +%F).sql.gz文件中：
[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz Enter password: [root@vm-002 ~]# ls /opt/backup/ ops_2016-09-25.sql.gz
-----------------
参数说明：
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
--master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
-----------------
待到数据库备份完成，就不用担心数据丢失了，因为有完全备份数据在！！
由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生
一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作
查看一下：
mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
也就是说， mysql-bin.000003 是用来记录4:00之后对数据库的所有“增删改”操作。
2）
早上9点上班了，由于业务的需求会对数据库进行各种“增删改”操作。
比如：在ops库下member表内插入、修改了数据等等：
先是早上进行插入数据：
mysql> insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'), ('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6'); Query OK, 5 rows affected (0.08 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | xiaoer | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec)
3）
中午又执行了修改数据操作：
mysql> update ops.member set name='李四' where id=4; Query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update ops.member set name='小二' where id=2; Query OK, 1 row affected (0.06 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | 小二 | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec)
4）
在下午18:00的时候，悲剧莫名其妙的出现了！
手贱执行了drop语句，直接删除了ops库！吓尿！
mysql> drop database ops; Query OK, 1 row affected (0.02 sec)
5）
这种时候，一定不要慌张！！！
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；
先备份一下最后一个binlog日志文件：
[root@vm-002 ~]# cd /var/lib/mysql/ [root@vm-002 mysql]# cp -v mysql-bin.000003 /opt/backup/ `mysql-bin.000003' -> `/opt/backup/mysql-bin.000003' [root@vm-002 mysql]# ls /opt/backup/ mysql-bin.000003 ops_2016-09-25.sql.gz
接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。按理说mysql-bin.000003
这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件。
mysql> flush logs;Query OK, 0 rows affected (0.13 sec) mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
6）
读取binlog日志，分析问题。
读取binlog日志的方法上面已经说到。
方法一：使用mysqlbinlog读取binlog日志：
[root@vm-002 ~]# cd /var/lib/mysql/ [root@vm-002 mysql]# mysqlbinlog mysql-bin.000003
方法二：登录服务器，并查看(推荐此种方法)
mysql> show binlog events in 'mysql-bin.000003'; +------------------+-----+-------------+-----------+-------------+--------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 1 | 106 | Server ver: 5.1.73-log, Binlog ver: 4 | | mysql-bin.000003 | 106 | Query | 1 | 173 | BEGIN | | mysql-bin.000003 | 173 | Intvar | 1 | 201 | INSERT_ID=3 | | mysql-bin.000003 | 201 | Query | 1 | 444 | use `ops`; insert into ops.member(`name`,`sex`,`age`,`gsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') | | mysql-bin.000003 | 444 | Xid | 1 | 471 | COMMIT /* xid=66 */ | | mysql-bin.000003 | 471 | Query | 1 | 538 | BEGIN | | mysql-bin.000003 | 538 | Query | 1 | 646 | use `ops`; update ops.member set name='李四' where id= | | mysql-bin.000003 | 646 | Xid | 1 | 673 | COMMIT /* xid=68 */ | | mysql-bin.000003 | 673 | Query | 1 | 740 | BEGIN | | mysql-bin.000003 | 740 | Query | 1 | 848 | use `ops`; update ops.member set name='小二' where id= | | mysql-bin.000003 | 848 | Xid | 1 | 875 | COMMIT /* xid=69 */ | | mysql-bin.000003 | 875 | Query | 1 | 954 | drop database ops | | mysql-bin.000003 | 954 | Rotate | 1 | 997 | mysql-bin.000004;pos=4 | +------------------+-----+-------------+-----------+-------------+----------------------------------------------------------+ 13 rows in set (0.00 sec)
或者：
mysql> show binlog events in 'mysql-bin.000003'\G; ......... ......... *************************** 12. row *************************** Log_name: mysql-bin.000003 Pos: 875 Event_type: Query Server_id: 1 End_log_pos: 954 Info: drop database ops *************************** 13. row *************************** Log_name: mysql-bin.000003 Pos: 954 Event_type: Rotate Server_id: 1 End_log_pos: 997 Info: mysql-bin.000004;pos=4 13 rows in set (0.00 sec)
通过分析，造成数据库破坏的pos点区间是介于 875--954 之间（这是按照日志区间的pos节点算的），只要恢复到875前就可。
7）
先把凌晨4点全备份的数据恢复：
[root@vm-002 ~]# cd /opt/backup/ [root@vm-002 backup]# lsmysql-bin.000003 ops_2016-09-25.sql.gz [root@vm-002 backup]# gzip -d ops_2016-09-25.sql.gz [root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql Enter password: -------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */ -------------- --------------/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */-------------- .......................... --------------/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */--------------
这样就恢复了截至当日凌晨(4:00)前的备份数据都恢复了。
mysql> show databases; #发现ops库已经恢复回来了 mysql> use ops; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +---------------+ | Tables_in_ops | +---------------+ | member | +---------------+ 1 row in set (0.00 sec) mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | +----+-----------+-----+-----+---------+ 2 rows in set (0.00 sec) mysql>
但是这仅仅只是恢复了当天凌晨4点之前的数据，在4:00--18:00之间的数据还没有恢复回来！！
怎么办呢？
莫慌！这可以根据前面提到的mysql-bin.000003的新binlog日志进行恢复。
8）
从binlog日志恢复数据
恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
--------------------------------------------------------
常用参数选项解释：
--start-position=875 起始pos点 --stop-position=954 结束pos点 --start-datetime="2016-9-25 22:01:08" 起始时间点 --stop-datetime="2019-9-25 22:09:46" 结束时间点 --database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
--------------------------------------------------------
不常用选项：
-u --user=name 连接到远程主机的用户名 -p --password[=name] 连接到远程主机的密码 -h --host=name 从远程主机上获取binlog日志 --read-from-remote-server 从某个MySQL服务器上读取binlog日志
--------------------------------------------------------
小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；
a）完全恢复(需要手动vim编辑mysql-bin.000003，将那条drop语句剔除掉)
[root@vm-002 backup]# /usr/bin/mysqlbinlog /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
b）指定pos结束点恢复(部分恢复)：
--stop-position=471 pos结束节点（按照事务区间算，是471）
注意：
此pos结束节点介于“member表原始数据”与更新“name='李四'”之前的数据，这样就可以恢复到更改“name='李四'”之前的数据了。
操作如下：
[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=471 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | xiaoer | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec)
恢复截止到更改“name='李四'”之间的数据（按照事务区间算，是673）
[root@vm-002 ~]# /usr/bin/mysqlbinlog --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec)
c）指定pso点区间恢复(部分恢复)：
更新 name='李四' 这条数据，日志区间是Pos[538] --> End_log_pos[646]，按事务区间是：Pos[471] --> End_log_pos[673]
更新 name='小二' 这条数据，日志区间是Pos[740] --> End_log_pos[848]，按事务区间是：Pos[673] --> End_log_pos[875]
c1）
单独恢复 name='李四' 这步操作，可这样：
按照binlog日志区间单独恢复：
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=538 --stop-position=646 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
按照事务区间单独恢复
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=673 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
c2）
单独恢复 name='小二' 这步操作，可这样：
按照binlog日志区间单独恢复：
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=740 --stop-position=848 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
按照事务区间单独恢复
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=673 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
c3）
将 name='李四'、name='小二' 多步操作一起恢复，需要按事务区间，可这样：
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-position=471 --stop-position=875 --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops
查看数据库：
mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | 小二 | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec)
这样，就恢复了删除前的数据状态了！！
另外：
也可指定时间节点区间恢复(部分恢复)：
除了用pos节点的办法进行恢复，也可以通过指定时间节点区间进行恢复，按时间恢复需要用mysqlbinlog命令读取binlog日志内容，找时间节点。
如上，误删除ops库后：
先进行全备份恢复
[root@vm-002 backup]# mysql -uroot -p -v < ops_2016-09-25.sql
查看ops数据库
mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | +----+-----------+-----+-----+---------+ 2 rows in set (0.00 sec) mysql>
查看mysq-bin00003日志，找出时间节点
[root@vm-002 ~]# cd /var/lib/mysql [root@vm-002 mysql]# mysqlbinlog mysql-bin.000003 ............. ............. BEGIN /*!*/; # at 173 #160925 21:57:19 server id 1 end_log_pos 201 Intvar SET INSERT_ID=3/*!*/; # at 201 #160925 21:57:19 server id 1 end_log_pos 444 Query thread_id=3 exec_time=0 error_code=0 use `ops`/*!*/; SET TIMESTAMP=1474811839/*!*/; insert into ops.member(`name`,`sex`,`age`,`classid`) values('yiyi','w',20,'cls1'),('xiaoer','m',22,'cls3'), ('zhangsan','w',21,'cls5'),('lisi','m',20,'cls4'),('wangwu','w',26,'cls6') #执行的sql语句 /*!*/; # at 444 #160925 21:57:19 server id 1 end_log_pos 471 Xid = 66 #开始执行的时间 COMMIT /*!*/; # at 471 #160925 21:58:41 server id 1 end_log_pos 538 Query thread_id=3 exec_time=0 error_code=0 #结束时间 SET TIMESTAMP=1474811921/*!*/; BEGIN /*!*/; # at 538 #160925 21:58:41 server id 1 end_log_pos 646 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1474811921/*!*/; update ops.member set name='李四' where id=4 #执行的sql语句 /*!*/; # at 646 #160925 21:58:41 server id 1 end_log_pos 673 Xid = 68 #开始执行的时间 COMMIT/*!*/; # at 673 #160925 21:58:56 server id 1 end_log_pos 740 Query thread_id=3 exec_time=0 error_code=0 #结束时间 SET TIMESTAMP=1474811936/*!*/; BEGIN /*!*/; # at 740 #160925 21:58:56 server id 1 end_log_pos 848 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1474811936/*!*/; update ops.member set name='小二' where id=2 #执行的sql语句 /*!*/; # at 848 #160925 21:58:56 server id 1 end_log_pos 875 Xid = 69 #开始执行的时间 COMMIT/*!*/; # at 875 #160925 22:01:08 server id 1 end_log_pos 954 Query thread_id=3 exec_time=0 error_code=0 #结束时间 SET TIMESTAMP=1474812068/*!*/; drop database ops /*!*/; # at 954 #160925 22:09:46 server id 1 end_log_pos 997 Rotate to mysql-bin.000004 pos: 4 DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
恢复到更改“name='李四'”之前的数据
[root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:57:19" --stop-datetime="2016-09-25 21:58:41" -- database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | xiaoer | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec) [root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:41" --stop-datetime="2016-09-25 21:58:56" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | guohuihui | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec) [root@vm-002 ~]# /usr/bin/mysqlbinlog --start-datetime="2016-09-25 21:58:56" --stop-datetime="2016-09-25 22:01:08" --database=ops /var/lib/mysql/mysql-bin.000003 | /usr/bin/mysql -uroot -p123456 -v ops mysql> select * from member; +----+-----------+-----+-----+---------+ | id | name | sex | age | classid | +----+-----------+-----+-----+---------+ | 1 | wangshibo | m | 27 | cls1 | | 2 | 小二 | w | 27 | cls2 | | 3 | yiyi | w | 20 | cls1 | | 4 | 李四 | m | 22 | cls3 | | 5 | zhangsan | w | 21 | cls5 | | 6 | lisi | m | 20 | cls4 | | 7 | wangwu | w | 26 | cls6 | +----+-----------+-----+-----+---------+ 7 rows in set (0.00 sec)
这样，就恢复了删除前的状态了！
总结：
所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
위 내용은 데이터 복구를 위해 binlog 로그를 사용하기 위한 MySQL binlog 로그 및 샘플 코드 공유의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!