数据库迁移到 mysql
# 前言
Jpom 服务端的数据默认存储到 H2database 中
部分用户有需求期望数据存储到 mysql 中
提醒
使用 mysql 模式,系统管理中的数据库备份功能将不可用,数据的备份管理需要自行维护
# 系统升级
使用jpom的在线升级功能将将服务端和插件端版本升级至 2.10.11
注意
升级前请仔细阅读版本更新日志,如果有特殊说明或者注意事项请仔细确认。升级前建议提前做好相关备份,避免出现意外造成数据丢失
# 如何创建 h2 数据备份
# 页面操作创建
# 命令行创建
bash ./bin/Server.sh restart -15 --backup-h2
1
# 修改配置
配置文件:conf/application.yml
- 修改 jpom.db.mode 为 MYSQL
- 修改 jpom.db.url 为你 mysql 的 jdbc 地址( jdbc:mysql://127.0.0.1: 3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false)
- 修改 jpom.db.user-name 为对应 mysql 账户 -修改 jpom.db.user-pwd 为对应 mysql 密码
db:
mode: MYSQL
url: jdbc:mysql://xx.xx.xx.xx:3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false
# 日志存储条数,将自动清理旧数据,配置小于等于零则不清理
log-storage-count: 10000
# 数据库账号 默认 jpom
user-name: root
# 数据库密码 默认 jpom 如果自行配置请保证密码强度
user-pwd: 123456
# h2 数据库缓存大小 kilobyte 1KB 1,024 megabyte 1MB 1,048,576
cache-size: 50MB
# 自动备份间隔天数 小于等于 0 不自动备份
auto-backup-interval-day: 1
# 自动备份保留天数 小于等于 0,不自动删除自动备份数据
auto-backup-reserve-day: 5
# 数据库连接池相关配置
max-active: 100
initial-size: 10
max-wait: 10
min-idle: 1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 执行命令
bash ./bin/Server.sh restart -15 --h2-migrate-mysql --h2-user=jpom --h2-pass=jpom
1
如果您修改过 h2 数据库的账号或者密码,请在命令行参数里面填写您修改过的
提醒
- 为了保证数据安全,在迁移前程序会自动创建一个数据库备份,注意查看日志输出相关信息
- 在所以数据都迁移成功后程序会自动删除 h2 中的数据,并且将 h2 数据相关文件移动到备份路径,注意查看日志输出相关信息
# 查看结果
2023-01-10 10:53:50.947 [main] INFO i.j.s.d.InitDb [InitDb.java:310] - 开始等待数据迁移
2023-01-10 10:53:50.950 [main] INFO i.j.s.d.InitDb [InitDb.java:109] - start load MYSQL db
2023-01-10 10:53:51.543 [main] INFO i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: table.all.v1.0.csv complete, and affected rows is: 0
2023-01-10 10:53:51.605 [main] INFO i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: execute.mysql.v1.0.sql complete, and affected rows is: 0
2023-01-10 10:53:51.625 [main] INFO i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: alter.all.v1.0.csv complete, and affected rows is: 0
2023-01-10 10:53:51.703 [main] INFO i.j.s.d.InitDb [InitDb.java:239] - exec init SQL file: index.all.v1.0.csv complete, and affected rows is: 0
2023-01-10 10:53:51.706 [main] INFO i.j.s.d.InitDb [InitDb.java:155] - 开始执行数据库事件:迁移数据
2023-01-10 10:53:51.706 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:85] - 开始迁移 h2 数据到 MYSQL
2023-01-10 10:53:52.097 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:95] - 成功连接 H2 ,开始尝试自动备份
2023-01-10 10:53:52.495 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:106] - H2 数据库备份成功:/opt/jpom_server/server-2-2/db/backup/20230110105352.sql
2023-01-10 10:53:52.527 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:111] - 成功连接 MYSQL jdbc:mysql://192.168.1.174:3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false
2023-01-10 10:53:52.537 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:124] - 准备迁移数据
2023-01-10 10:53:52.537 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 构建历史 BUILDHISTORYLOG
2023-01-10 10:53:55.069 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 构建历史 迁移成功 428 条数据
2023-01-10 10:53:55.069 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 构建信息 BUILD_INFO
2023-01-10 10:53:55.170 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 构建信息 迁移成功 16 条数据
2023-01-10 10:53:55.170 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 命令执行记录 COMMAND_EXEC_LOG
2023-01-10 10:53:55.171 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 命令执行记录 迁移成功 0 条数据
2023-01-10 10:53:55.172 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 命令管理 COMMAND_INFO
2023-01-10 10:53:55.173 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 命令管理 迁移成功 0 条数据
2023-01-10 10:53:55.173 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 docker 信息 DOCKER_INFO
2023-01-10 10:53:55.223 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - docker 信息 迁移成功 9 条数据
2023-01-10 10:53:55.224 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 docker 集群信息 DOCKER_SWARM_INFO
2023-01-10 10:53:55.226 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - docker 集群信息 迁移成功 0 条数据
2023-01-10 10:53:55.226 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 日志阅读 LOG_READ
2023-01-10 10:53:55.228 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 日志阅读 迁移成功 0 条数据
2023-01-10 10:53:55.228 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 监控信息 MONITOR_INFO
2023-01-10 10:53:55.383 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 监控信息 迁移成功 2 条数据
2023-01-10 10:53:55.383 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 监控通知 MONITORNOTIFYLOG
2023-01-10 10:53:55.399 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 监控通知 迁移成功 2 条数据
2023-01-10 10:53:55.400 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 监控用户操作 MONITOR_USER_OPT
2023-01-10 10:53:55.401 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 监控用户操作 迁移成功 0 条数据
2023-01-10 10:53:55.401 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点信息 NODE_INFO
2023-01-10 10:53:55.451 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点信息 迁移成功 8 条数据
2023-01-10 10:53:55.451 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点统计 NODE_STAT
2023-01-10 10:53:55.496 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点统计 迁移成功 8 条数据
2023-01-10 10:53:55.496 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 分发日志 OUTGIVINGLOG
2023-01-10 10:53:55.498 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 分发日志 迁移成功 0 条数据
2023-01-10 10:53:55.498 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点分发 OUT_GIVING
2023-01-10 10:53:55.499 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点分发 迁移成功 0 条数据
2023-01-10 10:53:55.499 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 项目信息 PROJECT_INFO
2023-01-10 10:53:55.516 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 项目信息 迁移成功 2 条数据
2023-01-10 10:53:55.516 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 仓库信息 REPOSITORY
2023-01-10 10:53:55.600 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 仓库信息 迁移成功 15 条数据
2023-01-10 10:53:55.601 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点脚本模版 SCRIPT_INFO
2023-01-10 10:53:55.694 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点脚本模版 迁移成功 17 条数据
2023-01-10 10:53:55.694 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点脚本模版执行记录 SCRIPT_EXECUTE_LOG
2023-01-10 10:54:47.038 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点脚本模版执行记录 迁移成功 9760 条数据
2023-01-10 10:54:47.038 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 脚本模版执行记录 SERVER_SCRIPT_EXECUTE_LOG
2023-01-10 10:54:47.046 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 脚本模版执行记录 迁移成功 1 条数据
2023-01-10 10:54:47.046 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 脚本模版 SERVER_SCRIPT_INFO
2023-01-10 10:54:47.061 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 脚本模版 迁移成功 2 条数据
2023-01-10 10:54:47.061 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 SSH 信息 SSH_INFO
2023-01-10 10:54:47.103 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - SSH 信息 迁移成功 7 条数据
2023-01-10 10:54:47.103 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 ssh 终端执行日志 SSHTERMINALEXECUTELOG
2023-01-10 10:54:47.634 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - ssh 终端执行日志 迁移成功 102 条数据
2023-01-10 10:54:47.635 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 节点监控记录 SYSTEMMONITORLOG
2023-01-10 10:55:40.990 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 节点监控记录 迁移成功 10000 条数据
2023-01-10 10:55:40.990 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 系统参数 SYSTEM_PARAMETERS
2023-01-10 10:55:41.017 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 系统参数 迁移成功 5 条数据
2023-01-10 10:55:41.017 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 触发器 token TRIGGER_TOKEN_LOG
2023-01-10 10:55:41.288 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 触发器 token 迁移成功 52 条数据
2023-01-10 10:55:41.288 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户(权限组)工作空间关系表 USER_BIND_WORKSPACE
2023-01-10 10:55:41.347 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户(权限组)工作空间关系表 迁移成功 11 条数据
2023-01-10 10:55:41.348 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户账号 USER_INFO
2023-01-10 10:55:41.376 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户账号 迁移成功 5 条数据
2023-01-10 10:55:41.377 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户操作日志 USEROPERATELOGV1
2023-01-10 10:55:59.010 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户操作日志 迁移成功 3127 条数据
2023-01-10 10:55:59.010 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 用户权限组 USER_PERMISSION_GROUP
2023-01-10 10:55:59.018 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 用户权限组 迁移成功 1 条数据
2023-01-10 10:55:59.018 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 工作空间环境变量 WORKSPACE_ENV_VAR
2023-01-10 10:55:59.019 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 工作空间环境变量 迁移成功 0 条数据
2023-01-10 10:55:59.019 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:143] - 开始迁移 工作空间 WORKSPACE
2023-01-10 10:55:59.030 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:179] - 工作空间 迁移成功 2 条数据
2023-01-10 10:55:59.032 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:130] - 迁移完成,累计迁移 23582 条数据,耗时:2分7秒305毫秒
2023-01-10 10:55:59.091 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:133] - 准备删除当前数据库文件
2023-01-10 10:55:59.095 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:135] - 自动备份 h2 数据库文件,备份文件位于:/opt/jpom_server/server-2-2/db/recover_backup/20230110105559
2023-01-10 10:55:59.096 [main] INFO i.j.s.d.InitDb [InitDb.java:158] - 数据库 迁移数据 事件执行结束,结果:false
2023-01-10 10:55:59.096 [main] INFO i.j.s.d.InitDb [InitDb.java:172] - MYSQL db Successfully loaded, url is 【jdbc:mysql://192.168.1.174:3306/jpom?useUnicode=true&characterEncoding=UTF-8&useSSL=false】
2023-01-10 10:55:59.222 [main] INFO i.j.c.JpomApplicationEvent [JpomApplicationEvent.java:222] - Server Successfully started,Can use happily => http://172.17.0.1:2122 【The current address is for reference only】
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
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
# 如果失败了,不要慌!!!
如果失败了,我们建议按照如下操作处理
- 删除 mysql 中的所有数据
- 删除服务端中的 db 目录下 h2 数据库相关的文件
- 查看上一次迁移失败操作时程序自动备份数据的 sql 文件
2023-01-10 10:53:52.097 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:95] - 成功连接 H2 ,开始尝试自动备份
2023-01-10 10:53:52.495 [main] INFO t.j.d.StorageServiceFactory [StorageServiceFactory.java:106] - H2 数据库备份成功:/opt/jpom_server/server-2-2/db/backup/20230110105352.sql
1
2
2
- 将对应的 sql 文件下载到您的本地电脑
- 确认上传 db 目录下 h2 数据库相关的文件
- 重新启动服务端
- 这时您进入服务端需要你重新初始化系统,不要慌正常操作即可
- 进入到 系统管理中的数据库备份管理
- 导入您之前下载的 sql 数据文件
- 还原数据
将上述步骤操作完您就还原到迁移数据之前的状态了,接下来您可以再次尝试迁移数据
如果多次操作仍然失败您可以联系官方协助您排查解决问题
还原数据您还可以通过命令行的方式操作
bash ./bin/Server.sh restart -15 --replace-import-h2-sql=/opt/jpom_server/server-2-2/db/backup/20230110105352.sql
1
帮助我们改善此文档 (opens new window)
上次更新: 2023/01/10, 15:22:03