MySQL How to setup MySQL and OTservers

LoLaye · Apr 30, 2010

Hello there, I found this tutorial in other forums so i decided to post it here Enjoy it

PLEASE READ THE FAQ BEFORE POSTING ANY QUESTIONS. IT MIGHT HELP YOU....
THANKS.........

Index
I. Downloading and Installing Sources.
II. How to setup XAMMP and MySQL Database.
III. Configuring and running the server.
IV. Explanation of Database Tables.
V. Port forwarding, URL Redirection & Online Hosting.
VI. Simple F.A.Q
VII. Securing phpMyAdmin

Sources and files required
I. Download XAMPP from SourceForge.net
II. An SQL Server. http://otland.net/f18/8-54-forgotten-server-0-3-6pl1-crying-damson-59924/. You can use your desired MySQL Server not TFS if you want)[/I][/COLOR]

LETS BEGIN OUR TUTORIAL

I. Downloading and Installing Sources.

1. Download Xampp.
2. Run the installer.
3. Select the desired language.
4. Press "Next".
5. Choose the "Installation Directory" (Preferable to be away from Windows Local Disk)
6. Check on "Select Apache as service" & "Select MySQL as service". <See Figure 1.0>

7. Press "Next" and wait till the installation of files is finished.
8. After that 2 windows of commands ( Like DOS windows) will appear. First <See Figure 1.1> and secondly a one checking ports.

<Figure 1.1>
NOTE : WAIT TILL "Have fun with ApacheFriends XAMPP!" appears

9. Press "Ok" then a message will appear, confirm by clicking "Yes".

10. You must have Apache Service and MySQL Service running on your computer. <See Figure 1.2.>

Apache Service : is your database. If it is not running you can't access the database to modify it.
MySQL Service : is your MySQL server. If it is not running you will not be able to run your server giving you "Unable to load bans" error.

II. How to setup XAMPP and MySQL Database.
I Won't be explaining SQLite as it is not efficient and a lot of errors are caused during saving of server and after it...

11. Now, you need to setup XAMPP first, so, Go to your internet browser and enter this address 127.0.0.1 ( This is called Localhost).
12. Choose your desired Language.
13. Choose "Security" from the left list < See Figure 1.3>

14. You will get a page showing you the security of your XAMPP, PHP and POP3 servers.
15. Search for this link. (It is immediately under the security check table) and click on it.<See Figure 1.4>

16. Ok, You will get this page <See Figure 1.5> . Enter the required password and renter it again. Remember it well. It is your key to access your database and to run your server.

< Figure 1.5>

17. Press "Password Changing".
18. If a message appeard by your Internet Browser, Press "Yes"
19. Confirmation of success of password changing. <See Figure 1.6>

20. Alright Now, We are finished from the setup of XAMPP.
21. Lets begin in the setup of the Database for our server.
22. Enter in your Address bar in the Internet Browser
http://127.0.0.1/phpmyadmin
23. You 'll get the phpMyAdmin Login page. <See Figure 1.7>

24. Enter UserName as root and your password that I have told how to make it in Step 16
25. You will get the lovely phpMyAdmin homepage. Don't get terrified.
26. Enter your database name here <See Figure 1.8>. We 'll call it TFS. Then press "Create"

NOTE : DON'T use special characters or numbers. They won't be accepted. Spaces will be ignored like they are not even entered.

27. You 'll find your database with no tables right now.
28. Extract your server
29. Press on that button (marked with red square)
<See Figure 1.9> at the upper left of the screen to begin importing your database file

30. Choose "Import Files" Tab <See Figure 2.0>

31. Browse to your database file which is called forgottenserver.sql OR schema.mysql as we are using TFS.
31. Press "Go". You have successfully imported your database.
You 'll find at the left list the accounts, players, items, skills, ...etc.

III. Configuring and running the server.

32. Time to configure your server itself, Go to your server directory. Go to your server folder, and open config.lua in a notepad or whaterver a text editing program

33. Now check your MySQL properties

Lua:

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

Lua:

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

34. Now your server is ready to be launched. Try to run the server now.

IV. Explanation of Database Tables.

Although this section is huge, It is self explanatory.
(You can Skip this section for now if you want)

35. I will be going to explain each table and its contents on its own. To access any table press on it and then press "Browse" from the above tabs <See Figure 2.1>

<Figure 2.1>

------- :Means that I don't know what the field is for.
If you know, please post what you know.

a. 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.

b. 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

c. 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)

d. 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

e. 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)

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

g. guild_ranks
id: Rank ID
guild_id: Guild ID
name: Rank Name
level: Rank Level

h. 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

i. house_list
house_id: ID of the house
listid: ------
list: -----

j. 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.

k. 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: -----

l. 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: ------

m. 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: ------

n. 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.

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

p. 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

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

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: ------

Next part coming!

LoLaye · Apr 30, 2010

HOW TO MAKE A GOD CHARACTER ?
Some servers requires only to change the group_id of the player. However in TFS, You need to change both group_id of the player in the players table and group_id of the account in the accounts table [/COLOR][/COLOR][/FONT][/COLOR][/COLOR][/FONT][/SIZE][/COLOR]in addition to the account type .

a. Go to your phpMyAdmin Page " Step 23 "
b. Choose your Database from the drop down list.
c. Click on the `Players` table
d. Choose "Browse" from the above tabs.
e. Check the box beside the required player.
f. Click on " Edit " ( The Pencil Shape Icon ) .
g. Seek the group_id value and change it to 3 -
h. Return back to the tables list.
i. Click on the `Accounts` table
j. Choose "Browse" from the above tabs .
k. Check the box beside the required account which is associated with the player.
l. Click on " Edit " ( The Pencil Shape Icon ) .
m. Seek the group_id value and change it to 3 -
n. Seek the account typevalue and change it to 5

Check your commands.xml to check the account types. ( e.g : GM, GOD, Player, Tutor, etc.... )

V. Port Forwarding, URL Redirection & Online Hosting.

36. Now, Time to make your Redirection URL. Go to No-IP - Dynamic DNS, Static DNS for Your Dynamic IP. If you have an account, it is fine. If not, create one.

37. After finishing, Log in. Click on "Add Host" <See Figure 2.2>

37. Well, Write your desired hostname. I called it sqltutorial.no-ip.org

NOTE : IF you are willing to make a custom client. Limit your total characters in the hostname to 16 or 17.

38. Choose Host Type : DNS Host (A)
38. Write your IP Address. If you don't know it. Visit What Is My IP Address - Shows Your IP Address

39. Click on "Create Host"

40. We are done with creating the hostname. Download the DYNAMIC DNS UPDATE CLIENT to keep your hostname updated with the last changed IP address.

41. Now, This is step is confusing. Everyone has a different router. Also each router has different setup. We need to make a port forward to allow ports 7171, 7172 and 8000 to be opened. You can check this tutorial How to port forward the easy way to know everything about it.

42. Follow the tutorial accurately.

43. After Finishing this step. Now go and open your

Lua:

 Connection Config
    ip = "127.0.0.1"
    port = 7171

44. Change 127.0.0.1 to our URL sqltutorial.no-ip.org.

NOTE : IF you got an error message when trying to login to the server and you didn't login. Change the URL to your actual IP. Sorry for not remembering that message

45. If the above happened. Simply get your IP from What Is My IP Address - Shows Your IP Address. and write it instead.
46. Now, Open your IP Changer.
47. Tell your friends to write in your hostname to login to your server. If that error message occurred to them. Do what i have told you above.
48. ENJOY !!

VI. Simple F.A.Q

1) I get the client error " Temple Position is wrong, Please contact the administrator " ?!

Cause: This is caused after the player death, because It will need to re spawn again , but it can't find the town ID that it should re spawn in.

Solution: 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.

2) I can log in using IP " 127.0.0.1 " configured in Config.lua, But I can't using my Global IP address.

Cause: This caused because your Global IP address is not port forwarded correctly.

Solution: *Disable your Firewall, or Allow the server to pass it
*Check PortForward.com - Port Forwarding Guides Listed by Manufacturer and Model for more details

3) One or both of Apache and MySQL Services in XAMPP are not running

Cause: This is because the necessary ports for one or both of the services are being used by other application running on your system or the service is not yet installed

Solution: *Check if necessary ports are open and free. Go to your Xampp folder and run " xampp-portcheck.exe " to check them.

4) One or both of Apache and MySQL Services in XAMPP are saying to be busy

Cause: This is mostly caused by your RAMs because they are not enough to run the service and other background processes in your system.

Solution: *Go to your xampp folder , Run
" xampp_stop.exe " then " xampp_start.exe ". If the problem persists, Re-insall Xampp

5) Xampp tells me That Port 80 or Port 3306 or both are being already in use. What should I do ?!

Cause: This is because the necessary ports for one or both of the services are being used by other application running on your system. Mostly happening in Windows Vista.

Solution: We have to check is the port is really being used or not. So we will preform a search for the port. Open Start Menu then Click on Run then type cmd . A Black like DOS window will appear. Type this command netstat -an | find "80". A list of all ports being used containing number 80 in their port will be displayed. If you find ( Just one of them not all)
0.0.0.0:80 : This means an internal application is using it and this application is no need to access the Internet
127.0.0.1:80 : This means that this application is operating on your local host and may access the internet anytime if needed
192.168.1.XY:80 : This means that this application actually using the inertent and sedning data throught this port. beside it .. You will find the IP and port which is sending the data to.

N.B : XY is your Address in the Group LAN connected to the router. Like if you have an Xbox 360/PS3 , a laptop connected to the router, a Cellphone and your main Desktop Computer. Each device will have a diffrent XY from the other. Sometimes the X is the same in all. But Y is surely not repeated again.

After we checked that the port is being used. We need to know the application using it. So Open your Task Manager ( Ctrl + Alt + Delete). Then, Click on the View Tab. Then Select Columns then Check the Box PID ( Process Identifier). Now, Return back to the CMD and type netstat -ano. Now serach for the 0.0.0.0:80 or127.0.0.1:80 or192.168.1.XY:80. You will find its PID beside it. . Now go to your Task Manage. List the PID in an Ascending Order. And See what is that program using port 80.

There is more easier command, But It takes some processing time. Instead of netstat -ano type netstat -bano. But this command tells you the name of the process (eg. blabla.exe)

N.B : That can be used for any port you want. But instead of 80, The port you want.

6) Question 5 doesn't help. How can I change ports for Apache and MySQL ?

Cause: This is because the necessary ports for one or both of the services are being used by other application running on your system. Mostly happening in Windows Vista. You don't know how to make things work, you are confused and you lost hope.

Solution: That could be the only way to make this works on Vista. Apache.exe uses port 80 and MySQL uses port 3306. You will change them to ports which are not used. Any ports, No specific Ports.
a. To change Apache ports, do the following :
Go to your xampp folder > apache > conf > and open the file httpd.conf with notepad or any text editing program. Search for

Code:

#Listen 12.34.56.78:80
Listen 80

and

Code:

ServerName localhost:80

Change 80 to any port you want. Save the file and your are done with Apache.

b. To change MySQL ports, do the following :
Go to your xampp folder > mysql > bin. Open your text editing program. Drag a file called "my". it has an icon like SpeedDial.
Drag the File into the text Editing Program. Search for the following

Code:

# The MySQL server
[mysqld]
port= 3306
socket= "C:/xampp/mysql/mysql.sock"
basedir="C:/xampp/mysql" 
tmpdir="C:/xampp/tmp" 
datadir="C:/xampp/mysql/data"
skip-locking

Change 3306 to any other port you like. it should be open. Don't change any other ports in the file. All 3306 ports in other parts of the file should not be changed. Don't forget to save.

We are not finished yet. Open your config.lua.

Seek this part

Lua:

   mysqlPort = 3306

Change 3306 to the port you have chosen for MySQL

Don't Forget to make Firewall bypass these ports.

7) How to Disable UAC ( User Account Control ) in Windows Vista ?

Cause: Not caused. It causes..

Solution:
*Method 1

1.Launch MSCONFIG by from the Run menu.
2. Click on the Tools tab. Scroll down till you find "Disable UAC" . Click on that line.
3. Press the Launch button.
4. A CMD window will open. When the command is done, you can close the window.
5. Close MSCONFIG. You need to reboot the computer for changes to apply.

*Method 2

1. Open Control Panel.
2. Under User Account and Family settings click on the "Add or remove user account".
3. Click on one of the user accounts.
4. Under the user account click on the "Go to the main User Account page" link.
5. Under "Make changes to your user account" click on the "Change security settings" link.
6. In the "Turn on User Account Control (UAC) to make your computer more secure" click to unselect the "Use User Account Control (UAC) to help protect your computer". Click on the Ok button.
7. You will be prompted to reboot your computer. Do so when ready.

VII. Securing phpMyAdmin

Follow this link !

I will continue the F.A.Q sometime later. More to come later,,,,

Some Problems are caused because you don't follow the tutorial accurately ESPECIALLY while IMPORTING the DATABASES

Thank you for reading my tutorial. If there is any missing parts or non cleared ones. Post it here.

If this tutorial helped you, Please Give me Reputation/Rate the thread and DON'T FORGET... COMMENTS

Sincerly,

Lolaye

sorcerers king · Apr 30, 2010

If you added main titles then some one use ctrl+f to find what he need would be better!

But this is great! good for new comers =]

Evan · May 3, 2010

It's nice, but the credits go to OT Flames @ OTFans.

Atleta · May 3, 2010

its for newbies

rep ++

Bogart · May 3, 2010

nice

LoLaye · May 16, 2010

Hevar · May 16, 2010

Great ^^

Black Rober · May 16, 2010

tnx bro its very nice

D

LoLaye · May 16, 2010

zoroo · May 17, 2010

zoroo · May 17, 2010

im egyption

LoLaye · May 22, 2010

Bump dis thread!

LoLaye · May 24, 2010

Bump it again

LoLaye · May 30, 2010

Bump comment please

GarneT · May 31, 2010

Nice work !!

Make new post "How to make site for your ot" .

Thanks , sir.

spice · May 31, 2010

Credits to the actually author and not this copy paster.

This was made by OT Flames at OtFans.net

[MySQL+Server] ULTIMATE TUTORIAL FOR HOSTING & SET UP OF MySQL SERVERS

GarneT · May 31, 2010

Hehehe , nice copy

LoLaye · May 31, 2010

read before posting, i said this wasnt my!

GarneT · May 31, 2010

Sorry , but thanks any way !

MySQL How to setup MySQL and OTservers

Active Member

Active Member

spo

A splendid one to behold

Banned User

...has super panda powers

Active Member

Click clack bam bam!

New Member

Active Member

New Member

New Member

Active Member

Active Member

Active Member

Silence

CABS ARE HERE

Silence

Active Member

Silence

Similar threads