Backup mySQL database with Java desktop or Android application

Problem: I’m developing a Java application with Eclipse which must be running on iMac, MacBook Pro and Windows 7 metals and may be on Android devices. One of the options is to let my user save his mySQL database with a click. I’m using mySQL 5 on a remote server. I want a very simple utility code to backup the database and store it in the specified folder location. I am stuck with my code as it does not run on Windows as expected. Can any one help me. Answer: Backing up an mySQL database by using mysqldump (or mysqldump.exe for Windows) can be the best way for most of the cases. Execute this utility with the desired arguments and save the sql dump string to a file: 1. Collect all mySQL connection data like server address, port number, database name, user name and password. Check out network connection if database is on a remote server. 2. Learn mysqldump. Details are here for the latest mySQL 5.5 server: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html 3. Download mysqldump file to your computer or extract it from the mySQL Workbench application. Where is it in OS X ? Find your Workbench.app and extract mysqldump from /Contents/Resources/. In Windows use mysqldump.exe. Remember the path. If you do not know what Workbench is, visit this site: http://dev.mysql.com/downloads/workbench/5.2.html 4. Write your code. 5. Test it. An example code is here:
// Create a new class or add your code to an existing class

// You may need these imports:
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.FileOutputStream;
import java.io.File;
import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

// Set buffer size
// STREAM_BUFFER's bigger value consumes more heaps but decrease loops,
// smaller value consumes less heaps but increase loops. Optimize to your needs.
private int STREAM_BUFFER = 512000;

public boolean BackupDatabase(){
    boolean success = false;
    try{
        // Get SQL DUMP data
        String dump = getServerDumpData("myhost", "3306", "username", "userpassword", "databasename");
        if(  !dump.isEmpty() ){        // or check length like dump.length() > 256
            byte[] data = dump.getBytes();
            // Set backup folder, this is just an example
            String rootpath = System.getProperty("user.dir") + "\\backup\";
            if( isOSX() ){
                rootpath = System.getProperty("user.dir") + "/backup/";
            }
            // See if backup folder exists
            File file = new File(rootpath);
            if( !file.isDirectory() ){
                // Create backup folder when missing. Write access is needed.
                file.mkdir();              
            }
            // Compose full path, create a filename as you wish
            DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
            Date date = new Date();
            String filepath = rootpath + "backup-" + dateFormat.format(date) + ".sql";
            // Write SQL DUMP file
            File filedst = new File(filepath);
            FileOutputStream dest = new FileOutputStream(filedst);
            dest.write(data);
            dest.close();
            success = true;
        }

   }catch (Exception ex){
        ex.printStackTrace();
   }
    return success;
}
How to dump database structure and data:
public String getServerDumpData(String host, String port, String user, String password, String db) {
    StringBuilder dumpdata = new StringBuilder();
    String execline = "";
    try {
        if( host!=null && user!=null && password!=null && db!=null){
            // Set path. Set location of mysqldump
            //  For example: current user folder and lib subfolder            
            if( isOSX() ){
                execline = System.getProperty("user.dir") + "/lib/mysqldump";
            }else{
                execline = System.getProperty("user.dir") + "\\lib\\mysqldump.exe";
            }
            // Usage: mysqldump [OPTIONS] database [tables]
            // OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
            // OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
            String command[] = new String[]{ execline,
                            "--host=" + host,
                            "--port=" + port,
                            "--user=" + user,
                            "--password=" + password,
                            "--compact",
                            "--complete-insert",
                            "--extended-insert",
                            "--skip-comments",
                            "--skip-triggers",
                            db };

            // Run mysqldump
            ProcessBuilder pb = new ProcessBuilder(command);
            Process process = pb.start();

            InputStream in = process.getInputStream();
            BufferedReader br = new BufferedReader(new InputStreamReader(in));

            int count;
            char[] cbuf = new char[STREAM_BUFFER];

            // Read datastream
            while ((count = br.read(cbuf, 0, STREAM_BUFFER)) != -1){
                dumpdata.append(cbuf, 0, count);
            }
            // Close
            br.close();
            in.close();
        }

    } catch (Exception ex) {
        // Handle exception as you wish
        ex.printStackTrace();
        return "";
    }
    return dumpdata.toString();
}
Determine OS when you wan to customize your code:
// Add some code to it when you need to look for Android, Windows, OS X etc. versions...
private boolean isOSX(){
    String lcOSName = System.getProperty("os.name").toLowerCase();
    return lcOSName.startsWith("mac os x");
}
Backup mySQL stored procedures in Java application