Results 1 to 1 of 1

Thread: sql doc start

  1. #1
    Super Moderator
    Join Date
    Jul 2010
    Location
    Very North Darwin
    Posts
    1,534

    sql doc start

    size=7]Introduction[/size]

    The purpose of this Howto is to get you set up with a fresh MySQL database, ready to be used by any PRoCon plugins
    that require such. This database can be further used for any number of purposes.

    There are typically two ways to accomplish this:

    1) Install MySQL server on a PC that is always on and connected to the internet with a static IP.
    2) Sign up for a MySQL hosting service.* FreeSQL.org offers free MySQL service, which I will use for example later.
    *If you pay for a website, you might already have MySQL server access. Check with your hosting provider!

    I will cover both methods in this Howto.

    What is a MySQL server? Database?

    A MySQL database is typically a file on a computer running MySQL server software. This file, or database, is meant to store information. Plugins typically store a small amount of information, but sometimes they will need to store large amounts of data. Data such as players' names, GUID's, and some information associated with them (like their killing statistics). This is where MySQL comes in - its purpose is to fill this need.

    How you decide to use and access MySQL is up to you. If you have a PC that is always on and connected to the internet, and is acting as your PRoCon server, then the simplest and best method is likely to install the free MySQL server software and just have PRoCon connect to the same computer it's running on. In this case, the PRoCon plugin would be configured to connect to MySQL on the computer it's already running on.

    Again, I strongly recommend this method if you are mainly running PRoCon on your own PC. The free MySQL server
    software won't require much system resources to fulfill PRoCon plugin needs. I run two PRoCon MySQL-connected plugins on 3 different servers, and right now MySQL is using under 1MB of RAM and under 1% CPU.

    If your PRoCon is being hosted remotely, and you don't have a PC that is always on/connected to dedicate, you'll need to set up a MySQL database to connect to remotely. There are tons of MySQL server and database hosting options available to you. Just google 'mysql hosting'.

    There are free MySQL hosting services too, but they typically won't allow remote connections (and the PRoCon plugin would, of course, be a remote connection). FreeSQL.org offers free MySQL databases and allows remote connections, so if you don't have a dedicated PC, and don't want to pay for MySQL hosting, http://www.FreeSQL.org is your friend.

    CAUTION: free hosting services typically come at the cost of data security! Your database might be wiped out one
    day for no reason, and that's perfectly ok! If this is unacceptable to you, you need to either host your own MySQL
    server (easy!), or pay for professional MySQL hosting.

    To summarize:
    If you're running PRoCon on a dedicated PC, you should just install the MySQL server software on that PC. It's easy.
    If your PRoCon is being hosted remotely, you'll probably want to set up some kind of external MySQL hosting.

    I will explain both methods in detail below, starting with the former. To set up MySQL remotely, skip ahead.

    Creating Your Own MySQL Server on a Dedicated PC

    To create your own MySQL server, you will need to download the files below or use the web installer.

    You only need either the 32-bit version, OR the 64-bit version of the following items!
    If you're unsure as to which you need, use the 32-bit version.

    Pick your version and Method here


    MySQL Connector/ODBC for 32-bit Windows:
    MySQL Connector/ODBC for 64-bit Windows:

    http://www.mysql.com/products/connector <---- Pick your connector

    The MySQL Workbench is great! It's optional for this, though.
    MySQL Workbench for 32 and 64-bit Windows: Sql Workbench link


    Installing the MySQL Server Software

    Run the MySQL Web installer.

    Attachment 4280

    Select the sql server of your choice, the workbench (Handy) and the connector (ODBC)

    Click next and install then finish.

    MySQL Instance Config will appear. Click next until you reach the menu shown below:



    Remember where you choose to store the database! Click the '...' to choose a directory.
    If you want to just leave the default settings, that's fine. Just remember where it's stored
    incase you need to retrieve it someday.

    Click next until you get to the menu below:



    If you are installing this MySQL server software on a PC that ISN'T THE SAME PC as the PC running
    PRoCon, then you need to check the box for 'Add firewall exception for this port'.

    Otherwise, just click next until you get to this menu:



    Enter a root password that you will remember. Do NOT enable root access from remote machines!

    Click next and finish.

    Alright, your MySQL server is now up and running with one user: 'root'! I'll bet you can't even tell.
    Now we need to connect to the server and perform some tasks.

    MySQL Server Configuration

    Open the MySQL Command Line Client. There should be a link to it in your Start Menu under
    All Programs/MySQL/MySQL Server 5.1/'MySQL Command Line Client'.

    Running the MySQL command line client opens this window:



    Enter the root password that you haven't forgotten from earlier and hit enter. Now you're here:



    Type the following text, with the ';' at the end, then hit enter:

    Code:
    create database procon;
    Unless you made a typo, it will output: Query OK, 1 row affected (0.00 sec)

    Now type in the following and hit enter:

    Code:
    use procon;
    Database changed.

    Enter the following, replacing 'phogue' with whatever password you want the user 'procon' to use:
    (Type it carefully, and exactly as shown!)

    Code:
    grant select,insert,update,delete,create on *.* to 'procon'@'localhost' identified by 'phogue';
    Output: Query OK, 0 rows affected (0.00 sec)

    If you got an error, double-check your typing and type all of the commands over again.
    If everything went well, you should see something very similar to this:



    At this point, you've installed MySQL Server, have connected to it, created
    a database, and created a user with access to that database. As far as setting
    up your MySQL server on your PC is concerned, you're done! But wait...

    Some PRoCon plugins that require MySQL access might need you to create some
    tables and stuff in your database. XpKiller's BFBC2 and MoH Stats loggers and my
    addon Currency will both automatically create the needed tables, so if you're
    reading this so that you can get one of those working, you're done!
    Skip to the MySQL Connector/ODBC section.

    Otherwise, the plugin author might have included a Something.sql file with the
    PluginName.cs file in the plugin's .zip archive that you downloaded (or will be
    downloading). This PluginName.sql file is just a bunch of MySQL commands.

    To run a .sql file so that you don't have to type all the commands inside
    it by hand, type the following at the mysql> prompt:

    If you closed the MySQL Command Line Client already, just open it back up, enter the password, and enter 'use procon;'
    Code:
    source C:\Users\YourName\Desktop\PluginName.sql
    Notice there is no semicolon - don't use one here. You'll want to replace the
    'C:\Users\...\PluginName.sql' with the path to the file on your PC. If it's
    running Windows 7, and your .sql file is on your desktop, it will look like
    what I typed above. This will run the queries (commands) in the .sql file on
    the database that you currently have selected.

    Again, to select a database to run a .sql script on, enter this first:

    Code:
    use databasename;
    If you've made it this far, and your PRoCon plugins' .sql file(s) ran
    successfully, you probably don't have to touch MySQL Command Line Client
    again! Woo! MySQL Server will do its own little thing in the background,
    using a relatively tiny amount of resources (for typical PRoCon plugin usage).

    Now you need to give the plugin a way to connect to the database.

    Skip to the MySQL Connector/ODBC section!


    Creating and using a free MySQL database via FreeSQL.org

    Mosey on over to http://www.freesql.org. On the left hand side, under 'MySQL', click 'Create'.

    You should see the following:



    Populate the fields using some common sense, WRITE THE INFO DOWN, and check your e-mail. If some black text
    appears that says 'grant error' or something to that effect, try a different username and database name. If
    everything goes well, black text should appear at the top of the page saying 'your database has been created
    and is now available'.

    Now you have a MySQL database set up! Woohoo, if you're setting up a database for XpKiller's BFBC2/MoH stats
    logger addons, or my Currency addon, you're done! Configure the plugin with your chosen Hostname\Username\Password\Database fields from FreeSQL.org.

    If you aren't using one of the above listed plugins, the plugin you're dealing with might require that you run a few queries, or commands, on the database before it's used. These commands should have been included in the plugin's archive, and will be in a file with a .sql extension. Like PluginName.sql. To run a .sql script on your database, you
    will need to connect to it.

    Download the following and install it with all default options.

    MySQL Workbench for 32 and 64-bit Windows: http://www.mysql.com/downloads/mirror.php?id=395400

    Open the MySQL Workbench program from your Start Menu/All Programs/MySQL/MySQL Workbench.

    Click 'New Connection' at the bottom left. You should now see this:



    Enter the text in the fields accordingly. For Hostname, use http://www.freesql.org as shown. When you're done
    and the Test Connection is successful, click Ok. The connection you just created will now show up in the
    box under 'Open Connection to Server'. Now is the time to open the .sql file that you need to run. You
    can just open it in notepad. Select the entire contents of the opened .sql file, then do a Copy.

    In MySQL Workbench, double-click the connection you just made. The screen will switch to what you see
    below. It's important for sanity's sake to immediately click in the top text-entry box (bordered in
    red below) and do a Paste. Then immediately click the leftmost lightning bolt:



    If it timed out, just wait a while, close the connection's tab (at the top there's a light blue X),
    then try again. I think you have about 10-15 seconds to paste the code in the box, then click the bolt.
    When you've successfully done this, you're finished with the server-side of MySQL!

    Now the computer that is running your PRoCon needs to be configured to connect to the database. If the
    computer is yours, then continue reading below under 'MySQL Connector/ODBC Installation and Configuration'.
    Otherwise, you'll need to contact your PRoCon host about connecting a plugin to your MySQL database. It
    isn't a very difficult problem for them, and they'll likely have it fixed up pretty quick. In that case,
    thank you for reading this Howto! As far as configuring your PRoCon plugin is concerned, enter the
    information into the Hostname\Username\Password\Database fields according to what you chose when you
    created your FreeSQL.org account. Have fun!



    MySQL Connector/ODBC Installation and Configuration

    Run the MySQL Connector file that you downloaded via the link above. It's the one that starts with
    'mysql-connector-odbc'. Install it with default options - just click next, next, install, finish.

    Next, if you're on Windows 7, click Start and type in 'data sources'. Data Sources (ODBC) will be at the top.
    Click it. If you're on Windows XP/Vista, click Start, then Run, and type in 'odbccp32.cpl'.

    Either way, a window that looks like this appears:



    Click the 'System DSN' tab and then click 'Add...'. A window will appear as shown below.



    Click on 'MySQL ODBC 5.1 Driver', then click finish. A new window will appear.

    If you set up your own MySQL server via the instructions earlier,
    fill the fields according to the example below.



    Otherwise, if you're connecting to a remote MySQL server, you'll populate the
    fields according to that server's info. If you make a free database at
    http://www.FreeSQL.org, your info will resemble the example below.



    Depending on what free MySQL hosting service you use, your database name might
    be the same as your username. Make sure to click 'Test' to test the connection.
    If it reports that everything is good, then you're done messing with MySQL
    completely! As far as configuring your PRoCon plugin is concerned, enter the
    same information into the Hostname\Username\Password\Database fields in PRoCon
    Plugin Settings as you did in the MySQL Connector/ODBC.

    I hope this Howto helps someone, and good luck with your plugins and future MySQL endeavors!
    Game nick Volo_Mortis
    https://forum.myrcon.com/showthread....-Procon-Manual
    Read the manual ?

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •