背景:新来的开发误操作数据库,导致数据丢失,但是没有数据备份,所以就写一个程序,定时备份数据库,体量也不是很大。
yml 配置
server:
port: 8080
# 备份的目录
backup:
sql:
path: usr/local/mysql/bak_data/
# 需要备份的数据库
mysql:
ip: 127.0.0.1
port: 3306
username: root
password: root
# mysqldump 目录
mysqldump:
path: /usr/bin/mysqldump
kotlin 使用 @Value获取配置文件的值时要多一个 \
private var logger = LoggerFactory.getLogger(BackupSqlService::class.java)
@Value("\${backup.sql.path}")
private lateinit var backPath: String
@Value("\${mysql.ip}")
private lateinit var ip: String
@Value("\${mysql.port}")
private lateinit var port: String
@Value("\${mysql.username}")
private lateinit var username: String
@Value("\${mysql.password}")
private lateinit var password: String
@Value("\${mysql.mysqldump.path}")
private lateinit var mysqldumpPath: String
备份数据库
fun backupSQL() {
var df = DateTimeFormatter.ofPattern("yyyyMMddHHmmss")
var time = df.format(LocalDateTime.now())
var fileName = "backup_$time.sql"
var cmd = "$mysqldumpPath -h$ip -P$port -u$username -p$password bank_actity > ${backPath}$fileName"
var cmdLinux = arrayOf("/bin/sh", "-c", cmd) // linux 命令
// var cmdWindows = arrayOf("cmd", "/c", cmd) // windows
Runtime.getRuntime().exec(cmdLinux )
logger.info("备份数据库成功")
}
获取所有备份的文件列表,后续根据文件名称进行恢复数据
fun backFiles(): ArrayList<String> {
var rs = arrayListOf<String>()
var file = File(backPath)
if (file.exists()) {
var files = file.listFiles()
files?.let {
for (f in files) {
println(f.name)
rs.add(f.name)
}
}
}
return rs
}
恢复数据
fun rollback(fileName: String) {
var file = File("${backPath}$fileName")
if (!file.exists()) {
throw NullPointerException("文件不存在")
}
var cmd = "mysql -h$ip -P$port -u$username -p$password --default-character-set=utf8 bank_actity"
// var cmdLinux = arrayOf("/bin/sh", "-c", cmd) // linux 命令
var cmdWindows = arrayOf("cmd", "/c", cmd) // windows
var os = Runtime.getRuntime().exec(cmdWindows).outputStream
var ins = FileInputStream(file)
var str = ins.bufferedReader().use(BufferedReader::readText)
var writer = OutputStreamWriter(os, "utf-8")
writer.write(str)
writer.flush()
writer.close()
ins.close()
logger.info("还原数据库成功")
}
这里说一下,一开始我是使用 BufferedReader().readLine() 实现的,但是出现内存不够的情况
正常java 写,但是在kotlin会报错了
String str = null;
StringBuffer sb = new StringBuffer()
while((str = br.readLine()) != null) {
sb.append(str + "\r\n")
}
所以kotlin 新的写法就是如下,虽然这样是可以了,但是append()方法会出现性能问题,
java.lang.OutOfMemoryError: Java heap space
var str: String? = null
var sb = StringBuffer()
while (({str = br.readLine();str}) != null) {
sb.append(str + "\r\n")
}
解决办法就是
var str = inputStream.bufferedReader().use(BufferedReader::readText)
定时程序,备份数据
@Component
class Scheduled {
@Autowired
private lateinit var service: BackupSqlService
// 每天23:30 备份数据,根据业务来
@Scheduled(cron = "0 30 23 * * ?")
fun cron() {
service.backupSQL()
}
}
又是学习的一天啊!