MySQL backup table operation based on Java

MySQL backup table operation based on Java

The core is mysqldump and Runtime
The operation is actually not very difficult. Create a class to perform the backup operation. After receiving the backup call, mark the table as being backed up, and then create a child thread to perform the backup operation. The required configuration information is read from the configuration file, and also pay attention to path issues under Windows and Linux.


The configuration files are as follows:

Java Code Collection Code
# Database address
dbAddress=localhost
# Name of the database to be backed up
databaseName=nms
# Database username
username = root
# Database password
password = root
#mysqldump pathLinux
mysqlpath = /usr/bin/
# Backup file storage location Linux
sqlFilePath = /MySQlBack/
#mysqldump path Windows
#mysqlpath = C\://Program Files//MySQL//MySQL Server 5.5//bin//
# Backup file storage location Windows
#sqlFilePath =C\://MySQl//

The code class that performs the function is as follows:

Java Code Collection Code
package com.nms.common.db;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
* For database backup operations
*/
public class DbBackUpMethod {
private static Log logger = LogFactory.getLog(DbBackUpMethod.class);
private static Properties pros = getPprVue("db.properties");
public static Map<String, String> backUpTableList = new ConcurrentHashMap<String, String>();
private static DbBackUpMethod backObj = new DbBackUpMethod();
public static DbBackUpMethod getDbBackUpMethod(){
return backObj;
}
public void backup(String tableName) {
if(null != backUpTableList.get(tableName)) return;
backUpTableList.put(tableName, tableName); // Mark as already used for backup
new Thread(new DbBackUpThread(tableName)).start();
}
/**
* Used to perform a backup of a table
*/
class DbBackUpThread implements Runnable {
String tableName = null;
public DbBackUpThread(String tableName){
this.tableName = tableName;
}
@Override
public void run() {
try {
String username = pros.getProperty("username");
String password = pros.getProperty("password");
String mysqlpaths = pros.getProperty("mysqlpath");
String address = pros.getProperty("dbAddress");
String databaseName = pros.getProperty("databaseName");
String sqlpath = pros.getProperty("sqlFilePath");
File backupath = new File(sqlpath);
if (!backupath.exists()) {
backupath.mkdir();
}
StringBuffer sb = new StringBuffer();
sb.append(mysqlpaths);
sb.append("mysqldump ");
sb.append("--opt ");
sb.append("-h ");
sb.append(address);
sb.append(" ");
sb.append("--user=");
sb.append(username);
sb.append(" ");
sb.append("--password=");
sb.append(password);
sb.append(" ");
sb.append("--lock-all-tables=true");
sb.append("--result-file=");
sb.append(sqlpath);
sb.append(tableName+".sql");
sb.append(" ");
sb.append("--default-character-set=utf8");
sb.append(databaseName);
sb.append(" ");
sb.append(tableName);
Runtime cmd = Runtime.getRuntime();
Process p = cmd.exec(sb.toString());
p.waitFor(); // This statement is used to mark that if the backup is not completed, the thread will continue to wait
} catch (Exception e) {
logger.error("Problem occurred in the backup operation", e);
}finally{
backUpTableList.remove(tableName); // will be removed eventually
}
}
}
public static Properties getPprVue(String propertyName) {
InputStream inputStream = DbBackUpMethod.class.getClassLoader().getResourceAsStream(properName);
Properties p = new Properties();
try {
p.load(inputStream);
inputStream.close();
} catch (IOException e) {
logger.error("Unable to read the configuration file for backup data", e);
}
return p;
}
}

In Action, you can directly call the backup operation method:

Java Code Collection Code
DbBackUpMethod.getDbBackUpMethod().backup(tableName); // Call backup

At the same time, if the page has an operation to delete the table, you should determine whether the table is being backed up before the operation.

Java Code Collection Code
if(null != DbBackUpMethod.backUpTableList.get(tableName))

Then when the page JSP is called, a response prompt can be given. My judgment is that only one table can be deleted:

function deleteTableByTableName(){
	var pk = table.getSelectedKeys();
	if(""==pk){
		alert("Please select a record!");
		return false;
	}
	if(pk.length > 1){
		alert("Please select a record!");
		return false;
	}
	var rows = table.get(pk);
	var tableName=rows.tableName;
	if(confirm("Are you sure you want to delete this table?")) {
		if(confirm("Do you need to back up the table before deleting it?\n\nAfter selecting backup, the system will perform related operations in the background!\nDuring this period, you cannot delete the table!\nThe backup operation may last for several hours! Please be aware!"")) {
			document.form1.action="backUpTable.action?tableName=" + tableName;
			document.form1.submit();
		}else{
			if(confirm("Are you sure you want to submit? The table will be deleted!")) {
				document.form1.action="del.action?tableName=" + tableName;
				document.form1.submit();
			}
		}
	}
}

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:
  • Solution to the error message "java.sql.SQLException: Incorrect string value:'\xF0\x9F\x92\xA9\x0D\x0A...'" when storing emoticons in MySQL
  • An example of how to use Java+MySQL recursion to concatenate tree-shaped JSON lists
  • How to enable Java backend MySQL database to support emoji expressions
  • Comparison table between Java data types and MySql data types
  • Java implements a feasible method to obtain the total number of records of all tables in the MySQL database
  • Implementation code for restoring MySQL database through java backup
  • Detailed tutorial on how to connect to MySQL database using Java (recommended)
  • Detailed explanation of the download process of the mysql-connector-java.jar package
  • Detailed explanation of mysql time zone problem in Java

<<:  Solution to the error problem of Vscode remotely connecting to Ubuntu

>>:  Vue uses element-ui to implement menu navigation

Recommend

Five things a good user experience designer should do well (picture and text)

This article is translated from the blog Usability...

JavaScript flow control (loop)

Table of contents 1. for loop 2. Double for loop ...

HTML table_Powernode Java Academy

To draw a table in HTML, use the table tag tr me...

Understand CSS3 Grid layout in 10 minutes

Basic Introduction In the previous article, we in...

Solve the cross-domain problem of Vue+SpringBoot+Shiro

Table of contents 1. Configure Vue front end 1. D...

How to change the MySQL database file directory in Ubuntu

Preface The company's Ubuntu server places th...

Common problems and solutions during MySQL MGR construction

Table of contents 01 Common Faults 1 02 Common Fa...

Linux /etc/network/interfaces configuration interface method

The /etc/network/interfaces file in Linux is used...

Explanation of the usage of replace and replace into in MySQL

MySQL replace and replace into are both frequentl...

Detailed steps for running springboot project in Linux Docker

Introduction: The configuration of Docker running...