mysql数据库备份与恢复

package com.hzys.common.datasourse; import com.hzys.common.exception.BusinessException; import com.hzys.common.utils.DateUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.stereotype.Component; import java.io.*; /** * @auther sjt * @date 14:02 */ @Component public class MysqlBackup { private static final Logger logger=LoggerFactory.getLogger(MysqlBackup.class); private static String mysqlBinUrl; private static String mysqlIp; private static String mysqlPort; private static String mysqlDatasource; private static String mysqlFileUrl; private static String username; private static String password; public static String backup() { InputStream in=null; InputStreamReader isr=null; BufferedReader br=null; OutputStreamWriter writer=null; FileOutputStream fout=null; File file=new File(mysqlFileUrl); if (!file.exists() && !file .isDirectory()){ file.mkdir(); } String fileUrl=mysqlFileUrl+DateUtils.getDate("yyyy-MM-dd hh-mm-ss")+".sql"; try { Runtime rt = Runtime.getRuntime(); // 调用 调用mysql的安装目录的命令 //路径为本地的mysqldump文件路径 Process child = rt .exec(mysqlBinUrl+"/mysqldump -h "+mysqlIp+" -P "+mysqlPort+" -u"+username+" -p"+password+" --default-character-set=utf8 "+mysqlDatasource); // 设置导出编码为utf-8。这里必须是utf-8 // 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行 in = child.getInputStream();// 控制台的输出信息作为输入流 isr = new InputStreamReader(in, "utf-8"); // 设置输出流编码为utf-8。这里必须是utf-8，否则从流中读入的是乱码 String inStr; StringBuffer sb = new StringBuffer(""); String outStr; // 组合控制台输出信息字符串 br = new BufferedReader(isr); while ((inStr = br.readLine()) != null) { sb.append(inStr + "\r\n"); } outStr = sb.toString(); // 要用来做导入用的sql目标文件： fout = new FileOutputStream(fileUrl); writer = new OutputStreamWriter(fout, "utf-8"); writer.write(outStr); writer.flush(); in.close(); isr.close(); br.close(); writer.close(); fout.close(); } catch (Exception e) { logger.error("备份数据库出错，联系研发人员！",e); throw new BusinessException(500,e.getMessage()); } return fileUrl; } public static void load(String fPath) { OutputStream outputStream=null; BufferedReader br=null; OutputStreamWriter writer=null; try { Runtime runtime = Runtime.getRuntime(); //-u后面是用户名，-p是密码-p后面最好不要有空格，-family是数据库的名字，--default-character-set=utf8，这句话一定的加 //我就是因为这句话没加导致程序运行成功，但是数据库里面的内容还是以前的内容，最好写上完成的sql放到cmd中一运行才知道报错了 //错误信息： //mysql: Character set 'utf-8' is not a compiled character set and is not specified in the ' //C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\Index.xml' file ERROR 2019 (HY000): Can't // initialize character set utf-8 (path: C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\)， //又是讨人厌的编码问题，在恢复的时候设置一下默认的编码就可以了。 Process process = runtime.exec(mysqlBinUrl+"/mysql -h "+mysqlIp+" -P "+mysqlPort+" -u"+username+" -p"+password+" --default-character-set=utf8 "+mysqlDatasource); outputStream = process.getOutputStream(); br = new BufferedReader(new InputStreamReader(new FileInputStream(fPath))); String str = null; StringBuffer sb = new StringBuffer(); while((str = br.readLine()) != null){ sb.append(str+"\r\n"); } str = sb.toString(); writer = new OutputStreamWriter(outputStream,"utf-8"); writer.write(str); writer.flush(); writer.close(); br.close(); outputStream.close(); } catch (Exception e) { logger.error("恢复数据库出错，联系研发人员！",e); throw new BusinessException(500,e.getMessage()); } } @Value("${mysql_bin_url}") public void setMysqlBinUrl(String mysqlBinUrl) { this.mysqlBinUrl = mysqlBinUrl; } @Value("${mysql_ip}") public void setMysqlIp(String mysqlIp) { this.mysqlIp = mysqlIp; } @Value("${mysql_port}") public void setMysqlPort(String mysqlPort) { this.mysqlPort = mysqlPort; } @Value("${mysql_datasource}") public void setMysqlDatasource(String mysqlDatasource) { this.mysqlDatasource = mysqlDatasource; } @Value("${mysql_file_url}") public void setMysqlFileUrl(String mysqlFileUrl) { this.mysqlFileUrl = mysqlFileUrl; } @Value("${master.datasource.username}") public void setUsername(String username) { this.username = username; } @Value("${master.datasource.password}") public void setPassword(String password) { this.password = password; } }