当前位置：首页 > news >正文

MySQL根据.idb数据恢复脚本，做成了EXE可执行文件

news 2025/9/13 1:09:51

文章目录

1.代码
2.Main方法打包
3.Jar包打成exe可执行文件
4.使用
- （1.）准备一个表结构一样得数据库
- （2.）打开软件
- （3.）输入路径
5.恢复成功

本文档只是为了留档方便以后工作运维，或者给同事分享文档内容比较简陋命令也不是特别全，不适合小白观看，如有不懂可以私信，上班期间都是在得

前文连接=====》mysql8 使用idb文件实现数据备份

需求，昨天写个数据恢复呗同事嫌麻烦，让我写个脚本一键恢复，一开始想着存储过程，但是存储过程不能移动文件，索性写个可视化界面吧。
下面直接cv就行了

1.代码

package com.joxp.mysql;import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.IOException;
import java.nio.file.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;public class MySQLConnectionApp {private static JFrame frame; // 主窗口private static JTextField userField; // 用户名输入框private static JPasswordField passwordField; // 密码输入框private static JTextField dbNameField; // 数据库名称输入框private static String selectedFolder1; // 第一个选择的文件夹路径private static String selectedFolder2; // 第二个选择的文件夹路径private static JLabel folderPathLabel1; // 显示文件夹路径的标签1private static JLabel folderPathLabel2; // 显示文件夹路径的标签2public static void main(String[] args) {SwingUtilities.invokeLater(MySQLConnectionApp::createAndShowGUI);}private static void createAndShowGUI() {frame = new JFrame("MySQL 连接器");frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);frame.setSize(400, 300);frame.setLayout(new GridLayout(5, 2));JLabel userLabel = new JLabel("用户名:");userField = new JTextField();JLabel passwordLabel = new JLabel("密码:");passwordField = new JPasswordField();JLabel dbNameLabel = new JLabel("数据库名:");dbNameField = new JTextField();// 创建右下角的标签JLabel footerLabel = new JLabel("By:XHao");footerLabel.setHorizontalAlignment(SwingConstants.LEFT); // 左对齐JButton connectButton = new JButton("连接");connectButton.addActionListener(new ConnectAction());JLabel messageLabel = new JLabel("", SwingConstants.CENTER);frame.add(userLabel);frame.add(userField);frame.add(passwordLabel);frame.add(passwordField);frame.add(dbNameLabel);frame.add(dbNameField);frame.add(connectButton);frame.add(messageLabel);frame.add(footerLabel); // 添加 footerLabel 到最后frame.setVisible(true);}private static void updateUIAfterConnection() {frame.getContentPane().removeAll();frame.setLayout(new GridLayout(5, 1)); // 新布局为5行1列frame.setSize(700, 350); // 根据需要调整宽度和高度JButton selectFolderButton1 = new JButton("选择备份文件夹");JButton selectFolderButton2 = new JButton("选择恢复文件夹");JButton queryButton = new JButton("查询数据路径");JButton recoverButton = new JButton("恢复数据");JLabel queryResultLabel = new JLabel(""); // 用于显示查询结果// 固定按钮大小Dimension buttonSize = new Dimension(80, 30);selectFolderButton1.setPreferredSize(buttonSize);selectFolderButton2.setPreferredSize(buttonSize);queryButton.setPreferredSize(buttonSize);recoverButton.setPreferredSize(buttonSize);folderPathLabel1 = new JLabel("未选择文件夹");folderPathLabel1.setSize(80, 30);folderPathLabel2 = new JLabel("未选择文件夹");folderPathLabel2.setSize(80, 30);selectFolderButton1.addActionListener(e -> {selectedFolder1 = openFolderChooser();folderPathLabel1.setText(selectedFolder1 != null ? selectedFolder1 : "未选择文件夹");});// 创建右下角的标签JLabel footerLabel = new JLabel("By:XHao");JLabel aaa = new JLabel("");footerLabel.setHorizontalAlignment(SwingConstants.LEFT); // 左对齐aaa.setHorizontalAlignment(SwingConstants.LEFT); // 左对齐selectFolderButton2.addActionListener(e -> {selectedFolder2 = openFolderChooser();folderPathLabel2.setText(selectedFolder2 != null ? selectedFolder2 : "未选择文件夹");});queryButton.addActionListener(e -> {String result = executeQuery(); // 假设 executeQuery() 返回查询结果queryResultLabel.setText(result); // 显示在下方标签});recoverButton.addActionListener(e -> executeRecovery());// 将按钮和标签添加到框架中frame.add(selectFolderButton1);frame.add(folderPathLabel1);frame.add(selectFolderButton2);frame.add(folderPathLabel2);frame.add(queryButton);frame.add(recoverButton);frame.add(queryResultLabel);frame.add(aaa, BorderLayout.SOUTH);frame.add(footerLabel, BorderLayout.SOUTH); // 将footerLabel放在底部frame.revalidate();frame.repaint();}private static String openFolderChooser() {JFileChooser folderChooser = new JFileChooser();folderChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);int returnValue = folderChooser.showOpenDialog(frame);if (returnValue == JFileChooser.APPROVE_OPTION) {File selectedFolder = folderChooser.getSelectedFile();return selectedFolder.getAbsolutePath();}return null;}private static String executeQuery() {String query = "SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@@datadir, '/', -1), '/', 1) as aa;";try (Connection conn = getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(query)) {StringBuilder result = new StringBuilder();while (rs.next()) {result.append(rs.getString("aa")).append("\n");}if (result.length() == 0) {result.append("没有找到任何结果。");}return result.toString();} catch (SQLException e) {showErrorDialog("查询失败: " + e.getMessage());}return null;}private static void executeRecovery() {// 第一步，执行第一个存储过程try (Connection conn = getConnection();Statement stmt = conn.createStatement()) {String dropProcedureSQL = "DROP PROCEDURE IF EXISTS " + "your_first_stored_procedure" + ";";stmt.execute(dropProcedureSQL);// 创建存储过程String abc = "CREATE DEFINER=`root`@`%` PROCEDURE `your_first_stored_procedure`(IN dbName VARCHAR(255))\n" +"BEGIN\n" +"    DECLARE done INT DEFAULT FALSE;\n" +"    DECLARE tableName VARCHAR(255);\n" +"\n" +"    -- 定义游标\n" +"    DECLARE cur CURSOR FOR \n" +"        SELECT TABLE_NAME \n" +"        FROM information_schema.TABLES \n" +"        WHERE TABLE_SCHEMA = dbName;\n" +"\n" +"    -- 错误处理\n" +"    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION\n" +"        BEGIN\n" +"            -- 错误发生时不做任何处理，继续执行\n" +"        END;\n" +"\n" +"    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;\n" +"\n" +"    -- 第一个循环：遍历所有表并执行 DISCARD TABLESPACE\n" +"    OPEN cur;\n" +"\n" +"    read_loop_1: LOOP\n" +"        FETCH cur INTO tableName;\n" +"        IF done THEN\n" +"            LEAVE read_loop_1;\n" +"        END IF;\n" +"\n" +"        -- 执行 DISCARD TABLESPACE\n" +"        SET @discard_sql = CONCAT('ALTER TABLE ', dbName, '.', tableName, ' DISCARD TABLESPACE;');\n" +"        PREPARE stmt FROM @discard_sql;\n" +"        EXECUTE stmt;\n" +"        DEALLOCATE PREPARE stmt;\n" +"\n" +"        -- 在此处可以执行其他操作\n" +"        -- 例如：INSERT INTO logging_table (message) VALUES (CONCAT('Discarded tablespace for ', tableName));\n" +"    END LOOP;\n" +"\n" +"    -- 关闭游标\n" +"    CLOSE cur;\n" +"END;";// 执行创建存储过程stmt.execute(abc);stmt.execute("CALL your_first_stored_procedure('" + dbNameField.getText() + "')");stmt.execute(dropProcedureSQL);// 第二步，复制文件if (selectedFolder1 != null && selectedFolder2 != null) {copyFiles(selectedFolder1, selectedFolder2);} else {showErrorDialog("请确保选择了两个文件夹。");return;}String aaaa = "DROP PROCEDURE IF EXISTS " + "your_second_stored_procedure" + ";";stmt.execute(aaaa);// 创建存储过程String aa = " CREATE DEFINER=`root`@`%` PROCEDURE `your_second_stored_procedure`(IN dbName VARCHAR(255))\n" +"BEGIN\n" +"    DECLARE done INT DEFAULT FALSE;\n" +"    DECLARE tableName VARCHAR(255);\n" +"\n" +"    -- 定义游标\n" +"    DECLARE cur CURSOR FOR \n" +"        SELECT TABLE_NAME \n" +"        FROM information_schema.TABLES \n" +"        WHERE TABLE_SCHEMA = dbName;\n" +"\n" +"    -- 错误处理\n" +"    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION\n" +"        BEGIN\n" +"            -- 错误发生时不做任何处理，继续执行\n" +"        END;\n" +"\n" +"    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;\n" +"\n" +"    -- 重新打开游标\n" +"    OPEN cur;\n" +"\n" +"    read_loop_2: LOOP\n" +"        FETCH cur INTO tableName;\n" +"        IF done THEN\n" +"            LEAVE read_loop_2;\n" +"        END IF;\n" +"\n" +"        -- 执行 IMPORT TABLESPACE\n" +"        SET @import_sql = CONCAT('ALTER TABLE ', dbName, '.', tableName, ' IMPORT TABLESPACE;');\n" +"        PREPARE stmt FROM @import_sql;\n" +"        EXECUTE stmt;\n" +"        DEALLOCATE PREPARE stmt;\n" +"\n" +"        -- 在此处可以执行其他操作\n" +"        -- 例如：INSERT INTO logging_table (message) VALUES (CONCAT('Imported tablespace for ', tableName));\n" +"    END LOOP;\n" +"\n" +"    -- 关闭游标\n" +"    CLOSE cur;\n" +"END;\n";stmt.execute(aa);// 第三步，执行第二个存储过程stmt.execute("CALL your_second_stored_procedure('" + dbNameField.getText() + "')"); // 替换为你的存储过程名stmt.execute(aaaa);JOptionPane.showMessageDialog(frame, "恢复数据成功。", "成功", JOptionPane.INFORMATION_MESSAGE);} catch (SQLException e) {showErrorDialog("执行存储过程失败: " + e.getMessage());} catch (IOException e) {showErrorDialog("文件复制失败: " + e.getMessage());}}private static void copyFiles(String sourceFolder, String destFolder) throws IOException {Path sourcePath = Paths.get(sourceFolder);Path destPath = Paths.get(destFolder);Files.walk(sourcePath).forEach(source -> {Path dest = destPath.resolve(sourcePath.relativize(source));try {if (Files.isDirectory(source)) {Files.createDirectories(dest);} else {Files.copy(source, dest, StandardCopyOption.REPLACE_EXISTING);}} catch (IOException e) {e.printStackTrace();}});}private static Connection getConnection() throws SQLException {String username = userField.getText();String password = new String(passwordField.getPassword());String dbName = dbNameField.getText();String url = "jdbc:mysql://localhost:3306/" + dbName;return DriverManager.getConnection(url, username, password);}private static class ConnectAction implements ActionListener {@Overridepublic void actionPerformed(ActionEvent e) {try {Class.forName("com.mysql.cj.jdbc.Driver");getConnection();updateUIAfterConnection();} catch (ClassNotFoundException ex) {showErrorDialog("驱动未找到: " + ex.getMessage());} catch (SQLException ex) {showErrorDialog("连接失败: " + ex.getMessage());}}}private static void showErrorDialog(String message) {JOptionPane.showMessageDialog(frame, message, "连接错误", JOptionPane.ERROR_MESSAGE);}
}