SETTING UP YOUR DATABASE ON WINDOWS 64 bits FOR DUMMIES
++ VMangos ++
MySQL5.5 is the most recommended version to be used with VMangos, but, before installing it... According to Oracle doc, Microsoft Visual C++ 2008 Redistributable Package must be installed.
Download Microsoft Visual C++ 2008 SP1 Redistributable Package (x64) from Official Microsoft Download Center
Now it is safe to start downloading MySQL5.5, and the next link will lead you directly to the 64 bits version:
MySQL :: Begin Your Download
Launch the installer and proceed with the given steps.
After the installation is finished, we want MySQL server to run in the background, as a service. For this we will create 2 batch files to start and stop it easily:
1. Create file StartMySql.bat
Code:
@echo off
echo Installing MySQL as a Service...
mysqld --install MySQL --defaults-file="D:\MySQL5.5\my.ini"
echo After installing the service, we start MySQL 5.5
net start MySQL
pause
2. Create file StopMySql.bat
Code:
@echo off
echo Stopping MySQL 5.5
net stop MySQL
echo bye bye MySQL...
pause
In order to let Windows find where are your mysql executables (like "mysqld" used in the batch files), add its path to the path environment variable:
1. Win+Pause
2. Click on Advanced System SETTING
3. Environment Variables
4. Under System variables, click on Path and then click on Edit button.
Add the path to the bin folder into your MySQL installation folder. In my case: "D:\MySQL5.5\bin"
Ok, now, you can easily start and stop MySQL by executing (with administrator privileges) your StartMySQL.bat and StopMySQL.bat files.
After a fresh installation of MySQL, the root user has no password assigned, so let's set one:
1. Open windows CMD (with administrator privileges) and type:
mysqladmin.exe -u root password "new_password" (replace new_password with your own).
*In case root would alreay have a password set the command will vary a bit:
mysqladmin.exe --user=root --password=oldpassword password "new_password"
Now that MySQL is running, let's make use of HeidiSQL: HeidiSQL - MariaDB, MySQL, MSSQL and PostgreSQL made easy So let's install it and launch it fast. Remember the root password is the one you set in the step above.
Click Open and... there we go, our brand new mysql set of DBs:
Let's see now what are the required databases for VMangos:
logs: This is where log data is stored. To create it use the logs.sql file.
realmd: This is the login server database. Account data is stored here. To create it use the logon.sql file.
characters: This is where all data about characters is stored. To create it use the characters.sql file.
mangos: This is the world database. It contains all game content like items, creatures, texts, etc.
To create them, In HeidiSQL, right click on the DB connection and click on Create new -> Database.
Once the 4 databases are created, we can start populating through the scripts into the sql folder available in the VMangos github: GitHub - vmangos/core: Progressive Vanilla Core aimed at all versions from 1.2 to 1.12
So start loading them 1 by 1 and querying them into HeidiSQL, be sure you select the proper Database you want the sql file to affect:
Start by loading the characters.sql:
1. Click on the characters DB, in the Heidi application, databases list. (you will see a green icon appearing)
2. Click on the folder icon in the top bar and select the file: characters.sql
3. Once loaded you can click on the blue-play button in the top bar or just press F9 to execute the query.
4. You may get a window popup informing you about a number of warnings that you can safely ignore.
5. Do the same for the rest of DBs.
To create and populate mangos, the world database. You must get the latest world db release from the database repository: GitHub - brotalnia/database. Pick the most recent one!
*Note: If you are unsure on what database corresponds an sql file, take a look at it and it should reference what DB it is affecting (logon.sql will affect realmd.account):
So let's apply the huge world database query to our mangos db. However, in this step, it is very possible that your mysql service crashes.
To fix this, you need to increase the maximal packet size limit in my.ini, at your MySQL install folder. So set
Code:
max_allowed_packet = 128M
, then restart your MySQL server (Execute the batch files we created at start) and try again. It should work perfect.
MIGRATIONS
After all your DBs are filled, now, let's run migrations so they are updated to the vmangos latest fresh version. To do it, you would normally go executing, one by one, all the sql files into the migration folder. However, there is a batch file that comes very handy to merge all of them, so lets execute merge.bat:
And now, with the mangos db selected in the Heide application, load an run world_db_updates.sql.
Same for logs db. Select it and run logs_db_updates.sql.
After this, you have all your DBs set and ready to run!
Finally, an optional step is to backup your brand new DBs that we can accomplish through Heidisql as well:
1. Right click in any of the DBs in the list and select Export as SQL.
2. In the popup window, select your 4 vmangos DBs, and in the right side options check the option Create for both Database and Tables.
3. Select Insert for the Data field
4. At the filename, select the destination for the sql file to be created. (For easyness, export as 1 single sql file)
5. Click Export and done!
So this is it.
I found a hard time to setup properly my DBs so I decided to do this tutorial which I hope may help someone else.
Thanks for reading!