metabase迁移底层数据库从H2到Mysql

最近用metabase搞了一套内部BI可视化系统。

这段时间看资料,发现metabase默认是用的H2数据库,会有单点风险。

今天有时间,把底层数据库从H2直接迁移到了Mysql。

注:metabase安装目录为/usr/local/metabase,下面所有操作命令都在改目录下进行。

  1. 备份H2数据库。
    进入metabase的启动目录,找到类似metabase.db.*的文件,有两个,拷贝到其他目录做备份。

    ll metabase/
    total 86712
    -rw-r--r-- 1 root root 57577472 Apr 19 18:23 metabase.db.mv.db
    -rw-r--r-- 1 root root 31212428 Apr 19 18:23 metabase.db.trace.db
  2. 配置metabase的mysql配置。往/etc/profile文件末尾,添加下面代码,将里面的配置信息改成你自己的,保存后记得加载一下配置。

    export MB_DB_TYPE=mysql
    export MB_DB_DBNAME=metabase数据库
    export MB_DB_PORT=mysql端口
    export MB_DB_USER=mysql用户
    export MB_DB_PASS=mysql密码
    export MB_DB_HOST=mysql机器ip
  3. 执行迁移脚本。

    java -jar metabase.jar load-from-h2 ./metabase.db

    输出结果类似如下:

    04-19 18:29:04 INFO metabase.util :: Loading Metabase...
    04-19 18:29:09 INFO util.encryption :: DB details encryption is DISABLED for this Metabase instance. ? 
    See http://www.metabase.com/docs/latest/operations-guide/start.html#encrypting-your-database-connection-details-at-rest for more information.
    04-19 18:29:20 INFO metabase.db :: Verifying mysql Database Connection ...
    04-19 18:29:21 INFO metabase.db :: Verify Database Connection ...  ?
    04-19 18:29:21 INFO metabase.db :: Running Database Migrations...
    04-19 18:29:21 INFO metabase.db :: Setting up Liquibase...
    04-19 18:29:21 INFO metabase.db :: Liquibase is ready.
    04-19 18:29:21 INFO metabase.db :: Checking if Database has unrun migrations...
    04-19 18:29:24 WARN liquibase :: modifyDataType will lose primary key/autoincrement/not null settings for mysql.  Use <sql> and re-specify all configuration if this is the case
    04-19 18:29:24 WARN liquibase :: modifyDataType will lose primary key/autoincrement/not null settings for mysql.  Use <sql> and re-specify all configuration if this is the case
    04-19 18:29:24 INFO metabase.db :: Database has unrun migrations. Waiting for migration lock to be cleared...
    04-19 18:29:24 INFO metabase.db :: Migration lock is cleared. Running migrations...
    04-19 18:29:25 WARN liquibase :: modifyDataType will lose primary key/autoincrement/not null settings for mysql.  Use <sql> and re-specify all configuration if this is the case
    04-19 18:29:25 WARN liquibase :: modifyDataType will lose primary key/autoincrement/not null settings for mysql.  Use <sql> and re-specify all configuration if this is the case
    04-19 18:30:28 INFO metabase.db :: Database Migrations Current ...  ?
    com.mchange.v2.cfg.DelayedLogItem [ level -> FINE, text -> "The configuration file for resource identifier 'hocon:/reference,/application,/c3p0,/' could not be found. Skipping.", exception -> null]
    Temporarily disabling DB constraints...
    [OK]
    Transfering 6 instances of Database....[OK]
    Transfering 6 instances of User....[OK]
    Transfering 12 instances of Setting....[OK]
    Transfering 103 instances of Table....[OK]
    Transfering 2270 instances of Field...........[OK]
    Transfering 1064 instances of FieldValues.......[OK]
    Transfering 352 instances of Revision.....[OK]
    Transfering 1409 instances of ViewLog........[OK]
    Transfering 73 instances of Session....[OK]
    Transfering 6 instances of Dashboard....[OK]
    Transfering 58 instances of Card....[OK]
    Transfering 42 instances of DashboardCard....[OK]
    Transfering 358 instances of Activity.....[OK]
    Transfering 4 instances of PermissionsGroup....[OK]
    Transfering 12 instances of PermissionsGroupMembership....[OK]
    Transfering 16 instances of Permissions....[OK]
    Transfering 4 instances of PermissionsRevision....[OK]
    Transfering 5 instances of Collection....[OK]
    Transfering 2 instances of CollectionRevision....[OK]
    Transfering 16 instances of DataMigrations....[OK]
    Re?nabling DB constraints...
    [OK]

    如果出现The configuration file for resource identifier 'hocon:/reference,/application,/c3p0,/' could not be found的错误,说明前面的配置没有加载,执行source /etc/profile加载配置后再执行上面命令。

  4. 重新启动metabase。

    nohup java -jar metabase.jar &

参考资料:
installing-and-running-metabase

标签: metabase

已有 4 条评论

  1. zhang zhang

    您好,我看了您这个文章,迁移成功了,但是重新输入 java -jar metabase.jar的时候,默认的数据库还是H2,您遇到过这种问题吗

    1. Don Don

      确定环境变量生效了吗?

  2. 疯子 疯子

    您好,想问下您的mysql数据库是什么编码,我连了mysql数据库之后保存数据源会出现中文乱码

    1. 疯子 疯子

      我自己解决啦,https://www.metabase.com/docs/latest/operations-guide/start.html#installing-and-running-metabase

添加新评论