Batch export of important tables

[Delaide]

So, I do alot of work with batch files, and have tried to figure out a way for people to automate character backup so you can always restore. Basically, I keep my server and my git pulls in a separate folder. When I update my server, I do certain steps.

1. backup all database requirements
2. Zip my current server so if there is a problem, I can restore it as it was before updating
3. Delete my server and copy all git files, excluding the .git folder, to the server file
4. Copy my backed-up tables to the SQL folder in the darkstar system (Over-writing the original ones that would have come from the git updates)
5. Edit appropriate configs and settings.lua
6. Build servers using Visual Express 2013, adjusting appropriate code as needed

So, I have always hand exported the tables one by one. Now, I am trying it by using this bat file. Please note, I have the reminders (REM) only to mention what I am doing and why I am doing it. You do not need to put them in your bat file.
(Please also note I will not do a full live test until January 17/18, so it is not advised to use this until I test it completely.)

Here is the bat file:

Code: Select all

REM I change to C because I put my bat file in a different drive, where my PlayOnline is kept, so I need to change the directory at start
c:

REM If your MySQL.exe is located in a different place, you need to change this. This is where my MySQL is installed.
cd C:\Program Files\MySQL\MySQL Server 5.6\bin

REM If you changed the root name, change the -uroot to -uwhatevernameyouused, the password to -pwhateveryourpasswordis. Change the save directory to anywhere you would like to back up your characters.
mysqldump.exe -e -uroot -proot dspdb auction_house > D:\charbackup\auction_house.sql
mysqldump.exe -e -uroot -proot dspdb accounts > D:\charbackup\accounts.sql
mysqldump.exe -e -uroot -proot dspdb accounts_banned > D:\charbackup\accounts_banned.sql
mysqldump.exe -e -uroot -proot dspdb char_effects > D:\charbackup\char_effects.sql
mysqldump.exe -e -uroot -proot dspdb char_equip > D:\charbackup\char_equip.sql
mysqldump.exe -e -uroot -proot dspdb char_exp > D:\charbackup\char_exp.sql
mysqldump.exe -e -uroot -proot dspdb char_inventory > D:\charbackup\char_inventory.sql
mysqldump.exe -e -uroot -proot dspdb char_jobs > D:\charbackup\char_jobs.sql
mysqldump.exe -e -uroot -proot dspdb char_look > D:\charbackup\char_look.sql
mysqldump.exe -e -uroot -proot dspdb char_merit > D:\charbackup\char_merits.sql
mysqldump.exe -e -uroot -proot dspdb char_pet > D:\charbackup\char_pet.sql
mysqldump.exe -e -uroot -proot dspdb char_pet_name > D:\charbackup\char_pet_name.sql
mysqldump.exe -e -uroot -proot dspdb char_points > D:\charbackup\char_points.sql
mysqldump.exe -e -uroot -proot dspdb char_profile > D:\charbackup\char_profile.sql
mysqldump.exe -e -uroot -proot dspdb char_recast > D:\charbackup\char_recast.sql
mysqldump.exe -e -uroot -proot dspdb char_skills > D:\charbackup\char_skills.sql
mysqldump.exe -e -uroot -proot dspdb char_stats > D:\charbackup\char_stats.sql
mysqldump.exe -e -uroot -proot dspdb char_storage > D:\charbackup\char_storage.sql
mysqldump.exe -e -uroot -proot dspdb char_style > D:\charbackup\char_style.sql
mysqldump.exe -e -uroot -proot dspdb char_vars > D:\charbackup\char_vars.sql
mysqldump.exe -e -uroot -proot dspdb char_weapon_skill_points > D:\charbackup\char_weapon_skill_points.sql
mysqldump.exe -e -uroot -proot dspdb chars > D:\charbackup\chars.sql
mysqldump.exe -e -uroot -proot dspdb conquest_system > D:\charbackup\conquest_system.sql
mysqldump.exe -e -uroot -proot dspdb delivery_box > D:\charbackup\delivery_box.sql
mysqldump.exe -e -uroot -proot dspdb linkshells > D:\charbackup\linkshells.sql

REM I always back up my full dspdb in case I make some mistake. I once forgot to backup my account, so when I replaced my directory, there was no account to log into. I was able to restore this only because I had the full dspdb dump
mysqldump.exe -e -uroot -proot dspdb > D:\charbackup\dspdb.sql

Confirmed (Please see discussing below for more information) this script should work with no issue. I still need to run a test, and will edit this out once I have confirmed completely.

[atom0s]

You are dumping binary data, so parts of the data are going to show up as odd chars because of how Notepad++ interprets them. \0 means null (or 0), then the rest are based on what the editor decides to show them as. But either way they are escaped data bytes. Values between 0 and 255. \0 \1 \2 \3 \4 etc.

So this is how Notepad++ shows that data:

[Delaide]

So, as a raw SQL file, these would re-import just fine? So no real problems with this data dump?

[atom0s]

Correct, it should import fine.

[DanteMccloud]

I feel it's worth noting you left out spells and merits... found that out the hard way.. thank god for that full database backup at the bottom haha

mysqldump.exe -e -uroot -proot dspdb char_spells > D:\charbackup\char_spells.sql
mysqldump.exe -e -uroot -proot dspdb char_merit > D:\charbackup\char_merit.sql

[Delaide]

lol, this is over a year old.

My current is:

Code: Select all

c:
cd C:\Program Files\MySQL\MySQL Server 5.7\bin

mysqldump.exe -e -uroot -ppassword dspdb auction_house > "D:\FFXIServer\charbackup\auction_house.sql"
mysqldump.exe -e -uroot -ppassword dspdb accounts > "D:\FFXIServer\charbackup\accounts.sql"
mysqldump.exe -e -uroot -ppassword dspdb accounts_banned > "D:\FFXIServer\charbackup\accounts_banned.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_effects > "D:\FFXIServer\charbackup\char_effects.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_equip > "D:\FFXIServer\charbackup\char_equip.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_exp > "D:\FFXIServer\charbackup\char_exp.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_inventory > "D:\FFXIServer\charbackup\char_inventory.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_jobs > "D:\FFXIServer\charbackup\char_jobs.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_look > "D:\FFXIServer\charbackup\char_look.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_merit > "D:\FFXIServer\charbackup\char_merits.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_pet > "D:\FFXIServer\charbackup\char_pet.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_pet_name > "D:\FFXIServer\charbackup\char_pet_name.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_points > "D:\FFXIServer\charbackup\char_points.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_profile > "D:\FFXIServer\charbackup\char_profile.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_skills > "D:\FFXIServer\charbackup\char_skills.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_spells > "D:\FFXIServer\charbackup\char_spells.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_stats > "D:\FFXIServer\charbackup\char_stats.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_storage > "D:\FFXIServer\charbackup\char_storage.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_style > "D:\FFXIServer\charbackup\char_style.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_vars > "D:\FFXIServer\charbackup\char_vars.sql"
mysqldump.exe -e -uroot -ppassword dspdb char_weapon_skill_points > "D:\FFXIServer\charbackup\char_weapon_skill_points.sql"
mysqldump.exe -e -uroot -ppassword dspdb chars > "D:\FFXIServer\charbackup\chars.sql"
mysqldump.exe -e -uroot -ppassword dspdb conquest_system > "D:\FFXIServer\charbackup\conquest_system.sql"
mysqldump.exe -e -uroot -ppassword dspdb delivery_box > "D:\FFXIServer\charbackup\delivery_box.sql"
mysqldump.exe -e -uroot -ppassword dspdb linkshells > "D:\FFXIServer\charbackup\linkshells.sql"

mysqldump.exe -e -uroot -ppassword dspdb > "D:\FFXIServer\charbackup\dspdb.sql"

pause

One thing I noted was, be careful of that char_merit, because the DSP gitpull uses char_merits.sql, while the table is char_merit. It is a little inconsistent. And yes, this is why I have the full database dump, in case there is a messup somewhere.