MYSQL8 根据IBD恢复表结构

获取打印json的脚本

#!/bin/bash

#folder = "/usr/local/DBdatas/mysql8/data/sjwgxsj"
#echo $folder
softfiles=$(ls "/usr/local/DBdatas/mysql8/data/ts")
for sfile in ${softfiles}
do
 echo "/usr/local/DBdatas/mysql8/bin/ibd2sdi /usr/local/DBdatas/mysql8/data/ts/${sfile}>>ts.json"
done

获取表名与列名字段

ibd2sdi ***.ibd |jq  '.[]?|.[]?|.dd_object?|({table:.name?},(.columns?|.[]?|{name:.name,type:.column_type_utf8}))' > ***.json

解析JSON的java代码

package com.test;

import com.alibaba.fastjson.JSON;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class Hello {
    public static void main(String[] args) throws IOException {
        String filePath = "H:\\ts.sql";
        List<String> list = new ArrayList<>();
        InputStreamReader Reader = new InputStreamReader(new FileInputStream(filePath), "UTF-8");
        //考虑到编码格式，new FileInputStream(myFile)文件字节输入流，以字节为单位对文件中的数据进行读取
        //new InputStreamReader(FileInputStream a, "编码类型")
        //将文件字节输入流转换为文件字符输入流并给定编码格式

        BufferedReader bufferedReader = new BufferedReader(Reader);
        //BufferedReader从字符输入流中读取文本，缓冲各个字符，从而实现字符、数组和行的高效读取。
        //通过BuffereReader包装实现高效读取

        String lineTxt = null;

        while ((lineTxt = bufferedReader.readLine()) != null) {
            //buffereReader.readLine()按行读取写成字符串
            if(!"{".equals(lineTxt)&&!"}".equals(lineTxt)){
                list.add(lineTxt.replace("\"","").replace(",",""));
            }

        }


        Reader.close();

        System.out.println(list.get(list.size()-1));
        StringBuffer tableBuf = new StringBuffer();
        List<String> createSql = new ArrayList<>();

        for (int i = 0; i < list.size(); i++) {

            String[] split = list.get(i).replace(" ","").replace("  ","").split(":");

            if(split[0].equals("table")){

                if(i !=0 ){

                    if(tableBuf.lastIndexOf(",")>=0){
                        String substring = tableBuf.substring(0, tableBuf.lastIndexOf(","));
                        tableBuf.setLength(0);
                        tableBuf.append(substring);

                    }
                    tableBuf.append(" ) ");
                    createSql.add(tableBuf.toString());
                    tableBuf.setLength(0);
                    tableBuf.append("create table "+split[1]+" ( ");
                }else{
                    tableBuf.append("create table "+split[1]+" ( ");
                }
            }else if(split[0].equals("name")){
                if(!split[1].equals("DB_ROW_ID")&&!split[1].equals("DB_TRX_ID")&&!split[1].equals("DB_ROLL_PTR")){
                    tableBuf.append("`"+split[1]+"` ");
                }
            }else if(split[0].equals("type")){
                try {
                    if(split.length>=2){
                        tableBuf.append(" "+split[1]+", ");
                    }

                } catch (Exception e) {
                    System.out.println(tableBuf);
                    throw new RuntimeException(e);
                }
            }

        }
        List<String> result = new ArrayList<>();
        for (int i = 0; i < createSql.size(); i++) {
            if(!createSql.get(i).contains("fts_")&& !createSql.get(i).contains("ts/")){
                result.add(createSql.get(i));
            }

        }
        for (int i = 0; i < result.size(); i++) {
            System.out.println(result.get(i));


        }
        System.out.println(result.size());
    }
}