Back up scripts for your characters

[Angelox]

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

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

#!/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

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

#!/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

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

#!/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

[Loftus]

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

[MNWatchdog]

Shouldn't you also include the quest_globals table for a full character backup?

[bad_captain]

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..