Migration from SQLite to MySQL/MariaDB

[Bahama03]

📑 I have found these related issues/pull requests

Related to #5274, wanting to migrate to MySQL.

🏷️ Feature Request Type

Deployment

🔖 Feature description

With a correct db-config.json file pointing to the MySQL instance, the tables are created but it is treated as a new installation. No tables are migrated.
Tested this with a copy of the database and a separate container.

✔️ Solution

In my case I would like to migrate my current installation on the latest version of v1 to v2, but directly migrating/importing in MySQL instead of sqlite3.

❓ Alternatives

No response

📝 Additional Context

No response

[CommanderStorm]

Please see https://github.com/louislam/uptime-kuma/wiki/Migration-From-v1-To-v2

Can I migrate my existing SQLite database to MariaDB?

Cannot be done directly. You will need to export your data from SQLite and import it into MariaDB using 3rd party tools.

TL;DR:
We don't currently have the (reviewer, support, triage, testing, ..) resources to build out the interop-tooling (incl. neessesary testing) surrounding this.

Not having this be part of the process is likely fine. v1 is quite limited in its scalablity (see #4500) anyways.
=> If you need the level of monitors that was previously not possible, you are going to be importing using the python wrapper / .. anyway. 🤷🏻‍♂️

[derekoharrow]

It would at least be good to have a guide on how to export, convert and import from sqlite to MySQL/MariaDB. I tried this manually myself yesterday and couldn't get it to work.

[monster010]

It is possible to migrate, I did it manually and by manual I mean:

• Create new Kuma instance (new folder, complete new installation)
• Run installer (did not use embedded mariadb because embedded caused problems)
• Opening kuma.db and manually transferring the data, making adjustments here and there because otherwise it doesn't work
  • For example, a dump from a SQLite contains '' instead of NULL. AUTO_INCREMENT is AUTOINCREMENT in SQLite and things like that.

I'm running MariaDB now and haven't noticed any problems so far.

[Apashh]

@monster010 nice !! It will be interesting to note this in Wiki migrate v1 SQLite to v2 Mariadb.

[CommanderStorm]

Sorry, but as stated above:
We don't have the resources to support this part. We still don't have the resources to maintain this part, even if it were to live in the wiki.

If you/somebody else creates a gist with an update script, that is fine for us though, we can link that.

[Mazvy]

If someone, even if unofficially, can shed some light into the steps required, or perhaps prepared a script in order to automate this - please share.

[cmbcbe]

no success while importing converted db to mariadb

apt instal python3 git
git clone https://github.com/majidalavizadeh/sqlite-to-mysql.git
cd sqlite-to-mysql
python3 export.py /srv/docker/uptime-kuma/data/kuma.db /srv/docker/uptime-kuma/data/kuma_mariadb.sql

i will try to play with python sqlite3mysql

[cmbcbe]

Hello, i have have successfully migrate from SQlite3 to Mariadb in this way on V2 beta 1.
First of all, if you don't want that the migration take a long time, pause all your monitors then clear statistics data, the idea behind is to get back ours monitors only.

Than you need to shutdown uptime-kuma container to get the sqlite3 database in a concice state (kuma.db)

Then modify your docker compose to add mariadb in the game, here is the example of mine that i use in a swarm docker environnement

version: '3.8'

services:
  webui:
    image: louislam/uptime-kuma:nightly2
    deploy:
      endpoint_mode: dnsrr
      replicas: 1
      restart_policy:
        condition: any
      placement:
        constraints:
          - node.hostname == docker003
    volumes:
      - /srv/docker/uptime-kuma/data:/app/data
      - /var/run/docker.sock:/var/run/docker.sock
    environment:
      - UPTIME_KUMA_DISABLE_FRAME_SAMEORIGIN=1
    dns:
      - 10.74.1.11
      - 10.75.3.15
    networks:
      - uptime-kuma_net
      - npm

  mariadb:
    image: mariadb:11.4
    deploy:
      endpoint_mode: dnsrr
      replicas: 1
      restart_policy:
        condition: any
      placement:
        constraints:
          - node.hostname == docker003
    environment:
      - MYSQL_ROOT_PASSWORD=Root@ccess1234!
      - MYSQL_DATABASE=uptime_kuma
      - MYSQL_USER=uptime_kuma
      - MYSQL_PASSWORD=Kum@ccess1234!
    volumes:
      - /srv/docker/uptime-kuma/mariadb/data:/var/lib/mysql
      - /srv/docker/uptime-kuma/mariadb/my.cnf:/etc/mysql/conf.d/my.cnf
    networks:
      - uptime-kuma_net
      - npm

networks:
  npm:
     external: true
  uptime-kuma_net:
    driver: overlay
    internal: true
    attachable: false

Thus update the db config file in uptime db-config.json to point to mariadb
example:
{
"type": "mariadb",
"port": 3306,
"hostname": "uptime-kuma_mariadb",
"username": "uptime_kuma",
"password": "mypassword",
"dbName": "uptime_kuma"
}

Spinup Uptime-Kuma container thus it will populate the MariadB database schema for Uptime.
I have export the empty database here if needed
After the database creation, shutdown uptime container to avoid conflict while importing data and live production.

In the way you want, the idea is to use a tool called sqlite3mysql to import kuma.db to your mariadb, here is how i've done:

i have copy from the docker host the file kuma.db inside uptime-kuma container data directory to the data directory of mariadb (corresponding inside the container to /var/lib/mysql), then while the mariadb container is running, i enter inside and do this:

docker exec -it <your container id of mariadb:11.4> /bin/bash
apt update && apt install python3-pip
pip install sqlite3-to-mysql --break-system-packages
sqlite3mysql -f /var/lib/mysql/kuma.db -d uptime_kuma -u root -p -h 127.0.0.1 -K -i IGNORE
The option "ignore" is used because some table time field does not work from sqlite3 to mariadb (format incompatible), because we have drop the statistics this not a problems.

you will get a screen like that while migrating, it can take a while

Start your uptime-kuma container and you should retrive your data on screen coming from mariadb!

[Bahama03]

@cmbcbe Thanks a lot! Works as a charm!