java 代码实现 Linux+Mysql 数据库 主从配置

主服务器：192.168.1.1
从服务器：192.168.1.2

复制一下代码即可使用

package com.ccit.vpn.utils;

import ch.ethz.ssh2.Connection;
import cn.hutool.core.io.resource.ClassPathResource;
import com.mysql.cj.x.protobuf.MysqlxDatatypes;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * @author lpx
 * @since 2022-06-23
 * 用于数据库数据同步和主从配置
 */
public class MysqlSlaveUtil {


    private static final String CONF="[client]\n" +
            "port=3306\n" +
            "socket=/root/mysql/mysql.sock\n" +
            "\n" +
            "[mysqld]\n" +
            "sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION\n" +
            "lower_case_table_names=1\n" +
            "port=3306\n" +
            "basedir=/root/mysql\n" +
            "datadir=/root/mysql/data\n" +
            "pid-file=/root/mysql/mysql.pid\n" +
            "socket=/tmp/mysql.sock\n" +
            "log_error=/root/mysql/error.log\n" +
            "server-id={serverId}\n" +
            "lower_case_table_names=1\n" +
            "max_connections=1000\n" +
            "secure-file-priv = ''\n" +
            "log-bin=MySQL-bin  # 开启mysql binlog功能\n" +
            "binlog-do-db={DBbinlog}      #需要同步的库\n" +
            "replicate-do-db={DBreplicate}    #需要同步的库\n" +
            "slave-net-timeout=60";

    /**
     * 同步数据库数据--方法
     * hostIp   源服务器IP
     * hostUser 源服务器数据库用户名
     * hostPass 源服务器数据库用户密码
     * targetIp 目标服务器IP
     * targetUser 目标服务器数据库用户名
     * targetPass 目标服务器数据库用户密码
     * DBname   需要同步数据的数据库名称
     * @return
     */
    public static List<String> mysqlDump(String hostIp, String hostUser, String hostPass
            , String targetIp, String targetUser, String targetPass, String DBname){
        List<String> cmds = new ArrayList<String>();
        /*mysql -uroot -proot*/
        /*同步前  先锁定数据库表  避免同步时 进行表写入等操作导致数据错误失败*/
        /*flush tables with read lock  全局读锁定，执行了命令之后所有库所有表都被锁定只读。
        * unlock tables
        * 全局锁  必须保持会话存在所以不考虑pass
        * show global variables like "%read_only%";
        * set global read_only=1;
        * set global super_read_only = 1;
        * mysql  -h192.168.1.1 -uroot -proot -e"use shujuku;set global read_only=1;set global super_read_only = 1;"
         *show slave status\G;
         *  */
        String cmd = "mysql  -h"+hostIp+" -u"+hostUser+" -p"+hostPass+" " +
                "-e'use "+DBname+";set global read_only=1;set global super_read_only = 1;'";
        cmds.add(cmd);
        /*
        mysqldump -h127.0.0.1 -uroot -proot -P3306 --default-character-set=utf8  dbname | mysql -h192.168.1.1 -uroot -proot -P3306 dbname
        * */
        cmd = " mysqldump " +
                "-h"+hostIp+" " +
                "-u"+hostUser+" " +
                "-p"+hostPass+" " +
                "-P3306 --default-character-set=utf8  " +
                DBname+" | mysql " +
                "-h"+targetIp+" " +
                "-u"+targetUser+" " +
                "-p"+targetPass+" " +
                "-P3306 --default-character-set=utf8  " +
                DBname+";";
        cmds.add(cmd);
        /*6.同步完成--解除数据库只读锁定*/
        cmd = "mysql  -h"+hostIp+" -u"+hostUser+" -p"+hostPass+" " +
                "-e'use "+DBname+";set global read_only=0;set global super_read_only = 0;'";
        cmds.add(cmd);
        return cmds;
    }

    /**
     * 配置数据库主从配置，并开启主从配置的方法
     * @param masterIp   主服务器IP
     * @param masterUser 主服务器数据库用户
     * @param masterServerName   主服务器远程用户
     * @param masteServerPass    主服务器远程密码
     * @param masterPass 主服务器数据库密码
     * @param userName   创建同步用户  用户名
     * @param password   创建同步用户  密码
     * @param backupIp   从服务器IP
     * @param DBname     备份数据库名称
     * @param proPath    数据库配置文件路径
     * @param backupName  从服务器数据库用户
     * @param backupPass    从服务器数据库密码
     * @param backupServerName  从服务器远程用户
     * @param backupServerPass    从服务器远程密码
     * @param ServerPort    服务器远程端口
     * @return
     */
    public static List<String> mysqlPropert(String masterIp,String masterUser,
                                            String masterServerName, String masteServerPass,
                                            String masterPass, String userName,
                                            String password,String backupIp,
                                            String backupName,String backupPass,
                                            String backupServerName,String backupServerPass,
                                            int ServerPort,
                                            String DBname, String proPath){
        List<String> cmds = new ArrayList<String>();
        Connection connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
        Connection connection2 = DMruntimeUtil.login(backupIp,backupServerName,backupServerPass,ServerPort);
        /*1.修改 数据库配置文件  /root/mysql/my.cnf   主从都要改*/
        String conf = CONF.replace("{DBbinlog}",DBname).replace("{DBreplicate}",DBname).replace("{serverId}","101");
        String cmd ="echo \""+conf+"\"  >  "+proPath;
        conf= CONF.replace("{DBbinlog}",DBname).replace("{DBreplicate}",DBname).replace("{serverId}","102");
        String cmd2 ="echo \""+conf+"\"  >  "+proPath;
        DMruntimeUtil.execute(connection,cmd);
        DMruntimeUtil.execute(connection2,cmd2);
        /*2.重启mysql数据库  主从都要进行重启*/
        connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
        connection2 = DMruntimeUtil.login(backupIp,backupServerName,backupServerPass,ServerPort);
        cmd = "systemctl restart mysql.server";
        DMruntimeUtil.execute(connection,cmd);
        DMruntimeUtil.execute(connection2,cmd);
        /*3.创建用于复制的专门用户,并刷新用户权限
        * mysql -uroot -proot -e"GRANT all privileges ON *.* TO tongbu@'192.168.1.2' IDENTIFIED BY '123456'";
         * */
        cmd ="mysql  -u"+masterUser+" -p"+masterPass+" " +
                "-e'GRANT all privileges ON *.* TO "+userName+"@\""+backupIp+"\" IDENTIFIED BY \""
                +password+"\";flush privileges;'";
        cmds.add(cmd);
        /*锁定数据库*/
        cmd = "mysql  -h"+masterIp+" -u"+masterUser+" -p"+masterPass+" " +
                "-e'use "+DBname+";set global read_only=1;set global super_read_only = 1;'";
        cmds.add(cmd);
        /*4.获取主服务器同步信息
        * mysql -uroot -proot -e"show master status;"
        * */
        connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
        cmd ="mysql  -u"+masterUser+" -p"+masterPass+" " +
                "-e'show master status;'";
        Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
        String value = (String) result.get("value");
        String[] arrs = value.split("\n");
        String[] arr = arrs[arrs.length-1].split("\t");
        /*5.配置从服务器  同步信息
        * change master to master_host='192.168.1.1',master_user='tongbu',master_password='123456',master_log_file='MySQL-bin.000002',master_log_pos=301126;
        * */
        cmd ="mysql -h"+backupIp+" -u"+backupName+" -p"+backupPass+" " +
                "-e'change master to master_host=\""+masterIp+"\",master_user=\""+userName+"\"," +
                "master_password=\""+password+"\",master_log_file=\""+arr[0]+"\"," +
                "master_log_pos="+arr[1]+";'";
        cmds.add(cmd);
        /*5.启动同步
        * start slave;
        * */
        cmd ="mysql -h"+backupIp+" -u"+backupName+" -p"+backupPass+" " +
                "-e'start slave;'";
        cmds.add(cmd);
        /*6.同步完成--解除数据库只读锁定*/
        cmd = "mysql  -h"+masterIp+" -u"+masterUser+" -p"+masterPass+" " +
                "-e'use "+DBname+";set global read_only=0;set global super_read_only = 0;'";
        cmds.add(cmd);
        return cmds;
    }

    /**
     * 综合调用 同步以及配置主从开启的方法  统一入参
     * @param masterIp   主服务器IP
     * @param masterUser 主服务器数据库用户
     * @param masterServerName   主服务器远程用户
     * @param masteServerPass    主服务器远程密码
     * @param masterPass 主服务器数据库密码
     * @param userName   创建同步用户  用户名
     * @param password   创建同步用户  密码
     * @param backupIp   从服务器IP
     * @param DBname     备份数据库名称
     * @param proPath    数据库配置文件路径
     * @param backupName  从服务器数据库用户
     * @param backupPass    从服务器数据库密码
     * @param backupServerName  从服务器远程用户
     * @param backupServerPass    从服务器远程密码
     * @param ServerPort    服务器远程端口
     * @return
     */
    public static String msyqlAll(String masterIp,String masterUser,
                                  String masterServerName, String masteServerPass,
                                  String masterPass, String userName,
                                  String password,String backupIp,
                                  String backupName,String backupPass,
                                  String backupServerName,String backupServerPass,
                                  int ServerPort,
                                  String DBname, String proPath){
        List<String> cmds =  mysqlDump(masterIp,masterUser,masterPass
                ,backupIp,backupName,backupPass,DBname);
        for (String cmd:cmds) {
            Connection connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
            Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
            String value = (String) result.get("value");
            if(value.contains("Error") || value.contains("error")){
                System.out.println("命令：："+cmd+"执行失败：："+value);
                connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
                cmd = "mysql  -h"+masterIp+" -u"+masterUser+" -p"+masterPass+" " +
                        "-e'use "+DBname+";set global read_only=0;set global super_read_only = 0;'";
                DMruntimeUtil.execute(connection,cmd);
                return value;
            }else{
                System.out.println("命令：："+cmd+"执行成功::"+value);
            }
        }
        cmds =  mysqlPropert(masterIp,masterUser,masterServerName,masteServerPass, masterPass,userName,password, backupIp,backupName,
                backupPass, backupServerName,backupServerPass,ServerPort, DBname,proPath);
        for (String cmd:cmds) {
            Connection connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
            Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
            String value = (String) result.get("value");
            if(value.contains("Error") || value.contains("error") ||value.contains("ERROR")){
                System.out.println("命令：："+cmd+"执行失败：："+value);
                connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
                cmd = "mysql  -h"+masterIp+" -u"+masterUser+" -p"+masterPass+" " +
                        "-e'use "+DBname+";set global read_only=0;set global super_read_only = 0;'";
                DMruntimeUtil.execute(connection,cmd);
                return value;
            }else{
                System.out.println("命令：："+cmd+"执行成功::"+value);
            }
        }
        return "success";
    }

    /**
     *  停止并删除 主从关系以及配置数据信息
     * @param masterIp   主服务器IP
     * @param ServerPort 远程端口
     * @param masterServerName   主服务器远程用户
     * @param masteServerPass    主服务器远程密码
     * @param backupIp    从服务器IP
     * @param backupName  从服务器数据库用户
     * @param backupPass  从服务器数据库密码
     * @return
     */
    public static String stopMysqlSlave(String masterIp, String masterServerName, String masteServerPass,
                                        int ServerPort, String backupIp, String backupName,String backupPass){
        /*
        * stop slave 停止主从同步
        * reset slave all  删除所有与主库之间的关联，如需再次启动需要重新配置连接信息
        * */
        Connection connection = DMruntimeUtil.login(masterIp,masterServerName,masteServerPass,ServerPort);
        String cmd ="mysql -h"+backupIp+" -u"+backupName+" -p"+backupPass+" " +
                "-e'stop slave;reset slave all;'";
        Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
        String value = (String) result.get("value");
        if(value.contains("Error") || value.contains("error") ||value.contains("ERROR")){
            System.out.println("命令：："+cmd+"执行失败：："+value);
            return value;
        }else{
            System.out.println("命令：："+cmd+"执行成功::"+value);
        }
        return "success";
    }

    /**
     * windows 测试代码
     * @param args
     */
    public static void main(String[] args) {
        /*---------------------主从同步测试--start----------------------------------*/
        List<String> cmds =  mysqlDump("192.168.1.1","root","root"
        ,"192.168.109.2","root","root","shujuku");
//       for (String cmd:cmds) {
//           Connection connection = DMruntimeUtil.login("192.168.1.1","root","123456",22);
//           Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
//            String value = (String) result.get("value");
//            if(value.contains("Error") || value.contains("error")){
//                System.out.println("命令：："+cmd+"执行失败：："+value);
//                connection = DMruntimeUtil.login("192.168.1.1","root","123456",22);
//                cmd = "mysql  -h192.168.1.1 -uroot -proot -e'use shujuku;set global read_only=0;set global super_read_only = 0;'";
//                DMruntimeUtil.execute(connection,cmd);
//                return;
//            }else{
//                System.out.println("命令：："+cmd+"执行成功::"+value);
//            }
//        }
        cmds =  mysqlPropert("192.168.1.1","root",
                "root","123456",
                "root","tongbu","123456",
                "192.168.1.2","root","root",
                "root","123456",22,
                "shujuku","/root/mysql/my.cnf");
        for (String cmd:cmds) {
            Connection connection = DMruntimeUtil.login("192.168.1.1","root","123456",22);
            Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
            String value = (String) result.get("value");
            if(value.contains("Error") || value.contains("error") ||value.contains("ERROR")){
                System.out.println("命令：："+cmd+"执行失败：："+value);
                connection = DMruntimeUtil.login("192.168.1.1","root","123456",22);
                cmd = "mysql  -h192.168.1.1 -uroot -proot" +
                        "-e'use shujuku;set global read_only=0;set global super_read_only = 0;'";
                DMruntimeUtil.execute(connection,cmd);
                return;
            }else{
                System.out.println("命令：："+cmd+"执行成功::"+value);
            }
        }
        /*---------------------主从同步测试--end----------------------------------*/

        /*------------------------主从停止测试--start-----------------------------*/
        /*
         * stop slave 停止主从同步
         * reset slave all  删除所有与主库之间的关联，如需再次启动需要重新配置连接信息
         * */
//        Connection connection = DMruntimeUtil.login("192.168.1.1","root","123456",22);
//        String cmd ="mysql -h192.168.1.2 -uroot -proot -e'stop slave;reset slave all;'";
//        Map<String, Object> result = DMruntimeUtil.execute(connection,cmd);
//        String value = (String) result.get("value");
//        if(value.contains("Error") || value.contains("error") ||value.contains("ERROR")){
//            System.out.println("命令：："+cmd+"执行失败：："+value);
//        }else{
//            System.out.println("命令：："+cmd+"执行成功::"+value);
//        }

        /*------------------------主从停止测试--end-----------------------------*/
    }

}

package com.utils;

import ch.ethz.ssh2.Connection;
import ch.ethz.ssh2.Session;
import ch.ethz.ssh2.StreamGobbler;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *工具类
 */
public class DMruntimeUtil {

    private static final String DEFAULT_CHARSET = "utf-8";

    private static final  Logger LOGGER = LoggerFactory.getLogger(DMruntimeUtil.class);


    /**
     * 登录主机
     *
     * @return 登录成功返回true，否则返回false
     */
    public static Connection login(String ip, String userName, String userPwd, Integer prot) {

        boolean flg = false;
        Connection conn = null;
        try {
            conn = new Connection(ip,prot);
            conn.connect();// 连接
            if (!conn.isAuthenticationPartialSuccess()){
                flg = conn.authenticateWithPassword(userName, userPwd);// 认证
                if (flg) {
                    LOGGER.info("=========登录成功=========" + conn);
                    return conn;
                }else{
                    LOGGER.error("=========登录失败=========" + conn);
                    return null;
                }
            }
            return conn;
        } catch (IOException e) {
            LOGGER.error("=========登录失败=========" + e.getMessage());
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 远程执行shll脚本或者命令
     *
     * @param cmd 即将执行的命令
     * @return 命令执行完后返回的结果值
     */
    public static Map<String, Object> execute(Connection conn, String cmd) {
        Map<String, Object> result =  new HashMap<String, Object>(16);
        try {
            if (conn != null) {
                Session session = conn.openSession();// 打开一个会话
                session.execCommand(cmd);// 执行命令
                result = processStdout(session.getStdout(), DEFAULT_CHARSET);
                // 如果为得到标准输出为空，说明脚本执行出错了
                if (StringUtils.isBlank(result.get("value")+"")) {
                    LOGGER.info("得到标准输出为空,链接conn:" + conn + ",执行的命令：" + cmd);
                    result = processStdout(session.getStderr(), DEFAULT_CHARSET);
                } else {
                    LOGGER.info("执行命令成功,链接conn:" + conn + ",执行的命令：" + cmd);
                }
                conn.close();
                session.close();
            }
        } catch (IOException e) {
            LOGGER.info("执行命令失败,链接conn:" + conn + ",执行的命令：" + cmd + "  " + e.getMessage());
            e.printStackTrace();
            result= null;
        }
        return result;
    }


    /**
     * 远程执行shll脚本或者命令
     *
     * @param cmd 即将执行的命令
     * @return 命令执行完后返回的结果值
     */
    public static Map<String, Object> execute(String cmd) {
    	Map<String, Object> result =  new HashMap<String, Object>(16);
        try {
            Process p = Runtime.getRuntime().exec(cmd);
            result = processStdout(p.getInputStream(), DEFAULT_CHARSET);
            // 如果为得到标准输出为空，说明脚本执行出错了
            if (StringUtils.isBlank(result.get("value")+"")) {
                LOGGER.info("得到标准输出为空,执行的命令：" + cmd);
                result = processStdout(p.getErrorStream(), DEFAULT_CHARSET);
            } else {
                LOGGER.info("执行命令成功,执行的命令：" + cmd);
            }
        } catch (IOException e) {
            LOGGER.error("执行命令失败,执行的命令：" + cmd + "  " , e);
            result= null;
        }
        return result;
    }

 /**
     * 解析脚本执行返回的结果集
     *
     * @param in      输入流对象
     * @param charset 编码
     * @return 以纯文本的格式返回
     */
    private static Map<String, Object> processStdout(InputStream in, String charset) {
    	//创建String 集合
    	List<String> arrString = new ArrayList<String>(); 
        InputStream stdout = new StreamGobbler(in);
        StringBuffer buffer = new StringBuffer();
        ;
        try {
            BufferedReader br = new BufferedReader(new InputStreamReader(stdout, charset));
            String line = null;
            while ((line = br.readLine()) != null) {
                buffer.append(line + "\n");
                arrString.add(line);
            }
        } catch (UnsupportedEncodingException e) {
            LOGGER.error("解析脚本出错：" + e.getMessage());
            e.printStackTrace();
        } catch (IOException e) {
            LOGGER.error("解析脚本出错：" + e.getMessage());
            e.printStackTrace();
        }
        Map<String, Object> result =  new HashMap<String, Object>(16);
        result.put("arrString", arrString);
        result.put("value", buffer.toString());
        return result;
    }

}

调用数据库主从的时候，涉及到了重启服务器，请使用线程调用，避免重启mysql导致连接池问题。

   new Thread(() -> {
                    /*首先同步数据库数据  开启数据库主从配置*/
                    MysqlSlaveUtil.msyqlAll();
                } ).start();