使用java程序生成.sql文件备份数据库。
application.yml
在yml文件中配置需要备份的数据库信息以及备份文件路径
server:
port: 8180
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: 123456
url: jdbc:mysql://localhost:3306/db_robot_check?characterEncoding=utf8&serverTimezone=GMT&useSSL=false&allowMultiQueries=true
backup:
datasource:
ip: 127.0.0.1
db: db_robot_check #定义数据库名
# table: ntcp_data_ #定义数据库表名
port: 3306
username: root
password: 123456
backupDay: 0 #备份多少天前的数据库数据
deleteDay: 2 #删除多少天前数据库备份文件
backupPath: D:\\JavaCode\\testmaven29MySqlBasckup\\backup\\
备份数据库
执行命令
mysqldump -h127.0.0.1 -p3306 -uroot -p123456 --default-character-set=utf8 db_robot_check
public class DbUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(DbUtil.class);
/**
* 备份数据库
* @param ip
* @param host
* @param username
* @param password
* @param databaseName
* @param savePath
* @param fileName
*/
public static void exportDatabaseTool(String ip,String host,String username,String password,String databaseName,String savePath,String fileName){
PrintWriter printWriter = null;
BufferedReader bufferedReader = null;
try {
File saveFile = new File(savePath);
if (!saveFile.exists()) {
// 目录不存在创建新的文件夹
saveFile.mkdirs();
}
if (!savePath.endsWith(File.separator)) {
savePath = savePath+File.separator;
}
printWriter = new PrintWriter(new OutputStreamWriter(new FileOutputStream(savePath+fileName),"utf-8"));
Process process = Runtime.getRuntime().exec(" mysqldump -h"+ip+" -p"+host+" -u"+username+" -p"+password+" --default-character-set=utf8 "+databaseName);
InputStreamReader inputStreamReader = new InputStreamReader(process.getInputStream(),"utf8");
bufferedReader = new BufferedReader(inputStreamReader);
String line;
while ((line = bufferedReader.readLine()) != null) {
printWriter.println(line);
}
printWriter.flush();
LOGGER.info("已备份数据库");
} catch (Exception e) {
e.printStackTrace();
LOGGER.error(e.toString());
} finally {
try {
if (bufferedReader != null) {
bufferedReader.close();
}
if (printWriter != null) {
printWriter.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
执行
利用quartz定时调用备份数据库方法
/**
* @author huangzixiao
* @Description
* @date 2023/5/11
*/
public class QuartzSqlBackup extends QuartzJobBean {
/**
* 数据库用户名
*/
@Value("${backup.datasource.username}")
private String user;
/**
* 数据库密码
*/
@Value("${backup.datasource.password}")
private String password;
/**
* 数据库地址
*/
@Value("${backup.datasource.ip}")
private String ip;
/**
* 数据库
*/
@Value("${backup.datasource.db}")
private String db;
/**
* 表名
*/
//@Value("${backup.datasource.table}")
//private String table;
/**
* 备份多少天前的数据库数据
*/
@Value("${backup.backupDay}")
private String backupDay;
/**
* 删除多少天前数据库备份
*/
@Value("${backup.deleteDay}")
private String deleteDay;
/**
* 备份路径
*/
@Value("${backup.backupPath}")
private String backupPath;
private static SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmss");
/**
* Execute the actual job. The job data map will already have been
* applied as bean property values by execute. The contract is
* exactly the same as for the standard Quartz execute method.
*
* @param context
* @see #execute
*/
@Override
protected void executeInternal(JobExecutionContext context) throws JobExecutionException {
// 定时备份数据库
// 指定名称
String sqlFileName = db+"_sql_back_up_"+sf.format(new Date())+".sql";
DbUtil.exportDatabaseTool(ip,"3306",user,password,db,backupPath,sqlFileName);
// 删除过期时间的文件
FileUtil.deleteOverTimeFile(backupPath);
}
}
为了防止sql文件过多,定期删除过期文件
public class FileUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(FileUtil.class);
public static void deleteOverTimeFile(String filePath) {
// 指定时间
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
calendar.add(Calendar.MINUTE,-10);
// 判断文件夹是否为空或不存在
File deleteFilePath = new File(filePath);
if (deleteFilePath.exists()) {
File[] files = deleteFilePath.listFiles();
if (files!=null&&files.length>0) {
for (File file : files) {
if (new Date(file.lastModified()).before(calendar.getTime())) {
LOGGER.info("删除文件:"+file.getName());
file.delete();
}
}
}
}
}
}