自写BINLOG2SQL方法
接上篇 Mysql回退方法（binlog数据回退的具体方法） 下面是根据binlog文件格式，将其转译成sql文件（支持误删、误更新操作），如有需求可以直接修改readFileToSQL 方法，在commit 里面自定义转译的语句。如果代码有写不好，不合理的地方，请多多指教。
package main;
import org.apache.commons.lang3.StringUtils;
import java.io.*;
import java.lang.reflect.Parameter;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author 爱喝姜撞奶
*/
public class BinLog2SqlMain {
public static final int WRITE_READ_UTF_MAX_LENGTH = 65535;
public enum SQL_PROGRESS {
/**
* 开始找
*/
BEGIN,
/**
* 找到操作关键字
*/
OPERATION,
/**
* 找到条件关键字
*/
CONDITION,
/**
* 结束
*/
COMMIT
}
public enum FORMAT_SQL_TYPE {
/**
* 根据主键更新全部
*/
updateAll,
/**
* 新增全部
*/
insertAll
}
public static final String PREFIX = "###";
public enum FORMAT_SQL_CONSTRAINT {
DELETE,
UPDATE,
WHERE,
SET,
INSERT,
VALUES
}
public static class init {
public static final String DATABASE = "DATABASE_NAME";
public static final String MYSQL_URL = "172.0.0.1:3306";
public static final String USER = "root";
public static final String PASSWORD = "PASSWORD";
public static final String TABLE_NAME = "TABLE_NAME";
//填写 "updateAll" 则根据主键更新全部，"insertAll" 则新增全部 ,否则自写要更新的语句
public static FORMAT_SQL_TYPE FORMAT_SQL = FORMAT_SQL_TYPE.insertAll;
public static final String BINLOG_FILE_PATH = "E://worklog//bin.000917.sql";
//POS点位置限制，可赋值
public static final String BINLOG_POS = "";
public static final String TARGET_FILE_PATH = "E://worklog//flushFormat.txt";
public static void printInit() {
checkParams();
System.out.println("======================================= 初始化数据 ==================================");
System.out.println(" 数据库名： " + init.DATABASE);
System.out.println(" 表名： " + init.TABLE_NAME);
System.out.println(" MYSQL主机端口： " + init.MYSQL_URL);
System.out.println(" 用户名： " + init.USER);
System.out.println(" 密码： " + init.PASSWORD);
System.out.println(" BINLOG文件路径： " + init.BINLOG_FILE_PATH);
System.out.println(" 目标文件路径： " + init.TARGET_FILE_PATH);
if (FORMAT_SQL_TYPE.updateAll.toString().equals(init.FORMAT_SQL.toString())) {
System.out.println(" 目的：将Binlog文件内容转换成 update 语句");
System.out.println(" 转化的SQL语句模板：UPDATE [table] SET ([Field =value,....]) WHERE id ='' ");
} else if (FORMAT_SQL_TYPE.insertAll.toString().equals(init.FORMAT_SQL.toString())) {
System.out.println(" 目的：将Binlog文件内容转换成 insert 语句");
System.out.println(" 转化的SQL语句模板：INSERT INTO [table] ([Field,..]) VALUES([value,....]) ");
} else {
System.out.println(" 转化的SQL语句模板： " + init.FORMAT_SQL);
}
}
}
public static void main(String[] args) throws Exception {
System.out.println("============================================ 主程序启动 =====================================");
init.printInit();
writeFilePath();
System.exit(0);
}
/**
* 获取对应的表结构
* 使用jdbc链接，DESCRIBE 进行解析表
*
* @return Map<String, Object> Key：主键 Field：字段列表
* @throws Exception jdbc相关操作引起
*/
private static Map<String, Object> getTableStructure() throws Exception {
Map<String, Object> resultMap = new HashMap<>();
List<String> result = new ArrayList<>();
Connection connection = null;
try {
String formatURL = "jdbc:mysql://%s/%s?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";
String url = String.format(formatURL, init.MYSQL_URL, init.DATABASE);
String driver = "com.mysql.cj.jdbc.Driver";
Class.forName(driver);
//链接数据库
connection = DriverManager.getConnection(url, init.USER, init.PASSWORD);
} catch (ClassNotFoundException e) {
System.out.println("连接数据库失败：" + e);
} catch (SQLException e) {
e.printStackTrace();
}
// 根据查询获取列表签
Statement st = connection.createStatement();
String sql = "DESCRIBE %s;";
sql = String.format(sql, init.TABLE_NAME);
ResultSet rset = st.executeQuery(sql);
int i = 0;
try {
while (rset.next()) {
//只需要选一个主键就OK
if ("PRI".equals(rset.getString("Key")) && i < 1) {
resultMap.put("Key", rset.getString("Field"));
}
result.add(rset.getString("Field"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
rset.close();
resultMap.put("Field", result);
System.out.println("===================================== getTableStructure 成功=============================");
System.out.println(" 数据库(表)：" + init.DATABASE + '(' + init.TABLE_NAME + ')');
System.out.println(" 主键：" + resultMap.get("Key"));
System.out.println(" 字段: " + resultMap.get("Field"));
}
return resultMap;
}
private static void writeFilePath() throws Exception {
String readPath = init.BINLOG_FILE_PATH;
String writePath = init.TARGET_FILE_PATH;
writeSQLToFile(writePath, readFileToSQL(readPath));
}
/**
* 读取mysqlbinlog工具解析后的sql文件
* 注意：读取文件使用BufferedReader的readLine逐行读取处理
*
* @param path 读取文件的路径(绝对路径)
* @return StringBuilder 拼接好的SQL
* @throws IOException 读取文件相关的操作引起
*/
private static StringBuilder readFileToSQL(String path) throws Exception {
Map<String, Object> tableStructureMap = getTableStructure();
List<String> fieldList = (List<String>) tableStructureMap.get("Field");
final String PRIMARY_KEY = tableStructureMap.get("Key").toString();
Map<String, String> fieldMap = new LinkedHashMap<>();
StringBuilder sql = new StringBuilder();
File file = new File(path);
//固定提取的字符串
String formatSQL = init.FORMAT_SQL.toString();
//修改前的值
FileInputStream fileInputStream = new FileInputStream(file);
BufferedReader reader = new BufferedReader(new InputStreamReader(fileInputStream, "UTF-8"));
SQL_PROGRESS sqlProgress = SQL_PROGRESS.BEGIN;
//逐行读取
int i = 0;
boolean enablePos = false;
int a=0;
while (true) {
String line = reader.readLine();
if (Objects.isNull(line)) {
break;
}
String str = StringUtils.EMPTY;
//如果没有写POS点，则根据表名以及要求全部转译
//如果有POS点，则先寻找到POS点在进行下一步
if (StringUtils.isNotEmpty(init.BINLOG_POS)&&!enablePos) {
if (line.startsWith("# at")) {
str = line.substring(4);
str = str.replace(StringUtils.SPACE, "");
if (str.equals(init.BINLOG_POS)) {
enablePos = true;
a=4;
}
str = StringUtils.EMPTY;
}
continue;
}
if (a>0){
System.out.println(line);
a--;
}
if (enablePos&&line.startsWith("COMMIT/*!*/")){
break;
}
switch (sqlProgress) {
case BEGIN: {
//处理 ### databaseName.tableName
if (line.startsWith(PREFIX)) {
line = line.substring(3);
String rex = "`(.)*`";
Pattern pattern = Pattern.compile(rex);
Matcher matcher = pattern.matcher(line);
try {
matcher.find();
str = matcher.group();
str = str.substring(1, str.length() - 1);
} catch (Exception e) {
break;
}
}
if (StringUtils.isEmpty(str)) {
break;
}
//判断是否开头
boolean isDelete = line.replace(StringUtils.SPACE, StringUtils.EMPTY).startsWith(FORMAT_SQL_CONSTRAINT.DELETE.toString());
boolean isUpdate = line.replace(StringUtils.SPACE, StringUtils.EMPTY).startsWith(FORMAT_SQL_CONSTRAINT.UPDATE.toString());
if (isDelete || isUpdate) {
if (isDatabaseAndTableName(str)) {
sqlProgress = SQL_PROGRESS.OPERATION;
}
}
}
break;
case OPERATION: {
if (line.startsWith(PREFIX) && line.contains(FORMAT_SQL_CONSTRAINT.WHERE.toString())) {
sqlProgress = SQL_PROGRESS.CONDITION;
} else {
sqlProgress = SQL_PROGRESS.BEGIN;
}
}
break;
case CONDITION: {
int fieldIndex = -1;
String fieldValue = new String();
if (line.startsWith(PREFIX)) {
String rex = "@+?([1-9]+?[0-9]?)=";
Pattern pattern = Pattern.compile(rex);
Matcher matcher = pattern.matcher(line);
if (matcher.find()) {
str = matcher.group();
str = str.substring(1, str.length() - 1);
fieldIndex = Integer.valueOf(str) - 1;
fieldValue = line.substring(line.indexOf("=") + 1);
}
}
if (fieldIndex > -1) {
fieldMap.put(fieldList.get(fieldIndex), fieldValue);
} else {
//初始化
sqlProgress = SQL_PROGRESS.BEGIN;
fieldMap = new LinkedHashMap<>();
break;
}
if (fieldIndex == fieldList.size() - 1) {
sqlProgress = SQL_PROGRESS.COMMIT;
} else {
break;
}
}
//保留扩展
case COMMIT: {
//开始转译sql
String singleSql = null;
//更新
if (FORMAT_SQL_TYPE.updateAll.toString().equals(init.FORMAT_SQL.toString())) {
singleSql = FORMAT_SQL_CONSTRAINT.UPDATE.toString() + StringUtils.SPACE;
singleSql += "`" + init.DATABASE + "`" + ".`" + init.TABLE_NAME + "`" + StringUtils.SPACE;
singleSql += FORMAT_SQL_CONSTRAINT.SET.toString() + StringUtils.SPACE;
for (String key : fieldMap.keySet()) {
singleSql += "`" + key + "`=" + fieldMap.get(key) + ",";
}
singleSql = singleSql.substring(0, singleSql.length() - 1);
singleSql += StringUtils.SPACE + FORMAT_SQL_CONSTRAINT.WHERE + StringUtils.SPACE;
singleSql += PRIMARY_KEY + "=" + fieldMap.get(PRIMARY_KEY) + ";\r\n";
}//新增 INSERT INTO [table] ([Field,..]) VALUES([value,....])
else if (FORMAT_SQL_TYPE.insertAll.toString().equals(init.FORMAT_SQL.toString())) {
singleSql = FORMAT_SQL_CONSTRAINT.INSERT.toString() + StringUtils.SPACE + "INTO" + StringUtils.SPACE;
singleSql += "`" + init.DATABASE + "`" + ".`" + init.TABLE_NAME + "`" + StringUtils.SPACE;
singleSql += "(";
for (String key : fieldMap.keySet()) {
singleSql += "`" + key + "`" + ",";
}
singleSql = singleSql.substring(0, singleSql.length() - 1);
singleSql += ")" + StringUtils.SPACE + FORMAT_SQL_CONSTRAINT.VALUES + StringUtils.SPACE;
singleSql += "(";
for (String key : fieldMap.keySet()) {
singleSql += fieldMap.get(key) + ",";
}
singleSql = singleSql.substring(0, singleSql.length() - 1);
singleSql += ")" + ";\r\n";
} else {
}
if (StringUtils.isNotEmpty(singleSql)) {
sql.append(singleSql);
i++;
}
sqlProgress = SQL_PROGRESS.BEGIN;
fieldMap = new LinkedHashMap<>();
}
break;
default: {
throw new RuntimeException("SQL转译进程状态异常");
}
}
}
reader.close();
System.out.println("==================================== readFileToSQL 成功 ====================================");
System.out.println("====================================== 转译的sql语句总数：" + i + "==================================");
return sql;
}
/**
* 判断数据库名字与表名是否匹配
*
* @param str 读取的行数据
*/
private static Boolean isDatabaseAndTableName(String str) {
Boolean result = false;
List<String> names = Arrays.asList(str.split("`.`"));
try {
if (names != null) {
if (init.DATABASE.equals(names.get(0)) && init.TABLE_NAME.equals(names.get(1))) {
result = true;
}
}
} catch (Exception e) {
result = false;
}
return result;
}
/**
* 将SQL字符串写到某个文件上
*
* @param path 写入到的文件路径(绝对路径)
* @param sql 拼接好的SQL
* @throws IOException 写入文件操作引起
*/
public static void writeSQLToFile(String path, StringBuilder sql) throws IOException, InterruptedException {
if (StringUtils.isEmpty(sql)) {
System.out.println("没有可写入的SQL语句");
return;
}
try {
FileOutputStream file = new FileOutputStream(new File(path));
BufferedWriter bufferedWriter = new BufferedWriter(new OutputStreamWriter(file, "UTF-8"));
// bufferedWriter.write(sql.toString());
//防止超大数据
write(bufferedWriter, sql);
//刷新缓存后在关闭，防止还有数据在缓存里
bufferedWriter.flush();
bufferedWriter.close();
file.close();
} catch (IOException e) {
System.out.println("写入SQL失败：" + e);
}
System.out.println("======================================== writeSQLToFile 成功 ================================");
}
/**
* 将SQL字符串写到某个文件上
*
* @param sql 拼接好的SQL
* @throws IOException 写入文件操作引起
*/
public static void write(BufferedWriter file, StringBuilder sql) throws IOException {
if (sql.length() > WRITE_READ_UTF_MAX_LENGTH) {
for (int i = 1; i < sql.length() / WRITE_READ_UTF_MAX_LENGTH + 2; i++) {
file.write(sql.substring(WRITE_READ_UTF_MAX_LENGTH * (i - 1), WRITE_READ_UTF_MAX_LENGTH * i < sql.length() ? WRITE_READ_UTF_MAX_LENGTH * i : sql.length()));
}
} else {
//长度在0-65535默认写出
file.write(sql.toString());
}
}
public static void checkParams() {
if ("".equals(init.FORMAT_SQL)) {
throw new RuntimeException("FORMAT_SQL 不能为空");
}
if ("".equals(init.TARGET_FILE_PATH)) {
throw new RuntimeException("TARGET_FILE_PATH 不能为空");
}
if ("".equals(init.PASSWORD)) {
throw new RuntimeException("PASSWORD 不能为空");
}
if ("".equals(init.USER)) {
throw new RuntimeException("USER 不能为空");
}
if ("".equals(init.MYSQL_URL)) {
throw new RuntimeException("MYSQL_URL 不能为空");
}
if ("".equals(init.TABLE_NAME)) {
throw new RuntimeException("TABLE_NAME 不能为空");
}
if ("".equals(init.DATABASE)) {
throw new RuntimeException("不能为空");
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
