Migrate 2 servers to one database

[rubenbarkman]

I currently have 2 servers running Plan (one already on MySQL, and one on SQLite). I would like to move them both to the same MySQL database to only use one central panel.

Although I have found you can move your server from SQLite to MySQL, this does delete the data on the receiving MySQL database (https://github.com/plan-player-analytics/Plan/wiki/Database-management).

Now I was wondering if I could (even manually), merge both of them to the same MySQL database to get the same result as: https://github.com/plan-player-analytics/Plan/wiki/Bungee-Set-Up

[AuroraLS3]

It's not currently very feasible to do manually due to the amount of foreign keys (marked FK in the https://github.com/plan-player-analytics/Plan/wiki/Database-Schema). Foreign keys are used to link data together for faster joins in queries.

Since the database table for plan_users can contain duplicates for players, the ids in the 2 database don't add-up and the ids coming in from the other database would need to be changed to match in the resulting database.

Likewise both servers are likely to have server_id of 1 which would need to be corrected for the 2nd dataset (same is the case for other ids),

---

If you want to do it manually I would first migrate the two sqlite databases to two different MySQL databases and then perform the merging SQL queries on both databases, inserting to one of them (or even a 3rd one).

---

Here's how I would do it

• Prepare for creating lookup tables by copying plan_users, plan_servers & plan_worlds A and B to database C (Without calling them those names)
• Start up a Plan instance in database C so that the tables are created
• Merge the datasets of the copied tables into the newly created plan_users (etc) tables so that there's one entry per uuid
• Create lookup tables user_ids_A and user_ids_B (and the other tables) by making a table with columns:
  • old_id | new_id
• Insert any remaining data from A and B by converting ids with the lookup tables for respective database

Example of the lookup insert

USE DatabaseC;
INSERT INTO DatabaseC.plan_user_info VALUES (..., user_id) 
SELECT ..., (SELECT new_id FROM DatabaseC.user_ids_A WHERE old_id=user_id) FROM DatabaseA.plan_user_info;
INSERT INTO DatabaseC.plan_user_info VALUES (..., user_id) 
SELECT ..., (SELECT new_id FROM DatabaseC.user_ids_B WHERE old_id=user_id) FROM DatabaseB.plan_user_info;

[rubenbarkman]

Thanks for answering so fast! I'll go play around with this a little, and will see where I can get.

PS: Amazing plugin, keep up the good work!