package com.whaty.getConfigSql.service.impl;
import com.whaty.cbs.core.dao.EntityDao;
import com.whaty.cbs.core.util.DateUtil;
import com.whaty.cbs.plugin.service.service.impl.AbstractEntityServiceImpl;
import com.whaty.getConfigSql.Conastant;
import com.whaty.getConfigSql.PrintStream;
import com.whaty.getConfigSql.bean.GetConfig;
import com.whaty.getConfigSql.dao.GetConfigDao;
import com.whaty.getConfigSql.service.GetConfigService;
import net.sf.json.JSONObject;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.Future;
@Service
public class GetConfigServiceImp extends AbstractEntityServiceImpl<GetConfig> implements GetConfigService {
@Resource
private GetConfigDao getConfigDao;
@Resource
ThreadPoolTaskExecutor taskExecutor;
@Override
public EntityDao<GetConfig> getDao() {
return getConfigDao;
}
@Override
public String getConfigSqlFile(String startDate, String endDate)throws Exception {
File filesPath = new File(Conastant.sqlPath);
File[] files = filesPath.listFiles();
if(files== null || files.length<=0){
return null;
}
File[] getSqlFile = new File[files.length];
System.out.println("startDate:" + startDate);
System.out.println("endDate:" + endDate);
Date startDateD = DateUtil.parseDate(startDate,DateUtil.FULL_DATE_TIME);
Date endDateD = DateUtil.parseDate(endDate,DateUtil.FULL_DATE_TIME);
int fileNum = 0;
SimpleDateFormat simpleDateFormat = new SimpleDateFormat();
for(File file : files){
if(file.getName().indexOf(Conastant.aboutName)>-1 && file.getName().indexOf(Conastant.aboutName+"index") == -1){
Date fileDate = new Date(file.lastModified());
if(fileDate.before(endDateD) && fileDate.after(startDateD)){
getSqlFile[fileNum] = file;
fileNum ++;
}
}
}
String parentPath = "/www/htdocs/cbswebapps/hdsf/product/incoming/getSql/" + startDate.substring(0,10) + "_" + endDate.substring(0,10);
File f = new File(parentPath);
if(!f.exists()){
f.mkdir();
}
int futureNum = 0;
Future[] futures = new Future[getSqlFile.length];
JSONObject jsonObject = new JSONObject();
//提交任务
for(File file : getSqlFile){
if(file!=null){
Future future = taskExecutor.submit(new getSqlContent(file, startDate, endDate, parentPath));
futures[futureNum] = future;
futureNum ++ ;
String name = "";
System.out.println("name:" + file.getName());
if(file.getName().indexOf(".")>-1){
name = file.getName().substring(file.getName().indexOf("."));
}else{
name = file.getName();
}
jsonObject.element(file.getName(), parentPath + "/sql_" + name);
}
}
//等待任务执行结束
for(Future future : futures){
if(future!=null){
future.get();
}
}
GetConfig getConfig = new GetConfig();
getConfig.setPath(jsonObject.toString());
getConfig.setCreateDate(new Date());
getConfig.setUpdateDate(new Date());
getConfig.setStatus("1");
this.getConfigDao.create(getConfig);
return null;
}
@Override
public String getStartDate() {
GetConfig getConfig = (GetConfig)this.getConfigDao.getStartDate().get(0);
return getConfig.getCreateDate().toString();
}
@Override
public String saveEndDate() {
return null;
}
class getSqlContent implements Runnable{
private File file;
private String startDate;
private String endDate;
private String parentPath;
getSqlContent(File f, String startDate, String endDate, String parentPath){
this.file = f;
this.startDate = startDate;
this.endDate = endDate;
this.parentPath = parentPath;
}
@Override
public void run() {
String name = file.getName().substring(file.getName().indexOf(".")+1);
StringBuffer command = new StringBuffer();
command.append("/home/cbs/getSql.sh");
command.append(" " + startDate.substring(0,10));
command.append(" " + startDate.substring(11));
command.append(" " + endDate.substring(0,10));
command.append(" " + endDate.substring(11));
command.append(" " + file.getAbsolutePath());
command.append(" " + parentPath + "/sql_" + name+ ".sql");
System.out.println("command: "+command);
try {
Process process = Runtime.getRuntime().exec(command.toString());
taskExecutor.submit(new PrintStream(process.getErrorStream()));
taskExecutor.submit(new PrintStream(process.getInputStream()));
//等待进程结束
if(process.waitFor()==0){
process.destroy();
}
System.out.println("获取sql成功");
} catch (Exception e) {
e.printStackTrace();
}
}
}
public static void main(String[] args){
}
}
好久没更新,先来段代码
脚本文件getSql.sh
startDate=$1" "$2
endDate=$3" "$4
mysqlbinlog --no-defaults --start-datetime="$startDate" --stop-datetime="$endDate" --database=cbs_product_dev $5 |grep update |more >>$6
大家可以试试= =
获取mysqlbinlog里面的sql数据,导出来到项目文件夹中,筛选出update,delete等数据,因为二进制文件除了记录delete等(本来也不记录select,只记录对表的修改),需要去掉 mysql的一些属性字段。比如pos(position) ,日期等。
坑主要在 datetime这个值,传递的时候,我在代码中直接用mysqlbinlog命令,动态改变datetime,服务器一直报datetime format error
就自己写个脚本去运行。