Uptime-Kuma with MySQL/MariaDB in Docker with old data from SQLite

uptime-kuma-mysql-docker-with-old-data-from-sqlite.md

Uptime-Kuma with MySQL in Docker with old data from SQLite

Note: Uptime-Kuma comes with SQLite database support by default in its Docker v1 image. However, when you have many servers, say, 150 or more, due to the SQLite structure itself, Uptime-Kuma starts to slow down to the point of becoming unresponsive and occasionally crashing. The best way to avoid this is to use MySQL/MariaDB.

Note 2: Currently, the docker image supporting MySQL/MariaDB is louislam/uptime-kuma:2.0.0-beta.3, which, while still in beta, is almost production-ready. So we'll be using this one.

Procedure:

1. Update system and install pip:

apt update && apt install -y python3-pip

2. If you have old data, from version 1, and you want to have it after the upgrade to MySQL, you must convert the sqlite dabatase to MySQL format.

pip install sqlite3-to-mysql

3. Docker-Compose file, create it with this content:

---
services:
  uptime-kuma2:
    image: louislam/uptime-kuma:2.0.0-beta.3
    container_name: uptime-kuma
    depends_on:
      - mariadb
    volumes:
      - ./data/uptime-kuma:/app/data
    ports:
      - "127.0.0.1:3001:3001"
    restart: unless-stopped

  mariadb:
    image: mariadb:12
    container_name: mariadb-kuma
    volumes:
      - ./data/mariadb:/var/lib/mysql
    ports:
      - "127.0.0.1:3306:3306"
    environment:
      - MYSQL_ROOT_PASSWORD=mysql_root_password
      - MYSQL_USER=kuma_user
      - MYSQL_PASSWORD=kuma_password
      - MYSQL_DATABASE=kuma_db
    restart: unless-stopped

Note 3: I don't recommend having password inside docker-compose.yml file, but this is just for testing purposes only. If you want to have a clean compose, create a config folder and inside, create mysql.env file with env vars, and call it using env_file: ./config/mysql.env directive.

4. Example Nginx vhost:

server {
  server_name kuma.mydomain.com;
  location / {
    proxy_set_header   X-Real-IP $remote_addr;
    proxy_set_header   X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header   Host $host;
    proxy_pass         http://127.0.0.1:3001/;
    proxy_http_version 1.1;
    proxy_set_header   Upgrade $http_upgrade;
    proxy_set_header   Connection "upgrade";
    add_header         Strict-Transport-Security "max-age=31536000";
    add_header         X-Content-Type-Options "nosniff";
    add_header         X-Frame-Options "SAMEORIGIN";
    add_header         X-XSS-Protection "1; mode=block";
    add_header         Referrer-Policy "strict-origin-when-cross-origin";
    add_header         Content-Security-Policy "default-src 'self'; script-src 'self' 'unsafe-inline' 'unsafe-eval'; img-src 'self' data:; style-src 'self' 'unsafe-inline'; font-src 'self' data:";

    proxy_read_timeout 3600s;
    proxy_send_timeout 3600s;
    proxy_connect_timeout 3600s;

}
    listen 443 ssl http2; # managed by Certbot
    ssl_certificate /etc/letsencrypt/live/kuma.mydomain.com/fullchain.pem; # managed by Certbot
    ssl_certificate_key /etc/letsencrypt/live/kuma.mydomain.com/privkey.pem; # managed by Certbot
    include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot
    ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot

}

server {
    if ($host = kuma.mydomain.com) {
        return 301 https://$host$request_uri;
    } # managed by Certbot

  server_name kuma.mydomain.com;
  listen 80;
    return 404; # managed by Certbot
}

5. Just to create the DB, start up docker-compose stack:

docker-compose up -d

5.1. Open your browser and install Uptime-Kuma, this will create the DB.

6. Stop Kuma service:

docker stop uptime-kuma

7- Convert SQLite DB to MySQL and import it into the DB:

sqlite3mysql -f kuma.db -d kuma_db -u root -p -h 127.0.0.1 -K -i IGNORE

8. Start the stack again:

docker-compose up -d

9. Open your browser and point it to: https://kuma.mydomain.com

10. Login, and you will be able to see all your old data back, but on the new system.

11. Enjoy.