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