How to restore a MySQL database backup using Java

Question

I was able to create a backup of my current mysql database as .SQL file using the mysqldump.exe with the help of the following java code.

 Process runProcess = Runtime.getRuntime().exec("C:\\SCM Files\\SQL Backup\\mysqldump.exe -uroot -p123 rr -r\"C:\\SCM Files\\SQL Backup\\RR.sql");

Now I want to restore this same .SQL Backup file to mysql database using java code similar to above on the event of a button clicked.

Thanks a lot :)

So now I tried this ; Process runProcess = Runtime.getRuntime().exec("C:\\SCM Files\\SQL Backup\\mysqldump.exe -uroot -p123 rr < C:\\SCM Files\\SQL Backup\\RR.sql"); Still it didn't work :/

Duplicate: stackoverflow.com/questions/4755552/… (edit: this is not really related to Java, you can launch any command this way, of course.) — Martin Klinke
– Martin Klinke, Commented Feb 1, 2012 at 10:13

Torkashvand · Accepted Answer · 2012-03-30 15:21:00Z

5

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;  
}

source example : "E:\\My Backup\\Test\\file.sql"

answered Mar 30, 2012 at 15:21

Torkashvand

4267 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arshed · Accepted Answer · 2012-02-01 10:22:32Z

2

Runtime.getRuntime().exec("mysql -u username -ppassword database_name  FILE.sql")

This statement will regenerate database from the file

edited Feb 1, 2012 at 10:22

user647772

answered Feb 1, 2012 at 10:17

Arshed

3412 silver badges14 bronze badges

1 Comment

KCD Over a year ago

more specifically -e "\. file.sql" or -e "source file.sql" see batch-mode but use a String array

Ravindra Gullapalli · Accepted Answer · 2012-02-01 10:17:02Z

You have to use java swings where you can design forms. Here is some code which can do that.

import javax.swing.JFrame;

public final class RestoreMySQLDatabase extends JFrame {
    private static final long serialVersionUID = 1L;
    public static void main(String[] args) {
        RestoreMySQLDatabase restoreMySQL = new RestoreMySQLDatabase();
        restoreMySQL.setTitle("Restore mysql database");
        javax.swing.JButton butRestore = new javax.swing.JButton("Restore");
        butRestore.addActionListener(new java.awt.event.ActionListener(){
            public void actionPerformed(java.awt.event.ActionEvent event){
                try{
                    Runtime.getRuntime().exec("C:\\SCM Files\\SQL Backup\\mysqldump.exe -uroot -p123 rr -r\"C:\\SCM Files\\SQL Backup\\RR.sql");
                    javax.swing.JOptionPane.showMessageDialog((javax.swing.JButton)event.getSource(), "Successfully restored");
                }catch(java.lang.Exception e){
                    javax.swing.JOptionPane.showMessageDialog((javax.swing.JButton)event.getSource(), "Not able to restore");
                }
            }
        });
    }

}

Disapamok · Accepted Answer · 2017-07-20 10:09:13Z

1

I tried this code and works as perfect!

String[] restoreCmd = new String[]{"mysql ", "--user=" + DB.DB_USERNAME, "--password=" + DB.DB_PASSWORD, "-e", "source " + pathToFile};
    try {
        Process runtimeProcess = Runtime.getRuntime().exec(restoreCmd);
        int processComplete = runtimeProcess.waitFor();
        if (processComplete == 0) {
            System.out.println("Done");
        } else {
            System.out.println("Failed");
        }
    } catch (Exception ex) {
        ex.printStackTrace();
    }

answered Jul 20, 2017 at 10:09

Disapamok

1,4852 gold badges22 silver badges28 bronze badges

Comments

Harry Joy · Accepted Answer · 2012-02-01 10:18:24Z

You should try DbUnit for backup and restore of database.Here is the demo code for that:

    try {
        Class.forName(DBDRIVER);
        Connection jdbcConnection = DriverManager.getConnection(DBURL, DBUSERNAME, DBPASSWORD);
        IDatabaseConnection connection = new DatabaseConnection(jdbcConnection);
        connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY,
                    new MySqlDataTypeFactory());

         //////// Database backup
        ITableFilter filter = new DatabaseSequenceFilter(connection);
        IDataSet dataset = new FilteredDataSet(filter, connection.createDataSet());

        ExcludeTableFilter excludeFilter = new ExcludeTableFilter();
        excludeFilter.excludeTable("DATABASECHANGELOG*");
        IDataSet excludedataset = new FilteredDataSet(excludeFilter, dataset);
        FlatXmlDataSet.write(excludedataset, new FileOutputStream(backupfilename));

        System.out.println("\n Complete backup successful.");
         //////// Database backup


         //////// Database restore
        IDataSetProducer producer = new FlatXmlProducer(new InputSource(restoreFileName));
        IDataSet dataSet = new StreamingDataSet(producer);

        TransactionOperation operation = new TransactionOperation(DatabaseOperation.INSERT);
        operation.execute(connection, dataSet);
         //////// Database restore
    } catch (DatabaseUnitException e) {
        e.printStackTrace();
        flag = false;
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }

isobar · Accepted Answer · 2012-02-01 10:22:51Z

0

Use the same dump to import like this.

Process runProcess = Runtime.getRuntime().exec("C:\\SCM Files\\SQL Backup\\mysqldump.exe -uroot -p123 rr  database_name < "C:\\SCM Files\\SQL Backup\\RR.sql");

edited Feb 1, 2012 at 10:22

user647772

answered Feb 1, 2012 at 10:21

isobar

1,1758 silver badges10 bronze badges

1 Comment

wishman Over a year ago

This didn't work :/

Process runProcess = Runtime.getRuntime().exec("C:\\SCM Files\\SQL Backup\\mysqldump.exe -uroot -p123 rr < C:\\SCM Files\\SQL Backup\\RR.sql");

nikolas · Accepted Answer · 2013-08-08 11:34:50Z

0

For restore use the executeCmd in the form m.Torkashvand provided (array of strings). A working example on how to use these commands from JSP code can be found here

edited Aug 8, 2013 at 11:34

nikolas

9,0139 gold badges54 silver badges74 bronze badges

answered Aug 8, 2013 at 9:47

NithinS

711 silver badge2 bronze badges

Comments

Hemant Thorat · Accepted Answer · 2016-09-29 09:13:09Z

public static void mysqlExport(String host, String port, String user, String password, String dbname, String table, String folder, String query) {

    System.out.println("------ Exporting "+dbname+"."+table+" at "+folder+"---------------------------");
    try {
        String command = "mysqldump --host=" + host + " --port=" + port + " --user=" + user + " --password=" + password + " " + dbname + " " + table + " --where=\"" + query + "\" > " + folder + table + ".sql";
        System.out.println(command);
        int returnValue = executeCommand(Arrays.asList("mysqldump", "--host="+host, "--port="+port, "--user="+user, "--password="+password, dbname, table, "--where="+query), folder + table + ".sql");
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

public static void mysqlImport(String host, String port, String user, String password, String dbname, String table, String folder) {

    System.out.println("------ Importing "+dbname+"."+table+" at "+folder+"---------------------------");
    try {
        String command = "mysql --host=" + host + " --port=" + port + " --user=" + user + " --password=" + password + " " + dbname + " " + table + " < " + folder + table + ".sql";
        System.out.println(command);
        int returnValue = executeCommand(new String[]{"mysql", "--host="+host, "--port="+port,  "--user=" + user, "--password=" + password, dbname, "-e", "source " + folder + table + ".sql"});
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
}

public static int executeCommand(String[] commands) throws IOException
{
    System.out.println(commands.toString());
    Process process = Runtime.getRuntime().exec(commands);
    return dumpProcess(process);
}

public static int executeCommand(List<String> commands, String folder) throws IOException
{
    ProcessBuilder builder = new ProcessBuilder(commands);
    System.out.println(builder.command());
    builder.redirectOutput(new File(folder));
    Process process = builder.start();
    return dumpProcess(process);
}

public static int dumpProcess(Process process) throws IOException
{
    int returnValue = -1;
    try {
        String s = null;
        process.waitFor();
        returnValue = process.exitValue();
        if (returnValue == 0) {
            System.out.println("Command successful !!");
            BufferedReader stdInput = new BufferedReader(new InputStreamReader(process.getInputStream()));
            System.out.println("Here is the standard output of the command:\n");
            while ((s = stdInput.readLine()) != null) {
                System.out.println(s);
            }
        } else {
            System.out.println("Command failed. Exist Status code = "+returnValue);
            BufferedReader stdError = new BufferedReader(new InputStreamReader(process.getErrorStream()));
            System.out.println("Here is the standard error of the command (if any):\n");
            while ((s = stdError.readLine()) != null) {
                System.out.println(s);
            }
        }

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

    return returnValue;
}

Shubham Panchal · Accepted Answer · 2022-02-25 13:01:13Z

0

This is working code

public class Recover {

final static String filepath = "/home/shubhampanchal/Downloads/backup/configuration_files.sql";

public static void main(String[] args) throws Exception {
    try {
        
        String[] restoreCmd = new String[]{"mysql", "-uroot", "-p1", "Student", "-e", "source " + filepath};
        Runtime rt =Runtime.getRuntime();
        rt.exec(restoreCmd);
        System.out.println("Restored successfully!");
        
    } catch(Exception e) {
        e.printStackTrace();
    }
}

}

answered Feb 25, 2022 at 13:01

Shubham Panchal

11 bronze badge

Collectives™ on Stack Overflow

How to restore a MySQL database backup using Java

9 Answers 9

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

Comments

1 Comment

Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related