java实现Mysql的备份与还原

import java.io.File;import java.io.IOException;import java.io.InputStream;import java.util.Properties;  public class JavaMysql {   /*    * 备份数据库 1、读取配置文件 2、启动智能查询Mysql安装目录 3、备份数据库为sql文件    */   public static void backup(String sql) {        Properties pros = getPprVue("prop.properties");      String username = pros.getProperty("username");      String password = pros.getProperty("password");        CheckSoftware c = null;      try {         System.out.println("MySQL服务安装地址 ："+c.check().toString());      } catch (Exception e2) {         e2.printStackTrace();      }      String mysqlpaths;      try {         mysqlpaths = c.check().toString() + "bin" + "\\";           String databaseName = pros.getProperty("databaseName");         String address = pros.getProperty("address");         String sqlpath = pros.getProperty("sql");         File backupath = new File(sqlpath);         if (!backupath.exists()) {            backupath.mkdir();         }           StringBuffer sb = new StringBuffer();           sb.append(mysqlpaths);         sb.append("mysqldump ");         sb.append("--opt ");         sb.append("-h ");         sb.append(address);         sb.append(" ");         sb.append("--user=");         sb.append(username);         sb.append(" ");         sb.append("--password=");         sb.append(password);         sb.append(" ");         sb.append("--lock-all-tables=true ");         sb.append("--result-file=");         sb.append(sqlpath);         sb.append(sql);         sb.append(" ");         sb.append("--default-character-set=utf8 ");         sb.append(databaseName);         System.out.println("cmd指令 ："+sb.toString());         Runtime cmd = Runtime.getRuntime();         try {            Process p = cmd.exec(sb.toString());         } catch (IOException e) {            e.printStackTrace();         }      } catch (Exception e1) {         e1.printStackTrace();      }   }     /*    * 读取属性文件    */   public static Properties getPprVue(String properName) {        InputStream inputStream = JavaMysql.class.getClassLoader()            .getResourceAsStream(properName);      Properties p = new Properties();        try {         p.load(inputStream);         inputStream.close();      } catch (IOException e) {         e.printStackTrace();      }        return p;     }     /*    * 根据备份文件恢复数据库    */   public static void load(String filename) {      Properties pros = getPprVue("prop.properties");      String root = pros.getProperty("jdbc.username");      String pass = pros.getProperty("jdbc.password");      String mysqlpaths = c.check().toString() + "bin" + "\\";      String sqlpath = pros.getProperty("sql");      String filepath = mysqlpaths + sqlpath + filename; // 备份的路径地址        String stmt1 = mysqlpaths + "mysqladmin -u " + root + " -p" + pass            + " create finacing"; // -p后面加的是你的密码      String stmt2 = mysqlpaths + "mysql -u " + root + " -p" + pass            + " finacing < " + filepath;      String[] cmd = { "cmd", "/c", stmt2 };      try {         Runtime.getRuntime().exec(stmt1);         Runtime.getRuntime().exec(cmd);         System.out.println("数据已从 " + filepath + " 导入到数据库中");      } catch (IOException e) {         e.printStackTrace();      }     }     /*    * Test测试    */   public static void main(String[] args) throws IOException {      backup("2221.sql");   }}

ubuntu下操作

public static void main(String[] args) throws Exception{String cmd = "/bin/sh backup.sh  ";Process process = Runtime.getRuntime().exec(cmd); process.waitFor();//脚本中多任务时，由于下面有destroy，所以需要此语句 process.destroy();//销毁子线程}

 

backup.sh代码： #!/bin/bash backupTime=`date +%Y%m%d-%T` sudo mysqldump -u root -p123uusc --quick uuyx > /home/uu/db_$backupTime.sql

MYSQL备份还原指令

1.show processlist; //查看当前正在进行的进程，对于有锁表等情况的排查很有用处。
2.show open tables; //显示当前已经被打开的表列表
3.mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql //备份mysql数据库命令
4.mysqldump --add-drop-table -hhostsname -uusername -ppassword databasename > backupfile.sql //备份mysql数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库
5.mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz //直接将mysql数据库压缩备份
6.mysqldump -hhostname -uusername -ppassword database1 database2 database3 > multifile.sql //同时备份多个mysql数据库
7.mysqldump -hhostname -uusername -ppassword database1 tablename1 tablename2 tablename3 > multifile.sql //同时备份mysql数据库某个（些）表
8.mysqldump --no-data --databases -hhostname -uusername -ppassword databasename1 databasename2 >struct2.sql //仅仅备份数据库的结构
9.mysqldump --all-databases -hhostname -uusername -ppassword > backup.sql //备份服务器上的所有数据
================================================
1.mysql -hhostname -uusername -ppassword databasename < backup.sql //还原mysql数据库的命令
2.gunzip < backup.sql | mysql -hhostname -uusername -ppassword databasename //还原压缩的mysql数据库
3.mysqldump -hhostname -uusername -ppassword databasename | mysql --host=*.*.*.* -C databasename; //将数据库转移到新的服务器