How to take MySql DB backup from java

[Sumeet Chakraborty]

Hi all

I am trying to build a java utility which will enable me to take the backup of my MySql Database. I want the backup to be taken and dumped in a sql file in some location of my file system. Is there any way I could do it using Java code ? I searched a lot but didn't get any satisfying solution

Please help.

Thanks and Regards
Sumeet

[Fatih Keles]

Hi Sumeet,

Why do you need that? There is a command line utility called mysqldump for this purpose. Also you can find the following links useful from mysql manual.

http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html
http://dev.mysql.com/doc/refman/5.0/en/copying-databases.html

Regards,

Fatih.

[Sumeet Chakraborty]

Thanks Fatih

But thats what my boss has asked me to do and thats what the requirement is ..so cant help

Please help

[Campbell Ritchie]

You will probably have to call it from Runtine.exec, and that is Java ( ), but make sure to read when runtime.exec() won't by Michael Daconta first.

[sarath j nair]

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

[Wendy Gibbons]

Sumeet Chakraborty wrote:Thanks Fatih

But thats what my boss has asked me to do and thats what the requirement is ..so cant help

Please help

Have you mentioned the command line utility to your boss? don't just presume they know everything and are being akward on purpose.

[arief ginanjar]

Thank you sarath j nair for your sample, you make my day bright again...

[sarath j nair]

You are always welcome Arief Ginanjar .....

[Isuru Ranawaka]

Sumeet Chakraborty wrote:Hi all

I am trying to build a java utility which will enable me to take the backup of my MySql Database. I want the backup to be taken and dumped in a sql file in some location of my file system. Is there any way I could do it using Java code ? I searched a lot but didn't get any satisfying solution

Please help.

Thanks and Regards
Sumeet

Hi Sumeet, I created a solution myself. Please take a look and get if you think it will be useful.

It is not a complex solution, I used some SHOW CREATE and SELECT commands and parsed the strings.
I Tried to make it look alike to a backup you take from Navicat software.
Here is the code.

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 < colCount; i++) {                             if (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();
        }
    }