backupdb.jsp
<%--Document : backupdb
Created on : Oct 30, 2015, 2:55:19 PM
Author : Aravind Sankaran Nair
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<form method="get" action="<%=request.getContextPath()%>/BackupRestoreDataBase" enctype="multipart/form-data">
<center>
<br><br><br><br><br><br><br><br><br><br><br><br>
<table>
<tr>
<td>Backup</td>
<td></td>
<td><input type="submit" name="backup" value="Backup" id="submit" /></td>
</tr>
<tr>
<td>Restore</td>
<td><input type="file" name="file" ></td>
<td><input type="submit" name="restore" value="Restore" id="submit" /></td>
</tr>
</table>
</center>
</form>
</body>
</html>
BackupRestoreDataBase.java (Servlet)
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
/**
*
* @author Aravind Sankaran Nair
*/
public class BackupRestoreDataBase extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
if(request.getParameter("backup")!=null){
String status=DBBackup.mysqlDatabaseBackUp();
if(status.equals("y")){
HttpSession session=request.getSession(true);
session.setAttribute("currentSessionUser", "");
response.sendRedirect("backupsuccess.jsp");
}else if(status.equals("n")){
response.sendRedirect("backupfailure.jsp");
}
} else if(request.getParameter("restore")!=null){
String fileName=request.getParameter("file");
boolean status=DBBackup. mysqlDatabaseRestore("dbName","dbUserName","dbPassword","C:\\folderName\\"+fileName);
if(status==true){
System.out.println("restore success ");
HttpSession session=request.getSession(true);
session.setAttribute("currentSessionUser", "");
response.sendRedirect("restoresuccess.jsp");
}else{
System.out.println("restore failure ");
response.sendRedirect("restorefailure.jsp");
}
}
}
}
DBBackup.java
import java.io.BufferedReader;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
/**
*
* @author Aravind Sankaran Nair
*/
public class DBBackup {
public static boolean mysqlDatabaseRestore(String dbName, String dbUserName, String dbPassword, String source) {
boolean status=false;
String[] executeCmd = new String[]{"mysql", "--user=" + dbUserName, "--password=" + dbPassword, dbName,"-e", " source "+source};
Process runtimeProcess;
try {
runtimeProcess = Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if (processComplete == 0) {
System.out.println("Backup restored successfully");
status =true;
return status;
} else{
System.out.println("Could not restore the backup");
status=false;
return status;
}
}
catch(Exception ex)
{
}
return status;
}
public static String mysqlDatabaseBackUp() {
String status="";
File file = new File("C:\\folderName");
if (!file.exists()) {
if (file.mkdir()) {
System.out.println("Directory is created!");
String backUpPath= "C:\\folderName";
String host="localhost";
String mysqlPort="3306";
String dbUser="dbUserName";
String dbPassword ="dbPassword";
String database ="dbName";
DBBackup dbBackup = new DBBackup();
try
{
DateFormat dayFormat = new SimpleDateFormat("dd");
DateFormat monthFormat = new SimpleDateFormat("MM");
DateFormat yearFormat = new SimpleDateFormat("yyyy");
Calendar cal = Calendar.getInstance();
String backupDate=dayFormat.format(cal.getTime())+"-"+monthFormat.format(cal.getTime())+"-"+yearFormat.format(cal.getTime());
byte[] data = dbBackup.getData(host, mysqlPort, dbUser, dbPassword, database).getBytes();
File fileDestination = new File(backUpPath+"\\"+database+"_"+backupDate+".sql");
FileOutputStream destination = new FileOutputStream(fileDestination);
destination.write(data);
destination.close();
status="y";
System.out.println("Back Up Success");
return status;
}catch (Exception ex){
System.out.println("Back Up Failed");
status="n";
return status;
}
} else {
System.out.println("Failed to create directory!");
}
}
else{
String backUpPath= "C:\\folderName";
String host="localhost";
String mysqlPort="3306";
String dbUser="root";
String dbPassword ="root";
String database ="dbName";
DBBackup dbBackup = new DBBackup();
try
{
DateFormat dayFormat = new SimpleDateFormat("dd");
DateFormat monthFormat = new SimpleDateFormat("MM");
DateFormat yearFormat = new SimpleDateFormat("yyyy");
Calendar cal = Calendar.getInstance();
String backupDate=dayFormat.format(cal.getTime())+"-"+monthFormat.format(cal.getTime())+"-"+yearFormat.format(cal.getTime());
byte[] data = dbBackup.getData(host, mysqlPort, dbUser, dbPassword, database).getBytes();
File fileDestination = new File(backUpPath+"\\"+database+"_"+backupDate+".sql");
FileOutputStream destination = new FileOutputStream(fileDestination);
destination.write(data);
destination.close();
System.out.println("Back Up Success");
status ="y";
return status;
}catch (Exception ex){
System.out.println("Back Up Failed");
status="n";
return status;
}
}
return status;
}
private static ResultSet rs;
private static Connection con;
private Statement st;
private int BUFFER = 99999;
public String getData(String host, String port, String user, String password, String db) {
String Mysqlpath = getMysqlBinPath(user, password, db);
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
System.out.print("cant access mysql driver");
}
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + db, user, password);
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
} catch (Exception e) {
System.out.print("connection error");
e.printStackTrace();
}
System.out.println(Mysqlpath);
Process run = null;
try {
System.out.println(Mysqlpath + "mysqldump --host=" + host + " --port=" + port + " --user=" + user + " --password=" + password + " --compact --complete-insert --extended-insert " + "--skip-comments --skip-triggers " + db);
run = Runtime.getRuntime().exec(Mysqlpath + "mysqldump --host=" + host + " --port=" + port + " --user=" + user + " --password=" + password + " " + "--skip-comments --skip-triggers " + db);
} catch (IOException ex) {
}
InputStream in = run.getInputStream();
BufferedReader br = new BufferedReader(new InputStreamReader(in));
StringBuffer temp = new StringBuffer();
int count;
char[] cbuf = new char[BUFFER];
try {
while ((count = br.read(cbuf, 0, BUFFER)) != -1) {
temp.append(cbuf, 0, count);
}
} catch (IOException ex) {
}
try {
br.close();
in.close();
} catch (IOException ex) {
}
return temp.toString();
}
// Mysql path is required to locate the bin folder inside it because it contains the Mysqldump which performs a //main role while taking backup.
/*Function to find MySql Path*/
public String getMysqlBinPath(String dbUser, String dbPassword, String databaseName) {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
System.out.print("driver loading failed");
}
try {
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + databaseName, dbUser, dbPassword);
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
} catch (Exception e) {
System.out.print("connection error");
e.printStackTrace();
}
String a = "";
try {
rs = st.executeQuery("select @@basedir");
while (rs.next()) {
a = rs.getString(1);
}
} catch (Exception eee) {
eee.printStackTrace();
}
a = a + "bin\\";
System.err.println("Mysql path is :" + a);
return a;
}
}
backupsuccess.jsp
<%--
Document : backupsuccess
Created on : Oct 30, 2015, 3:18:39 PM
Author : Aravind Sankaran Nair
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>backup success</h1>
</body>
</html>
backupfailure.jsp
<%--
Document : backupfailure
Created on : Oct 30, 2015, 3:18:39 PM
Author : Aravind Sankaran Nair
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>backup failure</h1>
</body>
</html>
restoresuccess.jsp
<%--
Document : restoresuccess
Created on : Oct 30, 2015, 3:18:39 PM
Author : Aravind Sankaran Nair
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>restore success</h1>
</body>
</html>
restorefailure.jsp
<%--
Document : restorefailure
Created on : Oct 30, 2015, 3:18:39 PM
Author : Aravind Sankaran Nair
--%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body>
<h1>restore failure</h1>
</body>
</html>
Jar File Needed:
mysql-connector-java-5.1.18-bin.jar
output:
- java.sql.SQLException: Unknown system variable 'basedir'
ReplyDelete- bin\
bin\mysqldump --host=localhost --port=3306 --user=root --password=root --compact --complete-insert --extended-insert --skip-comments --skip-triggers telecom
Back Up Failed.
This 2 error occurred.
How to solve this issue?
-
which version of mysql connector jar file you are using??
Deletemysql-connector-java-5.1.41-bin.jar
ReplyDeletewaste idea its not working for me.. some exceptions are there..try to resolve and update
ReplyDeletewhat exception you are getting??
Deleteuse this link to get running program to backup
Deletehttps://drive.google.com/file/d/1Hlk1xIX65cODOi6jPQDkvJeDvuBLYbBv/view?usp=sharing
i am using mysql-connector-java-5.1.40.jar
java.sql.SQLException: Unknown system variable 'basedir'
ReplyDeletehttps://dba.stackexchange.com/questions/52597/mysql-5-1-unknown-variable-basedir-c-mysql
Delete