执行MySQL脚本文件是什么

一、本项目核心目的

（目前支持.sql 和 .py脚本，.java脚本跟.py脚本大同小异，只是命令不同）

1.从A服务器获取脚本文件2.上传到B服务器指定文件夹3.通过命令执行上传后得脚本文件4.返回执行结果二、核心jar包com.jcraftjsch0.1.54ch.ethz.ganymedganymed-ssh2262三、Spring Boot 与 nacos 版本不对应时会报错，当前Spring Boot版本为2.3.9.RELEASE

四、pom.xml 文件org.springframework.bootspring-boot-starterorg.springframework.bootspring-boot-starter-weborg.springframework.bootspring-boot-starter-testtestch.ethz.ganymedganymed-ssh2262com.alibaba.bootnacos-config-spring-boot-starter0.2.7org.projectlomboklombok1.18.20org.springframeworkspring-web5.2.12.RELEASEorg.springframework.cloudspring-cloud-commons2.2.2.RELEASEorg.springframework.cloudspring-cloud-context2.2.2.RELEASEcom.jcraftjsch0.1.54五、RemoteCommandConfig（服务器调用）import ch.ethz.ssh2.Connection;import ch.ethz.ssh2.Session;import ch.ethz.ssh2.StreamGobbler;import com.alibaba.fastjson.JSONObject;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.StringUtils;import org.springframework.stereotype.Component;import java.io.BufferedReader;import java.io.IOException;import java.io.InputStream;import java.io.InputStreamReader;@Slf4j@Componentpublic class RemoteCommandConfig {/** * 只是用来测试，无其他用处 */public static void main(String[] args) {Connection conn = login("这里填服务器IP地址", "账号", "密码");JSONObject execute = execute(conn, "cd /ｓｃｒｉｐｔ/ &&mysql -vvv -uadmin -p123456 spring_data < 测试.sql");System.out.println(execute.toString());}private static String DEFAULTCHART = "UTF-8";/** * @return 登录成功返回true，否则返回false * @描述 登录主机 */public static Connection login(String ip, String username, String password) {boolean flg;Connection conn = null;try {conn = new Connection(ip);conn.connect();//连接flg = conn.authenticateWithPassword(username, password);//认证if (flg) {return conn;}} catch (IOException e) {log.error("脚本执行登录服务器失败,error={}" + e.getMessage());e.printStackTrace();}return conn;}/** * @param cmd 即将执行的命令 * @return 命令执行完后返回的结果值 * @描述 远程执行shll脚本或者命令 */public static JSONObject execute(Connection conn, String cmd) {JSONObject result = new JSONObject();result.put("code", 200);String str = "";try {if (conn != null) {Session session = conn.openSession();//打开一个会话session.execCommand(cmd);//执行命令str = processStdout(session.getStdout(), DEFAULTCHART);//如果为得到标准输出为空，说明脚本执行出错了if (StringUtils.isBlank(str)) {result.put("code", 400);result.put("msg", "得到标准输出为空,链接conn:" + conn + ",执行的命令：" + cmd);str = processStdout(session.getStderr(), DEFAULTCHART);} else {result.put("msg", 200);result.put("msg", "执行命令成功,链接conn:" + conn + ",执行的命令：" + cmd);}conn.close();session.close();}} catch (IOException e) {log.info("执行命令失败,链接conn:" + conn + ",执行的命令：" + cmd + "error={}" + e.getMessage());}result.put("data", str);return result;}/** * @param in输入流对象 * @param charset 编码 * @return 以纯文本的格式返回 * @描述 解析脚本执行返回的结果集 */private static String processStdout(InputStream in, String charset) {InputStream stdout = new StreamGobbler(in);StringBuffer buffer = new StringBuffer();try {BufferedReader br = new BufferedReader(new InputStreamReader(stdout, charset));String line;while ((line = br.readLine()) != null) {buffer.append(line + "\n");}} catch (Exception e) {log.error("解析脚本出错,error={}" + e.getMessage());e.printStackTrace();}return buffer.toString();}六、RestTemplateConfig防止项目因为RestTemplate启动失败import org.springframework.boot.web.client.RestTemplateBuilder;import org.springframework.cloud.client.loadbalancer.LoadBalanced;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.web.client.RestTemplate;@Configurationpublic class RestTemplateConfig {@Bean@LoadBalancedpublic RestTemplate restTemplate(RestTemplateBuilder builder){return builder.build();}}七、ScriptDealWithController （方法整合）import ch.ethz.ssh2.Connection;import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.JSONObject;import com.alibaba.nacos.api.config.annotation.NacosValue;import com.jcraft.jsch.Channel;import com.jcraft.jsch.ChannelSftp;import com.jcraft.jsch.JSch;import com.jcraft.jsch.Session;import com.sd.config.RemoteCommandConfig;import org.apache.commons.lang3.StringUtils;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.cloud.context.config.annotation.RefreshScope;import org.springframework.http.*;import org.springframework.util.CollectionUtils;import org.springframework.util.LinkedMultiValueMap;import org.springframework.util.MultiValueMap;import org.springframework.web.bind.annotation.PostMapping;import org.springframework.web.bind.annotation.RequestBody;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import org.springframework.web.client.RestTemplate;import java.io.OutputStream;import java.util.Date;import java.util.HashMap;import java.util.Map;//nacos实时动态参数获取@RefreshScope@RestController@RequestMapping("/ｓｃｒｉｐｔ")public class ScriptDealWithController {/** * 本项目所在服务器地址 */@NacosValue(value = "${gyyh_login.ip}")private String gyyhIP;//登录获取ip@NacosValue(value = "${gyyh_login.username}")private String gyyhUsername;//系统账号@NacosValue(value = "${gyyh_login.password}")private String gyyhPassword;//系统密码/** * MySQL服务器地址 */@NacosValue(value = "${mysql_script.ip}")private String myIp;//MySQL所在ip@NacosValue(value = "${mysql_script.username}")private String myUsername;//MySQL所在ip账号@NacosValue(value = "${mysql_script.password}")private String myPassword;//MySQL所在ip密码@NacosValue(value = "${mysql_script.command}")private String myCommand;//MySQL脚本执行命令@NacosValue(value = "${mysql_script.file_path}")private String myFilePath;//文件保存路径/** * pythonL服务器地址 */@NacosValue(value = "${python_script.ip}")private String pyIp;//MySQL所在ip@NacosValue(value = "${python_script.username}")private String pyUsername;//MySQL所在ip账号@NacosValue(value = "${python_script.password}")private String pyPassword;//MySQL所在ip密码@NacosValue(value = "${python_script.command}")private String pyCommand;//MySQL脚本执行命令@NacosValue(value = "${python_script.file_path}")private String pyFilePath;//文件保存路径@Autowiredprivate RestTemplate restTemplate;/** * @描述 获取平台登录token * @参数 [] * @返回值 java.lang.String * @创建时间 2021/7/23 */public String getToken() {String url = gyyhIP + "/获取token地址";HttpHeaders headers = new HttpHeaders();headers.setContentType(MediaType.APPLICATION_FORM_URLENCODED);MultiValueMap params = new LinkedMultiValueMap();params.add("username", gyyhUsername);params.add("password", gyyhPassword);HttpEntity requestEntity = new HttpEntity(params, headers);ResponseEntity response = restTemplate.exchange(url, HttpMethod.POST, requestEntity, String.class);JSONObject body = JSON.parseObject(response.getBody());JSONObject data = body.getJSONObject("data");if (CollectionUtils.isEmpty(data)) {throw new RuntimeException("token获取失败");}return data.getString("token");}/** * @描述 调用平台文件下载接口获取文件流 * @参数 [body] * @返回值 byte[] * @创建时间 2021/7/23 */public byte[] download(String token, String type, String downloadId) {String url = gyyhIP + "文件下载接口地址";HttpHeaders headers = new HttpHeaders();headers.setContentType(MediaType.APPLICATION_JSON);headers.set("Authorization", token);Map params = new HashMap();params.put("type", type);params.put("downloadId", downloadId);HttpEntity requestEntity = new HttpEntity(params, headers);ResponseEntity response = restTemplate.postForEntity(url, requestEntity, byte[].class);return response.getBody();}/** * @描述 写入文件到指定目录下 * @参数 [bfile, filePath, fileName] * @返回值 void * @创建时间 2021/7/23 */public void upload(byte[] bfile, String ip, String username, String password, String filePath, String fileName) throws Exception {//服务器端口 默认22int port = 22;Session session = null;Channel channel = null;JSch jsch = new JSch();if (port