java调用mysqldump进行数据库远程备份

java调用mysqldump进行数据库远程备份

近日需要在应用中，进行远程数据库的备份，在网上排查了一番，很多文章能把重点说出来，但殊不知，还有很多细节没有体现。正是由于这些细节问题，导致运行不成功，特此记录下，与大家分享。
以mysql为例，网友大部分采用的方案是：java 调用process，执行mysqldump命令。本人也是在该方案基础上的尝试。
如果想让java应用执行mysqldump命令，前提必须在java应用的机器上存在mysqldump命令，可以不安装mysql数据库，但是该命令必须存在。以windows平台为例，在某个目录下必须存在mysqldump.exe文件。
同时采用jdbc方式连接数据库时，url需关闭ssl：

jdbc:mysql://localhost/springboot?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false //关闭SSL

另外，如果想获取process.waitfor()方法的错误信息，必须在调用该方法之前，单独开启线程获取：

new Thread() {
				@Override
				public void run() {
					BufferedReader err = null;
					try {
						err = new BufferedReader(new InputStreamReader(p.getErrorStream(), "gbk"));
					} catch (UnsupportedEncodingException e1) {
						e1.printStackTrace();
					}
					String line = null;
					StringBuilder result = new StringBuilder();
					try {
						while ((line = err.readLine()) != null) {
							result.append(line);
						}
						System.out.println("the error message is :" + result.toString());
					} catch (IOException e) {
						e.printStackTrace();
					} finally {
						try {
							err.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
				}
			}.start();
			int processStatus = p.waitFor();

最后，附上测试完成代码：

import java.io.BufferedReader;
import java.io.File;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

public class MysqlBack {
	public static void main(String[] args) {
		String DB_HOST = "192.168.1.11";
		String DB_PORT = "3306";
		String DB_NAME = "test";
		String DB_USERNAME = "root";
		String DB_PASSWORD = "root";
		String BACKUP_PATH = "d:\\backup\\";
		String filename = "backup_" + new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date()) + ".sql";
		Connection conn = null;
		Statement stmt = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			//关闭ssl
			String url = "jdbc:mysql://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME
					+ "?serverTimezone=UTC&characterEncoding=UTF-8&useSSL=false";
			conn = DriverManager.getConnection(url, DB_USERNAME, DB_PASSWORD);
			stmt = conn.createStatement();
			String backup = "mysqldump -h " + DB_HOST + " -P " + DB_PORT + " -u " + DB_USERNAME + " -p" + DB_PASSWORD
					+ " " + DB_NAME + " >" + BACKUP_PATH + "\\" + filename;
			System.out.println(backup);
			Runtime runtime = Runtime.getRuntime();
			//需要添加cmd.exe /c 
			String command = "cmd.exe /c " + backup;
			//需要指定mysqldump命令所在路径
			Process p = runtime.exec(command, null, new File("D:\\mysqldump"));
			new Thread() {
				@Override
				public void run() {
					BufferedReader in = new BufferedReader(new InputStreamReader(p.getInputStream()));
					String line = null;

					try {
						while ((line = in.readLine()) != null) {
							System.out.println("执行的结果为: " + line);
						}
					} catch (IOException e) {
						e.printStackTrace();
					} finally {
						try {
							in.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
				}
			}.start();

			new Thread() {
				@Override
				public void run() {
					BufferedReader err = null;
					try {
						err = new BufferedReader(new InputStreamReader(p.getErrorStream(), "gbk"));
					} catch (UnsupportedEncodingException e1) {
						e1.printStackTrace();
					}
					String line = null;
					StringBuilder result = new StringBuilder();
					try {
						while ((line = err.readLine()) != null) {
							result.append(line);
						}
						System.out.println("the error message is :" + result.toString());
					} catch (IOException e) {
						e.printStackTrace();
					} finally {
						try {
							err.close();
						} catch (IOException e) {
							e.printStackTrace();
						}
					}
				}
			}.start();
			int processStatus = p.waitFor();
			if (processStatus == 0) {
				System.out.println("Database has been backed up successfully.");
			} else {
				System.out.println("An error occurred during backup process.");
			}
			p.destroy();
		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}
}