Operations guide is not enough to migrate to MySQL database on Windows

A seemingly obvious questions, but I can’t work out the answer…

I would like to migrate our H2 DB to MySQL and then point to MySQL each time I run Metabase. I’m running both Metabase and MySQL on Windows.

The operations guide talks about setting up environment variables but they are all the same and would overwrite each other so clearly I’m thinking the ‘export’ Environment Variables are Windows environment variables but they must be something else.

TIA,

Bill

The most current version of that guide is http://www.metabase.com/docs/latest/operations-guide/start.html#migrating-from-using-the-h2-database-to-mysql-or-postgres

At this point, v0.15 is ancient history =)

Concretely, you need to do 4 things

  1. Set up a MySQL database that is accessible from the machine/account Metabase will be running under.
  2. Place the connection parameters for the new MySQL db in the environment as in the above link
  3. Run the migration command
  4. Run Metabase normally.

Once step 3 is run, you can back up the h2 database file, and we suggest moving it out of the directory so that if the connection variables aren’t set, you get an error instead of using stale data.

Thanks Sameer.

The issue I was having revolved around setting the environment variables in Windows. The instructions cover setting these in Unix of which I know very little. I’ve done a bit more back-reading based on what you’ve said and now realise that the Windows environment variables just need to ignore the ‘export’ part.

Setting up an Environment Variable pair (entering MB_DB_TYPE in Variable name and mysql in Variable value) does the job (and then repeating for the other five mentioned in the guide). I set them up as User variables for the current user but this is also likely to work as System variables.

The next hurdle is to refrain from specifying a DB name or path for the import. Type the command in the folder in which your .db file sits. For me that was d:\lcx\metabase

d:\lcx\metabase>java -jar metabase.jar load-from-h2

That will create the MySQL tables and import from H2. After this, starting Metabase as usual works a treat.

Having to leave the password as an environment variable is a security risk, but no more so than having to enter it in a command line so just make sure you protect your server from unauthorised access.

HTH.