Migrate DB from H2 to MySql

Hi to everybody
I can’t well understand the issue in H2 - MySql … I have read

but still have some doubt. I try to ask u so maybe u can clear me what I should do. I will enumerate questions so it’s easier

  • Metabase is hosted in a Centos 7 Server with Cpanel for this installation
  1. I have the files in /home/bi/ exactly I have:
    metabase.jar (117MB) last mod Dicember 2018
    h2.db.mv.db (82.98 MB) last modification today
    h2.db.trace.db (187 byte) last mod February 2019
    I don’t know why those files are not in /home/bi/public_html/ (really I don’t remember how I did in 2018) but actually if u go to our bi.site.com its’ working well …
    I did copy of that files in a subdirectory, is it correct? (in case something goes wrong)

  2. How can I have all in public_html? (I do all this operation beacuse I want upgrate metabase) and make that bi.site.com works well again?

  3. before upgrading, I read a suggestion to migrate from h2 default to MySql … I am not sure this is my case, but seems yes (because of the h2.db.mv.db files), Correct?

  4. So I would like to migrate it. I have found this in the previous web site

  5. Run the Metabase data migration command using the appropriate environment variables for the target database you want to migrate to.

**where should I set the follow things? **
export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=
export MB_DB_PASS=
export MB_DB_HOST=localhost

  1. **after that should I only restart metabase with this command as suggested and all should be ok? **
    java -jar metabase.jar

  2. How can I check if all was ok? and what if not? how can I roll back?

Sorry for all the questions, but I have all the company connected to the BI …and I need to be carefull :slight_smile:
Thanks for ur help

Hi @mirco_cervi

It’s really difficult to help you, when you don’t know how you’ve setup Metabase.

So to begin with, make sure that you have backups of h2.db.mv.db, since that’s where all the Metabase data is stored.

  1. No, you should absolutely not have the h2.db.mv.db file in a directory that is publicly accessible from your webserver.
  2. Same as 1.
  3. Like the migration guide says, it is not recommended to use H2 in production, so you should migrate to MySQL or Postgres. It also notes that you should not try to migrate and upgrade at the same time.
  4. Is there a question?
  5. Since I don’t know how you’re currently running Metabase, I cannot tell you what to do, but the best thing would be to setup Metabase as a service, then migrate, and then upgrade.
  6. Make sure you have backups of your entire server - I would guess that’s an option in cPanel - so you can roll back in case it doesn’t work.

I would recommend that you hire a server admin that will do everything for you, but on a new server, so everything is clean and up-to-date.

Thanks @flamber got it (unfortunately is a VPS server and I have not full access so I am asking the FarmServer to help me on it …but I need to address them …)
I got that:

  • 1, 2 OK perfect
  • 3 correct, first migration from H2 to MySql …and after … in case I will update
  • Metabase run as a service on Centos Server. Got all docs and could check that metabas.service has this info
    Description=Metabase server
    ExecStart=/bin/java -jar /home/directory_replaced/metabase.jar

so I am sure I have h2 …the point is …that I dont have the Metabase Config file in /etc/default/metabase in my installation… that’s why I have doubts.

If I create the Metabase Config files and Set it with MySql installation, will the “migration” of h2 default storage run automatically when I restart Metabase Service?

Thanks a lot for ur patience!

@mirco_cervi I’m a little confused - you say that you don’t have full access, but you show the service configuration file, which should only be available if accessed as root user.

Anyways, great, now we know it’s setup as a SystemD service.

Let me make this perfectly clear before you start: If you end up with a broken system, then that’s on you - and you should have backups, so you can revert.
If you’re unsure about anything, then stop, and go and hire someone who knows about servers.

To migrate:

  • first you would shutdown Metabase:
    systemctl stop metabase
  • then start the migration process manually - replace all the <...> with the correct references to your MySQL server:
cd /home/directory_replaced
java -DMB_DB_FILE=h2.db -DMB_DB_TYPE=mysql -DMB_DB_HOST=<host> -DMB_DB_PORT=<port> -DMB_DB_DBNAME=<db> -DMB_DB_USER=<user> -DMB_DB_PASS=<pass> -jar metabase.jar load-from-h2
  • then edit the service configuration, add the 6 environment variables that you listed in the first post instead of the existing two Environment lines
  • move the h2.db.mv.db to old-h2.db.mv.db, so you are not accidentally still using it
  • start Metabase service, which should now be using MySQL as the application database
    systemctl start metabase

Grate! I have ssh access only read (normal user) but I will forward your notes to admin of Server and ask for help.

@mirco_cervi You should really consider our hosting solution: Private beta for Hosted Metabase

sure I will