TIP: Reducing DB backup size for custom servers

[Drajor]

Hey guys,

I thought I would share this technique as someone else may find it useful. The server I am developing is customised in such a way that I am only using a subset of the many available zones (not uncommon in custom set ups). This means that there is a considerable amount of data redundancy in the DB across a few tables. In this example I am focusing on the 'grid', 'grid_entries' and 'doors' tables.

Create a duplicate of the 'grid', 'grid_entries' and 'doors' tables, naming them 'grid_copy', 'grid_entries_copy' and 'doors_copy'. These new tables will not be included in your regular DB backup.

The following SQL will create your copy tables and empty the original tables.
# grid table
CREATE TABLE grid_copy LIKE grid;
INSERT grid_copy SELECT * FROM grid;
DELETE FROM grid;

# grid_entries table
CREATE TABLE grid_entries_copy LIKE grid_entries;
INSERT grid_entries_copy SELECT * FROM grid_entries;
DELETE FROM grid_entries;

# doors table
CREATE TABLE doors_copy LIKE doors;
INSERT doors_copy SELECT * FROM doors;
DELETE FROM doors;

The following SQL can be adjusted to bring data back from your copy tables, such as when you add a new zone.

INSERT INTO grid SELECT * FROM grid_copy WHERE zoneid = 58;
INSERT INTO grid_entries SELECT * FROM grid_entries_copy where zoneid = 58;
INSERT INTO doors SELECT * FROM doors_copy where zone = "crushbone";

Note that this may or may not be appropriate if you have customisations in any of these tables. Someone could also fairly argue that these tables do not need to be backed up unless they are custom, but I prefer doing /complete/ back ups.

If anyone else has any tips or tricks for this kind of thing please share! I am still learning myself :)

[knowom]

This looks interesting I was doing this in a different manner by manually deleting excess zones/zone_points/doors ect things all tied to the specific zones that wouldn't be used. I kept some things just in case like quest's, npc's, and items since I might want to alter them a bit to reuse them.

After I deleted what I wanted I'd save the DB table's in SQL patch folders I made for different categories SQL Create, SQL Drop, and SQL Drop/Create. Then I could just access MySQL DB in cmd use the DB of choice and source in or out what I wanted as needed. There is probably a easier method to do it like what you suggested, but that's just the way I was able to figure it out personally like you I'm still learning.

Also all the various SQL patches I named accordingly in a way that made some to myself. You can just overwrite update them as needed as you make changes. You could also make a new SQL for those tables and place the older versions or alternate ones of those tables in a backup folder as you develop them. Then you could change and or revert to whatever one you liked later by sourcing the patches back into the DB at any time.

Like you I'm a fan of minimalism and being able to remove unneeded and unused clutter from my server DB keeping more compact in size. It has practicality merits though as it saves on your overall system memory that could be used to host more static_zones for example or to insert more overall spawn and player related stuff inside your zones and db in general.