Go Back   EQEmulator Home > EQEmulator Forums > Support > Support::MiniLogin

Support::MiniLogin Support forum for problems with the official MiniLogin release.

Reply
 
Thread Tools Display Modes
  #1  
Old 02-22-2009, 10:55 AM
Angelox
AX Classic Developer
 
Join Date: May 2006
Location: filler
Posts: 2,049
Default Back up scripts for your characters

This will get your characters and bots backed-up and will restore when ready (you don't need bots for this to work, so non-bot users apply). I made these mostly for Linux, as GeorgeS tools would not back up from Linux and that's where I keep my MySql databases.
With these files I can also back up /restore from a remote location.
One important thing you should know about Linux is security; Linux is a very secure system by default, so you just can't go around making and removing files, unless you logged in as root, and that's a bad idea (MySql is not allowed to write in your home directory). When I first attempted a backup on a remote server, I had trouble with permissions and MySql writing files in places other than where the database resides. One thing I discovered is, the /tmp directory is flagged as a 'world writable' directory, and I could use this directory without disrupting or changing things. I made my own directory in /tmp and is called /temp/updates. here is where my files are backed up, and since my scripts make a zip of what I have done and place it in my home directory, it doesn't matter if these files get lost or deleted.
I'll start with the BackUp scripts;
backup.sql
Code:
SELECT * FROM npc_types WHERE isbot=1 INTO OUTFILE '/tmp/updates/bots.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM botinventory INTO OUTFILE '/tmp/updates/botinventory.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM botleader INTO OUTFILE '/tmp/updates/botleader.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM botsowners INTO OUTFILE '/tmp/updates/botsowners.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM account INTO OUTFILE '/tmp/updates/account.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM character_ INTO OUTFILE '/tmp/updates/character_.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM character_backup INTO OUTFILE '/tmp/updates/character_backup.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM character_activities INTO OUTFILE '/tmp/updates/character_activities.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM eventlog INTO OUTFILE '/tmp/updates/eventlog.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM faction_values INTO OUTFILE '/tmp/updates/faction_values.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM graveyard INTO OUTFILE '/tmp/updates/graveyard.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM guild_members INTO OUTFILE '/tmp/updates/guild_members.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM guild_ranks INTO OUTFILE '/tmp/updates/guild_ranks.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM guild_relations INTO OUTFILE '/tmp/updates/guild_relations.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM guilds INTO OUTFILE '/tmp/updates/guilds.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM inventory INTO OUTFILE '/tmp/updates/inventory.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM keyring INTO OUTFILE '/tmp/updates/keyring.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM peq_admin INTO OUTFILE '/tmp/updates/peq_admin.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM petitions INTO OUTFILE '/tmp/updates/petitions.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM player_corpses INTO OUTFILE '/tmp/updates/player_corpses.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM player_corpses_backup INTO OUTFILE '/tmp/updates/player_corpses_backup.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM sharedbank INTO OUTFILE '/tmp/updates/sharedbank.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

SELECT * FROM zone_flags INTO OUTFILE '/tmp/updates/zone_flags.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Here is what you run;
backup_char
Code:
#!/bin/sh
echo compressing old files...
sleep 3
zip charbu.zip /tmp/updates/*.txt &
echo compressing old files...
sleep 10
rm -f /tmp/updates/*.txt &
echo removing old files...
sleep 5
mysql -ueq -ppassword_here -Dax_peq < backup.sql
echo back-up/adding new files...
sleep 5
Now, you can restore a newer database, doesn't matter if it has characters or not, you can first clear it out with this (THIS WILL CLEAN OUT YOUR ACCOUNTS!!!, so back up first);
clear_char.sql
Code:
DELETE QUICK FROM account;
DELETE QUICK FROM character_;
DELETE QUICK FROM character_backup;
DELETE QUICK FROM character_activities;
DELETE QUICK FROM eventlog;
DELETE QUICK FROM faction_values;
DELETE QUICK FROM graveyard;
DELETE QUICK FROM guild_members;
DELETE QUICK FROM guild_ranks;
DELETE QUICK FROM guild_relations;
DELETE QUICK FROM guilds;
DELETE QUICK FROM inventory;
DELETE QUICK FROM keyring;
DELETE QUICK FROM peq_admin;
DELETE QUICK FROM petitions;
DELETE QUICK FROM player_corpses;
DELETE QUICK FROM player_corpses_backup;
DELETE QUICK FROM sharedbank;
DELETE QUICK FROM zone_flags;

DELETE FROM npc_types where isbot=1;
DELETE QUICK FROM botinventory;
DELETE QUICK FROM botleader;
DELETE QUICK FROM botsowners;
This will run it;
clear_char
Code:
#!/bin/sh

echo clearing out accounts/characters...
mysql -ueq -ppassword_here -Dax_peq < clear_char.sql
sleep 5
echo done.
Finally, the restore scripts;
restore_char.sql
Code:
LOAD DATA INFILE '/tmp/updates/bots.txt' INTO TABLE npc_types
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/botinventory.txt' INTO TABLE botinventory
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/botleader.txt' INTO TABLE botleader
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/botsowners.txt' INTO TABLE botsowners
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/account.txt' INTO TABLE account
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/character_.txt' INTO TABLE character_
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/character_backup.txt' INTO TABLE character_backup
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/character_activities.txt' INTO TABLE character_activities
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/eventlog.txt' INTO TABLE eventlog
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/faction_values.txt' INTO TABLE faction_values
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/graveyard.txt' INTO TABLE graveyard
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/guild_members.txt' INTO TABLE guild_members
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/guild_ranks.txt' INTO TABLE guild_ranks
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/guild_relations.txt' INTO TABLE guild_relations
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/guilds.txt' INTO TABLE guilds
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/inventory.txt' INTO TABLE inventory
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/keyring.txt' INTO TABLE keyring
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/peq_admin.txt' INTO TABLE peq_admin
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/petitions.txt' INTO TABLE petitions
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/player_corpses.txt' INTO TABLE player_corpses
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/player_corpses_backup.txt' INTO TABLE player_corpses_backup
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/sharedbank.txt' INTO TABLE sharedbank
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/tmp/updates/zone_flags.txt' INTO TABLE zone_flags
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n';
and run this;
restore_char
Code:
#!/bin/sh

echo restoring accounts/characters...
mysql -ueq -ppassword_here -Dax_peq < restore.sql
sleep 5
echo done.
You can run these scripts from anywhere adn the back up zip will be where you ran it from. First time you back up, you should back up twice, so you get data into you zip backup.
Warning: there's no pop ups to ask you if you are sure you want to do this, so once executed, you're committed

Last edited by Angelox; 02-22-2009 at 06:57 PM..
Reply With Quote
  #2  
Old 02-23-2009, 02:04 PM
Loftus
Sarnak
 
Join Date: Feb 2009
Location: New Mexico
Posts: 34
Default

Thanks alot for that, but with my newbness it is too much for me to digest! :(

Ok, so I have a server running and Minilogin for LAN play. Everything is working, as far as I have tested.

Now, I want to be able to back-up the characters on the server in case I have a meltdown, RAID crash, etc.

Is it possible to give me a step-by-step on how to back-up the characters on the server and thus preventing catastrophy when something bad happens?

Thanks in advance,

- L
Reply With Quote
  #3  
Old 04-21-2010, 10:29 AM
MNWatchdog
Hill Giant
 
Join Date: Feb 2006
Posts: 179
Default

Shouldn't you also include the quest_globals table for a full character backup?
Reply With Quote
  #4  
Old 05-05-2010, 08:23 AM
bad_captain
Developer
 
Join Date: Feb 2009
Location: Cincinnati, OH
Posts: 512
Default

Probably, but since you can see that post was written over a year ago, the spells_global table was not in yet. =) Feel free to edit the script to include the table for a fuller backup..
Reply With Quote
Reply


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

   

All times are GMT -4. The time now is 03:08 PM.


 

Everquest is a registered trademark of Daybreak Game Company LLC.
EQEmulator is not associated or affiliated in any way with Daybreak Game Company LLC.
Except where otherwise noted, this site is licensed under a Creative Commons License.
       
Powered by vBulletin®, Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Template by Bluepearl Design and vBulletin Templates - Ver3.3