Metabase on Windows - Configuring for MySQL, Migration from H2

Hi

I faced 2 issues for which the standard documentation was not very helpful. After some searching, got an answer in one of Flamber’s responses. Presenting it below for easy access

1. How to configure Metabase application database to be MySQL (instead of the default H2)

1.1 Go to MySQL Command line :- Create database metabase;
1.2 Go to cmd :-
java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=xxx -DMB_DB_PASS=yyy metabase.jar

Note
Should always use above command to start metabase. If we use java -jar metabase.jar, the default H2 database will get created

2. Assume we already have worked with H2 for some time. Now want to migrate this work to MySQL

2.1 Go to MySQL Command line :- Create database metabase;
2.2 Go to cmd :-
java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=xxx -DMB_DB_PASS=yyy metabase.jar load-from-h2 ./metabase.db

(this assumes that the metabase.db file is in same path as metabase jar file)

2.3 In above step, after migration, metabase shuts down. To restart, use following
java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=xxx -DMB_DB_PASS=yyy metabase.jar

2.4 Remove metabase.db.mv and metabase.db.trace files from the folder and keep in another location as backup

Details of how to view the Metabase metadata are already covered nicely in following links
Copy dashboards and questions from one server to another
Metabase Metadata SQL

@flamber Pl suggest if anything amiss in above steps. Would also request if these steps can be incorporated in standard documentation, to avoid searching effort for future users.

Thanks
Krishna

3 Likes

Hi @Krishna

You’re more than welcome to create a PR for documentation.
https://github.com/metabase/metabase

I think the recommendation would probably be to use Docker on Windows, since that would make the environment variables similar to other systems, and then you don’t have to deal with Java, character encoding and a few other things on Windows.

I’d add creating a Window service - I use NSSM. Then you’ve a proper server application that doesn’t require a user session.

I’ve not tried using Docker yet - I think I’m old fashioned and like everything to be in a ‘proper’ VM where I can touch it rather than behind yet another abstraction layer.

Hi @flamber, @AndrewMBaines, Thanks for your feedback. Have not worked with Docker and NSSM, did a direct installation on my laptop using above steps. I guess you are saying using Docker or NSSM is a better approach.

Regarding documentation - can you share a sample pull request for adding a document. Will use that as reference.

Thanks
Krishna

NSSM would just create a service that calls your current command line. Typically using a bat file. Other than that, nothing about your installation would change.

Ok, got it. Thanks!

@Krishna There are many PRs for various changes to documentation (usually the PRs will have “docs” in their title):
https://github.com/metabase/metabase/pull/12710

Thanks @flamber, will check out.

@krisha Thank you very much. This will save people like us who have spending days to go about installing and migrating without success. I am very sure the cmd will work also in ubuntu.I am trying it out now and revert. Thanks

@Fredoluka Happy to know that this was of help to u! Thanks for acknowledging.

Not sure if this will help anyone in the future, but I struggled to migrate using the commands above.

Had to add the following:

java -jar -DMB_DB_TYPE=mysql -DMB_DB_DBNAME=metabase -DMB_DB_PORT=3306 -DMB_DB_USER=XXX-DMB_DB_PASS=XXX -DMB_DB_HOST=localhost C:\Metabase\metabase.jar load-from-h2 C:\Metabase\metabase.db

as well as specifying the full path of where the H2 metabase db was stored.

thanks for this excellent post, your post helped me to create a step-by-step document, I prepared this document for those who are beginners in metabase like me and do not want to lose their first data, I recommend that you always use the official documents of metabase to avoid data loss, even if it is difficult.