[MySQL] Ultimate Tutorial How To Create An Ot [WITH PICTURES]

[Zysen]

ULTIMATE TUTORIAL FOR SETTING & HOSTING AN OT SERVER





Heyaaaaaaaaaaaa!!
I know there isn't even one massive detailed tutorial how to create an open tibia server,so I will make one for you <.<
PS: Don't be mad because I didn't used The Uniform Server/Modern AAC;because my comp is bad so I can't show you that ​

Things we will do:

1.Downloading and installing programs we need
2.How to setup XAMPP and MySQL database
3.Making and running the server
4.Explaining database tables (accounts,players,etc..)
5.Portforwarding,URL Redirect & Online Hosting
6.Securing phpMyAdmin

Programs for download:

1.XAMPP 1.7.3
2.TFS 0.3.6pl1

-TUTORIAL-


1.Downloading and installing programs we need



-Download XAMPP 1.7.3 (link up)
-Run the installation file

-Press "Next" - wait till the installation finishes
-Now a windows like DOS will appear - just choose your answers [y- yes;n- no]
-Then open your shortcut of XAMPP 1.7.3
-You must have Apache Service and MySQL Service running

Apache Service: YOUR DATABASE
MySQL Service: YOUR MYSQL SERVER


2.How to setup XAMPP and MySQL database



-Go to your internet and type url 127.0.0.1[This is called LOCALHOST]
-Choose your language

-Choose SECURITY from the list

-You will get a page showing you the security of your XAMPP

-Enter your desired password and press "password changing"
-If a message appeard by your Internet Browser, Press "Yes"
-Confirmation of success of password changing

-We finished the setup of XAMPP
-Lets begin the setup of database for our server
-Enter http://127.0.0.1/phpmyadmin in your Adress Bar of your Internet Browser
-You will get phpMyAdmin login page

-Enter username "root" and enter your password that you've been made in security page
-You will get phpMyAdmin homepage.It's lovely isn't it?
-Enter your database name here

-We will call it TFS [don't use special characters and names,because they wont be accepted]
-You will found a database with no tables
-Extract your server
-Press on this button

-Choose "Import files"

-Browse to your database file which is called forgottenserver.sql OR schema.mysql as we are using TFS
-Press "Go" - You will get many tables - acoount,players,items,skills,etc...



3.Making and running the server


-Time to configure your server,go to your OtServ Director,and find a file called config.lua - open it with notepad,wordpad [any text editing program]
-Check your MySQL properties

-- MySQL
    mysqlHost = "localhost" 
    mysqlUser = "root"
    mysqlPass = "XXXXXX" -- your password  
    mysqlDatabase = "TFS" -- your database name
    mysqlPort = 3306

-- SQL
    sqlType = "mysql" -- change sqlite to mysql
    passwordType = "plain"

-Now try to launch [turn on] your server - find your OtServ.exe and run it



4.Explaining database tables (accounts,players,etc..)



ACCOUNTS TABLE

id: It is account number of the account
password: It is the password of the account
type: The Type of the account.
premdays: Number of premium days remaining
lastday: The last day the player had logged in to the server
key: It is the recovery key. Generated if you used a website supporting such feature
email: It is the player e-mail. Generated if you used a website supporting such feature.
blocked: 0 if the account is not banned. 1 if the account is banned
warnings: It is like the frags. If the account has 8 frags as adjusted by the server. The account is banned
groupid: It is the group_id of the account.

BANS_TABLE

type: -----
ip: IP of the banned account
mask: -----
player: The banned character ID.
account: The banned player account number.
time: Time of banishment
reason_id: ----- I do know a reason, but dunno what is the ID ?
action_id: -----
comment: Comment written by the banner when banishing the character
banned_by: The ID of the player who banned the other player


GLOBAL_STORAGE

key: As I know, keys are genrated like 1 then 2 then 3 for each vaue
value: Is the value which is written by setGlobalStorageValue(valueid, newvalue) & is called back by
getGlobalStorageValue(valueid)


GROUP

id: ID of the group. It is called group_id in the accounts and players table
name: Name of the ID (eg: player, gamemaster, etc...)
flags: These values make the player available to do certain actions or not. Get them at this link. <<Thanks to SimOne>>
access: Access value from 1 to 6. It differs from server to the other. Check commands.xml to understand it more
maxdepotitems: Maximum numbers of items in the depot of the player
maxviplist: Maximum number of players in this player VIP list

GUILDS

id: ID of the guild
name: Name of the guild
ownerid: The ID of the leader character of the guild
creationdate: The date when the guild is created
motd: Message of the day for the guild. It is like a motto for the guild (Not Sure)

GUILD_INVITES

player_id: The ID of the invited character
guild_id: The ID of the guild which the player_id is invited too.

GUILD_RANKS

id: Rank ID
guild_id: Guild ID
name: Rank Name
level: Rank Level

HOUSES

id: House ID. It s retrieved from [servername]-house.xml
owner: ID of the owner character
paid: I don't know if it is the value of money or this is a boolean value (0 or 1)
warnings: Number of warnings for the player to pay the house rent

HOUSE_LIST

house_id: ID of the house
listid: ------
list: -----

PLAYERS

id: ID of the player
name: Name of the character
group_id: Group ID of the character
account_id: ID of the account in accounts table
level: Character level
vocation: ID of the character vocation
health: Character health NOW
healthmax: Maximum value of character health
experience: Experience points of the character
lookbody: Body look ID
lookfeet: Feet Look ID
lookhead: Head Look ID
looklegs: Legs Look ID
looktype: 0 for females. 1 for males
lookaddon: Addon ID. 0 for nothing, 1 for first addon only. 2 for second addon only. 3 for both addons.
maglevel: Character Magic Level
mana: Character mana NOW
manamax: Characetr maximum mana
manaspent: Mana spent from date of character creation
soul:Soul points of the character
town_id: ID of the character residence town
posx: Character X position
posy Character Y position
poz: Character Z position
conditions: Condition of the character (eg. Poisoned, Electrified, etc..)
cap: Remaining Cap of the character
sex:0 for females. 1 for males (Not Sure)
lastlogin: Last login Date
lastip: Last IP the character has logged in from.
save: 0 if saving stats is disabled for this character. 1 if enabled
redskull: 0 if no has red skull. 1 if has red skull
redskulltime: Time of receiving the last red skull
rank_id: ------
guildnick: Nickname of the character in the guild
lastlogout: Last logout Date
blessings: 1 to 5 according to the received bleesings (Not Sure)
direction, lost_experience, loss_mana, loss_skills, premend : This values are not used by the server, They are located in vocations.xml excpet for direction and premend.
online: 0 if the player is offline. 1 if the player is online.

PLAYER_DEATHS

player_id: ID of the dead player
time: The time when the player is killed
level: Dunno of the level before death or after it ??
killed_by: Monster/Player ID or name (Not sure)
is_player: -----

PLAYER_DEPOTITEMS

player_id: ID of the character
depot_id: Depot ID
sid: Item ID
pid: -----
itemtype: Type of the item ???
count: How many of this item. You see numbers here if the item is stackable
attributes: ------

PLAYER_ITEMS

player_id: ID of the character
sid: Item ID
pid: -----
itemtype: Type of the item ???
count: How many of this item. You see numbers here if the item is stackable
attributes: ------

PLAYER_SKILLS

player_id: ID of the character
skillid: 0 Fist, 1 Club, 2 Sword, 3 Axe, 4 Distance, 5 Shielding, 6 Fishing.
value: The skill points (eg: 90)
count: How many times the character has tried to reach the next level. If the player has a sword skill 50 without death the count will be 50. If the player is dead 1 time and reached sword skill 49 and then reached sword skill 50 again. Then the count will be 51.

PLAYER_SPELLS

player_id:ID of the character
name: Name of the learned spell.

PLAYER-STORAGE

player_id:ID of the character
key: As I know, keys are genrated like 1 then 2 then 3 for each vaue
value: Is the value which is written by
setPlayerStorageValue(uid, valueid, newvalue)

q. player_viplist
player_id:ID of the character
vip_id:ID of the character in the VIP list

TILES
id: ID for the action of this tile
x:Tile X position
y: Tile Y position
y: Tile Z position

TILES

tile_id: ID of the tile
sid: Item ID that the tile will give you.
pid: -----
itemtype: Type of the item ???
count: How many of this item. You see numbers here if the item is stackable
attributes: ------




5.Portforwarding,URL Redirect & Online Hosting


-Now make your redirect URL - No-IP - Dynamic DNS, Static DNS for Your Dynamic IP - You need to have an accoun there [if you have it's fine,if you don't have create one]
-After creating an account,login on your account - Click on "ADD HOST"

-Write your desired hostname [i will name it mysqltutorial.no-ip.org]
-Choose Host Type : DNS Host (A)
-Write your IP Adress [if you don't know your ip adress visit What Is My IP Address - Shows Your IP Address
-Click "Create Host"
-We are done with creating the hostname - Download the DYNAMIC DNS UPDATE CLIENT to keep your hostname updated with the last changed IP address
-Now we got to portforward - little tutorial:

#1 - go to: PortForward.com - Port Forwarding Guides Listed by Manufacturer and Model
#2 - select your router.
#3 - select the game.
#4 - follow the instructions

-After finishing this go to your config.lua and find this:

Connection Config
    ip = "127.0.0.1"
    port = 7171

Change

ip = "127.0.0.1"

to

ip = "mysqltutorial.no-ip.org"

[or whatever hostname you created]

-Now open your IP Changer
-Tell your friends the hostname and tell them to login
-ENJOY!


Problem Solution "Temple Position is wrong":

-Go to your database > Select it > Choose the `players` table > Click on the " Browse " Tab > Edit the player using the Pencil icon after checking the box beside it > Seek the field " town_id "and change it . To check the Town IDs, open the map with a map editor
-Set up your position of town [x,y,z in mapeditor] in your config.lua - and set up your town_id


Credits: Zysen
OTFlames (OTFans)



Hope I explained all!




Yours,
Zysen​

 

[Znote]

I was searching the web, looking for alternatives to XAMPP, because using XAMPP for this Open Tibia community is a really bad habit!

XAMPP is for testing purposes mainly for your local machine

PS: Don't be mad because I didn't used The Uniform Server/Modern AAC;because my comp is bad so I can't show you that

XAMPP requires a better computer to run than Uniform Server. That is no excuse.

Anyway nice contribution.

 

[Lessaire]

The 'ultimate' thread would start out with caveats. Like usage of Windows OS is a definite exclusion.

I mean describing the hardware purchases involved for something like a triple server strategy with a Samba server running two RAID6: one with SSDs, and another with 2TB drives on a 2nd gen Areca SAS HBA + 4GB cache upgrade + BBU platform. A Percona/MariaDB MySQL server with a 4-socket motherboard all populated with quad-cores and all DIMM slots populated. Then a nice single socket with a Intel Q9950/X3360 cooled with a vapor-phase cascade (-60 celcius at full load) overclocked to 4.5GHz for the TFS installation. All directly connected two each other with teamed pairs of Gigabit links. Now that might scratch the surface of creating the Ultimate OT tutorial.

So really, ultimate is an inappropriate adjective for your real target goal.

If you really want to do something useful, perhaps a tutorial on compiling MySQL 5.5 from source and using the binaries to replace the ones included with XAMPP/UniServ. I'd Rep++ for that.

 

[Alyhide]

Good Tutorial, now just figure out how to run it with Modern AAC / Uniform Server.

 

[Zysen]

Bump!

 

[Zysen]

Bumpiii

 

[toshiie]

everthingworks with the examp .. but i don;t have any exe file in my server so howw can i get online:S i got stuck here...
with the xml server i have a .exe but not with sql. so please help and mail me to [email protected] thnxx

 

[Polat Alemdar]

when i will run server it say to me lua5.1.dll can not found on your computer please re install program what can i do with this i need help!

 

[Polat Alemdar]

When i runed server it worked but i cant login with ip changer and tibia client? why?

 

[Himii]

ERROR couuldnt estabilish conect to sqldatabase
something like that coming up on my sceen

 

[Homeslice]

ok i ran my server, but when i try to login acc manager loads but when i login him it "Cannot Connect to game server, Error Connection Timed Out. (10060)

 

[Meyfei]

I'm having issues with entering 127.0.0.1 in my browser, it's just not connecting/showing anything. Just says page does not exist and such.

YES i know it connects to ME. >.>

Anyone got suggestions?

 

[sp45]

Good Job ! Thank you !

 

[Karar92]

hey thnx for tut it helped but 1 thing how i change map? i want anther map on my server can u help me plz?

 

[Zysen]

@up

Just go get a map,go to data/world,delete the map,put ur map,go to config.lua,find the part when it writes : mapName = blabla,and change that to ur mapname.otbm

 

[Transeti]

Great post! thanks

 

[An2oneriksson]

Got a problem man, "Failed to connect to database. MYSQL ERROR: Access Denied for user @Localhost to database "db.s3db" What is wrong then? ;o

 

[kummen]

Good tutorial!, just that i have 1 problem. i can log in to the ot so i can see the character list but when i am going to pick character it just says "cannot connect to the game server. Error: Disconnected from server. (200002). please help me

- - - Updated - - -

ye i got it too :/

- - - Updated - - -

Well it was a good tutorial, but i got a problem more. i can connect to the server so i can she the character list but when i pick character is says "cannot connect to the server"

 

[kevin568]

When i open my server i just come in the real tibia server

 

[GhostX]

When i open my server i just come in the real tibia server

You need an ip changer to your ip