Java连接proxysql管理端口

雨点打透心脏的1/2处 2021-11-23 11:42 372阅读 0赞

# Java连接proxysql管理端口 #

### 文章目录 ###

*  Java连接proxysql管理端口
 *   *  为什么不使用JDBC
     *  解决方案

## 为什么不使用JDBC ##

众所周知，proxysql管理端口实现了mysql协议，可以使用mysql client连接并使用类似SQL的语法查询和修改配置。很多人基于此就大胆地说proxysql管理端口可以使用诸如navicat等工具去连接并管理，我试过之后严重怀疑他们并没有真的用naviacat操作过，因为根本就无法执行sql，只能尝试连接而已：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NMX1lE_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NMX1lE_size_16_color_FFFFFF_t_70 1]  
确实可连接，但是操作就别想了，因为任何展示和执行的操作都会报错`[Err] 1045 - ProxySQL Admin Error: near "SHOW": syntax error`，问题很明显：navicat做了一些额外操作，除了执行正真的sql，还会执行额外的sql来查询一些展示需要的信息，但是proxysql admin是基于sqlite的，很多语法都不支持。

同样地，如果使用JDBC连接proxysql admin，一样会报错：`[Err] 1045 - ProxySQL Admin Error: near "@": syntax error`，因为JDBC会额外查询一些系统变量(比如`SELECT @@report_host`)，这种语法一样也不是sqllite支持的，导致JDBC createConnection的时候报错。

## 解决方案 ##

如果有一个纯的java mysql client就能解决这个问题了，它的功能只需要像mysql shell那样简单，不要任何额外的增强功能。自己google了一阵儿，github也搜索过一阵儿，但是都不理想。最后一个同事推荐了vertx社区使用的异步非阻塞式mysql client——[jasync-sql][]，试用了一下效果很好。

代码示例：[proxysqlclient][]

package github.clyoudu.proxysqlclient;
    
    import com.alibaba.fastjson.JSON;
    import com.github.jasync.sql.db.Connection;
    import com.github.jasync.sql.db.QueryResult;
    import com.github.jasync.sql.db.ResultSet;
    import com.github.jasync.sql.db.RowData;
    import com.github.jasync.sql.db.mysql.MySQLConnectionBuilder;
    import org.apache.commons.lang.StringUtils;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.CompletableFuture;
    import java.util.concurrent.ExecutionException;
    import java.util.concurrent.TimeUnit;
    import java.util.concurrent.TimeoutException;
    
    /** * Create by IntelliJ IDEA * * @author chenlei * @dateTime 2019/7/26 17:39 * @description ProxysqlClient */
    public class ProxysqlClient { 
    
        private final static Logger log = LoggerFactory.getLogger(ProxysqlClient.class);
    
        /** * mysql 默认连接的schema * proxysql 虽然没有这个schema，但并不会报错 */
        public static final String DEFAULT_SCHEMA = "information_schema";
        private static final String EXECUTE_SQL_ERROR = "执行sql[%s]异常";
        private static final String EXECUTE_SQL_TIMEOUT = "执行sql[%s]超时";
    
        /** * 禁止用new实例化 */
        private ProxysqlClient() { 
        }
    
        /** * 执行proxysql语句，但不需要执行结果 * 可以多条语句顺序执行，所有语句在同一个事务中执行 * 注意：proxysql不支持事务，但并不会报错 * 测试通过：mysql、proxysql * * @param ip 必填，服务IP * @param port 必填，服务端口 * @param database 选填，数据库名称，默认 information_schema * @param user 必填，用户名 * @param pass 必填，密码 * @param sqlList 必填，待执行的sql列表 * @param timeout 选填，超时时间，单位s，默认不设超时时间 * @param transaction 是否开启事务 * @return 成功or失败 * @throws ProxysqlException 所有本方法可处理的异常都包装成 github.clyoudu.proxysqlclient.ProxysqlException，外层可单独catch此异常做处理 */
        public static boolean execute(String ip, Integer port, String database, String user, String pass, List<String> sqlList, Integer timeout, boolean transaction) { 
            //参数检查
            if (StringUtils.isBlank(ip) || port == null || StringUtils.isBlank(user) || StringUtils.isBlank(pass)) { 
                throw new ProxysqlException("参数错误：ip/port/user/pass不能为空");
            }
    
            //默认连接information_schema
            if (StringUtils.isBlank(database)) { 
                database = DEFAULT_SCHEMA;
            }
    
            if (sqlList == null || sqlList.isEmpty()) { 
                throw new ProxysqlException("执行sql为空");
            }
    
            Connection connection = MySQLConnectionBuilder.createConnectionPool(
                    String.format("jdbc:mysql://%s:%s/%s?user=%s&password=%s", ip, port, database, user, pass)
            );
    
            CompletableFuture<QueryResult> result = null;
            if (transaction) { 
                result = connection.inTransaction(c -> { 
                    CompletableFuture<QueryResult> future = null;
                    for (String sql : sqlList) { 
                        if (future == null) { 
                            future = c.sendQuery(sql);
                        } else { 
                            future = future.thenCompose(r -> c.sendQuery(sql));
                        }
                    }
                    return future;
                });
            } else { 
                for (String sql : sqlList) { 
                    if (result == null) { 
                        result = connection.sendQuery(sql);
                    } else { 
                        result = result.thenCompose(r -> connection.sendQuery(sql));
                    }
                }
            }
    
            if (result == null) { 
                log.error("执行sql失败！");
                return false;
            }
    
            try { 
                if (timeout != null && timeout > 0) { 
                    result.get(timeout, TimeUnit.SECONDS);
                } else { 
                    result.get();
                }
            } catch (InterruptedException e) { 
                log.error(String.format(EXECUTE_SQL_ERROR, StringUtils.join(sqlList, ";")), e);
                Thread.currentThread().interrupt();
                throw new ProxysqlException(String.format(EXECUTE_SQL_ERROR, StringUtils.join(sqlList, ";")) + ": InterruptedException");
            } catch (ExecutionException e) { 
                log.error(String.format(EXECUTE_SQL_ERROR, StringUtils.join(sqlList, ";")), e);
                throw new ProxysqlException(String.format(EXECUTE_SQL_ERROR, StringUtils.join(sqlList, ";")) + ": " + e.getMessage());
            } catch (TimeoutException e) { 
                log.error(String.format(EXECUTE_SQL_ERROR, StringUtils.join(sqlList, ";")), e);
                throw new ProxysqlException(String.format(EXECUTE_SQL_TIMEOUT, StringUtils.join(sqlList, ";")) + "：" + timeout + "s");
            }
    
            connection.disconnect();
    
            return true;
        }
    
        /** * 执行proxysql语句，返回执行结果 * * @param ip 必填，服务IP * @param port 必填，服务端口 * @param database 选填，数据库名称，默认 information_schema * @param user 必填，用户名 * @param pass 必填，密码 * @param sqlString 必填，待执行的sql * @param timeout 选填，超时时间，单位s，默认不设超时时间 * @return List<Map> 所有字段名均为数据库原始字段名 * @throws ProxysqlException 所有本方法可处理的异常都包装成 github.clyoudu.proxysqlclient.ProxysqlException，外层可单独catch此异常做处理 */
        public static List<Map<String, Object>> executeQuery(String ip, Integer port, String database, String user, String pass, String sqlString, Integer timeout) { 
            //参数检查
            if (StringUtils.isBlank(ip) || port == null || StringUtils.isBlank(user) || StringUtils.isBlank(pass)) { 
                throw new ProxysqlException("参数错误：ip/port/user/pass不能为空");
            }
    
            //默认连接information_schema
            if (StringUtils.isBlank(database)) { 
                database = DEFAULT_SCHEMA;
            }
    
            if (StringUtils.isBlank(sqlString)) { 
                throw new ProxysqlException("执行sql为空");
            }
    
            Connection connection = MySQLConnectionBuilder.createConnectionPool(
                    String.format("jdbc:mysql://%s:%s/%s?user=%s&password=%s", ip, port, database, user, pass)
            );
    
            CompletableFuture<QueryResult> future = connection.sendQuery(sqlString);
    
            QueryResult queryResult;
            try { 
                if (timeout == null || timeout <= 0) { 
                    queryResult = future.get();
                } else { 
                    queryResult = future.get(timeout, TimeUnit.SECONDS);
                }
            } catch (InterruptedException e) { 
                log.error(String.format(EXECUTE_SQL_ERROR, sqlString), e);
                Thread.currentThread().interrupt();
                throw new ProxysqlException(String.format(EXECUTE_SQL_ERROR, sqlString) + ": InterruptedException");
            } catch (ExecutionException e) { 
                log.error(String.format(EXECUTE_SQL_ERROR, sqlString), e);
                throw new ProxysqlException(String.format(EXECUTE_SQL_ERROR, sqlString) + ": " + e.getMessage());
            } catch (TimeoutException e) { 
                log.error(String.format(EXECUTE_SQL_ERROR, sqlString), e);
                throw new ProxysqlException(String.format(EXECUTE_SQL_TIMEOUT, sqlString) + "：" + timeout + "s");
            }
    
            ResultSet resultSet = queryResult.getRows();
            List<String> names = resultSet.columnNames();
            List<Map<String, Object>> resultList = new ArrayList<>();
    
            for (RowData rowData : resultSet) { 
                Map<String, Object> map = new HashMap<>(16);
                for (String name : names) { 
                    map.put(name, rowData.get(name));
                }
                resultList.add(map);
            }
    
            connection.disconnect();
    
            return resultList;
        }
    
        public static void main(String[] args) { 
            log.info(JSON.toJSONString(executeQuery("localhost", 3306, "mysql", "root", "root",
                    "select * from user",
                    null)));
        }
    
    }

使用方式：

public static void main(String[] args) { 
            //select
            log.info(JSON.toJSONString(executeQuery("localhost", 6032, "main", "root", "root",
                    "select * from mysql_users",
                    null)));
            //ddl & batch execute
            log.info(JSON.toJSONString(execute("localhost", 6032, "main", "root", "root",
                    Arrays.asList("INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('app','pass',2)", "load mysql user to runtime", "save mysql user to disk"),
                    null, false)));
        }

当然，jasync-sql不仅支持proxysql，任何符合mysql协议的server都可以使用jasync-sql作为client，因为它没有任何额外的增强。

支持批量执行，支持事务。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NMX1lE_size_16_color_FFFFFF_t_70]: /images/20211122/a9d7e7a8707a452c9b42ec2ad03c4114.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0NMX1lE_size_16_color_FFFFFF_t_70 1]: /images/20211122/efc57101d3d7481caf5a8fe2803ed326.png
[jasync-sql]: https://github.com/jasync-sql/jasync-sql
[proxysqlclient]: https://github.com/clyoudu/clyoudu-util/tree/master/src/main/java/github/clyoudu/proxysqlclient