mysql bin log 分析
时间:2015-10-10 11:37 来源:linux.it.net.cn 作者:IT
1、mysql现状
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 38735504 |
| mysql-bin.000011 | 38362054 |
| mysql-bin.000012 | 18725587 |
| mysql-bin.000013 | 802454 |
| mysql-bin.000014 | 29305 |
| mysql-bin.000015 | 5151 |
| mysql-bin.000016 | 41371 |
| mysql-bin.000017 | 13621 |
| mysql-bin.000018 | 218463 |
| mysql-bin.000019 | 13905 |
| mysql-bin.000020 | 244201 |
| mysql-bin.000021 | 23267 |
| mysql-bin.000022 | 18812698 |
| mysql-bin.000023 | 17488375 |
+------------------+-----------+
14 rows in set (0.00 sec)
2、插入一条数据后
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000010 | 38735504 |
| mysql-bin.000011 | 38362054 |
| mysql-bin.000012 | 18725587 |
| mysql-bin.000013 | 802454 |
| mysql-bin.000014 | 29305 |
| mysql-bin.000015 | 5151 |
| mysql-bin.000016 | 41371 |
| mysql-bin.000017 | 13621 |
| mysql-bin.000018 | 218463 |
| mysql-bin.000019 | 13905 |
| mysql-bin.000020 | 244201 |
| mysql-bin.000021 | 23267 |
| mysql-bin.000022 | 18812698 |
| mysql-bin.000023 | 17572942 |
+------------------+-----------+
14 rows in set (0.00 sec)
3、查看binlog event
mysql> show binlog events;
+------------------+----------+---------------+-----------+-------------+--------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+----------+---------------+-----------+-------------+--------------------------------------------------+
| mysql-bin.000010 | 4 | Format_desc | 11 | 112 | Server ver: 5.1.61-ndb-7.1.22-log, Binlog ver: 4 |
| mysql-bin.000010 | 112 | Query | 11 | 176 | BEGIN |
| mysql-bin.000010 | 176 | Table_map | 11 | 227 | table_id: 22 (dhafw.tab410_1) |
| mysql-bin.000010 | 227 | Table_map | 11 | 278 | table_id: 24 (dhafw.tab410_2) |
| mysql-bin.000010 | 278 | Table_map | 11 | 329 | table_id: 26 (dhafw.tab410_3) |
| mysql-bin.000010 | 329 | Table_map | 11 | 380 | table_id: 27 (dhafw.tab410_4) |
| mysql-bin.000010 | 380 | Table_map | 11 | 431 | table_id: 25 (dhafw.tab410_5) |
| mysql-bin.000010 | 431 | Table_map | 11 | 482 | table_id: 23 (dhafw.tab410_6) |
| mysql-bin.000010 | 482 | Table_map | 11 | 533 | table_id: 21 (dhafw.tab410_7) |
| mysql-bin.000010 | 533 | Table_map | 11 | 584 | table_id: 19 (dhafw.tab410_8) |
| mysql-bin.000010 | 584 | Table_map | 11 | 646 | table_id: 16 (mysql.ndb_apply_status) |
| mysql-bin.000010 | 646 | Write_rows_v1 | 11 | 705 | table_id: 16 |
| mysql-bin.000010 | 705 | Write_rows_v1 | 11 | 751 | table_id: 22 |
| mysql-bin.000010 | 751 | Write_rows_v1 | 11 | 797 | table_id: 24 |
| mysql-bin.000010 | 797 | Write_rows_v1 | 11 | 843 | table_id: 26 |
| mysql-bin.000010 | 843 | Write_rows_v1 | 11 | 889 | table_id: 27 |
| mysql-bin.000010 | 889 | Write_rows_v1 | 11 | 935 | table_id: 25 |
| mysql-bin.000010 | 935 | Write_rows_v1 | 11 | 981 | table_id: 23 |
| mysql-bin.000010 | 981 | Write_rows_v1 | 11 | 1027 | table_id: 21 |
| mysql-bin.000010 | 1027 | Write_rows_v1 | 11 | 1073 | table_id: 19 |
| mysql-bin.000010 | 1073 | Write_rows_v1 | 11 | 1119 | table_id: 22 |
| mysql-bin.000010 | 1119 | Write_rows_v1 | 11 | 1165 | table_id: 24 |
| mysql-bin.000010 | 1165 | Write_rows_v1 | 11 | 1211 | table_id: 26 |
| mysql-bin.000010 | 1211 | Write_rows_v1 | 11 | 1257 | table_id: 27 |
| mysql-bin.000010 | 1257 | Write_rows_v1 | 11 | 1303 | table_id: 25 |
| mysql-bin.000010 | 1303 | Write_rows_v1 | 11 | 1349 | table_id: 23 |
| mysql-bin.000010 | 1349 | Write_rows_v1 | 11 | 1395 | table_id: 21 |
| mysql-bin.000010 | 1395 | Write_rows_v1 | 11 | 1441 | table_id: 19 |
| mysql-bin.000010 | 1441 | Write_rows_v1 | 11 | 1487 | table_id: 22 |
| mysql-bin.000010 | 1487 | Write_rows_v1 | 11 | 1533 | table_id: 24 |
| mysql-bin.000010 | 1533 | Write_rows_v1 | 11 | 1579 | table_id: 26 |
| mysql-bin.000010 | 1579 | Write_rows_v1 | 11 | 1625 | table_id: 27 |
| mysql-bin.000010 | 1625 | Write_rows_v1 | 11 | 1671 | table_id: 25 |
| mysql-bin.000010 | 1671 | Write_rows_v1 | 11 | 1717 | table_id: 23 |
| mysql-bin.000010 | 1717 | Write_rows_v1 | 11 | 1763 | table_id: 21 |
| mysql-bin.000010 | 1763 | Write_rows_v1 | 11 | 1809 | table_id: 19 |
| mysql-bin.000010 | 1809 | Write_rows_v1 | 11 | 1855 | table_id: 22 |
| mysql-bin.000010 | 1855 | Write_rows_v1 | 11 | 1901 | table_id: 24 |
| mysql-bin.000010 | 1901 | Write_rows_v1 | 11 | 1947 | table_id: 26 |
| mysql-bin.000010 | 1947 | Write_rows_v1 | 11 | 1993 | table_id: 27 |
| mysql-bin.000010 | 1993 | Write_rows_v1 | 11 | 2039 | table_id: 25 |
| mysql-bin.000010 | 2039 | Write_rows_v1 | 11 | 2085 | table_id: 23 |
| mysql-bin.000010 | 2085 | Write_rows_v1 | 11 | 2131 | table_id: 21 |
| mysql-bin.000010 | 2131 | Write_rows_v1 | 11 | 2177 | table_id: 19 |
| mysql-bin.000010 | 2177 | Write_rows_v1 | 11 | 2223 | table_id: 22 |
| mysql-bin.000010 | 2223 | Write_rows_v1 | 11 | 2269 | table_id: 24 |
| mysql-bin.000010 | 2269 | Write_rows_v1 | 11 | 2315 | table_id: 26 |
| mysql-bin.000010 | 2315 | Write_rows_v1 | 11 | 2361 | table_id: 27 |
| mysql-bin.000010 | 2361 | Write_rows_v1 | 11 | 2407 | table_id: 25 |
| mysql-bin.000010 | 2407 | Write_rows_v1 | 11 | 2453 | table_id: 23 |
| mysql-bin.000010 | 2453 | Write_rows_v1 | 11 | 2499 | table_id: 21 |
| mysql-bin.000010 | 2499 | Write_rows_v1 | 11 | 2545 | table_id: 19 |
| mysql-bin.000010 | 2545 | Write_rows_v1 | 11 | 2591 | table_id: 22 |
| mysql-bin.000010 | 2591 | Write_rows_v1 | 11 | 2637 | table_id: 24 |
| mysql-bin.000010 | 2637 | Write_rows_v1 | 11 | 2683 | table_id: 26 |
| mysql-bin.000010 | 2683 | Write_rows_v1 | 11 | 2729 | table_id: 27 |
| mysql-bin.000010 | 2729 | Write_rows_v1 | 11 | 2775 | table_id: 25 |
| mysql-bin.000010 | 2775 | Write_rows_v1 | 11 | 2821 | table_id: 23 |
| mysql-bin.000010 | 2821 | Write_rows_v1 | 11 | 2867 | table_id: 21 |
| mysql-bin.000010 | 2867 | Write_rows_v1 | 11 | 2913 | table_id: 19 |
| mysql-bin.000010 | 2913 | Write_rows_v1 | 11 | 2959 | table_id: 22 |
| mysql-bin.000010 | 2959 | Write_rows_v1 | 11 | 3005 | table_id: 24 |
| mysql-bin.000010 | 3005 | Write_rows_v1 | 11 | 3051 | table_id: 26 |
| mysql-bin.000010 | 3051 | Write_rows_v1 | 11 | 3097 | table_id: 27 |
| mysql-bin.000010 | 3097 | Write_rows_v1 | 11 | 3143 | table_id: 25 |
| mysql-bin.000010 | 3143 | Write_rows_v1 | 11 | 3189 | table_id: 23 |
| mysql-bin.000010 | 3189 | Write_rows_v1 | 11 | 3235 | table_id: 21 |
| mysql-bin.000010 | 3235 | Write_rows_v1 | 11 | 3281 | table_id: 19 |
| mysql-bin.000010 | 3281 | Write_rows_v1 | 11 | 3327 | table_id: 22 |
| mysql-bin.000010 | 3327 | Write_rows_v1 | 11 | 3373 | table_id: 24 |
| mysql-bin.000010 | 3373 | Write_rows_v1 | 11 | 3419 | table_id: 26 |
| mysql-bin.000010 | 3419 | Write_rows_v1 | 11 | 3465 | table_id: 27 |
| mysql-bin.000010 | 3465 | Write_rows_v1 | 11 | 3511 | table_id: 25 |
4、用mysqlbinlog 工具来显示记录的二进制结果，然后导入到文本文件，为了以后的恢复
详细过程如下：
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog --start-position=4 --sto
p-position=106 mysqlbin-log.000001 > c:\\test1.txt
或者全部导出:
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog mysqlbin-log.000001 > c:\\test1.txt
5、导入结果到MYSQL中进行数据恢复。
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog --start-position=134 --stop-position=330 mysqlbin-log.000001 | mysql -uroot -p
或者
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog --start-position=134 --stop-position=330 mysqlbin-log.000001 >test1.txt
进入MYSQL导入
mysql> source c:\\test1.txt
还有一种办法是根据日期来恢复
C:\Program Files\MySQL\MySQL Server 5.0\bin >mysqlbinlog --start-datetime="2009-09-14 0:20:00" --stop-datetim="2009-09-15 01:25:00" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root
(责任编辑：IT)
1、mysql现状
2、插入一条数据后
mysql> show binary logs;
3、查看binlog event
mysql> show binlog events;
4、用mysqlbinlog 工具来显示记录的二进制结果，然后导入到文本文件，为了以后的恢复
详细过程如下： p-position=106 mysqlbin-log.000001 > c:\\test1.txt
或者全部导出: C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog mysqlbin-log.000001 > c:\\test1.txt
5、导入结果到MYSQL中进行数据恢复。
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog --start-position=134 --stop-position=330 mysqlbin-log.000001 | mysql -uroot -p
或者 C:\Program Files\MySQL\MySQL Server 5.0\bin>mysqlbinlog --start-position=134 --stop-position=330 mysqlbin-log.000001 >test1.txt 进入MYSQL导入 mysql> source c:\\test1.txt
还有一种办法是根据日期来恢复 C:\Program Files\MySQL\MySQL Server 5.0\bin >mysqlbinlog --start-datetime="2009-09-14 0:20:00" --stop-datetim="2009-09-15 01:25:00" /diskb/bin-logs/xxx_db-bin.000001 | mysql -u root (责任编辑：IT) |