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

发表于   |   分类于

Git Parameter

阅读本文大约需要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 表名; 执行建表语句;

代码实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157

@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);
    }


}