how to backup and restore mysql database using java

Question

I have a small application which I am going to use to backup and restore a mysql database, coding for backup is as follows and it works properly,

....
String command = "mysqldump --host=" + dataBase.getHost() + " --user=" + dataBase.getUserName() + " --password=" + dataBase.getPassword() + " "
                + dataBase.getDatabaseName() + " -r " + dataBase.getBackupPath() + "/ofm_mnu_backup_" + bkDate + ".sql";
        System.out.println(command);
        Process p = null;
        try {


            Runtime runtime = Runtime.getRuntime();
            p = runtime.exec(command);

            int processComplete = p.waitFor();

            if (processComplete == 0) {

                System.out.println("Backup created successfully");

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

But when I try to restore the database using following codes it is not working, please help

  ........
    String command = "mysqldump --host=" + dataBase.getHost() + " --user="+dataBase.getUserName() + " --password= " + dataBase.getPassword()+""+dataBase.getDatabaseName() + "  " + dataBase.getBackupPath();
        Process p = null;

        try {
            System.out.println(command);
            Runtime runtime = Runtime.getRuntime();
            p = runtime.exec(command);
            int processComplete = p.waitFor();

            if (processComplete == 0) {
                System.out.println("Backup restored successfully");

            } else {
                System.out.println("Could not restore the backup");
            }
     ......

After so many google searches I found the answer for my matter,

  ......
  String[] executeCmd = new String[]{"mysql", [database], "--user=" + [username],"--password=" + [password], "-e", " source " + [absolute path to the sql file]};
  p = Runtime.getRuntime().exec(executeCmd);
  int processComplete = p.waitFor();
  ......

In the above code most important thing is ** " source " + [absolute path to the sql file] ** there shouldn't be a comma between 'source' word and the file path.

this worked for me I hope it'll work for you guys too.

noup I didn't set the path to Mysql_home\bin, because mysql is running as a service in my servers — Harsha
– Harsha, Commented Dec 12, 2011 at 11:40

SuperTron · Accepted Answer · 2011-12-12 06:50:13Z

8

I don't think you are using the mysqldump command quite right. Shouldnt you use < or > characters to denote the backup/restore? This is discussed at length here. Something like this to backup:

String command = "mysqldump --host=" + dataBase.getHost() + " --user=" + dataBase.getUserName() + " --password=" + dataBase.getPassword() + " "
            + dataBase.getDatabaseName() + " > " + dataBase.getBackupPath() + "/ofm_mnu_backup_" + bkDate + ".sql";

and to restore:

String command = "mysqldump --host=" + dataBase.getHost() + " --user=" + dataBase.getUserName() + " --password=" + dataBase.getPassword() + " "
            + dataBase.getDatabaseName() + " < " + dataBase.getBackupPath() + "/ofm_mnu_backup_" + bkDate + ".sql";

edited Dec 12, 2011 at 6:50

answered Dec 12, 2011 at 6:45

SuperTron

4,2537 gold badges40 silver badges62 bronze badges

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

6 Comments

Harsha Over a year ago

-> I changed the backup code as you have mentioned above, it works properly in terminal but it is not working when it is put inside the java code.

SuperTron Over a year ago

Doesnt work as in processcomplete == 0, or some other problem? Also, what OS are you in?

SuperTron Over a year ago

ok, but what happens when you run this? Exception? Processcomplete == 0? says it works but it doesnt?

Harsha Over a year ago

I get no exceptions dude, backup part is working properly but restoring is not working I got the command using system.out when the restoring is occur and then copy it into the terminal and run, then it works properly, String command = "mysqldump --host=" + dataBase.getHost() + " --user=" + dataBase.getUserName() + " --password= " + dataBase.getPassword() + " " + dataBase.getDatabaseName() + " < " + dataBase.getBackupPath(); **********************>>>> mysqldump --host=127.0.0.1 --user=root --password= ofm_mnu_jvs < /home/harsha/Desktop/ofm_mnu_backup_1212201115.sql

Harsha Over a year ago

-> @SuperTron- Processcomplete is not equal to 0 it is 6

|

Petter Friberg · Accepted Answer · 2015-11-24 16:59:40Z

1

I would like to thank you guys for the support on how to restore mysql database with java code. Since none of the codes worked for me, but it gave me a clue to teak it to work.

Below is my final working code.

String[] executeCmd = new String[]{"C:\\xampp\\mysql\\bin\\mysql.exe",mydb, "--user=" + dbUserName, "--password=" + dbPassword, "-e", " source " + source};

edited Nov 24, 2015 at 16:59

Petter Friberg

21.8k10 gold badges67 silver badges116 bronze badges

answered Nov 24, 2015 at 16:36

Ballans

111 bronze badge

Comments

Mark Bramnik · Accepted Answer · 2011-12-12 07:16:42Z

0

Maybe its because of output stream assigned to the terminal... Is it an option to run your mysqldump command inside the cmd? for example

String command = "cmd /K mysqldump..."

you might be also interested to read this article:

Hope, this helps

answered Dec 12, 2011 at 7:16

Mark Bramnik

43k5 gold badges67 silver badges111 bronze badges

Comments

Maik · Accepted Answer · 2014-08-13 12:08:00Z

this really work for restore

 String comando = "C:\\MySQL\\bin\\mysql.exe  --host=localhost --port=3306 --user=root --password=123 < D:\\back.sql";
File f = new File("restore.bat");
FileOutputStream fos = new FileOutputStream(f);
fos.write(comando.getBytes());
fos.close();
Process run = Runtime.getRuntime().exec("cmd /C start restore.bat ");

and for backup

  DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.forLanguageTag("ru"));
    java.util.Date currentDate = new java.util.Date();
    Process p = null;
    try {
        Runtime runtime = Runtime.getRuntime();
        p = runtime.exec("C:\\MySQL\\bin\\mysqldump.exe  --default-character-set=utf8 -uroot -p123 -c  -B shch2 -r " + "D:/" + dateFormat.format(currentDate) + "_backup" + ".sql");

//change the dbpass and dbname with your dbpass and dbname 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();
    }

but you need to convey the exact path to mysql.exe and mysqldump.exe

Collectives™ on Stack Overflow

how to backup and restore mysql database using java

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related