自动化调优——快速复制数据库

阅读本文大约需要3分钟

背景

随着自动化用例的积累,自动化回归用例耗时越来越长。

为提高自动化回归效率,有的团队开始做减法,结合精准测试思想,只回归被影响的用例;这依赖于精准算法的准确度,而由于开发代码百花齐放,业界的精准算法也只是在趋于精准的道路上努力着,谁也不敢保证百分百精准,采用这种方案,有一定的技术难度。

而有的团队,则采用一分为二的方法,采用资源换时间。相信很多团队的自动化用例即使在单线程情况下都难以保证永远稳定,更别说在多线程的情况下了。那么就采取多套独立环境来执行自动化回归吧,我们称之为“伪并发”。“伪并发”需要多套互不干扰的独立环境(数据库、mc、redis、配置……), 此文我们仅介绍独立环境所需要的数据库如何快速初始化。

原理

1.如原自动化的数据库:10.100.199.101:3306,账号/密码:root/old;

被复制的数据库:10.100.199.102:3306,账号/密码:root/new;新旧数据库均包含information_schema库,链接该库执行以下sql;

2.执行sql,获取旧数据库中的所有库:SHOW DATABASES;

3.遍历所有的database(黑名单排除部分不需要的库),执行sql,获取所有表:SHOW TABLES;

4.遍历所有的table,执行sql,获取所有建表语句:SHOW CREATE TABLE 库名.表名;

5.建库,若数据库不存在,则新建数据库:CREATE DATABASE IF NOT EXISTS 库名;

6.建表,若表存在,则销毁后重建:USE 库名;DROP TABLE IF EXISTS 表名; 执行建表语句;

代码实现

@Slf4j
@Service
public class DataBaseService {
    
    static final String DB_URL_SUFFIX = "?useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true";

    Connection connection = null;
    Statement statement = null;
    String defaultDatabase = "information_schema";
    List<String> ignoreDatabases = Arrays.asList("information_schema", "log", "mysql", "performance_schema", "sys");

    public void connect() throws Exception {
        close();
        //默认从STAGING环境复制
        connection = MyDataSource.getConnection("STAGING", defaultDatabase);

        // 获取数据库连接失败,直接抛出异常
        if (connection == null) {
            throw new Exception("获取数据库连接失败");
        }

        statement = connection.createStatement();
    }

    public void connect(String hostPort, String user, String pass) throws Exception {
        close();
        connection = DriverManager.getConnection(String.format("jdbc:mysql://%s/", hostPort) + defaultDatabase + DB_URL_SUFFIX, user, pass);

        // 获取数据库连接失败,直接抛出异常
        if (connection == null) {
            throw new Exception("获取数据库连接失败");
        }

        statement = connection.createStatement();
    }

    public void close() {
        new AutoCloseUtils(connection, statement);
    }

    public List<Map<String, String>> showDatabases() {
        String sql = "SHOW DATABASES;";

        ResultSet resultSet = null;
        resultSet = execute(sql);

        List list = DataBaseUtils.convertToObjectList(resultSet);
        if (list != null && list.size() > 0) {
            return list;
        }
        return Collections.EMPTY_LIST;
    }

    public List<Map<String, String>> showTables(String database) {
        List list = null;
        execute("USE " + database + " ;");
        ResultSet resultSet = execute("SHOW TABLES;");
        list = DataBaseUtils.convertToObjectList(resultSet);

        if (list != null && list.size() > 0) {
            return list;
        }
        return Collections.EMPTY_LIST;
    }

    public List<Map<String, String>> showCreateTable(String database, String tableName) {
        String sql = "SHOW CREATE TABLE " + database + "." + tableName + " ;";

        ResultSet resultSet = null;
        resultSet = execute(sql);

        List list = DataBaseUtils.convertToObjectList(resultSet);
        if (list != null && list.size() > 0) {
            return list;
        }
        return Collections.EMPTY_LIST;
    }

    public ResultSet execute(String sql) {
        try {
            ResultSet resultSet = statement.executeQuery(sql);
            return resultSet;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<String> getCreateSql(String hostPort, String user, String pass) {

        List<String> sqlList = new ArrayList<>();
        try {
            if(StringUtils.isEmpty(hostPort) || StringUtils.isEmpty(user) || StringUtils.isEmpty(pass)){
                connect();
            }else{
                connect(hostPort, user, pass);
            }
            List<Map<String, String>> databases = showDatabases();
            for (Map<String, String> database : databases) {
                Set<String> databaseKeySet = database.keySet();
                for (String databaseKey : databaseKeySet) {
                    String databaseName = database.get(databaseKey);
                    if(ignoreDatabases.contains(databaseName)){
                        continue;
                    }
                    sqlList.add("CREATE DATABASE IF NOT EXISTS " + databaseName + ";");
                    sqlList.add("USE " + databaseName + ";");
                    List<Map<String, String>> tables = showTables(databaseName);
                    for (Map<String, String> table : tables) {
                        Set<String> tableKeySet = table.keySet();
                        for (String tableKey : tableKeySet) {
                            String tableName = table.get(tableKey);
                            List<Map<String, String>> createTable = showCreateTable(databaseName, tableName);
                            if (CollectionUtils.isNotEmpty(createTable) && StringUtils.isNotEmpty(createTable.get(0).get("Create Table"))) {
                                sqlList.add("DROP TABLE IF EXISTS " + tableName + ";");
                                sqlList.add(createTable.get(0).get("Create Table").replace("\"", "") + ";");
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close();
        }
        return sqlList;
    }

    public void execute(String hostPort, String user, String pass, List<String> sqlList) {
        try {
            connect(hostPort, user, pass);
            log.info("开始复制……");
            for (String sql : sqlList) {
                try{
                    statement.executeUpdate(sql);
                }catch (Exception e){
                    log.error(sql);
                    e.printStackTrace();
                }
            }
            log.info("复制结束!!!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void main(String... args) {
        DataBaseService dataBaseService = new DataBaseService();
        List<String> sqlList = dataBaseService.getCreateSql("10.100.199.101", "root", "old");
        dataBaseService.execute("10.100.199.102", "root", "new", sqlList);
    }


}
我来评几句
登录后评论

已发表评论数()

相关站点

+订阅
热门文章