使用apacha sshd通过ssh隧道链接Mysql数据库, 并导出表结构
大约 6 分钟
提示
一般java通过ssh代理使用的是jcraft
jar. 据我了解, 这个已经很久没维护了. 现在好多开源组件已经迁移到apache sshd
上了. 所以我也简单研究了一下. 写了下面一段代码.
1. 引入pom.xml
文件依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.sshd</groupId>
<artifactId>sshd-netty</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.7</version>
</dependency>
注意: 这里使用了easyexcel
. 但是不符合我的要求. 所以我基本使用apache poi
生成的原生代码
2. 定义表, 字段, 索引对象
字段:
@Data
public class Column {
private String tableSchema;
private String tableName;
private String columnName;
private String ordinalPosition;
private String columnDefault;
private String isNullable;
private String columnType;
private String characterSetName;
private String collationName;
private String columnKey;
private String extra;
private String columnComment;
}
索引:
@Data
public class Index {
private String tableSchema;
private String tableName;
private String indexName;
private String tableColumns;
}
表:
@Data
public class Table {
private String tableSchema;
private String tableName;
private String engine;
private String autoIncrement;
private String createOptions;
private String tableComment;
private String tableCollation;
private List<Column> columns;
private List<Index> indices;
}
3. 定义数据库连接工具类
public class DbConn {
private final Connection connection;
public DbConn(String jdbcUrl) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
connection = DriverManager.getConnection(jdbcUrl);
}
/**
* 根据mysql schema前缀, 过滤出所有表
*/
public List<Table> tables(String dbPrefix) throws SQLException {
QueryRunner run = new QueryRunner();
List<Table> list = run.query(connection,
"select TABLE_SCHEMA, TABLE_NAME, ENGINE, AUTO_INCREMENT, TABLE_COLLATION, " +
"CREATE_OPTIONS, TABLE_COMMENT\n" +
"from information_schema.TABLES\n" +
"where TABLE_SCHEMA like CONCAT('" + dbPrefix + "', '%')\n" +
" and TABLE_TYPE = 'BASE TABLE' order by TABLE_SCHEMA, TABLE_NAME;",
new BeanListHandler<>(Table.class, new BasicRowProcessor(new GenerousBeanProcessor())));
var columnMap = column(dbPrefix);
var indexMap = index();
for (Table t : list) {
String key = t.getTableSchema() + "__" + t.getTableName();
t.setColumns(columnMap.getOrDefault(key, Collections.emptyList()));
t.setIndices(indexMap.getOrDefault(key, Collections.emptyList()));
}
connection.close();
return list;
}
/**
* 根据mysql schema前缀, 过滤出字段
*/
public Map<String, List<Column>> column(String dbPrefix) throws SQLException {
QueryRunner run = new QueryRunner();
return run.query(connection, "select TABLE_SCHEMA,\n" +
" TABLE_NAME,\n" +
" COLUMN_NAME,\n" +
" ORDINAL_POSITION,\n" +
" COLUMN_DEFAULT,\n" +
" IS_NULLABLE,\n" +
" COLUMN_TYPE,\n" +
" CHARACTER_SET_NAME,\n" +
" COLLATION_NAME,\n" +
" COLUMN_KEY,\n" +
" EXTRA,\n" +
" COLUMN_COMMENT\n" +
"from information_schema.COLUMNS\n" +
"where TABLE_SCHEMA like CONCAT('" + dbPrefix + "', '%')\n" +
"order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;",
new BeanListHandler<>(Column.class, new BasicRowProcessor(new GenerousBeanProcessor())))
.stream().collect(Collectors.groupingBy(c -> c.getTableSchema() + "__" + c.getTableName()));
}
/**
* 查询所有的索引
*/
public Map<String, List<Index>> index() throws SQLException {
QueryRunner run = new QueryRunner();
return run.query(connection,
"SELECT a.TABLE_SCHEMA,\n" +
"a.TABLE_NAME,\n" +
"a.INDEX_NAME,\n" +
"GROUP_CONCAT(column_name ORDER BY seq_in_index) AS TABLE_COLUMNS\n" +
"FROM information_schema.statistics a\n" +
"GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name;",
new BeanListHandler<>(Index.class, new BasicRowProcessor(new GenerousBeanProcessor())))
.stream().collect(Collectors.groupingBy(i -> i.getTableSchema() + "__" + i.getTableName()));
}
}
4. 写入excel文件工具类
public class Writer {
// 写入到桌面
private static final File DESKTOP_DIR = FileSystemView.getFileSystemView().getHomeDirectory();
public static void write(List<Table> tables, boolean isProd) throws IOException {
// 根据mysql schema分组, 并且注意这里是已经排序了
Map<String, List<Table>> tableMap = tables.stream().collect(Collectors.groupingBy(Table::getTableSchema,
LinkedHashMap::new, Collectors.toList()));
File file = new File(DESKTOP_DIR, Instant.now().getEpochSecond() + (isProd ? "_prod" : "") + ".xlsx");
try (Workbook workbook = new XSSFWorkbook();
OutputStream os = new FileOutputStream(file)) {
Font font = workbook.createFont();
font.setBold(true);
CellStyle header = workbook.createCellStyle();
header.setBorderBottom(BorderStyle.THIN);
header.setBorderLeft(BorderStyle.THIN);
header.setBorderRight(BorderStyle.THIN);
header.setBorderTop(BorderStyle.THIN);
header.setFillPattern(FillPatternType.SOLID_FOREGROUND);
header.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
header.setFont(font);
CellStyle body = workbook.createCellStyle();
body.setBorderBottom(BorderStyle.THIN);
body.setBorderLeft(BorderStyle.THIN);
body.setBorderRight(BorderStyle.THIN);
body.setBorderTop(BorderStyle.THIN);
// 每个schema生成一个sheet页
for (Map.Entry<String, List<Table>> entry : tableMap.entrySet()) {
Sheet sheet = workbook.createSheet(entry.getKey());
sheet.setColumnWidth(0, 30 * 256);
sheet.setColumnWidth(1, 12 * 256);
sheet.setColumnWidth(2, 12 * 256);
sheet.setColumnWidth(3, 12 * 256);
sheet.setColumnWidth(4, 15 * 256);
sheet.setColumnWidth(5, 12 * 256);
sheet.setColumnWidth(6, 18 * 256);
sheet.setColumnWidth(7, 18 * 256);
sheet.setColumnWidth(8, 18 * 256);
sheet.setColumnWidth(9, 60 * 256);
int rowIndex = 0;
// 写入表
for (Table table : entry.getValue()) {
// 写入表头
rowIndex = writeTableHeader(header, sheet, rowIndex, table);
int columnIndex = 0;
Row row0 = sheet.createRow(++rowIndex);
write("字段名", row0, columnIndex, body);
write("是否为主键", row0, ++columnIndex, body);
write("是否唯一索引", row0, ++columnIndex, body);
write("是否可为空", row0, ++columnIndex, body);
write("字段类型", row0, ++columnIndex, body);
write("字符集", row0, ++columnIndex, body);
write("字符集排序", row0, ++columnIndex, body);
write("默认值", row0, ++columnIndex, body);
write("扩展属性", row0, ++columnIndex, body);
write("字段备注", row0, ++columnIndex, body);
// 写入表字段
for (Column column : table.getColumns()) {
writeColumn(body, sheet, rowIndex, column);
rowIndex++;
}
row0 = sheet.createRow(++rowIndex);
columnIndex = 0;
write("索引名", row0, columnIndex, header);
write("索引字段", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 9));
// 写入索引
for (Index index : table.getIndices()) {
writeIndex(body, sheet, rowIndex, index);
rowIndex++;
}
// 间隔三行
sheet.createRow(++rowIndex);
sheet.createRow(++rowIndex);
sheet.createRow(++rowIndex);
}
}
workbook.write(os);
os.flush();
}
}
private static int writeIndex(CellStyle body, Sheet sheet, int rowIndex, Index index) {
int columnIndex = 0;
Row row1 = sheet.createRow(++rowIndex);
write(index.getIndexName(), row1, columnIndex, body);
write(index.getTableColumns(), row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
write("", row1, ++columnIndex, body);
sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 9));
return rowIndex;
}
private static int writeColumn(CellStyle body, Sheet sheet, int rowIndex, Column column) {
int columnIndex = 0;
Row row1 = sheet.createRow(++rowIndex);
write(column.getColumnName(), row1, columnIndex, body);
// 是否为主键
write("PRI".equals(column.getColumnKey()) ? "✔️" : "❌", row1, ++columnIndex, body);
// 是否为联合主键
write("UNI".equals(column.getColumnKey()) ? "✔️" : "❌", row1, ++columnIndex, body);
// 是否不能为null
write("YES".equals(column.getIsNullable()) ? "✔️" : "❌", row1, ++columnIndex, body);
write(column.getColumnType(), row1, ++columnIndex, body);
write(column.getCharacterSetName(), row1, ++columnIndex, body);
write(column.getCollationName(), row1, ++columnIndex, body);
write(column.getColumnDefault(), row1, ++columnIndex, body);
write(column.getExtra(), row1, ++columnIndex, body);
write(column.getColumnComment(), row1, ++columnIndex, body);
return rowIndex;
}
private static int writeTableHeader(CellStyle header, Sheet sheet, int rowIndex, Table table) {
int columnIndex = 0;
Row row0 = sheet.createRow(rowIndex);
write("表名", row0, columnIndex, header);
write("引擎", row0, ++columnIndex, header);
write("表默认字符排序", row0, ++columnIndex, header);
write("自增值", row0, ++columnIndex, header);
write("创建参数", row0, ++columnIndex, header);
write("备注", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
write("", row0, ++columnIndex, header);
sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 5, 9));
columnIndex = 0;
Row row1 = sheet.createRow(++rowIndex);
write(table.getTableName(), row1, columnIndex, header);
write(table.getEngine(), row1, ++columnIndex, header);
write(table.getTableCollation(), row1, ++columnIndex, header);
write(table.getAutoIncrement(), row1, ++columnIndex, header);
write(table.getCreateOptions(), row1, ++columnIndex, header);
write(table.getTableComment(), row1, ++columnIndex, header);
write("", row1, ++columnIndex, header);
write("", row1, ++columnIndex, header);
write("", row1, ++columnIndex, header);
write("", row1, ++columnIndex, header);
sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 5, 9));
return rowIndex;
}
private static void write(String val, Row row, int columnIdx, CellStyle cellStyle) {
Cell cell = row.createCell(columnIdx);
cell.setCellStyle(cellStyle);
cell.setCellValue(val);
}
}
5. 使用sshd
连接数据库
/**
* jdbc -> 本地转发端口(localhost:12345) -> ssh(xxx.xxx.xxx.xxx:22) -> mysql(xx.xx.xx.xx:3306)
*/
public class Main {
// 本地数据库连接地址, 用来测试excel生成是否正常
private static final String LOCAL_DB_JDBC_URL = "jdbc:mysql://127.0.0.1:3306?user=root&password=123456";
// 需要导出的schema前缀
private static final String SCHEMA_PREFIX = "test";
// 跳板服务器ip
private static final String SSH_IP = "xxx.xxx.xxx.xxx";
// 跳板服务器ssh端口
private static final int SSH_PORT = 22;
// 跳板服务器连接用户
private static final String SSH_USER = "root";
// 跳板服务器密码
private static final String SSH_PASSWORD = "123456";
// 本地请求转发端口
private static final int REDIRECT_PORT = 12345;
// 本地请求host
private static final String REDIRECT_HOST = "localhost";
// 生产mysql服务器ip
private static final String MYSQL_HOST = "xx.xx.xx.xx";
// 生产mysql服务器端口
private static final int MYSQL_PORT = 3306;
// 生产mysql服务器连接用户名
private static final String MYSQL_USER = "root";
// 生产mysql服务器连接密码
private static final String MYSQL_PASSWORD = "123456";
public static void main(String[] args) throws Exception {
boolean userSSH = true;
if (userSSH) {
connectSsh(() -> {
try {
connectMysql("jdbc:mysql://" + REDIRECT_HOST + ":" + REDIRECT_PORT
+ "?user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD, true);
} catch (Exception e) {
throw new RuntimeException(e);
}
});
} else {
connectMysql(LOCAL_DB_JDBC_URL, false);
}
}
private static void connectMysql(String jdbc, boolean isProd) throws Exception {
// 连接服务器, 生成文件
DbConn conn = new DbConn(jdbc);
var tables = conn.tables(SCHEMA_PREFIX);
Writer.write(tables, isProd);
}
private static void connectSsh(Runnable runnable) {
// 创建ssh客户端
try (SshClient client = SshClient.setUpDefaultClient()) {
// 客户端启动
client.start();
SshdSocketAddress address = null;
ClientSession session = null;
try {
// 连接跳板机服务器
session = client.connect(SSH_USER, SSH_IP, SSH_PORT).verify().getSession();
// 设置密码
session.addPasswordIdentity(SSH_PASSWORD);
// 验证密码并等待连接
boolean result = session.auth().verify().await();
if (!result) {
System.out.println("链接ssh失败");
} else {
// 开启端口转发
address = session.startLocalPortForwarding(REDIRECT_PORT,
new SshdSocketAddress(MYSQL_HOST, MYSQL_PORT));
// 执行sql任务
runnable.run();
}
} finally {
if (session != null) {
if (address != null) {
// 关闭端口转发
session.stopLocalPortForwarding(address);
}
session.close();
}
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
}