Metabase H2 DB Migration to MySQL 5.7 @ DanBrother的部落格

Metabase H2 DB Migration to MySQL 5.7

#### On Aurora New MySQL ####################
create database metabase charset utf8mb4;
create user `metabase_app`@`192.168.%` identified by 'xxxxxx';
grant all on metabase.* to `metabase_app`@`192.168.%`;

# get latest metabase (V0.35.4)
cd /data
wget https://downloads.metabase.com/v0.35.4/metabase.jar

# Stop Metabase
root
systemctl stop metabase
systemctl status metabase -l
cd /data/metabase

# backup H2 db file
tar cvzf metabase.db.mv.db.tar.gz metabase.db.mv.db
chown appuser:appuser metabase.db.mv.db.tar.gz

# Upgrade metabase software
pwd
mv metabase.jar v0.33.3/
cp -p v0.35.4/metabase.jar ./

export MB_DB_TYPE=mysql
export MB_DB_DBNAME=metabase
export MB_DB_PORT=3306
export MB_DB_USER=metabase_app
export MB_DB_PASS=xxxxxx
export MB_DB_HOST=10.0.1.100

# check connection
mysql -h$MB_DB_HOST -P$MB_DB_PORT -u$MB_DB_USER -p$MB_DB_PASS -e "select table_schema,table_name,table_rows from information_schema.tables where table_schema = 'metabase'"
>>
Make sure no rows are returned!

# Start Migration
/home/jdk1.8.0/bin/java -jar metabase.jar load-from-h2 ./metabase.db

Command failed with exception: (conn=52) Duplicate entry '3-DBAMONITER-PREIOD_NUMBER_H_V_n' for key 'idx_uniq_table_db_id_schema_name'

# alter table metabase_table drop KEY idx_uniq_table_db_id_schema_name;
mysql -h$MB_DB_HOST -P$MB_DB_PORT -u$MB_DB_USER -p$MB_DB_PASS -e " alter table metabase.metabase_table drop KEY idx_uniq_table_db_id_schema_name"

-- Re-run again
/home/jdk1.8.0/bin/java -jar metabase.jar load-from-h2 ./metabase.db

-- Start Metabase
vi /etc/systemd/system/metabase.service
>>
Environment=MB_DB_TYPE=mysql
Environment=MB_DB_DBNAME=metabase
Environment=MB_DB_PORT=3306
Environment=MB_DB_USER=metabase_app
Environment=MB_DB_PASS=xxxxxx
Environment=MB_DB_HOST=10.0.1.100

.
.
WorkingDirectory=/data/metabase

mv metabase.db.mv.db old_dbs/

# Make sure the following drivers exists
>>
bigquery.metabase-driver.jar
druid.metabase-driver.jar
googleanalytics.metabase-driver.jar
google.metabase-driver.jar
mongo.metabase-driver.jar
ojdbc8.jar
oracle.metabase-driver.jar
orai18n.jar
presto.metabase-driver.jar
redshift.metabase-driver.jar
snowflake.metabase-driver.jar
sparksql.metabase-driver.jar
sqlite.metabase-driver.jar
sqlserver.metabase-driver.jar
vertica.metabase-driver.jar