文章目录
本文档只是为了留档方便以后工作运维，或者给同事分享文档内容比较简陋命令也不是特别全，不适合小白观看，如有不懂可以私信，上班期间都是在得
需求，昨天写个数据恢复呗同事嫌麻烦，让我写个脚本一键恢复，一开始想着存储过程，但是存储过程不能移动文件，索性写个可视化界面吧。
下面直接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; // 显示文件夹路径的标签1
private static JLabel folderPathLabel2; // 显示文件夹路径的标签2
public 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 {
@Override
public 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);
}
}
2.Main方法打包
3.Jar包打成exe可执行文件
4.使用
（1.）准备一个表结构一样得数据库
（2.）打开软件
打开软件输入账号密码和要恢复得库
（3.）输入路径
点击恢复
5.恢复成功
待优化，理论上只需要输入备份得文件夹，但是领导催我干别的了，以后有机会在优化一下。
如果点赞多，评论多会更新详细教程，待补充。
标签：数据恢复,EXE,String,frame,add,static,new,JLabel,idb From： https://blog.csdn.net/xh365647/article/details/143178958