mysql binlog日志提取方式

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