java备份以及还原mysql数据库

来源：互联网发布：二战德军重要战役数据编辑：程序博客网时间：2024/05/21 10:52

参考：http://xp84012493.iteye.com/blog/199290

java.io.BufferedReader;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.io.InputStreamReader;import java.io.OutputStream;import java.io.OutputStreamWriter;import java.text.SimpleDateFormat;import java.util.Date;/** * 功能：备份以及还原mysql数据库 * 需要将mysql安装的bin目录C:\Program Files\MySQL\MySQL Server 5.5\bin设置到环境变量，否则不知道怎么调用mysqldump[⊙﹏⊙b汗] * 用java备份的话，不能使用">"了，所以调用cmd命令时，需要更改里面的参数 * mysqldump -h127.0.0.1 -uroot -proot erp > backupfile.sql * mysqldump  -h 127.0.0.1 -u root -proot erp  --default-character-set=utf8  --result-file=D:backupfile.sql * @author Administrator * */public class BackupsDB {/** * 备份mysql数据库 * @param rootmysql登录名 * @param rootPass  登录密码 * @param dbName  要备份的数据库名称 * @param backupsPath  备份的路径 * @param backupsSqlFileName  备份文件的名字 * @return */public static String dbBackUp(String root,String rootPass,String dbName,String backupsPath,String backupsSqlFileName){//生成临时备份文件//SimpleDateFormat sd=new SimpleDateFordckupsSqlFileName;String pathSql = backupsPath+backupsSqlFileName;try {File fileSql = new File(pathSql);if(!fileSql.exists()){fileSql.createNewFile();}StringBuffer sbs = new StringBuffer();sbs.append("mysqldump ");sbs.append(" -h 127.0.0.1 ");sbs.append(" -u ");sbs.append(root+" ");sbs.append("-p"+rootPass+" ");sbs.append(dbName);sbs.append(" --default-character-set=utf8 ");//sbs.append(">"+pathSql);sbs.append(" --result-file="+pathSql);System.out.println("cmd命令为：——>>>"+sbs.toString());        Runtime runtime = Runtime.getRuntime();        Process child = runtime.exec(sbs.toString());                //读取备份数据并生成临时文件        InputStream in = child.getInputStream();        OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(pathSql), "utf8");        BufferedReader reader = new BufferedReader(new InputStreamReader(in, "utf8"));        String line=reader.readLine();        while (line != null) {                writer.write(line+"\n");                line=reader.readLine();         }         writer.flush();         System.out.println("数据库已备份到——>>"+pathSql);} catch (Exception e) {}return pathSql;}/** * @param args */public static void main(String[] args) {// TODO Auto-generated method stub//mysqldump -h 192.168.110.10 --user=root --password=fortune123 --lock-all-tables=true --result-file=D:tt.sql\ --default-character-set=utf8 ERPSimpleDateFormat sd=new SimpleDateFormat("yyyyMMddHHmmss");String root = "root";String rootPass = "root";String dbName = "erp";String backupsPath = "D:";String backupsSqlFileName = "erpDB_"+sd.format(new Date())+".sql";dbBackUp(root, rootPass, dbName, backupsPath, backupsSqlFileName);backup();load();}/** * 备份erp数据库到D:db.sql * @return */public static boolean backup() {//备份    try {        Runtime rt = Runtime.getRuntime();          String mysql = "mysqldump -uroot -proot erp --default-character-set=utf8 ";  //        mysql ="C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\"+mysql;                System.out.println("mysql=============>>>>"+mysql);        // 调用 mysql 的 cmd:          Process child = rt          .exec("cmd /c "+mysql);// 设置导出编码为utf8。这里必须是utf8            // 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行          InputStream in = child.getInputStream();// 控制台的输出信息作为输入流            InputStreamReader xx = new InputStreamReader(in, "utf8");// 设置输出流编码为utf8。这里必须是utf8，否则从流中读入的是乱码            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();            // 要用来做导入用的sql目标文件：          FileOutputStream fout = new FileOutputStream(                  "D:/db.sql");          OutputStreamWriter writer = new OutputStreamWriter(fout, "utf8");          writer.write(outStr);          // 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免          writer.flush();            // 别忘记关闭输入输出流          in.close();          xx.close();          br.close();          writer.close();          fout.close();            System.out.println("/* Output OK! */");                                String error="备份成功!";        } catch (Exception e) {          e.printStackTrace();      }        return true;  }  /** * 还原D:db.sql到erp数据库 * @return */public static boolean load() {//还原      try {        String fPath = "D:/db.sql";          Runtime rt = Runtime.getRuntime();            // 调用 mysql 的 cmd:          Process child = rt.exec("mysql -uroot -proot erp ");          OutputStream out = child.getOutputStream();//控制台的输入信息作为输出流          String inStr;          StringBuffer sb = new StringBuffer("");          String outStr;          BufferedReader br = new BufferedReader(new InputStreamReader(                  new FileInputStream(fPath), "utf8"));          while ((inStr = br.readLine()) != null) {              sb.append(inStr + "\r\n");          }          outStr = sb.toString();            OutputStreamWriter writer = new OutputStreamWriter(out, "utf8");          writer.write(outStr);          // 注：这里如果用缓冲方式写入文件的话，会导致中文乱码，用flush()方法则可以避免          writer.flush();          // 别忘记关闭输入输出流          out.close();          br.close();          writer.close();                    System.out.println("/* Load OK! */");      } catch (Exception e) {          e.printStackTrace();      }      return true;  }   }

参考：http://www.cnblogs.com/acpp/archive/2010/01/06/1640542.html

备份MySQL数据库的命令

mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql

备份MySQL数据库为带删除表的格式
备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。

mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql
直接将MySQL数据库压缩备份

mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
备份MySQL数据库某个(些)表

mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
同时备份多个MySQL数据库

mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
仅仅备份数据库结构

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
备份服务器上所有数据库

mysqldump –all-databases > allbackupfile.sql
还原MySQL数据库的命令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql
还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
将数据库转移到新服务器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename

0 0