Hello everyone, first of all I would like to say this tutorial come from tibiafans and I decided to post it here because this tutorial helped me to start with MySql and I see alot of people asking for help.
This tutorial been made by: Black Swordsman
So here we go
---------------------------------------------------------------------------------------------------------------------------------------------------
This will (hopefully) be an all-around tutorial on setting up and using SQL servers, especially ones running on MySQL.
Part 1 - finding a good server to use
Of course, the most important thing to do is to figure out which SQL server to use.
If you don't have a quality source editor available, I suggest that you use The Forgotten Server [TFS]. Currently, the two newest versions of TFS can be found in the OT Files section here at Tibiafans.
Otherwise, I suggest using a new version of the official Open-Tibia server. This promises to be up-to-date with features and bugfixes, plus it lacks some stupid codes that other servers (known as "distros") do have. It's likely to be the least laggy and most stable that it can be. You can download the newest SVN (from the last day) through Khaos' nightly build system, which you can find in a stickied thread in Unverified OT Files.
Of course, you really should decide for yourselves. There are quite a few distros out there besides TFS, if you're willing to look. Some may suit your needs better than TFS or clean SVN.
Part 2 - Getting a MySQL server
ow, we need to get a mysql server. You could just get a mysql server without being a part of a package at the official mysql page (here), or you can download it as part of a package. I suggest downloading it as part of a package, and the particular package I suggest is xampp. Xampp can be downloaded from xampp's sourceforge page (Windows/Linux).
When installing xampp, install apache, filezilla, and mysql as a service.
(xampp users)Part 3 - Using phpmyadmin to upload your database
1) Go to: http://localhost/phpmyadmin in your web browser.
2) Find where it says "Create new database".(Image)
3) Enter the name of your database and press "Create".
4) You'll be taken to a new page where it says "database [name] was created". At the top of the page, there are a bunch of buttons. Find the one that says "import" and click it.(Image)
5) You'll be taken to another new page that gives you the option to import from a text file.
If your server is from like half a year ago, that text file would be the database.sql (forgottenserver.sql) in your OTServ's root folder (containing the otserv executable and your data folder). Otherwise it will be "schema.mysql".
Find that file using the "Browse..." button and then click the "Go" button.(Image)
6) The next page will indicate you of any or of success. Now that you've gotten the database up, you can use your SQL AAC to create new characters. Of course, you may want to tweak your OTServ database first.
Part 4 - Editting your OTServ's database
After creating your database, it can be selected from the drop-down list on the left side (it's got a different background from the main page). Do so, and you'll be brought to a new page.
On the left side, you can select which *table you wish to edit. Let's start with 'groups' (not in evo~ or older servers), as both accounts and players rely on that now. What we want to do is *insert a new *row into this *table. You can do this by clicking the "insert" button (up near where the "import" button was for the last part).
4.1 - Groups
For starters, you need to understand what a Group is. By the name, you can gather that it's some sort of 'collective entity' (or group , in simple english). In the case of OTServ, players and accounts belong to a specific group in order to have special powers. Groups are basically the new way of doing GMs and GODs, instead of just access=3 or access=5.
Groups have 6 *fields.
Now that you've learned all that, go ahead and make your groups. I suggest 0 access and 0 flags for normal players!
Just fill the fields and click the "Go" button.
4.2 Accounts
Now that we have our groups configured, let's make ourselves a GOD account since we're hosting the server and everything. Let's start by clicking 'accounts' on the left side, and clicking the 'insert' button; just like we did for 'groups'. Now, let's see what fields that account has:
-- continued in next post --
This tutorial been made by: Black Swordsman
So here we go
---------------------------------------------------------------------------------------------------------------------------------------------------
This will (hopefully) be an all-around tutorial on setting up and using SQL servers, especially ones running on MySQL.
Part 1 - finding a good server to use
Of course, the most important thing to do is to figure out which SQL server to use.
If you don't have a quality source editor available, I suggest that you use The Forgotten Server [TFS]. Currently, the two newest versions of TFS can be found in the OT Files section here at Tibiafans.
Otherwise, I suggest using a new version of the official Open-Tibia server. This promises to be up-to-date with features and bugfixes, plus it lacks some stupid codes that other servers (known as "distros") do have. It's likely to be the least laggy and most stable that it can be. You can download the newest SVN (from the last day) through Khaos' nightly build system, which you can find in a stickied thread in Unverified OT Files.
Of course, you really should decide for yourselves. There are quite a few distros out there besides TFS, if you're willing to look. Some may suit your needs better than TFS or clean SVN.
Part 2 - Getting a MySQL server
ow, we need to get a mysql server. You could just get a mysql server without being a part of a package at the official mysql page (here), or you can download it as part of a package. I suggest downloading it as part of a package, and the particular package I suggest is xampp. Xampp can be downloaded from xampp's sourceforge page (Windows/Linux).
When installing xampp, install apache, filezilla, and mysql as a service.
(xampp users)Part 3 - Using phpmyadmin to upload your database
1) Go to: http://localhost/phpmyadmin in your web browser.
2) Find where it says "Create new database".(Image)
3) Enter the name of your database and press "Create".
4) You'll be taken to a new page where it says "database [name] was created". At the top of the page, there are a bunch of buttons. Find the one that says "import" and click it.(Image)
5) You'll be taken to another new page that gives you the option to import from a text file.
If your server is from like half a year ago, that text file would be the database.sql (forgottenserver.sql) in your OTServ's root folder (containing the otserv executable and your data folder). Otherwise it will be "schema.mysql".
Find that file using the "Browse..." button and then click the "Go" button.(Image)
6) The next page will indicate you of any or of success. Now that you've gotten the database up, you can use your SQL AAC to create new characters. Of course, you may want to tweak your OTServ database first.
Part 4 - Editting your OTServ's database
After creating your database, it can be selected from the drop-down list on the left side (it's got a different background from the main page). Do so, and you'll be brought to a new page.
On the left side, you can select which *table you wish to edit. Let's start with 'groups' (not in evo~ or older servers), as both accounts and players rely on that now. What we want to do is *insert a new *row into this *table. You can do this by clicking the "insert" button (up near where the "import" button was for the last part).
4.1 - Groups
For starters, you need to understand what a Group is. By the name, you can gather that it's some sort of 'collective entity' (or group , in simple english). In the case of OTServ, players and accounts belong to a specific group in order to have special powers. Groups are basically the new way of doing GMs and GODs, instead of just access=3 or access=5.
Groups have 6 *fields.
- The first, "id", is pretty self-explanatory: it's a number used to identify the group from other groups. Each "id" needs to be unique, you cannot have two groups with the same "id".
- The second, "name", isn't really used. It's mostly to tell them apart, I think.
- The third, "flags", is probably the most important part. Flags control almost everything. There are flags for players to not be attacked, not be able to attack, not be able to attack players, not be able to be banned, etc. These are completely seperate from access levels, so you can safely protect a group of players from having to deal with monsters without needing to give them a high enough access to use commands; or possibly to be able to have a character who can use commands but aside for that is just a regular player (say, a secondary character for your GOD). The means of finding out what to set this number to is pretty complicated, so blackknight from OTF made a tool to do it for you: http://hem.bredband.net/johannesrosen/playerflags.html
- The fourth, "access", is the same as it's always been. You need a certain access to use certain commands.
- The fifth, "maxdepotitems", is self-explanatory. This number specifies how many items a player can have in his depot. It's nice for GMs to be able to hold more items, etc.
- The sixth, "maxviplist", is also self-explanatory. This number specifies how many players a player can have on his VIP. It's nice for GMs to have alot of players on his VIP.
Now that you've learned all that, go ahead and make your groups. I suggest 0 access and 0 flags for normal players!
Just fill the fields and click the "Go" button.
4.2 Accounts
Now that we have our groups configured, let's make ourselves a GOD account since we're hosting the server and everything. Let's start by clicking 'accounts' on the left side, and clicking the 'insert' button; just like we did for 'groups'. Now, let's see what fields that account has:
- id - this is the account's account number. don't ask me why they chose to call it 'id' instead.
- password - speaks for itself. this is the account's password
- group_id - this is the id of the group we want to set it to. This is why we needed to create our groups before our accounts. Setting this should only affect the # of people on your VIP, though it may affect other things.
- email - speaks for itself. This is the email to be associated with the account. It's not used in OTServ source, but you can use it on your AAC to add email verification and prevent creation of multiple accounts.
- blocked - set to 1 to block an account, set to 0 to unblock it. No idea if it actually does anything yet.
-- continued in next post --
Last edited: