MySQL 二进制文件恢复

先不说话  先来一段代码块

  1 mysql> show variables like 'autocommit';
  2 +---------------+-------+
  3 | Variable_name | Value |
  4 +---------------+-------+
  5 | autocommit    | ON    |
  6 +---------------+-------+
  7 1 row in set (0.00 sec)
  8 
  9 mysql> set autocommit=0;
 10 Query OK, 0 rows affected (0.00 sec)
 11 
 12 mysql> show variables like 'autocommit';
 13 +---------------+-------+
 14 | Variable_name | Value |
 15 +---------------+-------+
 16 | autocommit    | OFF   |
 17 +---------------+-------+
 18 1 row in set (0.00 sec)
 19 
 20 
 21 mysql> show master status;
 22 +------------------+----------+--------------+------------------+-------------------+
 23 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 24 +------------------+----------+--------------+------------------+-------------------+
 25 | mysql-bin.000034 |      120 |              |                  |                   |
 26 +------------------+----------+--------------+------------------+-------------------+
 27 1 row in set (0.00 sec)
 28 
 29 
 30 #第二个
 31 mysql> create database luna;
 32 Query OK, 1 row affected (0.00 sec)
 33 
 34 mysql> show master status;
 35 +------------------+----------+--------------+------------------+-------------------+
 36 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 37 +------------------+----------+--------------+------------------+-------------------+
 38 | mysql-bin.000034 |      214 |              |                  |                   |
 39 +------------------+----------+--------------+------------------+-------------------+
 40 1 row in set (0.00 sec)
 41 
 42 mysql> use luna;
 43 Database changed
 44 mysql> create table t1(id int);
 45 Query OK, 0 rows affected (0.08 sec)
 46 
 47 mysql> show master status;
 48 +------------------+----------+--------------+------------------+-------------------+
 49 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 50 +------------------+----------+--------------+------------------+-------------------+
 51 | mysql-bin.000034 |      311 |              |                  |                   |
 52 +------------------+----------+--------------+------------------+-------------------+
 53 1 row in set (0.00 sec)
 54 
 55 
 56 mysql> insert into t1 values(1);
 57 Query OK, 1 row affected (0.00 sec)
 58 
 59 mysql> show master status;
 60 +------------------+----------+--------------+------------------+-------------------+
 61 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 62 +------------------+----------+--------------+------------------+-------------------+
 63 | mysql-bin.000034 |      311 |              |                  |                   |
 64 +------------------+----------+--------------+------------------+-------------------+
 65 1 row in set (0.00 sec)
 66 
 67 mysql> commit;
 68 Query OK, 0 rows affected (0.33 sec)
 69 
 70 mysql> show master status;
 71 +------------------+----------+--------------+------------------+-------------------+
 72 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 73 +------------------+----------+--------------+------------------+-------------------+
 74 | mysql-bin.000034 |      499 |              |                  |                   |
 75 +------------------+----------+--------------+------------------+-------------------+
 76 1 row in set (0.00 sec)
 77 
 78 #update
 79 mysql> update t1 set id=11 where id=1;
 80 Query OK, 1 row affected (0.00 sec)
 81 Rows matched: 1  Changed: 1  Warnings: 0
 82 
 83 mysql> show master status;
 84 +------------------+----------+--------------+------------------+-------------------+
 85 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 86 +------------------+----------+--------------+------------------+-------------------+
 87 | mysql-bin.000034 |      857 |              |                  |                   |
 88 +------------------+----------+--------------+------------------+-------------------+
 89 1 row in set (0.00 sec)
 90 
 91 mysql> show master status;
 92 +------------------+----------+--------------+------------------+-------------------+
 93 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 94 +------------------+----------+--------------+------------------+-------------------+
 95 | mysql-bin.000034 |     1051 |              |                  |                   |
 96 +------------------+----------+--------------+------------------+-------------------+
 97 1 row in set (0.00 sec)
 98 
 99 
100 #delete
101 mysql> delete from t1 where id=2;
102 Query OK, 1 row affected (0.00 sec)
103 
104 mysql> show master status;
105 +------------------+----------+--------------+------------------+-------------------+
106 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
107 +------------------+----------+--------------+------------------+-------------------+
108 | mysql-bin.000034 |     1051 |              |                  |                   |
109 +------------------+----------+--------------+------------------+-------------------+
110 1 row in set (0.00 sec)
111 
112 mysql> commit;
113 Query OK, 0 rows affected (0.01 sec)
114 
115 mysql> show master status;
116 +------------------+----------+--------------+------------------+-------------------+
117 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
118 +------------------+----------+--------------+------------------+-------------------+
119 | mysql-bin.000034 |     1239 |              |                  |                   |
120 +------------------+----------+--------------+------------------+-------------------+
121 1 row in set (0.00 sec)
122 
123 
124 #drop  
125 mysql> select * from t1;
126 +------+
127 | id   |
128 +------+
129 |   11 |
130 |    3 |
131 |    4 |
132 +------+
133 3 rows in set (0.00 sec)
134 
135 mysql> update t1 set id=44 where id=4;
136 Query OK, 1 row affected (0.00 sec)
137 Rows matched: 1  Changed: 1  Warnings: 0
138 
139 mysql> drop table t1;
140 Query OK, 0 rows affected (0.04 sec)
141 
142 mysql> drop database luna;
143 Query OK, 0 rows affected (0.08 sec)
144 
145 mysql> show master status;
146 +------------------+----------+--------------+------------------+-------------------+
147 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
148 +------------------+----------+--------------+------------------+-------------------+
149 | mysql-bin.000034 |     1633 |              |                  |                   |
150 +------------------+----------+--------------+------------------+-------------------+
151 1 row in set (0.00 sec)
152 
153 
154 
155 
156 #工具查看
157 mysql> show binlog events in 'mysql-bin.000034';
158 
159 
160 #在命令行查看
161 
162 [root@db01-sa mysql]# mysqlbinlog --base64-output=decode-rows -vvv /data/mysql/mysql-bin.000034
163 
164 
165 
166 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
167 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
168 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
169 DELIMITER /*!*/;
170 # at 4
171 #180627 17:49:07 server id 6  end_log_pos 120 CRC32 0x75f5723b     Start: binlog v 4, server v 5.6.38-log created 180627 17:49:07 at startup
172 # Warning: this binlog is either in use or was not closed properly.
173 ROLLBACK/*!*/;
174 # at 120
175 #180627 18:21:12 server id 6  end_log_pos 214 CRC32 0x0a1b14fc     Query    thread_id=2    exec_time=0    error_code=0
176 SET TIMESTAMP=1530094872/*!*/;
177 SET @@session.pseudo_thread_id=2/*!*/;
178 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
179 SET @@session.sql_mode=1075838976/*!*/;
180 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
181 /*!C utf8 *//*!*/;
182 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
183 SET @@session.lc_time_names=0/*!*/;
184 SET @@session.collation_database=DEFAULT/*!*/;
185 create database luna
186 /*!*/;
187 # at 214
188 #180627 18:22:16 server id 6  end_log_pos 311 CRC32 0x9fe876dc     Query    thread_id=2    exec_time=0    error_code=0
189 use `luna`/*!*/;
190 SET TIMESTAMP=1530094936/*!*/;
191 create table t1(id int)
192 /*!*/;
193 # at 311
194 #180627 18:23:02 server id 6  end_log_pos 383 CRC32 0xa66c8e7d     Query    thread_id=2    exec_time=0    error_code=0
195 SET TIMESTAMP=1530094982/*!*/;
196 BEGIN
197 /*!*/;
198 # at 383
199 #180627 18:23:02 server id 6  end_log_pos 428 CRC32 0xf0097518     Table_map: `luna`.`t1` mapped to number 70
200 # at 428
201 #180627 18:23:02 server id 6  end_log_pos 468 CRC32 0x2b1aa647     Write_rows: table id 70 flags: STMT_END_F
202 ### INSERT INTO `luna`.`t1`
203 ### SET
204 ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
205 # at 468
206 #180627 18:23:28 server id 6  end_log_pos 499 CRC32 0x62719421     Xid = 37
207 COMMIT/*!*/;
208 # at 499
209 #180627 18:25:06 server id 6  end_log_pos 571 CRC32 0x84c9efe6     Query    thread_id=2    exec_time=0    error_code=0
210 SET TIMESTAMP=1530095106/*!*/;
211 BEGIN
212 /*!*/;
213 # at 571
214 #180627 18:25:06 server id 6  end_log_pos 616 CRC32 0x74d151e2     Table_map: `luna`.`t1` mapped to number 70
215 # at 616
216 #180627 18:25:06 server id 6  end_log_pos 656 CRC32 0xd10d7120     Write_rows: table id 70 flags: STMT_END_F
217 ### INSERT INTO `luna`.`t1`
218 ### SET
219 ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
220 # at 656
221 #180627 18:27:16 server id 6  end_log_pos 701 CRC32 0x7895e39d     Table_map: `luna`.`t1` mapped to number 70
222 # at 701
223 #180627 18:27:16 server id 6  end_log_pos 741 CRC32 0x5acc32fe     Write_rows: table id 70 flags: STMT_END_F
224 ### INSERT INTO `luna`.`t1`
225 ### SET
226 ###   @1=3 /* INT meta=0 nullable=1 is_null=0 */
227 # at 741
228 #180627 18:27:30 server id 6  end_log_pos 786 CRC32 0xb4ed9f5a     Table_map: `luna`.`t1` mapped to number 70
229 # at 786
230 #180627 18:27:30 server id 6  end_log_pos 826 CRC32 0x819e8db3     Write_rows: table id 70 flags: STMT_END_F
231 ### INSERT INTO `luna`.`t1`
232 ### SET
233 ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
234 # at 826
235 #180627 18:27:58 server id 6  end_log_pos 857 CRC32 0x452e1f31     Xid = 41
236 COMMIT/*!*/;
237 # at 857
238 #180627 18:29:56 server id 6  end_log_pos 929 CRC32 0x5e68dff7     Query    thread_id=2    exec_time=0    error_code=0
239 SET TIMESTAMP=1530095396/*!*/;
240 BEGIN
241 /*!*/;
242 # at 929
243 #180627 18:29:56 server id 6  end_log_pos 974 CRC32 0xf4e4e2bf     Table_map: `luna`.`t1` mapped to number 70
244 # at 974
245 #180627 18:29:56 server id 6  end_log_pos 1020 CRC32 0x2d809738     Update_rows: table id 70 flags: STMT_END_F
246 ### UPDATE `luna`.`t1`
247 ### WHERE
248 ###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
249 ### SET
250 ###   @1=11 /* INT meta=0 nullable=1 is_null=0 */
251 # at 1020
252 #180627 18:30:25 server id 6  end_log_pos 1051 CRC32 0x08029580     Xid = 52
253 COMMIT/*!*/;
254 # at 1051
255 #180627 18:31:25 server id 6  end_log_pos 1123 CRC32 0xcf1b6980     Query    thread_id=2    exec_time=0  error_code=0
256 SET TIMESTAMP=1530095485/*!*/;
257 BEGIN
258 /*!*/;
259 # at 1123
260 #180627 18:31:25 server id 6  end_log_pos 1168 CRC32 0x7729069b     Table_map: `luna`.`t1` mapped to number 70
261 # at 1168
262 #180627 18:31:25 server id 6  end_log_pos 1208 CRC32 0x04cb5496     Delete_rows: table id 70 flags: STMT_END_F
263 ### DELETE FROM `luna`.`t1`
264 ### WHERE
265 ###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
266 # at 1208
267 #180627 18:31:36 server id 6  end_log_pos 1239 CRC32 0x27093f44     Xid = 56
268 COMMIT/*!*/;
269 # at 1239
270 #180627 18:33:45 server id 6  end_log_pos 1311 CRC32 0x8be80fc2     Query    thread_id=2    exec_time=0  error_code=0
271 SET TIMESTAMP=1530095625/*!*/;
272 BEGIN
273 /*!*/;
274 # at 1311
275 #180627 18:33:45 server id 6  end_log_pos 1356 CRC32 0x77578bf1     Table_map: `luna`.`t1` mapped to number 70
276 # at 1356
277 #180627 18:33:45 server id 6  end_log_pos 1402 CRC32 0x9c7bf8df     Update_rows: table id 70 flags: STMT_END_F
278 ### UPDATE `luna`.`t1`
279 ### WHERE
280 ###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
281 ### SET
282 ###   @1=44 /* INT meta=0 nullable=1 is_null=0 */
283 # at 1402
284 #180627 18:34:17 server id 6  end_log_pos 1433 CRC32 0x354e0150     Xid = 60
285 COMMIT/*!*/;
286 # at 1433
287 #180627 18:34:17 server id 6  end_log_pos 1548 CRC32 0x52b3dc50     Query    thread_id=2    exec_time=0  error_code=0
288 SET TIMESTAMP=1530095657/*!*/;
289 DROP TABLE `t1` /* generated by server */
290 /*!*/;
291 # at 1548
292 #180627 18:34:26 server id 6  end_log_pos 1633 CRC32 0x2e40af97     Query    thread_id=2    exec_time=0  error_code=0
293 SET TIMESTAMP=1530095666/*!*/;
294 drop database luna
295 /*!*/;
296 DELIMITER ;
297 # End of log file
298 ROLLBACK /* added by mysqlbinlog */;
299 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
300 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
301 
302 
303 
304 #截取
305 [root@db01-sa mysql]# mysqlbinlog --start-position=120 --stop-position=857 /data/mysql/mysql-bin.000034 >/tmp/luna2.sql
306 WARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag
307 set. This might be because the last statement was not fully written to the log, or because you are using a 
308 --stop-position or --stop-datetime that refers to an event in the middle of a statement. The event(s) from 
309 the partial statement have not been written to output.
310  
311 警告:打印事件的范围以行或表映射事件结束,没有设置了STMT_END_F标志。这可能是因为过去的声明没有完全写入日志,或者因为您使
312 用的是——停止位置,stop-datetime指一个事件在一份声明中。部分语句中的事件尚未写入输出。
313  
314 #产生以上报错就是因为截取的时候 一定要注意begin  begin是一个语句的开始 一定要从begin之前开始截断
315 之前的语句mysqlbinlog --start-position=120 --stop-position=974 /data/mysql/mysql-bin.000034 >/tmp/luna.sql 当中的974要改成857
316  
317  mysql> set sql_log_bin=0;
318 Query OK, 0 rows affected (0.00 sec)
319 
320 mysql> source /tmp/luna.sql;
321 Query OK, 0 rows affected (0.00 sec)
322 
323 Query OK, 0 rows affected, 1 warning (0.00 sec)
324 
325 Query OK, 0 rows affected (0.00 sec)
326 
327 Query OK, 0 rows affected (0.00 sec)
328 
329 Query OK, 0 rows affected (0.00 sec)
330 
331 Query OK, 0 rows affected (0.00 sec)
332 
333 Query OK, 0 rows affected (0.00 sec)
334 
335 Query OK, 0 rows affected (0.00 sec)
336 
337 Query OK, 0 rows affected (0.00 sec)
338 
339 Query OK, 0 rows affected (0.00 sec)
340 
341 Charset changed
342 Query OK, 0 rows affected (0.00 sec)
343 
344 Query OK, 0 rows affected (0.00 sec)
345 
346 Query OK, 0 rows affected (0.00 sec)
347 
348 Query OK, 0 rows affected (0.00 sec)
349 
350 Query OK, 1 row affected (0.00 sec)
351 
352 Database changed
353 Query OK, 0 rows affected (0.00 sec)
354 
355 Query OK, 0 rows affected (0.36 sec)
356 
357 Query OK, 0 rows affected (0.00 sec)
358 
359 Query OK, 0 rows affected (0.00 sec)
360 
361 Query OK, 0 rows affected (0.01 sec)
362 
363 Query OK, 0 rows affected (0.00 sec)
364 
365 Query OK, 0 rows affected (0.01 sec)
366 
367 Query OK, 0 rows affected (0.00 sec)
368 
369 Query OK, 0 rows affected (0.00 sec)
370 
371 Query OK, 0 rows affected (0.03 sec)
372 
373 Query OK, 0 rows affected (0.00 sec)
374 
375 Query OK, 0 rows affected (0.01 sec)
376 
377 Query OK, 0 rows affected (0.00 sec)
378 
379 Query OK, 0 rows affected (0.00 sec)
380 
381 Query OK, 0 rows affected (0.00 sec)
382 
383 Query OK, 0 rows affected (0.00 sec)
384 
385 Query OK, 0 rows affected (0.00 sec)
386 
387 mysql> show database;
388 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
389 mysql> show databases;
390 +--------------------+
391 | Database           |
392 +--------------------+
393 | information_schema |
394 | binlog             |
395 | luna               |
396 | mysql              |
397 | nod01              |
398 | oldboy             |
399 | performance_schema |
400 | test               |
401 | world              |
402 +--------------------+
403 9 rows in set (0.00 sec)
404 
405 mysql> use luna
406 Database changed
407 mysql> 
408 mysql> 
409 mysql> show table;
410 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
411 mysql> show tables;
412 +----------------+
413 | Tables_in_luna |
414 +----------------+
415 | t1             |
416 +----------------+
417 1 row in set (0.00 sec)
418 
419 mysql> select * from t1;
420 +------+
421 | id   |
422 +------+
423 |    1 |
424 |    2 |
425 |    3 |
426 |    4 |
427 +------+
428 4 rows in set (0.00 sec)
429 
430 mysql> show master status;
431 +------------------+----------+--------------+------------------+-------------------+
432 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
433 +------------------+----------+--------------+------------------+-------------------+
434 | mysql-bin.000034 |     1633 |              |                  |                   |
435 +------------------+----------+--------------+------------------+-------------------+
436 1 row in set (0.00 sec)
View Code

MySQL binlog的补充

Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。
mysql的binlog日志必须打开log-bin功能才能生存binlog日志
-rw-rw---- 1 mysql mysql   669 8月  10 21:29 mysql-bin.000001
-rw-rw---- 1 mysql mysql   126 8月  10 22:06 mysql-bin.000002
-rw-rw---- 1 mysql mysql 11799 8月  15 18:17 mysql-bin.000003
 
 

2、Mysqlbinlog解析工具

　　Mysqlbinlog功能是将Mysql的binlog日志转换成Mysql语句，默认情况下binlog日志是二进制文件，无法直接查看。
　　Mysqlbinlog参数
参数 描述
-d 指定库的binlog
-r 相当于重定向到指定文件
--start-position--stop-position 按照指定位置精确解析binlog日志（精确），如不接--stop-positiion则一直到binlog日志结尾
--start-datetime--stop-datetime 按照指定时间解析binlog日志（模糊，不准确），如不接--stop-datetime则一直到binlog日志结尾
备注：myslqlbinlog分库导出binlog，如使用-d参数，更新数据时必须使用use database。
例：解析ceshi数据库的binlog日志并写入my.sql文件
#mysqlbinlog -d ceshi mysql-bin.000003 -r my.sql
 
 
 
使用位置精确解析binlog日志
#mysqlbinlog mysql-bin.000003 --start-position=100  --stop-position=200 -r my.sql
 
 
 
3、MySQL binlog的三种工作模式
　　（1）Row level
　　日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
　　优点：能清楚的记录每一行数据修改的细节
　　缺点：数据量太大
　　（2）Statement level（默认）
　　每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
　　优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高新能
　　缺点：容易出现主从复制不一致
　　（3）Mixed（混合模式）
　　结合了Row level和Statement level的优点

4、MySQL企业binlog模式的选择

  1. 互联网公司使用MySQL的功能较少（不用存储过程、触发器、函数），选择默认的Statement level
  2. 用到MySQL的特殊功能（存储过程、触发器、函数）则选择Mixed模式
  3. 用到MySQL的特殊功能（存储过程、触发器、函数），又希望数据最大化一直则选择Row模式
5、设置MySQL binlog模式
　　查看MySQLbinlog模式
 
mysql>show global variables like "binlog%";
+-----------------------------------------+-----------+
| Variable_name                         | Value     |
+-----------------------------------------+-----------+
| binlog_cache_size                      | 1048576   |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                          | STATEMENT |       #系统默认为STATEMENT模式
| binlog_stmt_cache_size                 | 32768     |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)　
 
 
 
 
MySQL中设置binlog模式
mysql>set global binlog_format='ROW';　
 
配置文件中设置binlog模式
 
#vim my.cnf
[mysqld]
binlog_format='ROW'          #放在mysqld模块下面
user    = mysql
port    = 3306
socket  = /data/3306/mysql.sock
 
 
 
6、配置完成后需要重启mysql服务
Row模式下解析binlog日志
 
#mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000001
 
以上内容转自：https://www.cnblogs.com/xhyan/p/6530861.html
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/nodchen/p/9235632.html