MySQL Backup/Restore in Windows, Linux and using Java Program

MySQL Commands

MySQL provide a great command line utility to take backup of your MySQL database and restore it. mysqldump command line utility is available with MySQL installation (bin directory) that can be used to achieve this.

Getting backup of a MySQL database using mysqldump.
mysqldump –-user [user name] –-password=[password] [database name] > [dump file]
or 
mysqldump –u[user name] –p[password] [database name] > [dump file]
Backup multiple databases in MySQL.
mysqldump –u[user name] –p[password] [database name 1] [database name 2] .. > [dump file]
Backup all databases in MySQL.
mysqldump –u[user name] –p[password] –all-databases > [dump file]
Backup a specific table in MySQL.
mysqldump --user [username] --password=[password] [database name] [table name] \ 
> /tmp/anish_accounts_contacts.sql
Restoring MySQL database.
mysql --u [username] --password=[password] [database name] < [dump file]
Run MySQL Backup/Restore commands in windows

1. Open a command prompt DOS window by typing in "cmd" in Start>>Run Menu
2. Now navigate to the bin directory of the MySQL installation(eg: c:/Program Files/MySQL/MySQL Server 5.0/bin)
3. For backup run the following command

mysqldump.exe -u root -pabc123 se_alfresco > d:\backup.sql

4. For restore run the following command
mysql.exe -u root -pabc123 se_alfresco < d:\backup.sql

Run MySQL Backup/Restore commands in Linux

1. Go to linux shell prompt
2. Use the below command for back up and restoration
3. For backup

    mysqldump -u root -pabc123 se_alfresco > d:\backup.sql
 

4. For restore
    mysql -u root -pabc123 se_alfresco < d:\backup.sql
 

MySQL Backup/Restore using Java Program
Backup
/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;

/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;
executeCmd = “mysqldump -u “+dbUser+” -p”+dbPass+” “+dbName+” -r backup.sql”;
}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){

out.println(“Backup taken successfully”);

} else {

out.println(“Could not take mysql backup”);

}
Restore
/******************************************************/
//Database Properties
/******************************************************/
String dbName = “dbName”;
String dbUser = “dbUser”;
String dbPass = “dbPass”;

/***********************************************************/
// Execute Shell Command
/***********************************************************/
String executeCmd = “”;

executeCmd = new String[]{“/bin/sh”, “-c”, “mysql -u” + dbUser+ ” -p”+dbPass+” ” + dbName+ ” < backup.sql” };

}
Process runtimeProcess =Runtime.getRuntime().exec(executeCmd);
int processComplete = runtimeProcess.waitFor();
if(processComplete == 0){

out.println(“success”);

} else {

out.println(“restore failure”);

}

1 comments

  1. A complete working example on how to use these commands from JSP code can be found here
    http://www.jvmhost.com/articles/mysql-postgresql-dump-restore-java-jsp-code

    ReplyDelete

 
© Behind Java
Designed by Anish Antony
Dedicated to all java developers
Posts RSSComments RSS
Back to top