Backup mySQL stored procedures in Java application
Question: I’ve just read your post of “Backup mySQL database with Java desktop or Android application” but you have not given us a sample how to backup store procedures from the database. Can it be done?
Answer: Yes. We can dump stored procedures and triggers as well. The previous post is here: http://www.weston-fl.com/blog/?p=2288
mysqldump backups by default all the triggers but not the stored procedures (functions). There are two mysqldump parameters that we must pay attention to: -routines and –triggers. The –triggers is set to true by default. Add only the –routines command line parameter to the argument list to backup the triggers and stored procedures as well.
For details look at the mysqldump options:
is here.
private int STREAMBUFFER_SIZE = 128000;
public String getServerRoutineData(String host, String port, String user, String password, String db){
StringBuilder serverRoutinedData = new StringBuilder();
try{
if( host!=null && user!=null && password!=null && db!=null){
// Set your path to where mysqldump is
String execline;
if( isOSX() ){ // Filter Android version here when you run this code on this OS
// OS X
execline = System.getProperty("user.dir") + "/mysqldump";
}else{
// Windows
execline = System.getProperty("user.dir") + "\\mysqldump.exe";
}
// Usage: mysqldump [OPTIONS] database [tables]
// OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
// OR mysqldump [OPTIONS] --all-databases [OPTIONS]
// Let’s assume we want to backup only the stored procedures and triggers and not the
// mysql tables and data here. I recommend this separated solution.
String command[] = new String[]{ execline,
"--host=" + host,
"--port=" + port,
"--user=" + user,
"--password=" + password,
"--compact",
"--skip-comments",
"--no-create-info",
"--no-data",
"--routines", // <-------
db };
// Run mysqldump
ProcessBuilder pb = new ProcessBuilder(command);
Process process = pb.start();
// Read stream
InputStream in = process.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(in));
StringBuffer temp = new StringBuffer();
int count;
char[] cbuf = new char[STREAMBUFFER_SIZE];
while ((count = br.read(cbuf, 0, STREAMBUFFER_SIZE)) != -1){
serverRoutinedData.append(cbuf, 0, count);
}
br.close();
in.close();
}
}catch (Exception ex){
ex.printStackTrace();
}
return serverRoutinedData.toString();
}