java解析 mysql binlog

在进入正题之前，我们需要知道binlog的event的类型，先来看看自己binlog文件有哪些？

其中红色部分为event_type。

binlog event 的类型有很多，具体可以参见mysql官方文档：http://dev.mysql.com/doc/internals/en/event-meanings.html

（一）Open Replicator中相关的Event类与接口

Open Replicator是一个用Java编写的MySQL binlog分析程序。Open Replicator 首先连接到MySQL（就像一个普通的MySQL Slave一样），然后接收和分析binlog，最终将分析得出的binlog events以回调的方式通知应用。

所有的Event实现了BinlogEventV4接口。

BinlogEventV4的接口如下：

[java]  view plain  copy

1. /** 
2.  * +=====================================+ 
3.  * | event  | timestamp         0 : 4    | 
4.  * | header +----------------------------+ 
5.  * |        | type_code         4 : 1    | 
6.  * |        +----------------------------+ 
7.  * |        | server_id         5 : 4    | 
8.  * |        +----------------------------+ 
9.  * |        | event_length      9 : 4    | 
10.  * |        +----------------------------+ 
11.  * |        | next_position    13 : 4    | 
12.  * |        +----------------------------+ 
13.  * |        | flags            17 : 2    | 
14.  * +=====================================+ 
15.  * | event  | fixed part       19 : y    | 
16.  * | data   +----------------------------+ 
17.  * |        | variable part              | 
18.  * +=====================================+ 
19.  * @author Jingqi Xu 
20.  */  
21. public interface BinlogEventV4 {  
22.       
23.     BinlogEventV4Header getHeader();  
24. }  

（二）利用Open Replicator解析binlog

在这里首先申明本人的测试环境为：mysql 5.1.61 ,binlog的类型设置为Row,本次解析只考虑insert、update、delete三种事件类型。我们先将三种类型的时间包装为一个新的Event,如下所示：

[java]  view plain  copy

1. public class LogEvent implements Serializable{  
2.   
3.     /** 
4.      * 只针对delete、insert、update事件 
5.      */  
6.     private static final long serialVersionUID = 5503152746318421290L;  
7.       
8.     private String eventId = null;  
9.     private String databaseName = null;  
10.     private String tableName = null;  
11.     private String  eventType  = null;  
12.     private Long    timestamp  = null;  
13.     private Long timestampRecepite = null;  
14.     private String binlogName = null;  
15.     private Long position = null;  
16.     private Long nextPosition = null;  
17.     private Long serverId = null;  
18.     private Map<String, String> before =null;  
19.     private Map<String, String> after = null;  
20.       
21.     public LogEvent(){  
22.           
23.     }  
24.       
25.     public LogEvent(final QueryEvent qe,String databaseName,String tableName){  
26.         this.init(qe);  
27.         this.databaseName=databaseName;  
28.         this.tableName=tableName;     
29.     }  
30.       
31.     public LogEvent(final AbstractRowEvent re){  
32.         this.init(re);  
33.         TableMapEvent tableMapEvent =re.getTme();  
34.         this.databaseName=tableMapEvent.getDatabaseName().toString();  
35.         this.tableName=tableMapEvent.getTableName().toString();   
36.     }  
37.       
38.     private void init(final BinlogEventV4 be){  
39.         this.eventId=UUID.randomUUID().toString();  
40.         BinlogEventV4Header header = be.getHeader();  
41.         this.binlogName = header.getBinlogName();  
42.         this.position = header.getPosition();  
43.         this.nextPosition = header.getNextPosition();  
44.         this.timestamp = header.getTimestamp();  
45.         this.timestampRecepite = header.getTimestampOfReceipt();  
46.         this.serverId=header.getServerId();  
47.         this.eventType=MySqlEventTypeIdToString.getInstance().get(header.getEventType());  
48.     }  
49.       
50.       
51.   
52.     @Override  
53.     public String toString() {  
54.         StringBuilder builder = new StringBuilder();  
55.         builder.append("{ eventId:").append(eventId);  
56.         builder.append(",databaseName:").append(databaseName);  
57.         builder.append(",tableName:").append(tableName);  
58.         builder.append(",eventType:").append(eventType);  
59.         builder.append(",timestamp:").append(timestamp);  
60.         builder.append(",timestampRecepite:").append(timestampRecepite);  
61.         builder.append(",binlogName:").append(binlogName);  
62.         builder.append(",position:").append(position);  
63.         builder.append(",nextPosition:").append(nextPosition);  
64.         builder.append(",serverId:").append(serverId);  
65.         builder.append(",before:").append(before);  
66.         builder.append(",after:").append(after).append(" }");  
67.         return builder.toString();  
68.     }  
69.   
70.   
71.   
72.     public String getEventId() {  
73.         return eventId;  
74.     }  
75.   
76.     public void setEventId(String eventId) {  
77.         this.eventId = eventId;  
78.     }  
79.   
80.     public String getDatabaseName() {  
81.         return databaseName;  
82.     }  
83.   
84.     public void setDatabaseName(String databaseName) {  
85.         this.databaseName = databaseName;  
86.     }  
87.   
88.     public String getTableName() {  
89.         return tableName;  
90.     }  
91.   
92.     public void setTableName(String tableName) {  
93.         this.tableName = tableName;  
94.     }  
95.   
96.     public String getEventType() {  
97.         return eventType;  
98.     }  
99.   
100.     public void setEventType(String eventType) {  
101.         this.eventType = eventType;  
102.     }  
103.   
104.     public Long getTimestamp() {  
105.         return timestamp;  
106.     }  
107.   
108.     public void setTimestamp(Long timestamp) {  
109.         this.timestamp = timestamp;  
110.     }  
111.   
112.     public Long getTimestampRecepite() {  
113.         return timestampRecepite;  
114.     }  
115.   
116.     public void setTimestampRecepite(Long timestampRecepite) {  
117.         this.timestampRecepite = timestampRecepite;  
118.     }  
119.   
120.     public String getBinlogName() {  
121.         return binlogName;  
122.     }  
123.   
124.     public void setBinlogName(String binlogName) {  
125.         this.binlogName = binlogName;  
126.     }  
127.   
128.     public Long getPosition() {  
129.         return position;  
130.     }  
131.   
132.     public void setPosition(Long position) {  
133.         this.position = position;  
134.     }  
135.   
136.     public Long getNextPosition() {  
137.         return nextPosition;  
138.     }  
139.   
140.     public void setNextPosition(Long nextPosition) {  
141.         this.nextPosition = nextPosition;  
142.     }  
143.   
144.     public Long getServerId() {  
145.         return serverId;  
146.     }  
147.   
148.     public void setServerId(Long serverId) {  
149.         this.serverId = serverId;  
150.     }  
151.   
152.     public Map<String, String> getBefore() {  
153.         return before;  
154.     }  
155.   
156.     public void setBefore(Map<String, String> before) {  
157.         this.before = before;  
158.     }  
159.   
160.     public Map<String, String> getAfter() {  
161.         return after;  
162.     }  
163.   
164.     public void setAfter(Map<String, String> after) {  
165.         this.after = after;  
166.     }  

其中 before、after为一个map,表示变化前后所在行的所有数据（columnName：columnValue)!

好的，先上主程序：

[java]  view plain  copy

1. public class OpenReplicatorTest {  
2.     public static void main(String args[]) throws Exception {     
3.         final OpenReplicator or = new OpenReplicator();  
4.         or.setUser("root");  
5.         or.setPassword("root");  
6.         or.setHost("xx.xxx.xx.xx");  
7.         or.setPort(3306);  
8.         or.setServerId(23);  
9.         or.setBinlogPosition(106);  
10.         or.setBinlogFileName("mysql-bin.000001");  
11.           
12.         or.setBinlogEventListener(new NotificationListener());  
13.         or.start();  
14.         }  
15.     }  

设置监控器NotificationListener, NotificationListener需要实现BinlogEventListener接口：

[java]  view plain  copy

1. public class NotificationListener implements BinlogEventListener{  
2.       
3.     private static Logger logger = LoggerFactory.getLogger(NotificationListener.class);  
4.       
5.     private String host="xx.xx.xx.xx";  
6.     private Integer port=3306;  
7.     private String username="root";  
8.     private String password="root";  
9.       
10.     public void onEvents(BinlogEventV4 event) {  
11.         if(event==null){  
12.             logger.error("binlog event is null");  
13.             return;  
14.         }  
15.   
16.         if(event instanceof UpdateRowsEvent){  
17.             UpdateRowsEvent updateRowsEvent = (UpdateRowsEvent)event;  
18.             LogEvent logEvent = new LogEvent(updateRowsEvent);  
19.               
20.             List<Pair<Row>> rows = updateRowsEvent.getRows();  
21.             List<Column> cols_after = null;  
22.             List<Column> cols_before = null;  
23.             for(Pair<Row> p : rows){  
24.                  Row after = p.getAfter();  
25.                  Row before = p.getBefore();  
26.                  cols_after = after.getColumns();  
27.                  cols_before = before.getColumns();  
28.                  break;  
29.             }  
30.             logEvent.setBefore(getMap(cols_before, updateRowsEvent.getTme().getDatabaseName().toString(), updateRowsEvent.getTme().getTableName().toString()));  
31.             logEvent.setAfter(getMap(cols_after, updateRowsEvent.getTme().getDatabaseName().toString(), updateRowsEvent.getTme().getTableName().toString()));  
32.             logger.info("update event is:"+logEvent);  
33.         }else if(event instanceof DeleteRowsEvent){  
34.             DeleteRowsEvent deleteRowsEvent = (DeleteRowsEvent)event;  
35.             LogEvent logEvent = new LogEvent(deleteRowsEvent);  
36.             List<Row> rows = deleteRowsEvent.getRows();  
37.             List<Column> before = null;  
38.             for(Row row:rows){  
39.                 before = row.getColumns();  
40.                 break;  
41.             }  
42.             logEvent.setBefore(getMap(before, deleteRowsEvent.getTme().getDatabaseName().toString(), deleteRowsEvent.getTme().getTableName().toString()));  
43.             logger.info("delete event is:"+logEvent);  
44.               
45.         }else if(event instanceof WriteRowsEvent){  
46.             WriteRowsEvent wrtiteRowsEvent = (WriteRowsEvent)event;  
47.             LogEvent logEvent = new LogEvent(wrtiteRowsEvent);  
48.             List<Row> rows = wrtiteRowsEvent.getRows();  
49.             List<Column> before = null;  
50.             for(Row row:rows){  
51.                 before = row.getColumns();  
52.                 break;  
53.             }  
54.             logEvent.setAfter(getMap(before, wrtiteRowsEvent.getTme().getDatabaseName().toString(), wrtiteRowsEvent.getTme().getTableName().toString()));  
55.             logger.info("write event is:"+logEvent);  
56.               
57.         }     
58.     }  
59.       
60.     private Map<String, String> getMap(List<Column> cols,String databaseName,String tableName){  
61.         if(cols==null||cols.size()==0){  
62.             return null;  
63.         }  
64.         List<String> columnNames = new TableInfo(host,username,password, port).getColumns(databaseName, tableName);  
65.         if(columnNames==null){  
66.             return null;  
67.         }  
68.         if(columnNames.size()!=cols.size()){  
69.             logger.error("the size does not match...");  
70.             return null;  
71.         }  
72.         Map<String, String> map = new HashMap<String, String>();  
73.         for(int i=0;i<columnNames.size();i++){  
74.             if(cols.get(i).getValue()==null){  
75.                 map.put(columnNames.get(i).toString(),"");  
76.             }else{  
77.                 map.put(columnNames.get(i).toString(),cols.get(i).toString());  
78.             }  
79.               
80.         }  
81.         return map;  
82.     }  

由于 Open Replicator 提供的Event中不包含数据库表中所有字段column name的信息，DeleteRowsEvent、UpdateRowsEvent、WriteRowsEvent包含变化前后的字段column value信息，而我们需要将其组合成before与after，因此需要想办法获取column names:

[java]  view plain  copy

1. public class TableInfo {  
2.     private static Logger logger = LoggerFactory.getLogger(TableInfo.class);  
3.       
4.     /** 
5.      * key:databaseName+""+tableName 
6.      * value:columns name 
7.      */  
8.     private static Map<String, List<String>> columnsMap = new HashMap<String, List<String>>();  
9.     private String host;  
10.     private Integer port;  
11.     private String username;  
12.     private String password;  
13.       
14.     public TableInfo(String host,String username,String password,Integer port){  
15.         this.host=host;  
16.         this.username=username;  
17.         this.password=password;  
18.         this.port = port;  
19.         if(columnsMap==null||columnsMap.size()==0){  
20.             MysqlConnection.setConnection(this.host,this.port,this.username,this.password);  
21.             columnsMap = MysqlConnection.getColumns();  
22.         }  
23.     }  
24.       
25.     public Map<String, List<String>> getMap(){  
26.         return columnsMap;  
27.     }  
28.       
29.     public List<String> getColumns(String databaseName,String tableName){  
30.         if(StringUtils.isNullOrEmpty(databaseName)||StringUtils.isNullOrEmpty(tableName)){  
31.             return null;  
32.         }  
33.         String key = databaseName + "."+tableName;  
34.         List<String> list =null;  
35.         if(columnsMap.size()==0){  
36.             MysqlConnection.setConnection(this.host,this.port,this.username,this.password);  
37.             columnsMap = MysqlConnection.getColumns();  
38.             list = columnsMap.get(key);  
39.         }else{  
40.             list=columnsMap.get(key);  
41.             if(list==null||list.size()==0){  
42.                 MysqlConnection.setConnection(this.host,this.port,this.username,this.password);  
43.                 columnsMap = MysqlConnection.getColumns();  
44.                 list = columnsMap.get(key);  
45.             }  
46.               
47.         }  
48.         return list;  
49.     }  

MysqlConnection实现类如下：

[java]  view plain  copy

1. public class MysqlConnection {  
2.   
3.     private static Connection conn;  
4.   
5.     private static final Logger logger = LoggerFactory.getLogger(MysqlConnection.class);  
6.     private static String host;  
7.     private static Integer port;  
8.     private static String user;  
9.     private static String password;  
10.   
11.     public static void setConnection(String mySQLHost, Integer mySQLPort, String mySQLUser,  
12.             String mySQLPassword) {  
13.         try {  
14.             if (conn == null || conn.isClosed()) {  
15.                 Class.forName("com.mysql.jdbc.Driver");  
16.   
17.                 conn = DriverManager.getConnection("jdbc:mysql://" + mySQLHost + ":" + mySQLPort  
18.                         + "/", mySQLUser, mySQLPassword);  
19.                 logger.info("connected to mysql:{} : {}", mySQLHost, mySQLPort);  
20.                 host = mySQLHost;  
21.                 port = mySQLPort;  
22.                 user = mySQLUser;  
23.                 password = mySQLPassword;  
24.             }  
25.         } catch (Exception e) {  
26.             logger.error(e.getMessage(), e);  
27.         }  
28.     }  
29.   
30.     public static Connection getConnection() {  
31.         try {  
32.             if (conn == null || conn.isClosed()) {  
33.                 setConnection(host, port, user, password);  
34.             }  
35.         } catch (Exception e) {  
36.             logger.error(e.getMessage(), e);  
37.         }  
38.         return conn;  
39.     }  
40.   
41.     public static Map<String, List<String>> getColumns(){  
42.         Map<String, List<String>> cols = new HashMap<String, List<String>>();  
43.         Connection conn = getConnection();  
44.         try {  
45.             DatabaseMetaData metaData = conn.getMetaData();  
46.             ResultSet r = metaData.getCatalogs();  
47.             String tableType[] = { "TABLE" };  
48.             while (r.next()) {  
49.                 String databaseName = r.getString("TABLE_CAT");  
50.                 ResultSet result = metaData.getTables(databaseName, null, null, tableType);  
51.                 while (result.next()) {  
52.                     String tableName = result.getString("TABLE_NAME");  
53.                     String key = databaseName + "." + tableName;  
54.                     ResultSet colSet = metaData.getColumns(databaseName, null, tableName, null);  
55.                     cols.put(key, new ArrayList<String>());  
56.                     while (colSet.next()) {  
57.                         String column = colSet.getString("COLUMN_NAME");  
58.                         cols.get(key).add(column);  
59.                     }  
60.                 }  
61.             }  
62.   
63.         } catch (SQLException e) {  
64.             logger.error(e.getMessage(), e);  
65.             return null;  
66.         }  
67.         return cols;  
68.     }  
69. }  

辅助类，根据event id获取event type:

[java]  view plain  copy

1. public class MySqlEventTypeIdToString {  
2.     private static Map<Integer, String> idToString = new HashMap<Integer, String>();  
3.     private MySqlEventTypeIdToString() {  
4.         Init();  
5.     }  
6.     public static MySqlEventTypeIdToString getInstance() {  
7.         return m;  
8.     }  
9.     private void Init() {  
10.         idToString.put(0,"UNKNOWN_EVENT");  
11.         idToString.put(1,"START_EVENT_V3");  
12.         idToString.put(2,"QUERY_EVENT");  
13.         idToString.put(3,"STOP_EVENT");  
14.         idToString.put(4,"ROTATE_EVENT");  
15.         idToString.put(5,"INTVAR_EVENT");  
16.         idToString.put(6,"LOAD_EVENT");  
17.         idToString.put(7,"SLAVE_EVENT");  
18.         idToString.put(8,"CREATE_FILE_EVENT");  
19.         idToString.put(9,"APPEND_BLOCK_EVENT");  
20.         idToString.put(10,"EXEC_LOAD_EVENT");  
21.         idToString.put(11,"DELETE_FILE_EVENT");  
22.         idToString.put(12,"NEW_LOAD_EVENT");  
23.         idToString.put(13,"RAND_EVENT");  
24.         idToString.put(14,"USER_VAR_EVENT");  
25.         idToString.put(15,"FORMAT_DESCRIPTION_EVENT");  
26.         idToString.put(16,"XID_EVENT");  
27.         idToString.put(17,"BEGIN_LOAD_QUERY_EVENT");  
28.         idToString.put(18,"EXECUTE_LOAD_QUERY_EVENT");  
29.         idToString.put(19,"TABLE_MAP_EVENT");  
30.         idToString.put(20,"PRE_GA_WRITE_ROWS_EVENT");  
31.         idToString.put(21,"PRE_GA_UPDATE_ROWS_EVENT");  
32.         idToString.put(22,"PRE_GA_DELETE_ROWS_EVENT");  
33.         idToString.put(23,"WRITE_ROWS_EVENT");  
34.         idToString.put(24,"UPDATE_ROWS_EVENT");  
35.         idToString.put(25,"DELETE_ROWS_EVENT");  
36.         idToString.put(26,"INCIDENT_EVENT");  
37.         idToString.put(27,"HEARTBEAT_LOG_EVENT");  
38.         idToString.put(28,"IGNORABLE_LOG_EVENT");  
39.         idToString.put(29,"ROWS_QUERY_LOG_EVENT");  
40.         idToString.put(30,"WRITE_ROWS_EVENT_V2");  
41.         idToString.put(31,"UPDATE_ROWS_EVENT_V2");  
42.         idToString.put(32,"DELETE_ROWS_EVENT_V2");  
43.         idToString.put(33,"GTID_LOG_EVENT");  
44.         idToString.put(34,"ANONYMOUS_GTID_LOG_EVENT");  
45.         idToString.put(35,"PREVIOUS_GTIDS_LOG_EVENT");  
46.     }  
47.     public String get(Integer eventId) {  
48.         return idToString.get(eventId);  
49.     }  
50. }  

运行：

[plain]  view plain  copy

1. update event is: {  
2.     eventId: a7acc3d0-7721-4ffe-84d4-4c2b7db5423a,  
3.     databaseName: test,  
4.     tableName: task,  
5.     eventType: UPDATE_ROWS_EVENT,  
6.     timestamp: 1450753740000,  
7.     timestampRecepite: 1450887259271,  
8.     binlogName: mysql-bin.000001,  
9.     position: 248,  
10.     nextPosition: 358,  
11.     serverId: 23,  
12.     before: {  
13.         id=791,  
14.         user_name=123,  
15.         topology_path=,  
16.         update_time=2015-08-05 10:53:57.0,  
17.         status=1,  
18.         department=,  
19.         name=user01,  
20.         create_time=2015-12-21 19:30:36.0,  
21.         user_id=-1  
22.     },  
23.     after: {  
24.         id=791,  
25.         user_name=123,  
26.         topology_path=,  
27.         update_time=2015-08-05 10:53:57.0,  
28.         status=2,  
29.         department=,  
30.         name=user02,  
31.         create_time=2015-12-22 11:09:00.0,  
32.         user_id=-1  
33.     }  
34. }