Simple Backup and Restore for mysql Database from Java

Question

How to backup a mysql database from a java code such that:

1. It's saving path is dynamically allocated.
2. Spaces in Path do not create problems.
3. Path is generated using the executing jar file.
4. DBname,DBusername or DBpass are dynamically allotted.
5. Creating a specialized folder to save the backup file.

Solution 1

Note: The codes given below are one way of solving the problem and probably not the best method. Everything is changeable inside the code. If you do not have mysql in environment variables, add the path before mysqldump and mysql (e.g. For XAMPP, C:\xampp\mysql\bin\mysqldump)

(Hope, this will solve your problems. Took me a day to completely figure out everything and implement them properly)

Method for Backup:

public static void Backupdbtosql() {
    try {

        /*NOTE: Getting path to the Jar file being executed*/
        /*NOTE: YourImplementingClass-> replace with the class executing the code*/
        CodeSource codeSource = YourImplementingClass.class.getProtectionDomain().getCodeSource();
        File jarFile = new File(codeSource.getLocation().toURI().getPath());
        String jarDir = jarFile.getParentFile().getPath();


        /*NOTE: Creating Database Constraints*/
        String dbName = "YourDBName";
        String dbUser = "YourUserName";
        String dbPass = "YourUserPassword";

        /*NOTE: Creating Path Constraints for folder saving*/
        /*NOTE: Here the backup folder is created for saving inside it*/
        String folderPath = jarDir + "\\backup";

        /*NOTE: Creating Folder if it does not exist*/
        File f1 = new File(folderPath);
        f1.mkdir();

        /*NOTE: Creating Path Constraints for backup saving*/
        /*NOTE: Here the backup is saved in a folder called backup with the name backup.sql*/
         String savePath = "\"" + jarDir + "\\backup\\" + "backup.sql\"";

        /*NOTE: Used to create a cmd command*/
        String executeCmd = "mysqldump -u" + dbUser + " -p" + dbPass + " --database " + dbName + " -r " + savePath;

        /*NOTE: Executing the command here*/
        Process runtimeProcess = Runtime.getRuntime().exec(executeCmd);
        int processComplete = runtimeProcess.waitFor();

        /*NOTE: processComplete=0 if correctly executed, will contain other values if not*/
        if (processComplete == 0) {
            System.out.println("Backup Complete");
        } else {
            System.out.println("Backup Failure");
        }

    } catch (URISyntaxException | IOException | InterruptedException ex) {
        JOptionPane.showMessageDialog(null, "Error at Backuprestore" + ex.getMessage());
    }
}

Method for Restore:

public static void Restoredbfromsql(String s) {
        try {
            /*NOTE: String s is the mysql file name including the .sql in its name*/
            /*NOTE: Getting path to the Jar file being executed*/
            /*NOTE: YourImplementingClass-> replace with the class executing the code*/
            CodeSource codeSource = YourImplementingClass.class.getProtectionDomain().getCodeSource();
            File jarFile = new File(codeSource.getLocation().toURI().getPath());
            String jarDir = jarFile.getParentFile().getPath();

            /*NOTE: Creating Database Constraints*/
             String dbName = "YourDBName";
             String dbUser = "YourUserName";
             String dbPass = "YourUserPassword";

            /*NOTE: Creating Path Constraints for restoring*/
            String restorePath = jarDir + "\\backup" + "\\" + s;

            /*NOTE: Used to create a cmd command*/
            /*NOTE: Do not create a single large string, this will cause buffer locking, use string array*/
            String[] executeCmd = new String[]{"mysql", dbName, "-u" + dbUser, "-p" + dbPass, "-e", " source " + restorePath};

            /*NOTE: processComplete=0 if correctly executed, will contain other values if not*/
            Process runtimeProcess = Runtime.getRuntime().exec(executeCmd);
            int processComplete = runtimeProcess.waitFor();

            /*NOTE: processComplete=0 if correctly executed, will contain other values if not*/
            if (processComplete == 0) {
                JOptionPane.showMessageDialog(null, "Successfully restored from SQL : " + s);
            } else {
                JOptionPane.showMessageDialog(null, "Error at restoring");
            }


        } catch (URISyntaxException | IOException | InterruptedException | HeadlessException ex) {
            JOptionPane.showMessageDialog(null, "Error at Restoredbfromsql" + ex.getMessage());
        }

    }

Solution 2

If Hibernate is configured properly, this is cake:

Session session = HibernateUtil.getSessionFactory().openSession();
// for every table, have the bean implement Serializable and use the next 4 lines
List <TblBean> tblCollection = session.createCriteria(TblBean.class).list();
FileOutputStream backup = new FileOutputStream("backupOf"+TblBean.getClass().getName()+".dat");
ObjectOutputStream backupWriter = new ObjectOutputStream(backup);
backupWriter.write(tblCollection);

Solution 3

public static String getData(String host, String port, String user, String password, String db,String table) throws Exception {

    //an "C:/xampp/mysql/bin/mysqldump" ---- location ito han mysqldump

    Process run = Runtime.getRuntime().exec(
            "C:/xampp/mysql/bin/mysqldump --host="  + host + " --port=" + port + 
            " --user=" + user + " --password=" + password +
            " --compact --databases --add-drop-table --complete-insert --extended-insert " +
            "--skip-comments --skip-triggers "+ db+" --tables "+table);

    InputStream in = run.getInputStream(); 
    BufferedReader br = new BufferedReader(new InputStreamReader(in));
    StringBuffer temp = new StringBuffer();
    int count;
    char[] cbuf = new char[BUFFER];

    while ((count = br.read(cbuf, 0, BUFFER)) != -1)
        temp.append(cbuf, 0, count);

    br.close();
    in.close();

    return temp.toString();
}

Comments

[chettyharish]

This is only for achieving backup, and it needs setting up Hibernate+ you need beans implementing all the tables (there could be 100's) the other code is simple and easy to implement for newbies (though insecure and inefficient)

[hd1]

The question starts with "How to backup a mysql database from a java code ". My answer does that in a secure and efficient manner, which the other code does not, as you noted.

[hd1]

As noted in the first comment, hibernate is not the most trivial thing to set up, but once it is setup, the code is cake

[Rafi Abro]

your method Backupdbtosql shows error when executed. Error at BackuprestoreCannot run program "mysqldump"; CreateProcess error=2, The System cannot find the file specified

[Rafi Abro]

I am using wampserver

[vLopez]

That error happens due to you don´t have put the path of mysqldump.exe, I had the same error, but harcoding it works perfect. For Windows: String executeCmd = "C:\\Program Files (x86)\\MySQL\\MySQL Workbench 6.1 CE\\mysqldump -u" + dbUser + " -p" + dbPass + " --database " + dbName + " -r " + savePath; This isn´t an optimal solution, due to prtable problems! Sorry about my english

[jemystack]

my friend .first thanks for this full answer second when i execute the command in Backupdbtosql() method this method don't run with the error is unknown option '--database' my solution is to replace --database with databases

[Palani]

Many thanks as it saves my time to solve the restore option.

[Bugs - not a bug]

if you want to export table structure with data you can replace this command String executeCmd = "mysqldump -u" + dbUser + " -p" + dbPass + " --database " + dbName + " -r " + savePath; with this String executeCmd = "mysqldump -u" + dbUser + " -p" + dbPass + " " + dbName + " -r " + savePath; by removing --database we do not add database name to dump file, which on import create tables. this also helps when you are importing backup file to a database with different name

[Eatsam ul haq]

@RafiAbro at my side, it is giving me Backup Failure. The value of processComplete is 2 and that is why, it is not giving any exception but also not generating backup.