Take backup from MYSQL through java

[prathana balaji]

Hi all,
Please someone help me --How to take backup automatically from the database(MYSQL) after certains number of records from java GUI.
When the record count exceeds certain count then the records should move from the table to backup.

Regards,
Prathana

[Jeanne Boyarsky]

Prathana,
Do you know the command line to do a backup from mySql? If so, you could call this from Java using Runtime.exec()

[Paul Campbell]

MySQL has a deprecated function for backing up a single table.

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

But keep in mind... this is a very poor way to back up your data in a relational database.

You would be better served by determining a way to handle time variancy in your table's structure

[prathana balaji]

thanks a lot.
jeanne,I know the command line that is given in sql command line
mysqldump -a -u root -p dbname > dbname.sql
How can I call that in java using Runtime.exec()?
Can you please explain me ...

Thanks in advance

Regards
Parthana

[Rob Spoor]

Originally posted by prathana balaji:
mysqldump -a -u root -p dbname > dbname.sql
How can I call that in java using Runtime.exec()?

Process p = Runtime.getRuntime().exec("mysqldump -a -u root -p dbname");
InputStream is = p.getInputStream();
OutputStream os = new FileOutputStream("dbname.sql");
// copy contents using read and write

[ November 30, 2007: Message edited by: Rob Prime ]

[Isuru Ranawaka]

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

This is in reply to this from Prarthana balaji.

prathana balaji wrote:Hi all,
Please someone help me --How to take backup automatically from the database(MYSQL) after certains number of records from java GUI.
When the record count exceeds certain count then the records should move from the table to backup.

Regards,
Prathana

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

Full usable 8.java file can be found here. http://isuru.diyatha.com/java-db-dump/