Full database update batch file

Applications/Tools written to make running DarkStar easier for the rest of us.
Post Reply
Delaide
Posts: 473
Joined: Sat Jun 14, 2014 8:58 am

Full database update batch file

Post by Delaide » Tue Apr 14, 2015 5:40 am

So, while still not that great, this is working. You will now only need to change the top SET variables in this script.
This will: backup important tables -> drop dspdb -> create dspdb -> populate your important tables first -> populate the rest of the tables from your dsp\sql folder (It will not over-write the important tables with the raw git tables, so no worries)

Create a text document and rename it to whatever, but make sure it is .bat (For example, mydspupdate.bat)

Right click, not double click, and edit it, pasting in the code at the bottom.

Set your mysql location:
SET mySQL=D:\wamp\bin\mysql\mysql5.6.17\bin
SET Filepath=D:

Set your database information:
SET User=-uroot
SET Pass=-proot

Set your backup directory and the location of your git pulls:
SET Charbackup=D:\PlayOnline\SquareEnix\charbackup\
SET dspsql="D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar\sql\"

Save, close, and click.

Please note, this only works for Windows.
I will try to release a table verification sql script later, and probably try to put directly to the git, or just put here if that doesn't work, so it will try to verify the table structures for the important tables and fix them if needed.

Code: Select all

@echo off

SET Filepath=D:
SET mySQL=D:\wamp\bin\mysql\mysql5.6.17\bin
SET User=-uroot
SET Pass=-proot
SET Charbackup=D:\PlayOnline\SquareEnix\charbackup\
SET dspsql="D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar\sql\"

%Filepath%
cd %mySQL%

IF not exist %Charbackup% (mkdir %Charbackup%)
mysqldump.exe -e %User% %Pass% dspdb auction_house > %Charbackup%auction_house.sql
mysqldump.exe -e %User% %Pass% dspdb accounts > %Charbackup%accounts.sql
mysqldump.exe -e %User% %Pass% dspdb accounts_banned > %Charbackup%accounts_banned.sql
mysqldump.exe -e %User% %Pass% dspdb char_effects > %Charbackup%char_effects.sql
mysqldump.exe -e %User% %Pass% dspdb char_equip > %Charbackup%char_equip.sql
mysqldump.exe -e %User% %Pass% dspdb char_exp > %Charbackup%char_exp.sql
mysqldump.exe -e %User% %Pass% dspdb char_inventory > %Charbackup%char_inventory.sql
mysqldump.exe -e %User% %Pass% dspdb char_jobs > %Charbackup%char_jobs.sql
mysqldump.exe -e %User% %Pass% dspdb char_look > %Charbackup%char_look.sql
mysqldump.exe -e %User% %Pass% dspdb char_pet > %Charbackup%char_pet.sql
mysqldump.exe -e %User% %Pass% dspdb char_pet_name > %Charbackup%char_pet_name.sql
mysqldump.exe -e %User% %Pass% dspdb char_points > %Charbackup%char_points.sql
mysqldump.exe -e %User% %Pass% dspdb char_profile > %Charbackup%char_profile.sql
mysqldump.exe -e %User% %Pass% dspdb char_skills > %Charbackup%char_skills.sql
mysqldump.exe -e %User% %Pass% dspdb char_stats > %Charbackup%char_stats.sql
mysqldump.exe -e %User% %Pass% dspdb char_storage > %Charbackup%char_storage.sql
mysqldump.exe -e %User% %Pass% dspdb char_vars > %Charbackup%char_vars.sql
mysqldump.exe -e %User% %Pass% dspdb char_weapon_skill_points > %Charbackup%char_weapon_skill_points.sql
mysqldump.exe -e %User% %Pass% dspdb chars > %Charbackup%chars.sql
mysqldump.exe -e %User% %Pass% dspdb conquest_system > %Charbackup%conquest_system.sql
mysqldump.exe -e %User% %Pass% dspdb delivery_box > %Charbackup%delivery_box.sql
mysqldump.exe -e %User% %Pass% dspdb linkshells > %Charbackup%linkshells.sql

mysqldump.exe -e %User% %Pass% dspdb > %Charbackup%dspdb.sql

mysqladmin.exe %User% %Pass% DROP dspdb
mysqladmin.exe %User% %Pass% CREATE dspdb

mysql.exe %User% %Pass% dspdb < %Charbackup%accounts.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%accounts_banned.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%auction_house.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_effects.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_equip.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_exp.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_inventory.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_jobs.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_look.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_pet.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_pet_name.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_points.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_profile.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_skills.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_stats.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_storage.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_vars.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%char_weapon_skill_points.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%chars.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%conquest_system.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%delivery_box.sql
mysql.exe %User% %Pass% dspdb < %Charbackup%linkshells.sql

mysql.exe %User% %Pass% dspdb < %dspsql%abilities.sql
mysql.exe %User% %Pass% dspdb < %dspsql%abilities_charges.sql
mysql.exe %User% %Pass% dspdb < %dspsql%accounts_parties.sql
mysql.exe %User% %Pass% dspdb < %dspsql%accounts_sessions.sql
mysql.exe %User% %Pass% dspdb < %dspsql%audit_chat.sql
mysql.exe %User% %Pass% dspdb < %dspsql%augments.sql
mysql.exe %User% %Pass% dspdb < %dspsql%bcnm_battlefield.sql
mysql.exe %User% %Pass% dspdb < %dspsql%bcnm_info.sql
mysql.exe %User% %Pass% dspdb < %dspsql%bcnm_loot.sql
mysql.exe %User% %Pass% dspdb < %dspsql%bcnm_treasure_chests.sql
mysql.exe %User% %Pass% dspdb < %dspsql%blacklist.sql
mysql.exe %User% %Pass% dspdb < %dspsql%blue_spell_list.sql
mysql.exe %User% %Pass% dspdb < %dspsql%blue_spell_mods.sql
mysql.exe %User% %Pass% dspdb < %dspsql%blue_traits.sql
mysql.exe %User% %Pass% dspdb < %dspsql%char_recast.sql
mysql.exe %User% %Pass% dspdb < %dspsql%elevators.sql
mysql.exe %User% %Pass% dspdb < %dspsql%exp_base.sql
mysql.exe %User% %Pass% dspdb < %dspsql%exp_table.sql
mysql.exe %User% %Pass% dspdb < %dspsql%fishing_fish.sql
mysql.exe %User% %Pass% dspdb < %dspsql%fishing_lure.sql
mysql.exe %User% %Pass% dspdb < %dspsql%fishing_rod.sql
mysql.exe %User% %Pass% dspdb < %dspsql%fishing_zone.sql
mysql.exe %User% %Pass% dspdb < %dspsql%guilds.sql
mysql.exe %User% %Pass% dspdb < %dspsql%guild_item_points.sql
mysql.exe %User% %Pass% dspdb < %dspsql%guild_shops.sql
mysql.exe %User% %Pass% dspdb < %dspsql%instance_entities.sql
mysql.exe %User% %Pass% dspdb < %dspsql%instance_list.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_armor.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_basic.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_furnishing.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_latents.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_mods.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_mods_pet.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_puppet.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_usable.sql
mysql.exe %User% %Pass% dspdb < %dspsql%item_weapon.sql
mysql.exe %User% %Pass% dspdb < %dspsql%linkshells.sql
mysql.exe %User% %Pass% dspdb < %dspsql%merits.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_droplist.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_family_mods.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_family_system.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_groups.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_pets.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_pools.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_pool_mods.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_skill.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_spawn_mods.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_spawn_points.sql
mysql.exe %User% %Pass% dspdb < %dspsql%mob_spell_lists.sql
mysql.exe %User% %Pass% dspdb < %dspsql%nm_spawn_points.sql
mysql.exe %User% %Pass% dspdb < %dspsql%npc_list.sql
mysql.exe %User% %Pass% dspdb < %dspsql%pet_list.sql
mysql.exe %User% %Pass% dspdb < %dspsql%pet_name.sql
mysql.exe %User% %Pass% dspdb < %dspsql%server_variables.sql
mysql.exe %User% %Pass% dspdb < %dspsql%skillchain_damage_modifiers.sql
mysql.exe %User% %Pass% dspdb < %dspsql%skill_caps.sql
mysql.exe %User% %Pass% dspdb < %dspsql%skill_ranks.sql
mysql.exe %User% %Pass% dspdb < %dspsql%spell_list.sql
mysql.exe %User% %Pass% dspdb < %dspsql%status_effects.sql
mysql.exe %User% %Pass% dspdb < %dspsql%synth_recipes.sql
mysql.exe %User% %Pass% dspdb < %dspsql%traits.sql
mysql.exe %User% %Pass% dspdb < %dspsql%transport.sql
mysql.exe %User% %Pass% dspdb < %dspsql%treasure_spawn_points.sql
mysql.exe %User% %Pass% dspdb < %dspsql%triggers.sql
mysql.exe %User% %Pass% dspdb < %dspsql%water_points.sql
mysql.exe %User% %Pass% dspdb < %dspsql%weapon_skills.sql
mysql.exe %User% %Pass% dspdb < %dspsql%zonelines.sql
mysql.exe %User% %Pass% dspdb < %dspsql%zone_settings.sql
mysql.exe %User% %Pass% dspdb < %dspsql%zone_weather.sql

Delaide
Posts: 473
Joined: Sat Jun 14, 2014 8:58 am

Re: Full database update batch file

Post by Delaide » Tue Apr 14, 2015 8:14 am

removing this, since it keeps wiping my characters.
Last edited by Delaide on Fri Apr 17, 2015 7:39 am, edited 1 time in total.

dacrybabysuck
Posts: 5
Joined: Mon Aug 27, 2012 9:54 pm

Re: Full database update batch file

Post by dacrybabysuck » Tue Apr 14, 2015 9:56 am

An easy way to check for a table is a with a query like "select table_name from information_schema.tables where table_name = 'chars'"; It will either return the table name if it's there or not if it doesn't exist.


EDIT: to be safer in the where clause you could also append the schema_name='dspdb' if you have more than one schema in your mysql instance.

The other approach to this is "show tables like 'chars';" This is a more direct mysql way of doing it.

Post Reply