Loading...   

[Show Table of Contents]


This Perl script was designed as a solution to back up a database with MINIMAL EFFORT for Windows servers and compress these backups efficiently for large storage savings and automation...

§What It Does

  • In essence, databases can be several GB's and they are strictly text, so doing a compression of that file after a backup can reduce the size from for example 1.6GB down to 52MB!
  • Specify specific database tables (Default will backup all) Example: tables="table1,table2,table3"
  • Specify a specific database (Default will use the database specified in the eqemu_config.xml) Example: database="dbname" 
  • Specify a specific location for the Backup to go to (Default will go in the server folder, or wherever you run the script) Example: location="C:\Backups"
  • Specify compression, which will use 7-Zip or RAR to compress (If they are installed) Example: compress
§Arguments
C:\Server>perl DB_Dumper.pl
ERROR! Need arguments
######################################################
        loc="C:\File Location"  - File path location to backup...
        database="dbname"       - Manually specify databasename, default is database in eqemu_config.xml
        tables="table1,table2,table3"   - Manually specify tables, default is to dump all tables from database
        compress                - Compress Database with 7-ZIP, will fallback to WinRAR depending on what is installed (Must be installed to default program dir)...
        Example: perl DB_Dumper.pl loc="E:\Backups"
######################################################

§The Script

  • Store this script IN YOUR SERVER FOLDER! Wherever your eqemu_config.xml is, this script should be there!
    • Name it DB_Dumper.pl
    • Download it HERE

§Basic Backup

  • Below example will backup the database specified in the eqemu_config.xml (Since none has been specified) and compress it
    • Since no location has been specified, it will show up in C:\Server> folder
C:\Server>perl DB_Dumper.pl compress

Todays Date: 04-24-2014
Compression SET
No save location specified... Saving to folder script is running in...
Backing up Database eqemu2...

--- CMD ---
mysqldump -uroot -pDBPASSWORD eqemu2 > "eqemu2 04-24-2014.sql"
---------------------------------------

Backup DONE... DB Backup File Size '1723061145' (1.60 GiB)

 ::: You have 7-Zip installed as 64 Bit...

Compressing Database with 7-ZIP...

--- CMD ---
"C:\Program Files\7-Zip\7z" a -t7z -m0=lzma -mx=9 "eqemu2 04-24-2014.7z" "eqemu2 04-24-2014.sql"
---------------------------------------


7-Zip [64] 9.20  Copyright (c) 1999-2010 Igor Pavlov  2010-11-18
Scanning

Creating archive eqemu2 04-24-2014.7z

Compressing  eqemu2 04-24-2014.sql   18%

§Targeted Table Backup

  • You can specify specific tables you want to backup, example:
§Player Tables
perl DB_Dumper.pl compress tables="aa_timers,account,account_flags,account_ip,account_rewards,adventure_details,adventure_members,adventure_stats,Banned_IPs,bugs,buyer,char_recipe_list,character_,character_activities,character_alt_currency,character_backup,character_buffs,character_enabledtasks,character_pet_buffs,character_pet_info,character_pet_inventory,character_tasks,chatchannels,completed_tasks,discovered_items,eventlog,faction_values,friends,gm_ips,group_id,group_leaders,guilds,guild_bank,guild_ranks,guild_relations,guild_members,hackers,instance_list_player,inventory,item_tick,keyring,launcher_zones,lfguild,mail,merchantlist_temp,name_filter,object_contents,petitions,player_corpses,player_corpses_backup,player_titlesets,qs_player_move_record,qs_player_move_record_entries,qs_player_npc_kill_record,qs_player_npc_kill_record_entries,qs_player_speech,qs_player_trade_record,qs_player_trade_record_entries,qs_merchant_transaction_record,qs_merchant_transaction_record_entries,qs_player_delete_record,qs_player_delete_record_entries,qs_player_handin_record,qs_player_handin_record_entries,quest_globals,raid_details,raid_members,reports,respawn_times,sharedbank,spell_globals,timers,trader,trader_audit,zone_flags,commands,launcher,rule_sets,rule_values,variables"
§Content Tables
perl DB_Dumper.pl compress tables="aa_actions,aa_effects,aa_required_level_cost,activities,adventure_template,adventure_template_entry,adventure_template_entry_flavor,altadv_vars,alternate_currency,base_data,blocked_spells,books,char_create_combinations,char_create_point_allocations,class_skill,damageshieldtypes,doors,faction_list,faction_list_mod,fear_hints,fishing,forage,goallists,graveyard,grid,grid_entries,ground_spawns,horses,instance_list,items,ldon_trap_templates,ldon_trap_entries,level_exp_mods,lootdrop,lootdrop_entries,loottable,loottable_entries,merc_armorinfo,merc_weaponinfo,merc_stats,merc_merchant_entries,merc_merchant_template_entries,merc_merchant_templates,merc_stance_entries,merc_templates,merc_npc_types,merc_name_types,merc_subtypes,merc_types,merc_spell_list_entries,merc_spell_lists,merc_buffs,mercs,merc_inventory,merchantlist,npc_emotes,npc_faction,npc_faction_entries,npc_spells,npc_spells_entries,npc_types,npc_types_metadata,npc_types_tint,object,pets,pets_equipmentset,pets_equipmentset_entries,proximities,races,saylink,skill_caps,spawn2,spawn_conditions,spawn_condition_values,spawn_events,spawnentry,spawngroup,spells_new,start_zones,starting_items,tasks,tasksets,titles,tradeskill_recipe,tradeskill_recipe_entries,traps,tribute_levels,tributes,veteran_reward_templates,zone,zone_points,zone_server,zone_state_dump,zoneserver_auth"

 

§Advanced

  • Below example is using every option available
    • Specifying specific database tables
    • Specifying a specific database
    • Specifying a specific location for the Backup to go to
    • Specifying compression, which will use 7-Zip or RAR to compress (If they are installed)
C:\Server>perl DB_Dumper.pl tables="npc_types,spawn_events,spawn_conditions,spawn2,spawnentry,spawngroup,rule_values,spells_new" compress loc="C:\Backups" database="eqemu2"

Todays Date: 04-24-2014
Backing up tables: 

############################
npc_types
spawn_events
spawn_conditions
spawn2
spawnentry
spawngroup
rule_values
spells_new
############################

Compression SET
Backup Directory: C:\Backups
Database is eqemu2
Directory currently exists... Adding files to it...

Performing table based backup...
Backing up Database eqemu2... 

--- CMD --- 
mysqldump -uroot -pxxxxxxxx eqemu2 npc_types spawn_events spawn_conditions spawn2 spawnentry spawngroup rule_values spells_new  > "C:\Backups\npc_types_spawn_even... 04-24-2014.sql"
---------------------------------------

Backup DONE... DB Backup File Size '57498847' (54.84 MiB)

 ::: You have 7-Zip installed as 64 Bit...

Compressing Database with 7-ZIP... 

--- CMD --- 
"C:\Program Files\7-Zip\7z" a -t7z -m0=lzma -mx=9 "C:\Backups\npc_types_spawn_even... 04-24-2014.7z" "C:\Backups\npc_types_spawn_even... 04-24-2014.sql" 
---------------------------------------


7-Zip [64] 9.20  Copyright (c) 1999-2010 Igor Pavlov  2010-11-18

Scanning

Updating archive C:\Backups\npc_types_spawn_even... 04-24-2014.7z

Compressing  npc_types_spawn_even... 04-24-2014.sql

Everything is Ok

Deleting RAW .sql Dump... 

--- CMD --- 
del "C:\Backups\npc_types_spawn_even... 04-24-2014.sql" 
---------------------------------------

Final file located: C:\Backups\npc_types_spawn_even... 04-24-2014.7z