Switch from SQLite to MySQL

Move a SQLite install to MySQL, what carries over, what does not, and when to do it.

You started on SQLite (the default) and now want the data in MySQL or MariaDB. Read this before you touch the config, because the most important fact is a limitation:

TDX Leaderboards has no import or migration command. Switching general.storage to mysql starts from empty tables. Your plugins/TDXLeaderboards/data.db is not touched, so you can always switch back, but nothing is copied for you.

What an empty start costs you

Data	After the switch
This period's standings and points	Start again from zero
Custom counters (blocks, mobs, votes)	Start again from zero
Permanent milestone flags	Gone, so milestones can be earned a second time
Archive of past periods	Empty
Rewards waiting for offline winners	Not carried over

Boards that read a live value, such as a Vault balance or a PlaceholderAPI number in CURRENT mode, are unaffected. They fill up again as soon as players are online.

Pick your moment

Do it right after a period reset. The standings, points and counters have just been cleared anyway, and the rewards for the period that ended have already been paid. What you still lose is the milestone flags, the archive, and any reward waiting for a winner who has not logged in since the reset. Give winners a few days to collect before you switch.

Steps

Stop the server

A clean stop flushes everything still held in memory to data.db.

Back up the SQLite files

Copy data.db, and data.db-wal and data.db-shm if they exist, out of plugins/TDXLeaderboards/ to somewhere safe. That copy is your whole rollback plan.

Create the MySQL database

Run this once on your database host, with your own name and password.

CREATE DATABASE tdxleaderboards CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'tdx'@'%' IDENTIFIED BY 'a-strong-password';
GRANT ALL PRIVILEGES ON tdxleaderboards.* TO 'tdx'@'%';
FLUSH PRIVILEGES;

Switch the plugin to MySQL

Edit plugins/TDXLeaderboards/config.yml:

plugins/TDXLeaderboards/config.yml

general:
  storage: mysql

mysql:
  host: "db.your-host.tld"
  port: 3306
  database: "tdxleaderboards"
  username: "tdx"
  password: "a-strong-password"
  table-prefix: "tdxlb_"
  pool-size: 10

Every key is explained on the storage page. The connection does not use SSL, so keep the database on a private network.

Start the server and check

On startup the plugin creates its ten tables, all starting with your table-prefix. If the connection fails, the reason is in console and the plugin disables itself. Open /leaderboard: the boards are there and empty, and they fill up as players come online and the next update cycle runs.

Copying the old data yourself

If losing the milestone flags or the archive is not acceptable, the rows can be copied by hand. Both backends use the same table and column names. MySQL only puts your table-prefix in front, so SQLite's milestone_flags is MySQL's tdxlb_milestone_flags.

The ten tables are monthly_snapshots, custom_stats, points_cache, board_cache, previous_positions, milestone_flags, period_archive, points_archive, metadata and pending_rewards. Any SQLite to MySQL copy tool can move them, with the server stopped, after the plugin has created the empty MySQL tables once.

This is not something the plugin does or checks for you. Try it on a copy of the database first, and keep your data.db backup until a full period has gone by without surprises.

Rollback

Stop the server.
Set general.storage back to sqlite.
Put your data.db backup back if you removed the file.
Start the server.

You are back where you started. Whatever was recorded in MySQL in the meantime stays there and is not merged back.

Pointing a second server at the same database

Two servers with the same table-prefix share one set of tables. Give each server its own prefix, for example tdxlb_survival_ and tdxlb_skyblock_, if their boards should stay separate.

Switch from SQLite to MySQL

On this page