Recently I had to migrate a relatively simple (no FKs or views) Grafana 4 database of about 20 tables and 80,000 rows from sqlite3 to mysql5.6. Below are some notes I made on the 3 methods I tried.
- First up was MySQL Workbench on Mac OS. This required first finding and installing a working ODBC driver for sqlite3, which was a hassle. (The Devart one worked.) Then configuring it in the Mac’s ODBC Manager. After clicking a few UI buttons, the import failed with an error message for each table and Workbench crashed, like usual for the past 2 decades. Tried again with another crash. Moving on …
- Next up was Navicat Premium Trial Edition. It was able to create a sql file that was syntactically valid with MySQL but had 3 major problems:
- it applied the TEXT cast to most of the string and date values
- which then meant it thought it had to declare most of the character and date columns as LONGTEXT and LONGBLOB
- which meant the index definitions using those columns were invalid because of length. In other words, a mess.
- Finally I used a bash script that enumerated the table names using .tables and did a sql dump using .mode insert on each table of individual INSERT statements. That worked fine.
Some other ideas to consider for more complex migrations are:
- Use the Amazon database migration tools. There’s at least 3 that I’m aware of.
- Use the professional-grade pgloader to migrate to Postgresql, then use another tool to pivot to MySQL.
The MySQL grants you need for grafana are:
GRANT USAGE ON grafana.* TO 'grafana'@'%' IDENTIFIED BY 'mypw';
GRANT ALL PRIVILEGES ON grafana.* TO 'grafana'@'%';
There is problem with the Grafana initial table creation script: it uses CREATE IF NOT EXISTS for the tables, but not the indexes, so you see this:
CREATE UNIQUE INDEX `UQE_user_login` ON `user` (`login`);