I recently installed MySQL for another purpose so I've decided to use it as well for Umami. Here are the few steps I did.
Let's assume that:
- Umami is currently running with PostgreSQL database (docker.umami.dev/umami-software/umami:postgresql-latest)
- PostgreSQL container is named "umami-postgres" and user, password and database name are "umami"
- MySQL container is already running, is named "mysql-container" and with root password being "c35f6910-8979-4647-8ba9-928a044be28a"
Create a database for umami
You need to create a database for Umami in your existing MySQL container with the following commands:
docker exec -it mysql-container bash mysql -uroot -pc35f6910-8979-4647-8ba9-928a044be28a CREATE DATABASE umami; SHOW DATABASES;
Create the database schema
You need to create a new Umami secondary container using MySQL (docker.umami.dev/umami-software/umami:mysql-latest) and running on a different port.
You need it to create the database schema in your MySQL database. Keep the primary one running in parallel.
You don't need to do anything except changing the admin account password. The rest of the data will be imported later.
Export the data
To export the data from your PostgreSQL database, you need to execute the following command. It will generate an export.sql file.
docker exec -t umami-postgres pg_dump -U umami --column-inserts --data-only --table=website --table=pageview --table=session umami > export.sql
Customize the export file
Few modifications need to be done on the export file to be MySQL compatible. You need to remove Milliseconds and Timezone from the timestamps and remove the public. prefix on insert statements which is not existing on MySQL. As you can have a huge amount of lines, the commands below will do it for you!
sed -E 's/(:[0-9]+).[0-9]+/\1/g' export.sql > export2.sql && mv export2.sql export.sql sed 's/+00//g' export.sql > export2.sql && mv export2.sql export.sql sed 's/INTO public./INTO /g' export.sql > export2.sql && mv export2.sql export.sql sed '/^INSERT INTO/!d' export.sql > export2.sql && mv export2.sql export.sql
Import the data to MySQL
To import the data in your MySQL container, you need to run the command below, it can take few minutes depending on the volume of data.
docker exec -i mysql-container mysql -uroot -pc35f6910-8979-4647-8ba9-928a044be28a umami < export.sql
You can now open your secondary Umami container and see all the data being imported. When the previous command is over and all data visible in the secondary Umami container, you just need to modify your docker compose file to shutdown the secondary Umami container and plug your primary one to the MySQL database.