Dump and restore MySQL and PostgreSQL from Java and JSP code

Making MySQL or PostgreSQL dumps and restores from Java and JSP is not very different than usual use of dump/restore tools. See how to perform these tasks in our Java hosting environment and what to be warned about.

The command line utilities pg_dump, psql, mysql and mysqldump are called from Java and JSP code using the exec() method of java.lang.Runtime class. The code below is self explaining. It was written for one of our Java hosting clients who needed to know how to perform these tasks.

It loads JDBC drivers so have them ready in WEB-INF/lib or comment out unused driver. Then it lists existing MySQL and PostgreSQL databases. You can then paste any of them into text field, choose database type and click Backup or Restore. You will be informed about success or failure. If dumping a database you will find the dump in your home directory. If restoring, it will be read fromn home directory too. You can modify the code if you want to have a dedciated directory for the dumps. Here goes screenshot of the script form and then some topics you should be aware of.

dump restore form

Provide passwords to dump tools

For PostgreSQL make sure you have ~/.pgpass file with the following content

localhost:5432:*:username:mypass

and that it is only writable/readable by you (chmod 600 ~/.pgpass). Java does not need it but pg_dump does. Mysqldump allows you to provide password as command line parameter but you may also choose to strip the parameter and use  ~/.my.cnf file containing credentials.

Database level permissions required to perform dumps

LOCK TABLES privilege is required to perform MySQL dump. cPanel defined database users have usually this privilege active when you chose ALL privileges when assigning user to a database. If you missed the privilege you will need to assign it. In case of cPanel drop the user and create it with proper privileges.

Filesysem permisions  required to perform dumps

Make sure you have write permissions for the directory you chose as dump destination. In case of cPanel you can by default write to any directory under your home directory. Make sure that the directory path you specify in java code is absolute or make sure it translates to a valid path in writable location if you specified it as relative path. If you only provide a destination filename for the dump (like we did in the example code), it will be saved in your home directory.

MySQL and PostgreSQL utilities location

In rare cases mysqldump or pg_dump commands may be not in your path. Ask support for full path to the commands and place it in the Java/JSP code or modify your
shell PATH variable (~/.bashrc). In case of cPanel server these tools will usually be in /usr/bin or /usr/local/bin.

List, dump and restore from Java/JSP – the code

You can modify the command parameters in executeCmd string according to your needs (for example strip database drop/create switch).  For PostgreSQL, instead of dumping to plain text format like we do in the script you can choose other formats and then load them with pg_restore. Check pg_dump and pg_restore manual pages. Here is the code of backup_restore_database.jsp.

<%@page import="java.sql.*,java.io.*,java.util.Arrays" %>
<%
String database = null;
// your cPanel username and password here - the user has MySQL LOCK TABLE right
String username = "java";
String password = "secret";
// String dumpdir = "./dumps";
String urlmysql = "jdbc:mysql://localhost/";
String urlpsql = "jdbc:postgresql://localhost/template1";

String dbtype = null;
Connection connection = null;
Statement statement = null;
ResultSet rset = null;
int result = -1;


try {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Class.forName("org.postgresql.Driver").newInstance();
} catch(ClassNotFoundException e) {
    out.println("Class not found: "+ e.getMessage());
    return;
}

if (request.getParameter("dbtype") != null) { dbtype = request.getParameter("dbtype"); };

try {

    connection = DriverManager.getConnection(urlmysql, username, password);
    statement = connection.createStatement();

    out.println("<b>List of MySQL databases accessible by user " + username + ":</b><br/>");
    rset = statement.executeQuery("SHOW DATABASES");
    while (rset.next())   {
        out.println(rset.getString(1) + "<br/>");
    }
    rset.close();
    out.println("<hr>");

    connection = DriverManager.getConnection(urlpsql, username, password);
    statement = connection.createStatement();

    out.println("<b>List of PostgreSQL databases accessible by user " + username + ":</b><br/>");
    rset = statement.executeQuery("SELECT datname FROM pg_database WHERE datistemplate = false and datname like '" + username +"_%';");
    while (rset.next())   {
        out.println(rset.getString(1) + "<br/>");
    }
    rset.close();
    statement.close();
    connection.close();
    out.println("<hr>");


    if (request.getParameter("database") != null) {
        database = (String)request.getParameter("database");
        if (request.getParameter("Backup") != null &&
            request.getParameter("Backup").equals("Backup")) {

            String executeCmd = "mysqldump -u " + username + " -p" + password + " --add-drop-database -B " + database + " -r " + database + ".sql";
            if (dbtype.equals("postgresql")) {
                executeCmd = "pg_dump -U " + username + " -w -c -f " + database + ".sql " + database;
            }

            Process runtimeProcess;
            try {
                 runtimeProcess = Runtime.getRuntime().exec(executeCmd);
                int processComplete = runtimeProcess.waitFor();
                 if (processComplete == 0) {
                    out.println("Backup created successfully");
                } else {
                    out.println("Could not create the backup");
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }

        } else if (request.getParameter("Restore") != null &&
            request.getParameter("Restore").equals("Restore")) {

            String[] executeCmd = new String[]{"mysql", "--user=" + username, "--password=" + password, "-e", "source "+ database + ".sql"};
            if (dbtype.equals("postgresql")) {
                executeCmd = new String[]{"psql", "--username=" + username, "--file=" + database + ".sql", database};
            }
   
//            out.println(Arrays.toString(executeCmd));

            Process runtimeProcess;
            try {
                runtimeProcess = Runtime.getRuntime().exec(executeCmd);
                int processComplete = runtimeProcess.waitFor();
                if (processComplete == 0) {
                    out.println("Backup restored successfully");
                } else {
                    out.println("Could not restore the backup");
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

%>

<form action="backup_restore_database.jsp" method="post"><table>
<tr><td align="left">Database name to backup or restore: <input type="text" name="database" size="20"></td></tr>
<tr><td><input type="radio" name="dbtype" value="mysql" checked="checked">MySQL<br>
<input type="radio" name="dbtype" value="postgresql">PostgreSQL</td></tr>
<tr><td align="left"><input type="submit" name="Backup" value="Backup">
<input type="submit" name="Restore" value="Restore">
<input type="reset" name="Reset" value="Reset"></td></tr>
</table></form>
   
<%
} catch (SQLException e) {
    out.println(e.getMessage());
} finally {
    try {
        if(connection != null) connection.close();
    } catch(SQLException e) {}
}
%>

See also similar article on database creation and deletion from Java and JSP code.

If you want to print shell command results you may add below snippet after runtimeProcess.waitFor():

BufferedReader buf = new BufferedReader(new InputStreamReader(runtimeProcess.getInputStream()));  
String line = ""; 
while ((line = buf.readLine()) != null) { 
     out.println("exec response: " + line + "<br/>"); 
}

If you have any comments or corrections you are welcome to post them below.

This entry was posted in Databases, Java. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


three × = 24

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>