mysql日志提取方式
show binlog events; mysql命令行内执行
mysqlbinlog 命令 Linxu命令行执行
#查看bin log日志数量
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 901 |
| mysql-bin.000002 | 150 |
| mysql-bin.000003 | 150 |
| mysql-bin.000004 | 150 |
| mysql-bin.000005 | 107 |
+------------------+-----------+
#查看binlog 开始位置查看
mysql> show binlog events;
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.32-log, Binlog ver: 4 |
| mysql-bin.000001 | 107 | Query | 1 | 177 | BEGIN |
| mysql-bin.000001 | 177 | Intvar | 1 | 205 | INSERT_ID=4 |
| mysql-bin.000001 | 205 | Query | 1 | 310 | use `wangdk`; insert into user(name) values('1234235') |
| mysql-bin.000001 | 310 | Xid | 1 | 337 | COMMIT /* xid=32 */ |
| mysql-bin.000001 | 337 | Query | 1 | 407 | BEGIN |
| mysql-bin.000001 | 407 | Intvar | 1 | 435 | INSERT_ID=5 |
| mysql-bin.000001 | 435 | Query | 1 | 540 | use `wangdk`; insert into user(name) values('1234235') |
| mysql-bin.000001 | 540 | Xid | 1 | 567 | COMMIT /* xid=33 */ |
| mysql-bin.000001 | 567 | Query | 1 | 637 | BEGIN |
| mysql-bin.000001 | 637 | Intvar | 1 | 665 | INSERT_ID=6 |
| mysql-bin.000001 | 665 | Query | 1 | 770 | use `wangdk`; insert into user(name) values('1234235') |
| mysql-bin.000001 | 770 | Xid | 1 | 797 | COMMIT /* xid=34 */ |
| mysql-bin.000001 | 797 | Query | 1 | 882 | drop database wangdk |
| mysql-bin.000001 | 882 | Stop | 1 | 901 | |
+------------------+-----+-------------+-----------+-------------+--------------------------------------------------------+
15 rows in set (0.00 sec)
#查看指定的binlog
mysql> show binlog events in 'mysql-bin.000004';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000004 | 4 | Format_desc | 1 | 107 | Server ver: 5.5.32-log, Binlog ver: 4 |
| mysql-bin.000004 | 107 | Rotate | 1 | 150 | mysql-bin.000005;pos=4 |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
#查看当前正在写入的binlog
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 107
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)
#按位置筛选日志
[root@166087 data]# mysqlbinlog --start-position="770" --stop-position="797" mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151102 4:55:20 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 151102 4:55:20 at startup
ROLLBACK/*!*/;
BINLOG '
CDM3Vg8BAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAIMzdWEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 770
#151102 4:56:51 server id 1 end_log_pos 797 Xid = 34
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#按照日期提取日志
[root@166087 data]# mysqlbinlog --start-datetime="2015-11-02 05:00:00" mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151102 4:55:20 server id 1 end_log_pos 107 Start: binlog v 4, server v 5.5.32-log created 151102 4:55:20 at startup
ROLLBACK/*!*/;
BINLOG '
CDM3Vg8BAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAIMzdWEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#按照指定的数据库提取日志,过滤掉注释
[root@166087 data]# mysqlbinlog --database=wangdk mysql-bin.000001 | grep -v "^#" | grep -v "^/"
DELIMITER /*!*/;
ROLLBACK/*!*/;
BINLOG '
CDM3Vg8BAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAIMzdWEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
SET TIMESTAMP=1446458210/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
SET INSERT_ID=4/*!*/;
use `wangdk`/*!*/;
SET TIMESTAMP=1446458210/*!*/;
insert into user(name) values('1234235')
COMMIT/*!*/;
SET TIMESTAMP=1446458211/*!*/;
BEGIN
SET INSERT_ID=5/*!*/;
SET TIMESTAMP=1446458211/*!*/;
insert into user(name) values('1234235')
COMMIT/*!*/;
SET TIMESTAMP=1446458211/*!*/;
BEGIN
SET INSERT_ID=6/*!*/;
SET TIMESTAMP=1446458211/*!*/;
insert into user(name) values('1234235')
COMMIT/*!*/;
SET TIMESTAMP=1446458279/*!*/;
drop database wangdk
DELIMITER ;
ROLLBACK /* added by mysqlbinlog */;
转载于:https://my.oschina.net/web256/blog/525013