javaweb数据库备份与还原

1.设置在jdbc.properties文件里设置相关的数据库连接语句

jdbc_url=jdbc:mysql://ip地址:端口/数据库名称?characterEncoding=utf8&zeroDateTimeBehavior=round
jdbc_username=用户名
jdbc_password=用户密码

2.在applicationContext.xml中读取改properties文件

<context:property-placeholder location="classpath:classpath:conf/jdbc.properties" />

3.声明一个数据库连接语句对象（我这边是sysBackupAndRecoveryConfig），属性如下（省略get，set）

    private String port;
    private String username;
    private String host;
    private String password;
    private String database;

4.ServiceImpl层注入上面这个属性对象，并且获取applicationContext中的jdbc.properties文件中我们想要得到的属性

    @Resource 
    private SysBackupAndRecoveryConfig sysBackupAndRecoveryConfig;  
    @Value("${jdbc_url}")
    private String sqlUrl;
    @Value("${jdbc_username}")
    private String userName;
    @Value("${jdbc_password}")
    private String passWord;

5.根据拿到的数据库连接语句url，从中解析出数据库名称，IP地址，字符编码

        /**
         *解析数据库连接语句获取想要的数据
         * 
         */
        public void setBackupInfo()
        {       
            String sqlPath=sqlUrl;
            String[] split = sqlPath.split(":");
            sysBackupAndRecoveryConfig.setHost(split[2].substring(2));
            String[] splits= split[3].split("/");
            sysBackupAndRecoveryConfig.setPort(splits[0]);  
            sysBackupAndRecoveryConfig.setCharset(splits[1].split("\\?")[1].split("&")[0].split("=")[1]);
            sysBackupAndRecoveryConfig.setDatabase(splits[1].split("\\?")[0]);
            sysBackupAndRecoveryConfig.setUsername( userName);
            sysBackupAndRecoveryConfig.setPassword(passWord);       
        }

6.数据库备份。
关键代码如下，网上有其他详细的，备份用 mysqldump

process = runtime.exec(" mysqldump -R --port="+sysBackupAndRecoveryConfig.getPort()+" -h" +sysBackupAndRecoveryConfig.getHost() + 
                    " -p"+sysBackupAndRecoveryConfig.getPassword()+" -u" + sysBackupAndRecoveryConfig.getUsername()+ " --set-charset="+sysBackupAndRecoveryConfig.getCharset()+" " + sysBackupAndRecoveryConfig.getDatabase());

完整代码

    /**
     * 备份
     * @throws ParseException 
     * @throws NumberFormatException 
     * 
     */
     private FileOutputStream fileOutputStream;
     private BufferedReader br ;
     private InputStreamReader reader;
     private InputStream inputStream;
    @Override
    public void dataBackup()  {     
        String realPath=getProjectPath();   
        setBackupInfo();
        SimpleDateFormat dateFormater = new SimpleDateFormat("yyyyMMddHHmmss");
        Date date=new Date();
        String sqlDate= dateFormater.format(date);
        long time = new Date().getTime();
        File file = new File(realPath+"/"+time); 
        if  (!file .exists()  && !file .isDirectory())      
        {     
            file.mkdirs();  
        }   
        File realfile=new File(file,sqlDate+"_"+sysBackupAndRecoveryConfig.getDatabase()+".sql");
        Runtime runtime = Runtime.getRuntime();  
        Process process;
        try {
            process = runtime.exec(" mysqldump -R --port="+sysBackupAndRecoveryConfig.getPort()+" -h" +sysBackupAndRecoveryConfig.getHost() + 
                    " -p"+sysBackupAndRecoveryConfig.getPassword()+" -u" + sysBackupAndRecoveryConfig.getUsername()+ " --set-charset="+sysBackupAndRecoveryConfig.getCharset()+" " + sysBackupAndRecoveryConfig.getDatabase());
              inputStream = process.getInputStream();
              reader= new InputStreamReader(inputStream,"utf-8");
              br = new BufferedReader(reader);  
              StringBuffer sb = new StringBuffer();  
              String s = null;  
              while((s = br.readLine()) != null){  
                    sb.append(s+"\r\n");  
                }
              s = sb.toString();              
              fileOutputStream = new FileOutputStream(realfile);
              fileOutputStream.write(s.getBytes());
              fileOutputStream.close();  
              br.close();  
              reader.close();  
              inputStream.close();
        } catch (IOException e1) {
            delFolder(realfile.getAbsolutePath());
            throw new RuntimeException("备份失败");
        }finally {
              try {
                  if (null != fileOutputStream)
                  {
                        fileOutputStream.close();
                  }
                  if (null != br)
                  {
                      br.close();  
                  }
                  if (null != reader)
                  {
                      reader.close();  
                  }
                  if (null != inputStream)
                  {
                      inputStream.close();
                  }             
            } catch (IOException e) {

                delFolder(realfile.getAbsolutePath());
                throw new RuntimeException("关闭失败");
            }      
        }       

    }

7.数据库还原
关键代码如下，网上有其他详细的，还原用 mysql

process = runtime.exec("mysql --default-character-set="+sysBackupAndRecoveryConfig.getCharset()+"  -port"+sysBackupAndRecoveryConfig.getPort()+" -u"+sysBackupAndRecoveryConfig.getUsername()+" -p"+sysBackupAndRecoveryConfig.getPassword()+" "+sysBackupAndRecoveryConfig.getDatabase()+"");

完整代码

    /**
     * 还原
     * 
     */
    private OutputStream outputStream ;
    private OutputStreamWriter writer;
    @Override
    public void dataRecovery(String fileName,String backupDate) {
        setBackupInfo();
        Runtime runtime = Runtime.getRuntime();
        String path=getProjectPath();
        String realpath = getRepath(path,fileName,backupDate);
        Process process;    
        //本地
        try {
            process = runtime.exec("mysql --default-character-set="+sysBackupAndRecoveryConfig.getCharset()+"  -port"+sysBackupAndRecoveryConfig.getPort()+" -u"+sysBackupAndRecoveryConfig.getUsername()+" -p"+sysBackupAndRecoveryConfig.getPassword()+" "+sysBackupAndRecoveryConfig.getDatabase()+"");
        //  process = runtime.exec("mysql --default-character-set=utf8  -port3306 -uroot -p9790 testtwn");
            outputStream = process.getOutputStream();
            br = new BufferedReader(new InputStreamReader(new FileInputStream(realpath)));
            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();
            outputStream.close();
            br.close();
            writer.close();     
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            throw new RuntimeException("还原失败");
        }finally {
              try {
                  if (null != outputStream)
                  {
                      outputStream.close();
                  }
                  if (null != br)
                  {
                      br.close();  
                  }
                  if (null != writer)
                  {
                      writer.close();  
                  }
            } catch (IOException e) {
                throw new RuntimeException("关闭失败");
            }      
        }  

    }

mysql数据库备份与还原基本就这些了，这也是我们项目的老大让我做的第一个模块，后面还用到了quartz定时器做自动备份，有时间我再整理出来，另外需要完整代码的小伙伴们可以找我，我整理出来后看看能不能发到这里。