Jpom
loyal
2023-01-10
目录

数据库迁移到 mysql

# 前言

Jpom 服务端的数据默认存储到 H2database 中

部分用户有需求期望数据存储到 mysql 中

提醒

使用 mysql 模式，系统管理中的数据库备份功能将不可用，数据的备份管理需要自行维护

# 系统升级

使用jpom的在线升级功能将将服务端和插件端版本升级至 2.10.11

注意

升级前请仔细阅读版本更新日志，如果有特殊说明或者注意事项请仔细确认。升级前建议提前做好相关备份,避免出现意外造成数据丢失

# 如何创建 h2 数据备份

# 页面操作创建

img.png

img_1.png

img_2.png

# 命令行创建

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

# 执行命令

bash ./bin/Server.sh restart -15 --h2-migrate-mysql --h2-user=jpom --h2-pass=jpom
1

如果您修改过 h2 数据库的账号或者密码，请在命令行参数里面填写您修改过的

提醒

  1. 为了保证数据安全，在迁移前程序会自动创建一个数据库备份，注意查看日志输出相关信息
  2. 在所以数据都迁移成功后程序会自动删除 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

# 如果失败了，不要慌！！！

如果失败了，我们建议按照如下操作处理

  1. 删除 mysql 中的所有数据
  2. 删除服务端中的 db 目录下 h2 数据库相关的文件

img.png

  1. 查看上一次迁移失败操作时程序自动备份数据的 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
  1. 将对应的 sql 文件下载到您的本地电脑
  2. 确认上传 db 目录下 h2 数据库相关的文件
  3. 重新启动服务端
  4. 这时您进入服务端需要你重新初始化系统，不要慌正常操作即可
  5. 进入到 系统管理中的数据库备份管理
  6. 导入您之前下载的 sql 数据文件 img_1.png img_2.png
  7. 还原数据 img_3.png

将上述步骤操作完您就还原到迁移数据之前的状态了，接下来您可以再次尝试迁移数据

如果多次操作仍然失败您可以联系官方协助您排查解决问题

还原数据您还可以通过命令行的方式操作

bash ./bin/Server.sh restart -15 --replace-import-h2-sql=/opt/jpom_server/server-2-2/db/backup/20230110105352.sql
1
帮助我们改善此文档 (opens new window)
#mysql
上次更新: 2023/01/10, 15:22:03
升级 Jpom 版本
如何将 2.9.x 升级到 2.10.x

← 如何将 2.9.x 升级到 2.10.x→

最近更新
01
2.9.x 下载地址
12-19
02
2.10.x
12-19
03
GitLab 提交代码后自动执行构建
12-04
更多文章>
Theme by Vdoing | Copyright © 2017-2023 MIT License  京ICP备17044819号
×