Linux下Mysql的备份与还原

0 前言

本以为跟windows下类似，sh文件直接写
mysqldump -h127.0.0.1 -uroot -p123456 mydb > /usr/local/db/20191016.sql
这样的命令即可。
编程之前做了测试，linux下直接写一个这样的sh文件，执行后备份失败。
提示Using a password on the command line interface can be insecure
于是加了一些其他的操作，请看下文

1 更改mysql配置

在/etc/my.cnf文件的 [client]选项以下增加3行代码

[client]
host = localhost　　　　　　　　//地址
user = root　　　　　　　　　　  //用户
password = '123456'　　　 //密码

更改后，重启mysql服务，

service mysqld restart

2 备份

   /**
	 * 
	 * @param dbName    要备份的数据库名，如 mydb
	 * @param sh_path   要创建的sh文件位置，如 /root/test/cmd.sh
	 * @param sql_path  导出的数据库文件位置，如 /root/test/1016.sql
	 * @return
	 * @throws IOException
	 */
	@RequestMapping("/db/backup")
	@ResponseBody
	public  Map<String, Object>  backup(String dbName, String sh_path, String sql_path) throws IOException  {
		Map<String, Object> result = new HashMap<String, Object>();
		// mysqldump --defaults-extra-file=/etc/my.cnf  mydb>  /root/test/1016.sql
		//组织cmd命令
		StringBuffer sb = new StringBuffer();
		sb.append("mysqldump  --defaults-extra-file=/etc/my.cnf");
		sb.append(" " + dbName + " > ");
		sb.append(sql_path);
		//创建sh文件，并写入内容
		File bat_file = new File(sh_path);  
		bat_file.createNewFile();// 创建文件,有同名的话覆盖
		//Linux下sh文件需要可执行权限
		bat_file.setExecutable(true);
		BufferedWriter bw =  new BufferedWriter(new FileWriter(sh_path));
		bw.write(sb.toString());
		bw.flush();
		bw.close();
		//执行sh文件
		CommandUtil commandUtil = new CommandUtil();
		commandUtil.executeCommand(sh_path);
		result.put("normal", commandUtil.getStdoutList()); //cmd正常过程信息
		result.put("error", commandUtil.getErroroutList()); //cmd错误提示
		result.put("backup", "备份结束");
		return result;
	}

3.还原

注意：执行此方法前，需确保要还原的数据库存在

/**
	 * 
	 * @param dbName     要还原的数据库名，如 mydb
	 * @param sql_path   导入的数据库文件位置，如 /root/test/1016.sql
	 * @param sh_path    执行的sh文件位置，如 /root/test/cmd.sh
	 * @return
	 * @throws IOException
	 */
	@RequestMapping("/db/recover")
	@ResponseBody
	public  Map<String, Object>  recover(String dbName, String sql_path, String sh_path) throws IOException  {
		Map<String, Object> result = new HashMap<String, Object>();
		// mysql --defaults-extra-file=/etc/my.cnf  microprinter < /root/1016.sql
		//组织cmd命令
		StringBuffer sb = new StringBuffer();
		sb.append("mysql --defaults-extra-file=/etc/my.cnf");
		sb.append(" " + dbName + " < ");
		sb.append(sql_path);
		//创建sh文件 并写入文件内容
		File bat_file = new File(sh_path);  
		bat_file.createNewFile();
		//Linux下sh文件需要可执行权限
		bat_file.setExecutable(true);
		BufferedWriter bw = new BufferedWriter(new FileWriter(sh_path));
		bw.write(sb.toString());
		bw.flush();
		bw.close();
		//执行sh文件
		CommandUtil commandUtil = new CommandUtil();
		commandUtil.executeCommand(sh_path);
		result.put("normal",  commandUtil.getStdoutList());//cmd正常过程信息
		result.put("error", commandUtil.getErroroutList());//cmd错误提示
		result.put("recover", "还原结束");
		return result;
	}
   

4.工具类

package com.cn.test;
 
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;
 
public class CommandUtil {
    private List<String> stdoutList = new ArrayList<String>();
    private List<String> erroroutList = new ArrayList<String>();
 
    public void executeCommand(String command) {
        stdoutList.clear();
        erroroutList.clear();
 
        Process p = null;
        try {
            p = Runtime.getRuntime().exec(command);
 
            ThreadUtil stdoutUtil = new ThreadUtil(p.getInputStream(), stdoutList);
            ThreadUtil erroroutUtil = new ThreadUtil(p.getErrorStream(), erroroutList);
            stdoutUtil.start();
            erroroutUtil.start();
 
            p.waitFor();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }
 
    public List<String> getStdoutList() {
        return stdoutList;
    }
 
    public List<String> getErroroutList() {
        return erroroutList;
    }
 
}
 
class ThreadUtil implements Runnable {
     
    private String character = "GB2312";
    private List<String> list;
    private InputStream inputStream;
 
    public ThreadUtil(InputStream inputStream, List<String> list) {
        this.inputStream = inputStream;
        this.list = list;
    }
 
    public void start() {
        Thread thread = new Thread(this);
        thread.setDaemon(true); 
        thread.start();
    }
 
    public void run() {
        BufferedReader br = null;
        try {
            br = new BufferedReader(new InputStreamReader(inputStream, character));
            String line = null;
            while ((line = br.readLine()) != null) {
                if (line != null) {
                    list.add(line);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                inputStream.close();
                br.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
 
}