GeorgeS: Dedicated character backup-restore tool

Angelox · #1 11-02-2006, 11:36 PM

I've had a few people run requests by me for sql updates.to the DB - I know when I started, this is what I wanted to do, but it became very hard as my "simple" method had to be abandoned due to the memory problems it created. It probably still can be done, but would be very hard and would take up a lot of time, that can be better spent on spawning and building zones.
I'm probably the only one constantly posting my updates (I know there must be more people like me, that hate to wait for updates!), I try to make it as simple as possible so everyone can benefit.
The best solution I can think of is a selective backup/restore tool for the database - I use MySql-Admin, and I also have used yours, but why not have a dedicated eqemu character back-up restore tool? one that only keeps track of of tables that are unique to the server, and has little configuration or thinking to do.
I usually keep a back up of these;

ACCOUNT
CHARACTER_
CHARACTER_BACKUP
INVENTORY
SHAREDBANK

But this is just me, and all I mostly do is run around the zones testing and doing db work.

I wonder if there would be more tables? If anyone can think of more, please post. I know another would probably be ;
some parts of VARIABLES (motd, logintype, etc)
For the most of them the utility would probaly need to DROP then remake tables, then add back-up data - but at least for VARIABLES, maybe use REPLACE?
And I wonder what other tables would be needed, if any?

This would be just a simple, user-friendly tool for back-up and restore tool for any type of server (public or minilogin), that would probably only require you know how to set the db.ini file.

John Adams · #2 11-03-2006, 12:27 AM

WARNING: This is just an FYI to Angelox - I do not recommend anyone using this script until you test it out first on a backed-up database.
------------

Angelox, whenever I grab one of your databases, I usually use the PEQ "dump_users.sql" to get rid of those tables in your database, then migrate the remaining "system" tables to the new database.

This is the full script I have used. First, I switch to the current AX_PEQ and drop server-specific tables:

Code:

use ax_peq;

DROP TABLE IF EXISTS aa_timers;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS character_;
DROP TABLE IF EXISTS character_backup;
DROP TABLE IF EXISTS commands;
DROP TABLE IF EXISTS eventlog;
DROP TABLE IF EXISTS faction_values;
DROP TABLE IF EXISTS guilds;
DROP TABLE IF EXISTS guild_ranks;
DROP TABLE IF EXISTS guild_relations;
DROP TABLE IF EXISTS guild_members;
DROP TABLE IF EXISTS guild_alliances;
DROP TABLE IF EXISTS guild_controllers;
DROP TABLE IF EXISTS hackers;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS launcher;
DROP TABLE IF EXISTS launcher_zones;
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS name_filter;
DROP TABLE IF EXISTS object_contents;
DROP TABLE IF EXISTS peq_admin;
DROP TABLE IF EXISTS petitions;
DROP TABLE IF EXISTS player_corpses;
DROP TABLE IF EXISTS player_corpses_backup;
DROP TABLE IF EXISTS quest_globals;
DROP TABLE IF EXISTS rule_sets;
DROP TABLE IF EXISTS rule_values;
DROP TABLE IF EXISTS variables;
DROP TABLE IF EXISTS spells;
DROP TABLE IF EXISTS sharedbank;
DROP TABLE IF EXISTS timers;

Then, I go to my current PEQ database and drop all system-related tables:

Code:

use peq;

DROP TABLE IF EXISTS aa_actions;
DROP TABLE IF EXISTS aa_levels;
DROP TABLE IF EXISTS aa_swarmpets;
DROP TABLE IF EXISTS altadv_vars;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS class_skill;
DROP TABLE IF EXISTS doors;
DROP TABLE IF EXISTS faction_list;
DROP TABLE IF EXISTS fear_hints;
DROP TABLE IF EXISTS fishing;
DROP TABLE IF EXISTS forage;
DROP TABLE IF EXISTS grid;
DROP TABLE IF EXISTS grid_entries;
DROP TABLE IF EXISTS ground_spawns;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS lootdrop;
DROP TABLE IF EXISTS lootdrop_entries;
DROP TABLE IF EXISTS loottable;
DROP TABLE IF EXISTS loottable_entries;
DROP TABLE IF EXISTS merchantlist;
DROP TABLE IF EXISTS merchantlist_temp;
DROP TABLE IF EXISTS merchantsets;
DROP TABLE IF EXISTS npc_faction;
DROP TABLE IF EXISTS npc_faction_entries;
DROP TABLE IF EXISTS npc_spells;
DROP TABLE IF EXISTS npc_spells_entries;
DROP TABLE IF EXISTS npc_types;
DROP TABLE IF EXISTS object;
DROP TABLE IF EXISTS pets;
DROP TABLE IF EXISTS spawn2;
DROP TABLE IF EXISTS spawn_conditions;
DROP TABLE IF EXISTS spawn_events;
DROP TABLE IF EXISTS spawnentry;
DROP TABLE IF EXISTS spawngroup;
DROP TABLE IF EXISTS start_zones;
DROP TABLE IF EXISTS starting_items;
DROP TABLE IF EXISTS tradeskill_recipe;
DROP TABLE IF EXISTS tradeskill_recipe_entries;
DROP TABLE IF EXISTS traps;
DROP TABLE IF EXISTS tribute_levels;
DROP TABLE IF EXISTS tributes;
DROP TABLE IF EXISTS zone;
DROP TABLE IF EXISTS zone_points;
DROP TABLE IF EXISTS zone_server;
DROP TABLE IF EXISTS zone_state_dump;
DROP TABLE IF EXISTS zoneserver_auth;
DROP TABLE IF EXISTS zone_flags;

I have not done this in a while, but this is what I was doing when databases were being updated routinely. I am currently running raw-PEQ, so I cannot vouch that this is perfect today. Also, this is one script - from use ax_peq to drop zone_flags. The last step is to then backup (or physically move) the remaining database files from AX_PEQ into your PEQ tables, and you're done.

Angelox · #3 11-03-2006, 01:23 AM

Thanks John, so probably if the person was using only ax_peq for the server, he would only need back up/restore The first codeblock;

Code:

use ax_peq;

DROP TABLE IF EXISTS aa_timers;
DROP TABLE IF EXISTS account;
DROP TABLE IF EXISTS character_;
DROP TABLE IF EXISTS character_backup;
DROP TABLE IF EXISTS commands;
DROP TABLE IF EXISTS eventlog;
DROP TABLE IF EXISTS faction_values;
DROP TABLE IF EXISTS guilds;
DROP TABLE IF EXISTS guild_ranks;
DROP TABLE IF EXISTS guild_relations;
DROP TABLE IF EXISTS guild_members;
DROP TABLE IF EXISTS guild_alliances;
DROP TABLE IF EXISTS guild_controllers;
DROP TABLE IF EXISTS hackers;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS launcher;
DROP TABLE IF EXISTS launcher_zones;
DROP TABLE IF EXISTS logs;
DROP TABLE IF EXISTS name_filter;
DROP TABLE IF EXISTS object_contents;
DROP TABLE IF EXISTS peq_admin;
DROP TABLE IF EXISTS petitions;
DROP TABLE IF EXISTS player_corpses;
DROP TABLE IF EXISTS player_corpses_backup;
DROP TABLE IF EXISTS quest_globals;
DROP TABLE IF EXISTS rule_sets;
DROP TABLE IF EXISTS rule_values;
DROP TABLE IF EXISTS variables;
DROP TABLE IF EXISTS spells;
DROP TABLE IF EXISTS sharedbank;
DROP TABLE IF EXISTS timers;

and have a backup of his original tables -
Maybe GeorgeS can simplify this in a new tool, I'd be glad to test it.
These look like all the tables needed.

Angelox · #4 11-03-2006, 03:38 AM

It just dawned on me;
I can easily make a "patch" version of the db;
Start with this;

Code:

use peq;

DROP TABLE IF EXISTS aa_actions;
DROP TABLE IF EXISTS aa_levels;
DROP TABLE IF EXISTS aa_swarmpets;
DROP TABLE IF EXISTS altadv_vars;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS class_skill;
DROP TABLE IF EXISTS doors;
DROP TABLE IF EXISTS faction_list;
DROP TABLE IF EXISTS fear_hints;
DROP TABLE IF EXISTS fishing;
DROP TABLE IF EXISTS forage;
DROP TABLE IF EXISTS grid;
DROP TABLE IF EXISTS grid_entries;
DROP TABLE IF EXISTS ground_spawns;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS lootdrop;
DROP TABLE IF EXISTS lootdrop_entries;
DROP TABLE IF EXISTS loottable;
DROP TABLE IF EXISTS loottable_entries;
DROP TABLE IF EXISTS merchantlist;
DROP TABLE IF EXISTS merchantlist_temp;
DROP TABLE IF EXISTS merchantsets;
DROP TABLE IF EXISTS npc_faction;
DROP TABLE IF EXISTS npc_faction_entries;
DROP TABLE IF EXISTS npc_spells;
DROP TABLE IF EXISTS npc_spells_entries;
DROP TABLE IF EXISTS npc_types;
DROP TABLE IF EXISTS object;
DROP TABLE IF EXISTS pets;
DROP TABLE IF EXISTS spawn2;
DROP TABLE IF EXISTS spawn_conditions;
DROP TABLE IF EXISTS spawn_events;
DROP TABLE IF EXISTS spawnentry;
DROP TABLE IF EXISTS spawngroup;
DROP TABLE IF EXISTS start_zones;
DROP TABLE IF EXISTS starting_items;
DROP TABLE IF EXISTS tradeskill_recipe;
DROP TABLE IF EXISTS tradeskill_recipe_entries;
DROP TABLE IF EXISTS traps;
DROP TABLE IF EXISTS tribute_levels;
DROP TABLE IF EXISTS tributes;
DROP TABLE IF EXISTS zone;
DROP TABLE IF EXISTS zone_points;
DROP TABLE IF EXISTS zone_server;
DROP TABLE IF EXISTS zone_state_dump;
DROP TABLE IF EXISTS zoneserver_auth;
DROP TABLE IF EXISTS zone_flags;

Then have a dump of all these tables from the updated database ready to follow and replace?

sesmar · #5 11-03-2006, 04:08 AM

What I usually do when I get a new version of the DB, such as Angelox's db is something similar to what has been posted here.

I use the base PEQ drop_users.sql script to find all the tables that are related to users. Then I just open up MySql Administrator and create a backup job that backs up those tables. Once I run that job I will open up the SQL script that it creates and find the first reference to the Database name (usually close to top of the file.) So if the Database was named peq I will look for the 'use peq' statement and then I do a find and replace on all instances of peq with my new database name (such as peq to ax_peq). Now all you have to do is restore the backup file and you are all done.

Angelox · #6 11-03-2006, 05:20 AM

I used John Adams post and made an "update" sql, that should update just what's needed, and nothing else. I know I should of thought of this before, as it is a simple solution. But I always make "mental blocks for myself.
I tried it out, seems ok if anyone wants to try, it's posted.
Thanks for the as usual help you all give me.

John Adams · #7 11-03-2006, 07:02 AM

Just don't forget your peq_admin, and rules* related tables that are not in the base drop_users.sql script (unless it's been added since my d/l on 7/16).

I think those are the only additional server-specific tables added since peq-luclin-rc1.