MySQL Database Backup & Restore Using Java

Hello Programmers !!

It’s been a while since I’ve written a blog post. Luckily I’m in a mood to write something at the moment. Today I thought about posting something I’ve learnt recently. It’s about database backups and restores through java.

If anyone wants to backup or restore a database without going through all these troubles, you can simply use MySQL GUI Tools Bundle freely distributed by the Oracle Corporation.

Sometimes it is necessary to integrate a backup & restore system to an application we are developing. In my case it was a java application. In this post I’m expecting to give you guys a rough idea about backing up and restoring your MySQL database through a Java Application.

1. Creating A Backup

When we install MySQL open source database it provides us a client called mysqldump for backup purposes. Here we execute this mysqldump command using the java Runtime.

Here’s a sample method to create a complete database backup including add, drop MySQL statements.

public boolean backupDB(String dbName, String dbUserName, String dbPassword, String path) {

        String executeCmd = "mysqldump -u " + dbUserName + " -p" + dbPassword + " --add-drop-database -B " + dbName + " -r " + path;
        Process runtimeProcess;
        try {

            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup created successfully");
                return true;
            } else {
                System.out.println("Could not create the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

Basically what we are doing here is similar executing the following command in the windows command line.

mysqldump -u Username -pPassword --add-drop-database -B databaseName -r backupPath

Here  -u, -p, -B, -r are options to indicate that we are inserting the database username, password, database name and location to save the backup respectively. The --add-drop-database option is to create a complete backup. It means that when we are restoring the backup file, if the database does not exist in that particular database the restoring process itself create the database automatically.

If by any chance you want to create a backup without add, drop database command. You can use the following command.

mysqldump -u Username -pPassword databaseName -r backupPath

For multiple databases use

mysqldump -u Username -pPassword --add-drop-database -B db1 db2 db3 -r backupPath

If you want to backup All the databases. You can use the command as follows.

mysqldump -u Username -pPassword --add-drop-database -A -r backupPath

Likewise you can create your backup according to the requirements. There are many other options that you can add to your command. You can visit mysqldump Docs for more options or simply type mysqldump --help on the command line to view the options.

That’s the basic idea about backing up the database. Let’s see how we can do the restoring process through java.

2. Restoring A Backup

I had hard time finding a working code for the restoring process. After making a few changes on the commands I’ve found on the internet, I’ve manage to create a workable runtime  command for the restore process.

Note: The following method is to restore a complete database backup (backup with add, drop database command)

public boolean restoreDB(String dbUserName, String dbPassword, String source) {

        String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, "-e", "source "+source};

        Process runtimeProcess;
        try {

            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup restored successfully");
                return true;
            } else {
                System.out.println("Could not restore the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

If the the backup doesn’t have the add, drop database, you can use the following method.

public static boolean restoreDB(String dbName, String dbUserName, String dbPassword, String source) {

        String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", "source "+source};

        Process runtimeProcess;
        try {

            runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup restored successfully");
                return true;
            } else {
                System.out.println("Could not restore the backup");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

        return false;
    }

I have tested all the commands and the methods mentioned in this post and they worked fine in Windows environment. That’s all about creating & restoring backups. Hope you guys learnt something new.
If you have any questions please post them as a comment & I’ll try my best to answer them. I’m backing up for now then. See you guys with another post. Hopefully! :)

Update

Make sure to add the path of  your ‘MySQL bin folder’ to the path variable in ‘Windows Environment Variables’.

 

References:
http://sureshk37.wordpress.com/2009/09/07/mysql-backup-and-restore-using-java/
http://forums.mysql.com

About these ads

96 responses to “MySQL Database Backup & Restore Using Java

  1. package files;

    import java.util.*;
    import java.io.*;
    public class tableBackup_1 {
    public boolean tbBackup(String dbName, String tbName, String dbUserName, String dbPassword, String path) {

    String executeCmd = “mysqldump -u ” + dbUserName + ” -p” + dbPassword +””
    + dbName + “–tables” + tbName + ” -r ” + path;

    Process runtimeProcess;
    try {
    runtimeProcess = Runtime.getRuntime().exec(executeCmd);
    int processComplete = runtimeProcess.waitFor();

    if (processComplete == 0) {
    System.out.println(“Backup created successfully”);
    return true;
    } else {
    System.out.println(“Could not create the backup”);
    }
    } catch (Exception ex) {
    ex.printStackTrace();
    }
    return false;
    }
    public static void main(String[] args){
    tableBackup_1 bb = new tableBackup_1();
    bb.tbBackup(“nsetrans”,”price”, “root”, “sa”, “C:/New Folder/table.sql”);

    }
    }

    ******** not working pls. what am i doing wrong?

    • Replace your executeCmd with this:

      String executeCmd = "mysqldump -u " + dbUserName + " -p" + dbPassword + " "
      + dbName + " --tables " + tbName + " -r " + path;

      And change your path like this: "\"C:/New Folder/table.sql\""

      when your folder name has a space, you should include the path within double quotations.
      should work fine

  2. Thank you for answer, I have question,
    in my computer problem with mysqldump
    java.io.IOException: Cannot run program “mysqldump”: CreateProcess error=2, The system cannot find the file specified

    • Check whether you have given the path to the dump file correctly. If it doesn’t solve your problem, post your code here. I can check it for you if you like.

    • Follow this example:

      String[] executeCmd = new String[]{"mysql", "--host=" + dbIP, "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", "source "+source};

      Replace ‘dbIP’ with the IP of your server (ex: 192.168.1.25). The username you’re using should have privileges to access the database remotely.

      • but i have to execute this code on the from remote pc right??
        i am creating a backup of db in pc1 and from pc1 i wnt to restore it to pc2.
        so what do i do??

  3. Got this problem on windows xp. what is the cause.

    ERROR com.busytech.barba.exception.BarbaException – java.io.IOException: Cannot run program “C:\Program”: CreateProcess error=2, The system cannot find the file specified
    com.busytech.barba.exception.BarbaException: Cannot run program “C:\Program”: CreateProcess error=2, The system cannot find the file specified
    at com.busytech.barba.persistence.BackUpAndRestoreDatabaseManager.getTableBackup(BackUpAndRestoreDatabaseManager.java:52)
    at com.busytech.barba.servlet.checkcash.CashSummaryServlet.processRequest(CashSummaryServlet.java:95)
    at com.busytech.barba.servlet.checkcash.CashSummaryServlet.doPost(CashSummaryServlet.java:495)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:864)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1665)
    at java.lang.Thread.run(Unknown Source)
    java.io.IOException: Cannot run program “C:\Program”: CreateProcess error=2, The system cannot find the file specified
    at java.lang.ProcessBuilder.start(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
    at com.busytech.barba.persistence.BackUpAndRestoreDatabaseManager.getTableBackup(BackUpAndRestoreDatabaseManager.java:30)
    at com.busytech.barba.servlet.checkcash.CashSummaryServlet.processRequest(CashSummaryServlet.java:95)
    at com.busytech.barba.servlet.checkcash.CashSummaryServlet.doPost(CashSummaryServlet.java:495)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:864)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1665)
    at java.lang.Thread.run(Unknown Source)
    Caused by: java.io.IOException: CreateProcess error=2, The system cannot find the file specified
    at java.lang.ProcessImpl.create(Native Method)
    at java.lang.ProcessImpl.(Unknown Source)
    at java.lang.ProcessImpl.start(Unknown Source)
    … 21 more
    347156 [http-8080-4] ERROR com.busytech.barba.exception.BarbaException – java.io.IOException: Cannot run program “C:\Program”: CreateProcess error=2, The system cannot find the file specified
    com.busytech.barba.exception.BarbaException: Cannot run program “C:\Program”: CreateProcess error=2, The system cannot find the file specified
    at com.busytech.barba.persistence.BackUpAndRestoreDatabaseManager.getTableBackup(BackUpAndRestoreDatabaseManager.java:52)
    at com.busytech.barba.servlet.checkcash.CashSummaryServlet.processRequest(CashSummaryServlet.java:95)
    at com.busytech.barba.servlet.checkcash.CashSummaryServlet.doPost(CashSummaryServlet.java:495)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:864)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1665)
    at java.lang.Thread.run(Unknown Source)

    • From the look at it, it may be an error with your backup file path. Usually a path is given as follows in case you don’t know "\"C:/New Folder/table.sql\""

      If the problem still exist, put your Backup/Restore method as here. I’ll try my best to find the error.

  4. hello,
    I tested this code on windows, but it doesn’t work
    java.io.IOException: Cannot run program “mysqldump”: CreateProcess error=2
    maybe because before executing mysqldump in the windows command line i have to specify it’s path .Otherwise it worked perfectly on linux.
    Can you tell me what is the problem!
    sorry for my english :$ and thank you for this blog it’s really interesting

  5. String[] executeCmd = new String[]{“mysql”, “–user=” + dbUserName, “–password=” + dbPassword, “-e”, “source “+”D:/backup.sql\””};

    i need to get back up of all the data base.my back file is in D drive.i got the like this exeption. java.io.IOException: Cannot run program “mysqldump”: CreateProcess error=2, The system cannot find the file specified .
    can you help me to solve that exception. thank you very much for your attention.

    • Sorry for taking so long to reply mac. There’s a small error in your ‘path’ from what I can see. Try replacing the statement as follows

      String[] executeCmd = new String[]{“mysql”, “–user=” + dbUserName, “–password=” + dbPassword, “-e”, “source ” + “\”D:/backup.sql\””};

  6. i develop my application test as you discuss as that :

    Backup class that contain backup method :

    public class Backup {

    public Backup() {
    }

    public boolean setBackup(String username,String password,String dbName,String path){
    try {
    String backupCommand=”mysqldump -u root -p e*ce11enceg@te db1 -r \”D:/mine/backup.sql\””;
    Process myProcess=Runtime.getRuntime().exec(backupCommand);
    int processComplete = myProcess.waitFor();
    if(processComplete==0){
    System.out.println(“backup executed”);
    return true;
    }else{
    System.out.println(“backup not executed”);
    }

    } catch (InterruptedException ex) {
    Logger.getLogger(Backup.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
    Logger.getLogger(Backup.class.getName()).log(Level.SEVERE, null, ex);
    }
    return false;
    }
    }

    Main method :

    public static void main(String[] args) {
    Backup backup=new Backup();
    boolean setBackup = backup.setBackup(“root”, “e*ce11enceg@te”, “db1″, “\”D:/mine/SQLBackup/db1.sql\””);
    if(setBackup){
    System.out.println(“success”);
    }else{
    System.out.println(“fail”);
    }
    }

    But i have an error that :

    Jul 27, 2012 5:37:43 PM backuptest.Backup setBackup
    fail
    SEVERE: null
    java.io.IOException: Cannot run program “mysqldump”: CreateProcess error=2, The system cannot find the file specified
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:1029)
    at java.lang.Runtime.exec(Runtime.java:615)
    at java.lang.Runtime.exec(Runtime.java:448)
    at java.lang.Runtime.exec(Runtime.java:345)
    at backuptest.Backup.setBackup(Backup.java:24)
    at backuptest.BackupTest.main(BackupTest.java:18)
    Caused by: java.io.IOException: CreateProcess error=2, The system cannot find the file specified
    at java.lang.ProcessImpl.create(Native Method)
    at java.lang.ProcessImpl.(ProcessImpl.java:189)
    at java.lang.ProcessImpl.start(ProcessImpl.java:133)
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:1021)
    … 5 more

    i don’t know if there is some thing error that i make

    • Try replacing ‘backupCommand‘ String as following. There shouldn’t be a space after ‘-p

      String backupCommand = "mysqldump -u root -pe*ce11enceg@te db1 -r \”D:/mine/backup.sql\”";

      Also make sure there is a folder ‘mine’ in Driver ‘D:’

      • thanks omindu for your help , but i found the error and solve it that write the path of “mysqldump” as that :

        C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysqldump.exe

        that code couldn’t run until i write it

        thanks for your help again

      • Glad it worked. And thanks a lot for your information. Lots of folks were having a similar problem and it never occurred to me that the problem might be with the mysql path.

  7. Hi, Really nice program. But when i run this in servlet program with tomcat 6 server in ubuntu 10.04 server, its not working. What i have to do if i want run it from server ? i tried lot. it will work with simple program but not in server. Please help.

    • Hello Arun,
      Sorry for taking a long time to reply. I am not much familiar with servlet programs. I hope you found a solution for your problem. Good Luck

    • Hello Shruthi,
      Sorry for taking too long to reply. I couldn’t find a direct way to create such a dump. May be you can create a ‘view’ and create a dump for that view. I have never tried this though. But it might work Please leave a comment if it works. :)

  8. Hello, I have tried some of your code and it works fine. Thanks for that.
    I wonder if there is a code for remote back up. In other words I want to back up database from remote computer. I saw there is code for remote restore…

    • you can try something like this:

      mysqldump -u Username -pPassword --add-drop-database -h hostIP -B databaseName -r backupPath

      Hope this help. Reply If you need a further clarification.

      • Thanks… it works fine.
        Just to pinpoint as to my current knowledge if I want to back up or restore database from remote computer I think that i have to have MySQL server instaled on that remote computer in order that I can use mysqldump..
        …or not…?

  9. thanks for this.
    i have a problem my back code works fine but wen i use the restore code it dosent work. i will post my code if u can please help me.

    public boolean restoreDB(String dbName, String dbUserName, String dbPassword, String source) {

    String[] executeCmd = new String[]{“C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysql.exe”, “–user=” + dbUserName, “–password=” + dbPassword, dbName,”-e”, “source=”+source};

    Process runtimeProcess;

    try {

    runtimeProcess = Runtime.getRuntime().exec(executeCmd);

    int processComplete = runtimeProcess.waitFor();

    if (processComplete == 0) {

    System.out.println(“Backup restored successfully”);

    return true;

    } else {

    System.out.println(“Could not restore the backup”);

    }

    }
    catch (Exception e) {
    JOptionPane.showMessageDialog(this, “”+e.getMessage()+” Error, !”,””,JOptionPane.OK_OPTION);
    }

    return false;

    }

    • Hello Pubudu,
      I think I have found the error. Replace your executCmd as follows
      String[] executeCmd = new String[]{"C:\\Program Files\\MySQL\\MySQL Server 5.5\\bin\\mysql.exe", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", "source "+source};

      Note that there’s No ‘=’ after the ‘source’

      Hope this helps.

  10. Omindu, thank you so much for this article, after more than one year this article still very very useful. I tried to lot of codes before found your article, thanx very much again

    eranga

  11. java.io.IOException: Cannot run program “mysql”: CreateProcess error=2, The system cannot find the file specified
    at java.lang.ProcessBuilder.start(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
    at java.lang.Runtime.exec(Unknown Source)
    at com.nvl.admindao.restoreDB.restoreDB(restoreDB.java:19)
    at com.nvl.admindao.restoreDB.main(restoreDB.java:53)
    Caused by: java.io.IOException: CreateProcess error=2, The system cannot find the file specified
    at java.lang.ProcessImpl.create(Native Method)
    at java.lang.ProcessImpl.(Unknown Source)
    at java.lang.ProcessImpl.start(Unknown Source)
    … 5 more
    Data is restored unsuccessfully

    Please Note: – your code shows above exceptions

  12. NIce thread of comments. I find the command line utility not useful when the path is not set to the MySQL bin folder. So I created a solution using simple JDBC statements. here it is. Hope it will useful to someone here.

    ResultSet rs = query(“SHOW FULL TABLES WHERE Table_type != ‘VIEW’”);
    while (rs.next()) {
    String tbl = rs.getString(1);

    sb.append(“\n”);
    sb.append(“– —————————-\n”)
    .append(“– Table structure for `”).append(tbl)
    .append(“`\n– —————————-\n”);
    sb.append(“DROP TABLE IF EXISTS `”).append(tbl).append(“`;\n”);
    ResultSet rs2 = query(“SHOW CREATE TABLE `” + tbl + “`”);
    rs2.next();
    String crt = rs2.getString(2) + “;”;
    sb.append(crt).append(“\n”);
    sb.append(“\n”);
    sb.append(“– —————————-\n”).append(“– Records for `”).append(tbl).append(“`\n– —————————-\n”);

    ResultSet rss = query(“SELECT * FROM ” + tbl);
    while (rss.next()) {
    int colCount = rss.getMetaData().getColumnCount();
    if (colCount > 0) {
    sb.append(“INSERT INTO “).append(tbl).append(” VALUES(“);

    for (int i = 0; i 0) {
    sb.append(“,”);
    }
    String s = “”;
    try {
    s += “‘”;
    s += rss.getObject(i + 1).toString();
    s += “‘”;
    } catch (Exception e) {
    s = “NULL”;
    }
    sb.append(s);
    }
    sb.append(“);\n”);
    buff.append(sb.toString());
    sb = new StringBuilder();
    }
    }
    }

    ResultSet rs2 = query(“SHOW FULL TABLES WHERE Table_type = ‘VIEW’”);
    while (rs2.next()) {
    String tbl = rs2.getString(1);

    sb.append(“\n”);
    sb.append(“– —————————-\n”)
    .append(“– View structure for `”).append(tbl)
    .append(“`\n– —————————-\n”);
    sb.append(“DROP VIEW IF EXISTS `”).append(tbl).append(“`;\n”);
    ResultSet rs3 = query(“SHOW CREATE VIEW `” + tbl + “`”);
    rs3.next();
    String crt = rs3.getString(2) + “;”;
    sb.append(crt).append(“\n”);
    }

    buff.flush();
    buff.close();
    } catch (Exception e) {
    e.printStackTrace();
    }
    }

    Full resource is at http://isuru.diyatha.com/java-db-dump/.

  13. Pingback: Criando dump do MySQL com java no Linux | Cláudio HMB – Developer

  14. fine work thanks alot after a long chase i finally able to restore me database thanks again

  15. HI Dude,
    i’m try to do a backup of my database but my code not work,i have some error.
    this is the code
    public boolean tbBackup(String dbName, String dbUserName, String dbPassword, String path) {

    String[] executeCmd = new String []{ “mysqldump -u ” + dbUserName + ” -p ” + dbPassword + ” –add-drop-database -B ” + dbName + ” -r ” +path};

    Process runtimeProcess;
    try {
    runtimeProcess = Runtime.getRuntime().exec(executeCmd);

    int processComplete = runtimeProcess.waitFor();
    if(processComplete == 0)
    {
    System.out.println(“Backup Réalisé avec Success”);
    return true;
    }else{

    System.out.println(“Impossible de Réaliser le Backup Système!!!”);
    }
    } catch (Exception e) {
    e.printStackTrace();
    // TODO: handle exception
    }

    public static void main(String[] args) {
    BackupRestor backupRestor = new BackupRestor();
    backupRestor.tbBackup(“chantier”, “root”, “”, “\”C:/Torrent Stream/table.sql\””);

    }

    The are is:

    java.io.IOException: CreateProcess: “mysqldump -u root -p –add-drop-database -B chantier -r “C:\Torrent Stream\table.sql”” error=2
    at java.lang.ProcessImpl.create(Native Method)
    at java.lang.ProcessImpl.(ProcessImpl.java:81)
    at java.lang.ProcessImpl.start(ProcessImpl.java:30)
    at java.lang.ProcessBuilder.start(ProcessBuilder.java:451)
    at java.lang.Runtime.exec(Runtime.java:591)

    Please help me,Thank

    • Try replacing “String[] executeCmd” with:
      String executeCmd = "mysqldump -u " + dbUserName + " -p" + dbPassword + " --add-drop-database -B " + dbName + " -r " + path;

  16. public class tableBackup_1 {
    public boolean tbBackup() {

    String[] Cmd = new String[]{“C:\\wamp\\bin\\mysql\\mysql5.0.51b\\bin” , “mysqldump -u root -p nepldb -r D:\\backup\\db_backup” };

    Process runtimeProcess;
    try {
    runtimeProcess = Runtime.getRuntime().exec(Cmd);
    int processComplete = runtimeProcess.waitFor();

    if (processComplete == 0) {
    System.out.println(“Backup created successfully”);
    return true;
    } else {
    System.out.println(“Could not create the backup”);
    }
    } catch (Exception ex) {
    ex.printStackTrace();
    }
    return false;
    }
    }

    java.lang.NoSuchMethodError: main
    Exception in thread “main”

      • Have you declared your main() correctly?
        public static void main(String[] args) {
        new tableBackup_1().tbBackup();
        }

        Usually this error occurs when there’s an error with the main() declaration.

  17. Hi, m trying to restore database file. But I am unable to do. Following is my code.

    package restore;

    public class Restore{

    public static boolean restoreDB(String source) {

    String dbUserName= “root”;
    String dbPassword=””;
    String dbName=”tushar”;
    String[] executeCmd = new String[]{“C:\\xampp\\mysql\\bin\\mysql”, “–user=” + dbUserName, “–password=” + dbPassword, dbName,”-e”, “source “+source};

    Process runtimeProcess;
    try {

    runtimeProcess = Runtime.getRuntime().exec(executeCmd);
    int processComplete = runtimeProcess.waitFor();
    System.out.println(“processComplete “+processComplete);
    if (processComplete == 0) {
    System.out.println(“Backup restored successfully”);
    return true;
    } else {
    System.out.println(“Could not restore the backup”);
    }
    } catch (Exception ex) {
    ex.printStackTrace();
    }

    return false;
    }

    public static void main(String[]args){

    new Restore().restoreDB(“C:/Users/Tushar/Downloads/tushar.sql”);
    }

    }

    Output:
    processComplete 1
    Could not restore the backup

    Plz Help.
    Thanks in advance.

    • Sorry for replying so late Tushar. Try using this line. It’s working fine in my PC.
      executeCmd = new String[]{"C:\\xampp\\mysql\\bin\\mysql", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", "source "+source};

  18. Hello!

    Thanks for this post.

    I only copied your method backupDB.
    Indeed this method is running for me but unfotunately forever. It does not want to stop and red icon in eclipse console says about it. I think the problem is in method waitFor but if I substitute it with exitValue I reeive exception
    java.lang.IllegalThreadStateException: process has not exited
    So I must use waitFor method.
    Couldn’t you tell me what may cause waitFor method to run forever?

    Thanks!

    • Sorry volodiaL. I can’t think of anything that might be causing the error. I have never encountered that error while working with the code. Hope you’ll manage to find a solution.

  19. Could help me please?
    my program :
    String database=”db1″;
    String user=”root”;
    String pass=””;
    String path=”D:\\table.sql”;
    String path1=”C:\\Program Files\\EasyPHP5\\mysql\\bin\\mysqldump.exe”;

    String dumpCommand =path1+” –database ” + database + ” -u ” + user +” -r ” + path;

    String executeCmd = path1 + ” -u ” + user +” -p ” + pass + database + ” -r ” + path;
    Process runtimeProcess;
    try {

    runtimeProcess = Runtime.getRuntime().exec(dumpCommand);
    int processComplete = runtimeProcess.waitFor();

    if (processComplete == 0) {
    System.out.println(“Backup created successfully”);

    } else {
    System.out.println(processComplete+” Could not create the backup”);
    }
    } catch (Exception ex) {
    ex.printStackTrace();
    and my program dosen’t create my file and it display: 2 could not create.
    thanks

  20. Pls Help me………

    My Program:

    class Backup {

    public boolean backupDB() {
    String user = “root”;
    String pass = “password”;
    String db = “school”;
    String path = “\”E:/DBbackp/backup.%DATE:~10,4%%DATE:~7,2%%DATE:~4,2%.sql\””;
    //System.out.println(“”);
    //System.out.println(“Could not create the backup”);
    //System.out.println(“Could not create the backup”);
    //System.out.println(“Could not create the backup”);

    String executeCmd = “mysqldump -u ” + user + ” -p” + pass + ” –add-drop-database -B ” + db + ” -r ” + path;

    Process runtimeProcess;
    try {

    runtimeProcess = Runtime.getRuntime().exec(executeCmd);
    int processComplete = runtimeProcess.waitFor();

    if (processComplete == 0) {
    System.out.println(“Backup created successfully”);
    return true;
    } else {
    // Runtime.getRuntime().exec(“mysql-backup.bat”);
    System.out.println(“Could not create the backup”);
    }
    } catch (Exception ex) {
    ex.printStackTrace();
    }

    return false;
    }
    }

    Output:
    Could not create the backup

    pls help…

    Thanks in Advance.,

  21. Both options that you write for restoring the database are equal. I found my own solution using a comand like this:
    “cmd.exe /C ” + “C:\\wamp\\bin\\mysql\\mysql5.6.12\\bin\\mysql –user=” + Username + ” –password=” + Password + ” –default-character-set=utf8 –comments” + ” < " + Path ;

    I hope it will help somebody.
    Doing it like this mysql is able to read some of the lines inside the .sql that are encoded in a different way.
    Good luck!

  22. Backing up process is taking too much time which is making my web app hang but when i used same code in cmd prompt it backs up in a sec what might be the problme any idea?

    • I had the same problem and this is how I solved, my code goes like this…

      ////////////////////////////////////////////////////////////////////////////////////////////////////////////
      String executeCmd = “your mysqldump or mysql command”;

      Process runtimeProcess = Runtime.getRuntime().exec(executeCmd);
      InputStream is = runtimeProcess.getInputStream();

      int byteRead = -1;
      while ((byteRead = is.read()) != -1) {
      System.out.print((char)byteRead );
      }

      int processComplete = runtimeProcess.waitFor();
      if (processComplete == 0) {

      // this is when the backing up is done successfully.
      }
      ///////////////////////////////////////////////////////////////////////////////////////////////////////////

      hope this helps… good luck

  23. I am using external MySQL server means not installed MySQL server in PC for my desktop application now i want export and import database from server present in E folder.your code running perfectly for installed MySQL server. I am using following path but it does not work.
    String executeCmd = ” mysqldump -u ” + dbUserName + ” -p” + dbPassword + ” –add-drop-database -B ” + dbName + ” -r ” + path;

      • This is my BackUp function

        public static boolean backupDB(String dbName, String dbUserName, String dbPassword, String path) {
        String executeCmd = “mysqldump.exe -u ” + dbUserName + ” -p ” + dbPassword + ” –add-drop-database -B ” + dbName + ” -r ” + path;
        Process runtimeProcess;
        try {
        runtimeProcess = Runtime.getRuntime().exec(executeCmd);
        int processComplete = runtimeProcess.waitFor();

        if (processComplete == 0) {
        System.out.println(“Backup created successfully”);
        return true;
        } else {
        System.out.println(“Could not create the backup”);
        }
        } catch (Exception ex) {
        ex.printStackTrace();
        }
        return false;
        }

        With this empty sql file is created.

      • I’m not sure what’s causing the error. Can you try replacing your executeCmd exactly as follows.

        "mysqldump.exe -u " + dbUserName + " -p" + dbPassword + " --add-drop-database -B " + dbName + " -r " + path;

    • I am not using MySQL installed on computer.I create process which start specified MySQL by following code

      StartMysqlServer.p = Runtime.getRuntime().exec(“server/bin/mysqld –defaults-file=server/my.ini –standalone –console –basedir=server/”);

      I want database Import,Export present in this started MySQL server.

      • Thanks to omindu,Amit ranjan. I have solved problem.Before this Mysql server has no password now i set password and run then i got back up of database suceesfully.

    • I exported database successfully but when i import that then i get message backup successfully restore.when i see database then it is empty but when i use another sql file created by cmd then restore successfully and there is data in database.so i think problem in my export i used following code public static boolean backupDB(String dbName, String dbUserName, String dbPassword, String path) {
      Process runtimeProcess;
      String executeCmd=”mysqldump -d -u ” + dbUserName + ” -p” + dbPassword + ” –add-drop-database -B ” + dbName + ” -r ” + path;
      try {
      runtimeProcess = Runtime.getRuntime().exec(executeCmd);
      int processComplete = runtimeProcess.waitFor();

      if (processComplete == 0) {
      System.out.println(“Backup created successfully”);
      return true;
      } else {
      System.out.println(“Could not create the backup”);
      }
      } catch (Exception ex) {
      ex.printStackTrace();
      }
      return false;
      }

    • I have another query if i used MySQL Server present at path D:/Copies/10-10-2013/server – Copy (2)/bin> then how i run dump command on this path means i want import/export databases present in mentioned path Server.

      • You can use something like this,
        String[] executeCmd = new String[]{"D:\\Copies\\10-10-2013\\server – Copy (2\\bin\\mysql.exe", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", "source "+source};

  24. my backup java program is successfully compile and run……..but output is not able to satisfied. database have table.

    output is :could not create the backup

    please help me

  25. Please help

    my backup java program is successfully compile and run……..but output is not import java.util.*;
    import java.sql.*;
    public class DbbackUp
    {
    public boolean backupDB(String dbName,String dbUserName,String dbPassword, String path)
    {
    String executeCmd =”mysqldump -u” + dbUserName + ” -p” + dbPassword + ” databaseName ” + dbName + ” -r ” +path;
    Process runtimeProcess;
    try {

    //runtimeProcess = Runtime.getRuntime().exec(executeCmd);
    runtimeProcess=Runtime.getRuntime().exec(new String[] { “cmd.exe”, “/c”, executeCmd });
    int processComplete = runtimeProcess.waitFor();

    if (processComplete == 0) {
    System.out.println(“Backup created successfully”);
    return true;
    } else {
    System.out.println(“Could not create the backup”);
    }
    } catch (Exception ex) {
    ex.printStackTrace();
    }

    return false;
    }
    public static void main(String args[])
    {
    DbbackUp db=new DbbackUp();
    db.backupDB(“root”,”12345″,”test”,”\”C:/New Folder/employeewe.sql\””);
    }
    }

    output: Could not create the backup.

    i had changed the file name to a similar but still i am getting same output
    please help me

    • Hello Amit,
      Please add these lines on the top of your “try block ”

      ////////////////////////////////////////////////////////////////////////////////////////
      InputStream is = runtimeProcess.getInputStream();

      int byteRead = -1;
      while ((byteRead = is.read()) != -1) {
      System.out.print((char)byteRead );
      }
      ////////////////////////////////////////////////////////////////////////////////////////

      And check the output.

      • Noticed a few errors in your runtime execution. Try the following,

        String executeCmd = "mysqldump -u " + dbUserName + " -p" + dbPassword + " " + dbName + " -r " + path;

        Also check whether you are passing the correct arguments for your backupDB function.
        Looks to me that you have mistakenly swapped the database and username values.

        One more thing, sometimes windows doesn’t allow you to write files to the windows partition if you are not running your IDE as an administrator. Try saving the file in a different partition.

  26. Hi Omindu,

    I’ve followed your code with minor modifications but my program could not complete the backup process. Here’s my code:

    public int backupDb()
    {
    String executeCmd = “\”C:\\Program Files\\MySQL\\MySQL Server 5.6\\bin\\mysqldump\” –add-drop-database –add-drop-table -h localhost -u=root -p=root -r c:\\backup\\backup.sql –dump-date –databases salondb”;
    Process runtimeProcess;
    try
    {
    runtimeProcess = Runtime.getRuntime().exec(executeCmd);
    int processComplete = runtimeProcess.waitFor();
    if (processComplete != 0)
    return 1; // backup failed
    }
    catch (Exception ex)
    {
    ex.printStackTrace();
    }

    // backup successful
    return 0;
    }

    The backup file is created in folder C:\backup but it does not contain anything, as in 0 bytes file size. I tried running the command in a command window and got this error:

    mysqldump: Got error: 1045: Access denied for user ‘=root’@’localhost’ (usi
    ng password: YES) when trying to connect.

    I’m already using root for crying out loud! Double-checked root’s permission and roles, everything’s allowed on any database.

    Any ideas why this is? I use Windows 7, Glassfish 4.0.1 and MySQL v5.6.14. Thank you in advance.

  27. I have query, I export database successfully now i want to export a database but some tables with data and remaining tables should be empty.Is this possible?

  28. It’s not working here …

    executeCmd =”mysqldump -u root –all-databases > G:\\dump.sql”;

    It Simply Prints :- Could not create the backup

  29. hey…i am getting a problem while taking backup from remote mysqlserver…i followed ur code and instruction..its working on local machine but not on remote..

    plz reply if u can help..

    public class back
    {
    public static void main(String args[])
    {
    Process p = null;
    try {
    Runtime runtime = Runtime.getRuntime();
    p = runtime.exec(“C:/Program Files (x86)/MySQL/MySQL Server 5.0/bin/mysqldump -h remotehostip -u root -ppassword –add-drop-database -B dbname -r D:\\abc.sql”);
    int processComplete = p.waitFor();

    if (processComplete == 0) {

    System.out.println(“Backup created successfully!”);

    } else {
    System.out.println(“Could not create the backup”);
    }

    } catch (Exception e) {
    e.printStackTrace();
    }
    }
    }

    output is : could not create backup…

  30. this code not worked on windows 8..it says cannot find path..what should i do now ?..
    here is my code example –>>>

    try {
    //
    File file = new File(“C://AgencyBackup”);
    if (!file.exists()) {
    file.mkdir();
    }

    String path = file.getAbsolutePath() + “/” + new SimpleDateFormat(“yyyy-MM-dd_hh.mm a”).format(new Date()) + “.sql”;

    String execute = ” -u ” + “root” + ” -p” + “123″ + ” –add-drop-database -B ” + “agent” + ” -r ” + path;
    Process proccess = Runtime.getRuntime().exec(“C:/Program Files (x86)/MySQL/MySQL Server 5.1//bin/mysqldump” + execute.trim());
    int success = proccess.waitFor();

    if (success == 0) {
    JOptionPane.showMessageDialog(null, “Backup Credted Successfully.”, “SUCCESS MESSAGE MESSAGE”, JOptionPane.INFORMATION_MESSAGE);

    } else {
    JOptionPane.showMessageDialog(null, “Could Not Create The Backup”, “ERROR MESSAGE”, JOptionPane.ERROR_MESSAGE);
    }
    } catch (IOException ex) {
    model.writeLog(ex);
    ex.printStackTrace();
    JOptionPane.showMessageDialog(null, “File : ” + ex.getMessage(), “ERROR MESSAGE”, JOptionPane.ERROR_MESSAGE);
    } catch (InterruptedException ex) {
    model.writeLog(ex);
    ex.printStackTrace();
    JOptionPane.showMessageDialog(null, “Backup : ” + ex.getMessage(), “ERROR MESSAGE”, JOptionPane.ERROR_MESSAGE);
    }

  31. I was using the following instead of using String[], but it didn’t work and took forever to run the restore. Just wondering why?

  32. String executeCmd = “mysql -u ” + dbUserName + ” -p” + dbPassword + ” ” + dbName + ” < " + source;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s