一次基于binlog恢复数据的经验
现状
历史代码导致,id是有序的,接口设计是传id到后台,然后安全测试出现了越权,一个服务商的修改了其他服务商的数据。需要恢复数据。(同时也是一个经验教训)
处理方式
1.找数据库管理员想通过操作数据库工具根据binlog恢复,但是说binlog时间太久不在数据库管理范围内,被上传到OSS
2.自己拿到binlog执行解析
3.已知条件
----合并工单操作------- 1.UPDATE ticket_comment SET ticket_id=#{newTicketId} WHERE ticket_id IN (#{oldTicketIds}) oldTicketIds=删除工单id newTicketId=合并到指定工单 2.每一个old id记录一个日志 id 工单状态 动作 修改状态 old工单id 56739319 新建 已删除 TYPE_TICKET_UPDATE_STATUS 125980158 select t.`created_at`,tl.`created_at` , o.* from `ticket_log` tl join `ticket` t on t.id=tl.`ticket_id` join `operation` o on o.`ticket_log_id` =tl.id where tl.user_id=17861134 and type='TYPE_TICKET_UPDATE_STATUS' order by tl.`created_at` desc 3.每一个newTicketid记录日志 如果是多个合并到一个则是多条 id 工单编号(对应被合并工单) 动作 修改状态 new工单id 56737231 #2211380(0034058401) TYPE_TICKET_MERGE 125977965 select t.`created_at`,tl.`created_at` , o.* from `ticket_log` tl join `ticket` t on t.id=tl.`ticket_id` join `operation` o on o.`ticket_log_id` =tl.id where tl.user_id=17861134 and type='TYPE_TICKET_MERGE' order by tl.`created_at` desc 4.为oldTicket记录时间轴 id 创建日志 动作 状态删除 操作人呢 old工单id 62820621 2022-11-07 13:04:39 delete 合并删除 deleted 17861134 14132989 3378
4.拿到ossbinlog根据这批单子被修改了id来判断
用的解析binlog框架:https://github.com/osheroff/mysql-binlog-connector-java 也可以百度自己找
5.简单做个笔记整个贴了
package org.budo.binlog; import com.github.shyiko.mysql.binlog.BinaryLogFileReader; import com.github.shyiko.mysql.binlog.event.Event; import com.github.shyiko.mysql.binlog.event.EventData; import com.github.shyiko.mysql.binlog.event.EventHeader; import com.github.shyiko.mysql.binlog.event.EventType; import com.github.shyiko.mysql.binlog.event.TableMapEventData; import com.github.shyiko.mysql.binlog.event.UpdateRowsEventData; import com.github.shyiko.mysql.binlog.event.deserialization.EventDeserializer; import org.budo.mysql.binlog.event.EventMessage; import org.budo.mysql.binlog.event.EventMessage.ColumnValue; import org.budo.mysql.binlog.event.EventMessage.Type; import org.budo.mysql.binlog.event.parser.converter.StringConverter; import org.budo.mysql.binlog.metainf.TableMetaInf; import org.budo.mysql.binlog.metainf.TableMetaInf.ColumnMetaInf; import org.budo.mysql.binlog.metainf.TableMetaInfFactory; import org.budo.support.lang.util.StringUtil; import java.io.File; import java.io.Serializable; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.HashMap; import java.util.HashSet; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import java.util.TreeSet; /** * @author liqiang * @date 2022/11/8 */ public class BinlogFileWatcherTest implements Serializable { public static final TableMetaInfFactory tableMetaInfFactory = new TableMetaInfFactory(null); private static final long serialVersionUID = -8653330525900442434L; private static final StringConverter stringConverter = new StringConverter(); public static void main(String[] args) throws Exception { File binlogFile = new File("/Users/liqiang/Desktop/code_source_read/canal-canal-1.1.4/mysql-bin.002417"); EventDeserializer eventDeserializer = new EventDeserializer(); eventDeserializer.setCompatibilityMode( EventDeserializer.CompatibilityMode.DATE_AND_TIME_AS_LONG, EventDeserializer.CompatibilityMode.CHAR_AND_BINARY_AS_BYTE_ARRAY ); generatorTicketCommentMetaInfo(); generatorTicketLogMetaInfo(); BinaryLogFileReader reader = new BinaryLogFileReader(binlogFile, eventDeserializer); //List<String> tableNames = Arrays.asList("ticket_log"); List<String> tableNames = Arrays.asList("ticket_comment"); try { String operationTableName = null; TableMapEventData eventMetadata = null; for (Event event; (event = reader.readEvent()) != null; ) { EventHeader eventHeader = event.getHeader(); EventType eventType = eventHeader.getEventType(); if (!filterEventType(eventType)) { continue; } EventData eventData = event.getData(); if (eventData instanceof TableMapEventData) { operationTableName = null; eventMetadata = (TableMapEventData) eventData; if (!tableNames.contains(eventMetadata.getTable())) { continue; } //tableMetaInfFactory.putIfAbsent(eventMetadata.getTableId(), eventMetadata.getTable()); operationTableName = eventMetadata.getTable(); continue; } else if (StringUtil.isEmpty(operationTableName)) { continue; } if (eventData instanceof UpdateRowsEventData) { final UpdateRowsEventData data = event.getData(); final TableMetaInf tableMetaInf = tableMetaInfFactory.getTableMetaInf(data.getTableId()); for (Entry<Serializable[], Serializable[]> entry : data.getRows()) { final Serializable[] beforeData = entry.getKey(); final Serializable[] afterData = entry.getValue(); final EventMessage eventMessage = new EventMessage()// .setEvent(event)// .setTableMetaInf(tableMetaInf)// .setType(Type.UPDATE)// .setBeforeDataSupplier(() -> convertRowValue(beforeData, tableMetaInf.getColumnMetaInfs()))// .setAfterDataSupplier(() -> convertRowValue(afterData, tableMetaInf.getColumnMetaInfs())); generatorTicketCommentRestSql(eventMessage); //generatorTicketLogRestSql(eventMessage); } } } int count = 0; for (Integer providerId : providerSqlMap.keySet()) { count = count + providerSqlMap.get(providerId).size(); System.out.println("/**----------providerId=" + providerId + "---------------**\\"); for (String sql : providerSqlMap.get(providerId)) { System.out.println(sql); } } System.out.println("update总修改量=" + count); System.out.println("修复总工单量=" + totalTicketCount.size()); System.out.println("筛选条件工单量=" + ticketIds.size()); System.out.println("eventMessages=" + eventMessages.size()); } finally { reader.close(); } } static Map<Integer, List<String>> providerSqlMap = new HashMap<>(); static List<Integer> ticketIds = Arrays.asList(14132661); static Set<Integer> totalTicketCount = new HashSet<>(); static List<EventMessage> eventMessages = new ArrayList<>(); static Set<Integer> beforeTicket = new TreeSet<>(); static Set<Integer> afterTicket = new TreeSet<>(); public static void generatorTicketLogRestSql(EventMessage eventMessage) throws Exception { String beforeTicketId = "ticket_id".equals(eventMessage.getBeforeData().get(3).getName()) ? eventMessage.getBeforeData().get(3).getValue().toString() : null; String afterTicketId = "ticket_id".equals(eventMessage.getAfterData().get(3).getName()) ? eventMessage.getAfterData().get(3).getValue().toString() : null; if (StringUtil.isEmpty(beforeTicketId) || StringUtil.isEmpty(afterTicketId)) { System.err.println("错误"); throw new Exception(); } if (beforeTicketId.equals(afterTicketId)) { return; } eventMessages.add(eventMessage); beforeTicket.add(Integer.valueOf(beforeTicketId)); afterTicket.add(Integer.valueOf(afterTicketId)); if (ticketIds.contains(Integer.valueOf(afterTicketId))) { String sql = " UPDATE ticket_log t set t.ticket_id=%s where id=%s; "; Integer providerId = Integer.valueOf(eventMessage.getBeforeData().get(8).getValue().toString()); List<String> sqls = providerSqlMap.computeIfAbsent(providerId, k -> new ArrayList<>()); sqls.add(String.format(sql, beforeTicketId, eventMessage.getBeforeData().get(0).getValue())); totalTicketCount.add(Integer.valueOf(beforeTicketId)); } } public static void generatorTicketCommentRestSql(EventMessage eventMessage) throws Exception { String beforeTicketId = "ticket_id".equals(eventMessage.getBeforeData().get(7).getName()) ? eventMessage.getBeforeData().get(7).getValue().toString() : null; String afterTicketId = "ticket_id".equals(eventMessage.getAfterData().get(7).getName()) ? eventMessage.getAfterData().get(7).getValue().toString() : null; if (StringUtil.isEmpty(beforeTicketId) || StringUtil.isEmpty(afterTicketId)) { System.err.println("错误"); throw new Exception(); } if (beforeTicketId.equals(afterTicketId)) { return; } eventMessages.add(eventMessage); beforeTicket.add(Integer.valueOf(beforeTicketId)); afterTicket.add(Integer.valueOf(afterTicketId)); if (ticketIds.contains(Integer.valueOf(afterTicketId))) { String sql = " UPDATE ticket_comment t set t.ticket_id=%s where id=%s; "; Integer providerId = Integer.valueOf(eventMessage.getBeforeData().get(16).getValue().toString()); List<String> sqls = providerSqlMap.computeIfAbsent(providerId, k -> new ArrayList<>()); sqls.add(String.format(sql, beforeTicketId, eventMessage.getBeforeData().get(0).getValue())); totalTicketCount.add(Integer.valueOf(beforeTicketId)); } } public static void generatorTicketLogMetaInfo() { final TableMetaInf tableMetaInf = new TableMetaInf(); tableMetaInf.setTableId(807); tableMetaInf.setDatabase("eweits"); tableMetaInf.setTableName("ticket_log"); final List<ColumnMetaInf> columnMetaInfs = new ArrayList<>(); int columnIndex = 0; List<String> columnNames = new ArrayList<>(); columnNames.add("id"); columnNames.add("created_at"); columnNames.add("chat_id"); columnNames.add("ticket_id"); columnNames.add("ticket_comment_id"); columnNames.add("user_id"); columnNames.add("service_desk_id"); columnNames.add("have_terminal_equipment"); columnNames.add("provider_id"); List<String> types = new ArrayList<>(); types.add("int"); types.add("text"); types.add("timestamp"); types.add("bit"); types.add("int"); types.add("datetime"); types.add("int"); types.add("int"); types.add("int"); for (int i = 0; i < columnNames.size(); i++) { final String columnName = columnNames.get(i); final String columnType = types.get(i); columnMetaInfs.add(new ColumnMetaInf(columnIndex, columnName, columnType)); } tableMetaInf.setColumnMetaInfs(columnMetaInfs); tableMetaInfFactory.putIfAbsent(807L, tableMetaInf); } public static void generatorTicketCommentMetaInfo() { final TableMetaInf tableMetaInf = new TableMetaInf(); tableMetaInf.setTableId(73); tableMetaInf.setDatabase("eweits"); tableMetaInf.setTableName("ticket_comment"); final List<ColumnMetaInf> columnMetaInfs = new ArrayList<>(); int columnIndex = 0; List<String> columnNames = new ArrayList<>(); columnNames.add("id"); columnNames.add("content"); columnNames.add("created_at"); columnNames.add("open"); columnNames.add("type"); columnNames.add("updated_at"); columnNames.add("chat_id"); columnNames.add("ticket_id"); columnNames.add("user_id"); columnNames.add("rich_text_content_id"); columnNames.add("question_Id"); columnNames.add("address"); columnNames.add("axis_x"); columnNames.add("axis_y"); columnNames.add("equipment"); columnNames.add("u_id"); columnNames.add("provider_id"); columnNames.add("extra"); columnNames.add("call_record_uid"); List<String> types = new ArrayList<>(); types.add("int"); types.add("text"); types.add("timestamp"); types.add("bit"); types.add("int"); types.add("datetime"); types.add("int"); types.add("int"); types.add("int"); types.add("int"); types.add("int"); types.add("varchar"); types.add("double"); types.add("double"); types.add("varchar"); types.add("varchar"); types.add("int"); types.add("varchar"); types.add("varchar"); for (int i = 0; i < columnNames.size(); i++) { final String columnName = columnNames.get(i); final String columnType = types.get(i); columnMetaInfs.add(new ColumnMetaInf(columnIndex, columnName, columnType)); } tableMetaInf.setColumnMetaInfs(columnMetaInfs); tableMetaInfFactory.putIfAbsent(73L, tableMetaInf); } private static boolean filterEventType(EventType eventType) { return eventType == EventType.ROTATE || eventType == EventType.TABLE_MAP || EventType.isRowMutation(eventType); } public static List<ColumnValue> convertRowValue(Serializable[] values, List<ColumnMetaInf> columnMetaInfs) { if (values.length != columnMetaInfs.size()) { return Collections.emptyList(); // Does not correspond } final List<ColumnValue> result = new ArrayList<>(); for (int i = 0; i < values.length; i++) { // TODO: 先全部转换为字符串 final String columnName = columnMetaInfs.get(i).getColumnName(); final String columnValue = stringConverter.convert(values[i]); result.add(new ColumnValue(columnName, columnValue)); } return result; } }
6.执行结果
7.尝试先恢复几个单子没有问题整个交给运维执行
8.整个都是基于id恢复 所以很安全
整体思路
1.先拿到被异常修改的数据id
2.再拿到那段时间的binlog
3.解析binlog判断修改和修改后,当发现修改前和修改后ticket Id发生了变更,基于修改前修改后的数据反向生成update sql
注意事项
generatorTicketCommentMetaInfo、generatorTicketLogMetaInfo
以上2个方法是手动生成元数据,binlog里面只有table_id和column index
查询column信息sql
select * from information_schema.columns s where TABLE_NAME='ticket_log'
根据table_id查询 table
select * from information_schema.INNODB_SYS_TABLES t where t.`TABLE_ID` =3;
