标签：文件 log 批量 备份 db mysqldump mysql import append
mysqldump Mysql数据备份恢复
目录
一、需求
/**
* mysqldump命令
* 备份库
* 备份所有数据库：mysqldump -h 主机IP -uroot -p --all-database > /usr/dball.sql
* 备份多个数据库：mysqldump -h 主机IP -uroot -p db1 db2 db3 >/usr/db123.sql
* 备份单个数据库：mysqldump -h 主机IP -uroot -p db >/usr/db.sql
* <p>
* 备份表
* 备份多张表：mysqldump -h 主机IP -uroot -p db table1 table2 >/data/db_table12.sql
* 备份单张表：mysqldump -h 主机IP -uroot -p db table >/data/db_table.sql
* 备份表时用 where 筛选：mysqldump -h 主机IP -uroot -p db table --where " 查询条件" >/data/db_table.sql
* <p>
* 还原：mysqldump -h 主机IP -uroot -p db < /root/db.sql
* source命令：source /root/db.sql
*/
二、Java 实现Mysql备份与恢复
备份单个数据库：mysqldump -h 主机IP -uroot -p db >/usr/db.sql
pom.xml
<dependencies>
<!--mysql 测试8.0.13 生产5.1.35-->
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<!-- <version>1.2.16</version>-->
<version>1.1.12</version>
</dependency>
<dependency>
<groupId>org.json</groupId>
<artifactId>json</artifactId>
<version>20180130</version>
</dependency>
<!-- log4j的依赖 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.36</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-reload4j</artifactId>
<version>1.7.36</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-assembly-plugin</artifactId>
<version>3.3.0</version>
<configuration>
<archive>
<manifest>
<mainClass>com.gtja.main.SinkMysqlMain</mainClass>
</manifest>
</archive>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>8</source>
<target>8</target>
</configuration>
</plugin>
</plugins>
</build>
main
package com.gtja.main;
import com.gtja.model.MysqlBackups;
import com.gtja.model.MysqlReduction;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/*
* @Description: mysql数据安全（备份与还原）
* @Auther: wsy
* @Date: 2023/05/17/13:29
*/
/**
* mysqldump命令
* 备份库
* 备份所有数据库：mysqldump -h 主机IP -uroot -p --all-database > /usr/dball.sql
* 备份多个数据库：mysqldump -h 主机IP -uroot -p db1 db2 db3 >/usr/db123.sql
* 备份单个数据库：mysqldump -h 主机IP -uroot -p db >/usr/db.sql
* <p>
* 备份表
* 备份多张表：mysqldump -h 主机IP -uroot -p db table1 table2 >/data/db_table12.sql
* 备份单张表：mysqldump -h 主机IP -uroot -p db table >/data/db_table.sql
* 备份表时用 where 筛选：mysqldump -h 主机IP -uroot -p db table --where " 查询条件" >/data/db_table.sql
* <p>
* 还原：mysqldump -h 主机IP -uroot -p db < /root/db.sql
* source命令：source /root/db.sql
*/
public class MysqlDump {
//TODO 需求：备份还原单个数据库
private static Logger log = LoggerFactory.getLogger(MysqlDump.class);
public static void main(String[] args) {
if (args.length != 2) {
log.error("参数传入错误，需要传入两个参数 {} {}，例: java -jar mysqlDump.jar bf wjdm ", "model", "dbName");
log.warn("{} bf: 表示备份mysql数据,hy: 表示还原备份数据", "model");
log.warn("{} 数据库用户名,使用 {} 拼接 conf.properties,来定制不同数据库的不同配置文件", "dbName", "dbName");
System.exit(1);
}
if (args[0].equals("bf")) {
//备份 mysql 表
log.info("开始备份mysql数据！");
MysqlBackups.Backup(args[1], log);
} else if (args[0].equals("hy")) {
//还原备份至mysql
log.info("开始还原mysql备份数据至mysql！");
MysqlReduction.Reduction(args[1], log);
} else {
log.error("参数传入错误，第一个参数，备份请传入：bf ,还原请传入：hy");
System.exit(1);
}
}
}
MysqlBackups
- com.gtja.model.MysqlBackups
package com.gtja.model;
import com.gtja.utils.ConfProp;
import org.slf4j.Logger;
import java.io.File;
import java.io.IOException;
import java.util.Properties;
/*
* @Description: 备份 mysql 单个库
* @Auther: wsy
* @Date: 2023/05/17/14:11
*/
public class MysqlBackups {
//备份单个数据库：mysqldump -h 主机IP -uroot -p db >/usr/db.sql
//TODO 备份单个 mysql 库
public static void Backup(String dbName, Logger log) {
long start = System.currentTimeMillis();
Properties prop = new Properties();
ConfProp.getConf(prop, dbName, log);
//获取备份路径
String savePath = prop.getProperty("savePath");
File saveFile = new File(savePath);
if (!saveFile.exists()) {
//如果目录不存在，则创建文件夹
saveFile.mkdirs();
}
//数据库备份文件地址
String filePath = savePath + "/" + dbName + ".sql";
log.info("数据库备份文件地址：" + filePath);
//TODO 开始备份逻辑
StringBuilder cmd = new StringBuilder();
cmd.append("mysqldump")
.append(" --single-transaction ")
.append(" --opt")
.append(" --host=").append(prop.getProperty("host"))
.append(" --databases ").append(dbName)
.append(" --user=").append(prop.getProperty("user"))
.append(" --password=").append(prop.getProperty("password"))
.append(" --result-file=").append(filePath)
.append(" --default-character-set=utf8 ");
try {
//调用外部执行exe文件的javaAPI
Process process = Runtime.getRuntime().exec(cmd.toString());
long end = System.currentTimeMillis();
long timeElapsed = (end - start)/1000;
if (process.waitFor() == 0) {// 0 表示线程正常终止
log.info(dbName + " 数据库备份完成! 执行时间：{} 秒",timeElapsed);
} else {
log.error(dbName + " 数据库备份失败! 执行时间：{} 秒",timeElapsed);
}
} catch (IOException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
MysqlReduction
- com.gtja.model.MysqlReduction
package com.gtja.model;
import com.gtja.utils.ConfProp;
import org.slf4j.Logger;
import java.io.IOException;
import java.util.Properties;
/*
* @Description: 还原备份文件
* @Auther: wsy
* @Date: 2023/05/17/14:11
*/
public class MysqlReduction {
public static void Reduction(String dbName, Logger log) {
long start = System.currentTimeMillis();
Properties prop = new Properties();
ConfProp.getConf(prop, dbName, log);
//获取备份路径
String savePath = prop.getProperty("savePath");
//数据库备份文件地址
String filePath = savePath + "/" + dbName + ".sql";
log.info("获取数据库备份文件地址：" + filePath);
StringBuilder cmd = new StringBuilder();
cmd.append("mysql")
.append(" -u ")
.append(prop.getProperty("user"))
.append(" -p")
.append(prop.getProperty("password"))
.append(" ")
.append(dbName)
.append(" < ")
.append(filePath);
//执行命令
String[] command = {"/bin/bash", "-c", cmd.toString()};
try {
Process process = Runtime.getRuntime().exec(command);
long end = System.currentTimeMillis();
long timeElapsed = (end - start) / 1000;
if (process.waitFor() == 0) {// 0 表示线程正常终止
log.info(dbName + " 数据库还原完成! 执行时间：{} 秒", timeElapsed);
} else {
log.error(dbName + " 数据库还原失败! 执行时间：{} 秒", timeElapsed);
}
} catch (IOException e) {
e.printStackTrace();
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}
ConfProp
package com.gtja.utils;
import org.apache.log4j.PropertyConfigurator;
import org.slf4j.Logger;
import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
/*
* @Description: 获取配置文件配置【本地、生产】
* @Auther: wsy
* @Date: 2023/03/15/13:20
*/
public class ConfProp {
public static boolean debug;
static {
String osName = System.getProperties().getProperty("os.name");
if (osName.startsWith("Windows") || osName.startsWith("Mac OS X")) {
debug = true;
} else {
debug = false;
}
}
public static Properties getConf(Properties props, String dbName,Logger log) {
try {
File file;
FileInputStream fis;
if (ConfProp.debug) {
//本地执行
file = new File("src/main/resources/"+dbName+"conf.properties");
fis = new FileInputStream(file);
props.load(fis);
} else {
//服务器执行
PropertyConfigurator.configure("../conf/log4j.properties");
file = new File("../conf/"+dbName+"conf.properties");
fis = new FileInputStream(file);
props.load(fis);
}
if(!file.exists() || file.length() == 0) {
log.error("conf 配置文件为空！");
System.exit(1);
}
} catch (Exception e) {
e.printStackTrace();
}
return props;
}
}
resources
host=node01
user=root
password=123456
savePath=/Users/ailian/Documents/WorkTasks/mysqlCheckpoint/mysqlDump/data/dbFile
执行
#!/bin/bash
model=$1
dbName=$2
java -jar ../lib/mysqlDump.jar $1 $2
标签：文件,
log,
批量,
备份,
db,
mysqldump,
mysql,
import,
append
From： https://www.cnblogs.com/ChloeAilian/p/17419969.html