mysql数据库备份

mysql带条件备份数据库成sql文件格式

需求：备份整个数据库，如果某张表中有time字段，则截取一段时间
环境：本人用的是Servlet进行开发，如有不同，敬请谅解！

servlet层

@WebServlet("/DownloadServlet")
public class DownloadServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;
	
	private DownLoadService loadService = new DownLoadService();

	protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doGet(request, response);
	}

	protected synchronized void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String method = request.getParameter("method");
		 if (method.equals("test")) {
			test(request, response);
		}
	}

	private void test(HttpServletRequest request, HttpServletResponse response) {
		// String dataBase = request.getParameter("dataBase");
		String dataBase = "数据库名";
		// 得到数据库的所有表名
		List<Map<String, Object>> tablesName;
		List<Map<String, Object>> columnsName;
		String appendSql = "";
		String startTime = "2019-03-26 09:24:27";
		String endTime = "2019-03-26 10:26:43";
		try {
			tablesName = loadService.showTablesName(dataBase);
			// System.out.println("数据库名为："+dataBase);
			for (Map<String, Object> m : tablesName) {
				for (String key : m.keySet()) {
					// 表名为m.get(key).toString()
					columnsName = loadService.showTableColumns(dataBase, m.get(key).toString());
					for (Map<String, Object> c : columnsName)
						for (String k : c.keySet()) {
							//字段名为c.get(k).toString()
							System.out.println(m.get(key).toString() + "表中字段有" + c.get(k).toString());
							//判断表中字段有无time
							if (c.get(k).toString().equals("time")) {
									//"--where=\"time between '2019-03-26 09:26:39' and '2019-04-22 11:26:26'\""
							appendSql += "--where=\"time between '"+startTime+"' and '"+endTime+"'\"";
							
						}
					}
					Properties properties = new Properties();
					try {
						//读取jdbc.properties
						properties.load(
								new FileInputStream("jdbc.properties的绝对路径"));
						String mysqldump = loadService.mysqlDump(properties, m.get(key).toString(), appendSql);
						//System.out.println("mysqldump语句为："+mysqldump);
						backup(mysqldump, m.get(key).toString());
					} catch (FileNotFoundException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} catch (IOException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					appendSql = "";
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
	//数据备份
	public static void backup(String cmd,String tab) {
		try {
			Runtime rt = Runtime.getRuntime();
			Process child = rt.exec(cmd);
			InputStream in = child.getInputStream();// 控制台的输出信息作为输入流
			InputStreamReader xx = new InputStreamReader(in, "utf-8");
			String inStr;
			StringBuffer sb = new StringBuffer("");
			String outStr;
			BufferedReader br = new BufferedReader(xx);
			while ((inStr = br.readLine()) != null) {
				sb.append(inStr + "\r\n");
			}
			outStr = sb.toString();
			FileSystemView fsv = FileSystemView.getFileSystemView();
			File home = fsv.getHomeDirectory();
			String savePath = home.getPath();
			FileOutputStream fout = new FileOutputStream(savePath + "/"+tab+".sql");
			OutputStreamWriter writer = new OutputStreamWriter(fout, "utf-8");
			writer.write(outStr);
			writer.flush();
			in.close();
			xx.close();
			br.close();
			writer.close();
			fout.close();
			System.out.println("");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

service层

public class DownLoadService {
	DownLoadDao downLoadDao = new DownLoadDao();
	//查询某个数据库下的所有表名
	public List<Map<String, Object>> showTablesName(String dataBase) throws SQLException{
		return downLoadDao.showTablesName(dataBase);
	}
	//查询某个表下的所有字段名
	public List<Map<String, Object>> showTableColumns(String dataBase,String tableName) throws SQLException{
		return downLoadDao.showTableColumns(dataBase,tableName);
	}
	
	//获取mysqldump执行的命令
	public String mysqlDump(Properties properties,String table,String appendSql ){
		return downLoadDao.mysqlDump(properties,table,appendSql);
	}
}

dao层

public class DownLoadDao extends BaseDao {

	// 查询数据库下所有的表名
	public List<Map<String, Object>> showTablesName(String dataBase) throws SQLException {
		String sql = "select table_name from information_schema.tables where table_schema='" + dataBase + "'";
		QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
		return qr.query(sql, new MapListHandler());
	}

	// 查询某张表下所有的字段
	public List<Map<String, Object>> showTableColumns(String dataBase, String tableName) throws SQLException {
		String sql = "select column_name from information_schema.columns where table_schema='" + dataBase
				+ "' and table_name='" + tableName + "'";
		QueryRunner qr = new QueryRunner(DBUtils.getDataSource());
		return qr.query(sql, new MapListHandler());
	}

	// 拼接mysqldump命令
	public String mysqlDump(Properties properties, String table, String appendSql) {
		StringBuffer command = new StringBuffer();
		// 备份地址默认为桌面
		FileSystemView fsv = FileSystemView.getFileSystemView();
		File home = fsv.getHomeDirectory();
		// 这便是读取桌面路径的方法了
		String savePath = home.getPath();
		String userName = properties.getProperty("jdbc.userName");// 用户名
		String password = properties.getProperty("jdbc.password");// 用户密码
		String exportDatabaseName = properties.getProperty("jdbc.exportDataBaseName");// 需要导出的数据库名
		String host = properties.getProperty("jdbc.host");// 从哪个主机导出数据库，如果没有指定这个值，则默认取localhost
		String port = properties.getProperty("jdbc.port");// 使用的端口号
		String MysqlPath = properties.getProperty("mysqlPath"); // 路径是mysql中 bin
																// 文件 的位置
		command.append(MysqlPath).append("mysqldump -u").append(userName).append(" -p").append(password)// 密码是用的小p，而端口是用的大P。
				.append(" -h").append(host).append(" -P").append(port).append(" ").append(exportDatabaseName)
				.append(" ").append(table).append(" ").append(appendSql);
		return command.toString();
	}

}

jdbc.properties

jdbc.userName=root
jdbc.password=ok
jdbc.host=localhost
jdbc.port=3306
jdbc.exportDataBaseName=数据库名
mysqlPath=D:/mysql5.5/bin/

还有不懂的点这里！皮这一下真的很开心！！