current server backup and rebuild script

[Delaide]

I have shown people elements of what I have done to automate my server startup, my character backup, and my automated build file.

I have now completed a script that will allow me to backup my characters, delete the old server, copy the git files from my update folder, rebuild the server, and restore my character backups.

I have this in multiple files, and I will list each file.

Please keep in mind though, there are probably many better ways to do this (And feel free to make suggestions if you know how I can simplify them). You will also need to change everything to fit your environment, so this isn't an easy to use, create and go. Hopefully I will finish

Although I doubt anyone specifically will want this, hopefully it can help people come up with their own modifications to make their server control automated.

Here are my files:

Char_backup.bat (Backs up the appropriate tables from your mySQL database):

Code: Select all

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

mysqldump.exe -e -uroot -proot dspdb auction_house > "D:\PlayOnline\SquareEnix\Char backup\auction_house.sql"
mysqldump.exe -e -uroot -proot dspdb accounts > "D:\PlayOnline\SquareEnix\Char backup\accounts.sql"
mysqldump.exe -e -uroot -proot dspdb accounts_banned > "D:\PlayOnline\SquareEnix\Char backup\accounts_banned.sql"
mysqldump.exe -e -uroot -proot dspdb char_effects > "D:\PlayOnline\SquareEnix\Char backup\char_effects.sql"
mysqldump.exe -e -uroot -proot dspdb char_equip > "D:\PlayOnline\SquareEnix\Char backup\char_equip.sql"
mysqldump.exe -e -uroot -proot dspdb char_exp > "D:\PlayOnline\SquareEnix\Char backup\char_exp.sql"
mysqldump.exe -e -uroot -proot dspdb char_inventory > "D:\PlayOnline\SquareEnix\Char backup\char_inventory.sql"
mysqldump.exe -e -uroot -proot dspdb char_jobs > "D:\PlayOnline\SquareEnix\Char backup\char_jobs.sql"
mysqldump.exe -e -uroot -proot dspdb char_look > "D:\PlayOnline\SquareEnix\Char backup\char_look.sql"
mysqldump.exe -e -uroot -proot dspdb char_pet > "D:\PlayOnline\SquareEnix\Char backup\char_pet.sql"
mysqldump.exe -e -uroot -proot dspdb char_pet_name > "D:\PlayOnline\SquareEnix\Char backup\char_pet_name.sql"
mysqldump.exe -e -uroot -proot dspdb char_points > "D:\PlayOnline\SquareEnix\Char backup\char_points.sql"
mysqldump.exe -e -uroot -proot dspdb char_profile > "D:\PlayOnline\SquareEnix\Char backup\char_profile.sql"
mysqldump.exe -e -uroot -proot dspdb char_skills > "D:\PlayOnline\SquareEnix\Char backup\char_skills.sql"
mysqldump.exe -e -uroot -proot dspdb char_stats > "D:\PlayOnline\SquareEnix\Char backup\char_stats.sql"
mysqldump.exe -e -uroot -proot dspdb char_storage > "D:\PlayOnline\SquareEnix\Char backup\char_storage.sql"
mysqldump.exe -e -uroot -proot dspdb char_vars > "D:\PlayOnline\SquareEnix\Char backup\char_vars.sql"
mysqldump.exe -e -uroot -proot dspdb char_weapon_skill_points > "D:\PlayOnline\SquareEnix\Char backup\char_weapon_skill_points.sql"
mysqldump.exe -e -uroot -proot dspdb chars > "D:\PlayOnline\SquareEnix\Char backup\chars.sql"
mysqldump.exe -e -uroot -proot dspdb conquest_system > "D:\PlayOnline\SquareEnix\Char backup\conquest_system.sql"
mysqldump.exe -e -uroot -proot dspdb delivery_box > "D:\PlayOnline\SquareEnix\Char backup\delivery_box.sql"
mysqldump.exe -e -uroot -proot dspdb linkshells > "D:\PlayOnline\SquareEnix\Char backup\linkshells.sql"

mysqldump.exe -e -uroot -proot dspdb > "D:\PlayOnline\SquareEnix\Char backup\dspdb.sql"

I changed the way I do this (restore characters). Now I just copy my backed up sql files to my server sql folder, and run the normal DSP_Import.bat script.
I do recommend using the wiki provided file though, as it works just fine.

Code: Select all

cd c:\darkstar\sql
del auction_house.sql
del chars.sql
del accounts.sql
del accounts_banned.sql
del char_effects.sql
del char_equip.sql
del char_exp.sql
del char_inventory.sql
del char_jobs.sql
del char_look.sql
del char_pet.sql
del char_pet_name.sql
del char_points.sql
del char_profile.sql
del char_skills.sql
del char_stats.sql
del char_storage.sql
del char_vars.sql
del char_weapon_skill_points.sql
del chars.sql
del conquest_system.sql
del delivery_box.sql
del linkshells.sql
FOR %%X IN (*.sql) DO ECHO Importing %%X & "c:\program files\mysql\mysql server 5.6\bin\mysql" dspdb -h localhost -u root -pMYSQLPASS < %%X

DSP_full.bat (I use this to automate the whole process. You can just run the bats 1 by 1):

Code: Select all

Call D:\PlayOnline\SquareEnix\Char_backup.bat
Call D:\PlayOnline\SquareEnix\Serverbackup.bat
Call D:\PlayOnline\SquareEnix\ServerReplace.bat
Call D:\PlayOnline\SquareEnix\Serverbuild.bat
Call D:\PlayOnline\SquareEnix\DSP_Import.bat
Call D:\PlayOnline\SquareEnix\Char_restore.bat

DSP_Import.bat (This can be found from the darkstar wiki, and is for importing the updated sql tables from the project): edit-As noted above, after initial load, it is probably better to use the second bat file for updating:

Code: Select all

@ECHO OFF
REM =============================================================================
REM =============================================================================
REM ======                                                              =========
REM ====== THis script will drop the DB specificed, then create the DB  =========
REM ====== specified, and then load all .sql tables from its run dir to =========
REM ====== the the DB.                                                  =========
REM ======                                                              =========
REM ====== File needs to be run from within the \dsp\sql folder (same   =========
REM ====== folder with all the .sql files. Please edit as needed. By    =========
REM ====== default it WILL DROP the standard dspdb DB, loosing all      =========
REM ====== accounts and characters. If this is not desired, then update =========
REM ====== the file to load the new DB into a new DB name.              =========
REM ======                                                              =========
REM ====== Update -p with MySQL password. If you password is 'foo',     =========
REM ====== then change '-pMYSQLPASS' to '-pfoo' (3 places).             =========
REM ======                                                              =========
REM ====== If you want to use a different database name, change 'dspdb' =========
REM ====== with a database name of your choosing.                       =========
REM ======                                                              =========
REM =============================================================================
REM =============================================================================
REM ======                                                              =========
REM ====== by Thrydwolf 9/8/2012                                        =========
REM ====== Updated with status by bluekirby0 3/30/2012                  =========
REM ====== Updated by Thrydwolf 9/18/2012                               =========
REM ======                                                              =========
REM =============================================================================
REM =============================================================================

ECHO Creating Database dspdb
mysqladmin -h localhost -u root -proot DROP dspdb

ECHO Creating Database dspdb
mysqladmin -h localhost -u root -proot CREATE dspdb

ECHO Loading dspdb tables into the database
cd D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar\sql
FOR %%X IN (*.sql) DO ECHO Importing %%X & "c:\program files\mysql\mysql server 5.6\bin\mysql" dspdb -h localhost -u root -proot < %%X

ECHO Finished!

Serverbackup.bat (I use this to backup my server, by zipping it up. This script requires the zip.vbs at the bottom):

Code: Select all

del D:\PlayOnline\SquareEnix\FFXIServerBackup.zip
D:
CD D:\PlayOnline\SquareEnix
CScript  zip.vbs  "D:\PlayOnline\SquareEnix\Final Fantasy XI server"  D:\PlayOnline\SquareEnix\FFXIServerBackup.zip

Serverbuild.bat (builds your lobby, search, and game servers):

Code: Select all

"C:\Program Files (x86)\MSBuild\12.0\Bin\MSBuild.exe" "D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar\win32\DSConnect-server\DSConnect-server.sln" /p:Configuration=Debug
"C:\Program Files (x86)\MSBuild\12.0\Bin\MSBuild.exe" "D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar\win32\DSGame-server\DSGame-server.sln" /p:Configuration=Debug
"C:\Program Files (x86)\MSBuild\12.0\Bin\MSBuild.exe" "D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar\win32\DSSearch-server\DSSearch-server.sln" /p:Configuration=Debug

ServerReplace.bat (completely empties the server directory, and replaces it completely from the clean folder where I pull from git into):

Code: Select all

rmdir /s /q "D:\PlayOnline\SquareEnix\Final Fantasy XI server\darkstar"
ROBOCOPY "D:\PlayOnline\SquareEnix\Server Update" "D:\PlayOnline\SquareEnix\Final Fantasy XI server" /s

zip.vbs (used to zip up the current server for backup):

Code: Select all

'Get command-line arguments.
Set objArgs = WScript.Arguments
InputFolder = objArgs(0)
ZipFile = objArgs(1)

'Create empty ZIP file.
CreateObject("Scripting.FileSystemObject").CreateTextFile(ZipFile, True).Write "PK" & Chr(5) & Chr(6) & String(18, vbNullChar)

Set objShell = CreateObject("Shell.Application")

Set source = objShell.NameSpace(InputFolder).Items

objShell.NameSpace(ZipFile).CopyHere(source)

'Required!
objShell.NameSpace(ZipFile).CopyHere(source)

' Wait for compression window to open
set scriptShell = CreateObject("Wscript.Shell")
Do While scriptShell.AppActivate("Compressing...") = FALSE   
   WScript.Sleep 500 ' Arbitrary polling delay
Loop  

' Wait for compression to complete before exiting script
Do While scriptShell.AppActivate("Compressing...") = TRUE   
   WScript.Sleep 500 ' Arbitrary polling delay
Loop

[Delaide]

Hmm, okay, I have a problem with my character restore. This is why I create backups. I will change the post once I get it fixed. For now, I will just warn you that you will need to reimport them.

[nasomi]

There's about a million automated server backup's out there already. I have mine scripted on 30min intervals. Just dumps char data. No need to do so for maintenance, etc.

[Delaide]

Yeah, but I haven't seen many out here. Basically, this was part of an entire solution I built. Then it would do everything except pull the most recent git update. Honestly, I think to use my code, someone would need to understand batch files anyways, since they would need to modify alot. It was more of a "See how I do this in case you want to do something yourself". At some point, I want to do this in my DSP-Control app. I just haven't had time to work on it, since my free time has been spent trying to enable some of the WotG stuff.

[Desufire]

Why don't you put this as an option in your Control app?

[Delaide]

I will be going forward, but I don't want to rely on cmd commands. I need to study up on it. lol, this is a learning tool for me. It was more difficult than you might believe just to get the "start servers" to launch the exe files when you are learning from square 1, and most learning walkthroughs consist of Make a button, click it, look, now you have a second form!

One reason I have posted my goals with the app on my DSP-Control topic, so everyone can see not only where I am, but where I am going.

I hope to eventually have a "Download this app, download these files (ffxi setup file), click this button to download the server or download the server update, auto build, auto sql, including backups and restores, etc."

Basically, I want a simple to use click a few things and go from zero to full server, or from outdated server to latest release. Maybe just a popup "Your server is out of date, would you like to update now" and it automates it all.

I will also have more optional software stuff, like the ah populator, or the dspAdmin. So, you download my controller, download FFXI, and make sure VS is installed, and everything else can be done easily. But this is a long term goal, not an immediate.

[Desufire]

I wish I was fluent in this stuff so I could help. An all-in-one tool would make people splooge lol. As it is right now, I use 3 programs found here just to run my AH lol.

[Delaide]

Yes, right now I am studying up on web scraping. I want to pull all the items from ffxidb to build an item list with item numbers. Then, I want to do a scrape from ffxiah. I have looked at the files created by some of these, and they just do a full data dump instead of selecting the proper data only.

I want to try to re-write it in pure PHP. I am even starting to play around with my DSP-Control in PHP. My idea is to make it a plugin for the XIWeb system. What can I say, I am studying a little of everything it seems. Eh, I learn better by doing than by just studying.