Mysql binlog ログと、binlog ログを使用してデータを回復するためのサンプル コードの共有
- 黄舟オリジナル
- 2017-03-27 13:42:531291ブラウズ
以下のエディターは、mysqlbinlog ログの説明と、binlog ログを使用してデータを回復する方法を示します。編集者はこれが非常に良いものだと思ったので、皆さんの参考として今から共有します。エディターをフォローして見てみましょう
ご存知のとおり、binlog ログは mysql データベースにとって非常に重要です。データ損失という緊急事態が発生した場合、私たちは、その場を救うために、データ回復に binlog ログ機能 (スケジュールされた完全バックアップ + binlog ログ回復増分データ部分) を使用することをよく考えます。
早速、binlog ログの操作について説明します。
1. binlog の予備理解
MySQL のバイナリ ログ binlog は、MySQL の最も重要なログであると言えます。 MySQL の記録 すべての DDL および DML ステートメント (データ クエリ ステートメントの選択を除く) はイベントの形式で記録され、MySQL のバイナリ ログはトランザクション セーフです。
------------------------------------------------- -------------------------------------------------- --------------------------------------------------
DDL
---- データ定義言語 データベース定義言語
主なコマンドは、CREATE、ALTER、DROP などです。DDL は主に、テーブル (TABLE) の構造、データ型、リンクと制約を定義または変更するために使用されます。テーブルやその他の初期化作業。そのほとんどはテーブルの作成時に使用されます。
DML
---データ操作言語 データ操作言語
その名前のように、これらの 4 つのコマンドはデータベース内のデータを操作するために使用される言語です。 ---------------------------------------------- --- ---------------------------------------------- --- --------------------------------------
--start-datetime: 指定されたタイムスタンプ以降のバイナリ ログから、ローカル コンピューター以降を読み取ります。
--stop-datetime: 指定された時間未満のバイナリ ログから読み取ります。タイムスタンプまたはローカルコンピュータに等しい時間値は上記と同じです
--start-position: バイナリログから指定された位置イベント位置を開始として読み取ります。
--stop-position:
********************* 時点のイベントとしてバイナリログから指定された位置イベント位置を読み取ります。 ********* **************************************** *******一般的に来てください binlog を有効にすると、約 1% のパフォーマンスが低下すると言われています。binlog ログには 2 つの最も重要な使用シナリオがあります:
MySQL マスター/スレーブ レプリケーション: MySQL レプリケーションはマスター側で binlog を有効にし、マスターはそのバイナリ ログをスレーブに渡して master-スレーブデータの一貫性の目的。
mysqlbinlog ツールを使用してデータを回復するデータ回復です。
バイナリログには 2 種類のファイルが含まれます:
ファイル名のサフィックスは .index) はすべてのバイナリファイルを記録するために使用されます
ファイル名)接尾辞は .index) 00000*) は、すべての DDL および DML (データ クエリ ステートメント select を除く) ステートメント イベントをデータベースに記録します。
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 サービスが再起動されるたびに自動的に実行されます。このコマンドは、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语句逐个重新执行一次而已。
以上がMysql binlog ログと、binlog ログを使用してデータを回復するためのサンプル コードの共有の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。