I am developing a Java application with MySql as the database. I have to dump the MySql database from my application periodically(let say every day at 10 a.m.) and I have written a batch (.bat) file for dumping the database. The batch file is working fine, but the problem is that it is asking for password each time during its execution.
Is there any way to dump MySql database without prompting for password and achieve it from Java application periodically?
You can definitely put the password on the command line, as others have pointed out. As far as getting it to run from Java (though the purists may object), you can use Runtime.exec() to just call your mysql command: http://java.sun.com/javase/6/docs/api/java/lang/Runtime.html.
If you're doing it from within your java app, you might also want to consider not just doing it periodically, though. Depending on your database usage (for instance frequent reads, but infrequent writes), you may find that you can dump only when you know that the database has been changed, or something along those lines.
If you really want to just dump the database periodically though, you might be better off just setting up a cron-job. Here's a previous post on SO regarding the Windows approach to cron: What is the Windows version of cron?.
Backup:
/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;
/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;
executeCmd = “mysqldump -u “+dbUser+” -p”+dbPass+” “+dbName+” -r backup.sql”;
}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){
out.println(“Backup taken successfully”);
} else {
out.println(“Could not take mysql backup”);
}
Restore:
/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;
/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;
executeCmd = new String[]{“/bin/sh”, “-c”, “mysql -u” + dbUser+ ” -p”+dbPass+” ” + dbName+ ” < backup.sql” };
}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){
out.println(“success”);
} else {
out.println(“restore failure”);
}
You'll want the following option for mysqldump
--password[=password], -p[password]
You don't tell how you are actually dumping the database, but I'll assume that you are using mysqldump.
You can specify the database password on the command line using the switch --password=.... Or if you want to be more secure, use a password in an option file. See man mysqldump for more information.
