[HowTo] Set up a MySQL server for Procon

[ImportBot]

Originally Posted by PapaCharlie9*:

 

How to set up a MySQL server for Procon

 

These instructions are intended for admins who have control (root or admin access) of a host machine (e.g., local PC, rented server, Amazon EC2 instance, or Virtual Dedicated Server). If you get all of your administration from a hosting service (e.g., Branzone, Gameservers, NFO, etc.), you may be able to get MySQL services set up for you without needing these instructions.

 

These instructions are only about setup and configuration up to the point of being ready to create a table and use queries. The specifics of the table creation and queries are not included in this post, you will have to get them from Procon or plugin instructions. These instructions are general: they apply to setting up a server for Battlelog Cache as well as other purposes, such as community-based leaderboards, community-based logging, community-based cheat detection, etc. Anything that requires persisted data across multiple Procon sessions and/or instances.

 

NOTE: I created these instructions from notes I took as a first-time user of MySQL. There are probably better/faster ways to do some of these things. Please post suggestions for improvement.

 

Roadmap

---

There are three separate software program types to be aware of. There is your game server, your Procon instances (layers or local clients), and your MySQL server. Your game server always runs on a separate host machine, none of the following instructions apply to that software or host machine. These instructions apply only to the host machines that run your Procon instances and your MySQL server.

 

Where each software program gets installed may vary. It is possible to run Procon and MySQL server on the same host. That's what I do, I have Procon (directly connected to my game server) and MySQL running on my laptop. I don't use a layer. It is also possible to run MySQL server on one host machine, run a Procon layer instance connected to the game server on another host machine, and a Procon client connected to the Procon layer on yet a third machine (see the Procon Manual* for a full explanation with diagrams of remote setups using layers and clients). You may also use a MySQL service provided by a hosting company or by a free shared database host, in which case you do not need to install the MySQL server yourself.

 

Before starting these instructions, you should draw a roadmap of where each software program is installed. Draw a box for each separate individual hardware host that you use.

 

Label the box where the MySQL server will be installed as MySQL Host.

 

Label the box(es) where the Procon instance(s) connected to your game server is(are) installed as Procon remote (this is either a layer or a directly connected client).

 

Label the box(es) where the Procon client(S) connected to your Procon layer(s), if any, is(are) installed as Procon client.

 

Use this roadmap with the instructions below to match up where each item should be installed.

 

All of the instructions refer to Windows. Linux instructions will be different in the details, but the same in spirit and concepts.

 

Example: For me, I only have one box (my PC) and it has two labels, MySQL Host and Procon remote. Since I don't use a real layer, I don't use the Procon client label.

 

Download & Install MySQL Community Server

---

This is installed on your MySQL Host, if and only if you are not getting your MySQL services from some other provider. You are setting up your own server. It needs to have network connectivity with Procon, so if you are putting your MySQL server inside your firewall and have Procon outside your firewall, you will need to enable ports and addresses through the firewall to make this work.

 

Make sure you are logged in with Administrator privileges (whatever account access level can install and modify the system).

 

Determine the operating system your Procon instance or layer runs on. For example, I use Win64. Choose the appropriate individual "GA" (General Availability) download from this page:

 

http://dev.mysql.com/downloads/mysql/

 

Since I have Win64, I used : "Windows (x86, 64-bit), MSI Installer, 5.5.28, 32.6M"

 

Note: You may also use the "MySQL Installer for Windows" all-in-one, but it will install a lot of other stuff you don't necessarily need for Procon. It also might be limited to Win32 only (not sure). You may skip the individual install instructions below of the connectors and Workbench GUI if you use the all-in-one.

 

Note: The rest of these instructions are specific to the Win64 installer. Other OS installers will differ in details.

 

Follow the instructions of the installer

 

Accept the license agreement.

 

Choose a Typical setup and Install.

 

Click Yes for the User Account Control (if you have it enabled).

 

Click Next if you are asked about MySQL Enterprise subscription.

 

Click Next if you are asked about MySQL Enterprise Monitor Service.

 

On the installer window, make sure the "Launch the MySQL Instance Configuration Wizard" checkbox is checked (if you forget, there is a way to start the wizard independently, see below) and click Finish.

 

Follow the instructions of the configuration wizard

 

Click Yes for the User Account Control (if you have it enabled).

 

Click Next on the welcome panel.

 

On the "Please select a configuration type" panel, make sure Detailed Configuration is selected and click Next.

 

On the "Please select a server type" panel, make the appropriate selection. If you are doing plugin development on a local machine that you don't intend to run full time for a game server, select Developer Machine. Otherwise, select Server Machine. Click Next.

 

On the "Please select the database usage" panel, select Multifunctional Database and click Next.

 

On the "Please select the drive for the InnoDB datafile" panel, make the appropriate selection. For a Developer Machine, the default C: Installation Path is acceptable. For a server machine, you may want to put the file someplace where you can do backups easily. C: \MySQL Datafiles\ is one of the default choices for this case. Once you have made your selection, click Next.

 

On the "Please set the appropriate number of concurrent connections to the server" panel, make the appropriate selection. For a developer machine, the default Decision Support (DSS)/OLAP option is acceptable. For a server machine, you can make a Manual Setting based on the number of connections you expect to be made to the database. The default of 15 is usually sufficient.

 

On the "Please set the networking options" panel, make sure both checkboxes (Enable TCP/IP Networking, Enable StrictMode) are checked. The default port of 3306 is acceptable, but if you are already using that port on your layer/server, choose a different one. You can change it later if there is a conflict.

 

On the "Please select the default character set" panel, select Best Support For Multiligualism and click Next.

 

On the "Please set the Windows options" panel, check the box for Install As Windows Service and for Include Bin Directory in Windows PATH (both check boxes should be checked). Leave the Service Name "MySQL" and leave "Launch the MySQL Server automatically" checkbox checked.

 

On the "Please set the security options" panel, choose a password for root (admin super user). Choose a strong password, particularly for a server installation (see this article on MySQL Security Best Practices if you are running a server that is connected to the public Internet). Make the appropriate selection for Enable root access from remote machines (should be unchecked for a Development machine configuration). Do not Create An Anonymous Account (leave unchecked). Click Next.

 

On the "Ready to execute ..." panel, click Execute.

 

When it is done, click Finished.

 

To check to see that your installation worked correctly, find the "MySQL 5.x Command Line Client" in the Start menu and launch it. You should get a command window that prompts you to Enter password. Enter the password your created for root. If you installation is correct, you will get a "mysql>" command prompt. If there was a problem, you will not be able to logon or you will get an error message. Start over from the installation step or run the configuration wizard manually (see below).

 

As a final test, type this command into the mysql> prompt:

 

Code:

SELECT version();

You should see 1 row of output that shows the version of the MySQL server that you just installed.

 

Type "quit" to exit the command window.

 

Launching the configuration wizard manually

 

Do this only if you need to re-configure or correct the configuration of your installation.

 

Navigate to your MySQL server installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.x\. Navigate into the "bin" folder and launch "MySQLInstanceConfig.exe".

 

If you may also edit the my.ini config file in the top level installation directory directly, or use the MySQL Workbench to set configuration parameters manually. Restart the MySQL service if you change the configuration.

 

 

 

Download & Install The MySQL Workbench GUI

---

This is installed on your MySQL Host if you have GUI access to it. If you don't, or you don't have any Procon clients, you may install it on your Procon remote host, as long as you have direct GUI access to that host. This is a GUI tool you use to administer your database server and to experiment with queries if you are a plugin developer.

 

Make sure you are logged in with Administrator privileges (whatever account access level can install and modify the system).

 

Determine the operating system your Procon instance or layer runs on. For example, I use Win64. Choose the appropriate download from this page:

 

http://dev.mysql.com/downloads/workbench/

 

I have Win64, but the only option is Win32 : "Windows (x86, 32-bit), MSI Installer, 5.2.44, 26.3M"

 

Follow the instructions of the installer

 

Click next to the welcome panel.

 

Accept or change the installation folder. Click Next.

 

On the "Please select a setup type", accept Complete (default) and click Next.

 

Click Install.

 

Click Yes for the User Account Control (if you have it enabled).

 

Click Finish and allow the Workbench to launch. This will set you up for the next step.

 

Create a user account

---

If you haven't already, launch the MySQL Workbench GUI.

 

If you installed Workbench on MySQL Host: Under Server Administration, double-click the highlighted pre-loaded connection "Local MySQL".

 

If you installed Workbench on some other host: Under Server Administration, click on the New Server Instance at the bottom and fill in the connection details for your MySQL Remote Host.

 

Logon with your root password. If you save your password in the vault, you won't have to type it again, but it also means that anyone who launches this app can have root access. I did not save my password in the vault, which means I have to type it each time I connect.

 

On the left hand panel, click on Users and Privileges.

 

In the Server Access Management tab, click on the Add Account button at the bottom.

 

Fill out the Details for account ... form

 

For Login Name, replace "newuser" with the user name you want to use. It can be the name of a person, or, it can be the name of the Procon instance or plugin instance you want to use. For example, if you set up a server that is accessed both by Procon and by a PHP web site, you should set up separate user names for each: one for Procon and one for the PHP web site. An example of a Procon instance user name is "branzone23".

 

Since I was setting up a developer system, I used "papacharlie9".

 

Set Limit Connectivity to Hosts Matching. For a developer machine where you are installing everything on one host, use "localhost". For a server machine, use explicit IP or server DNS names for all of the host machines that may need access. Pay attention to which machines you give root access over the network to. Ideally this should be none, but for initial configuration you may have not choice if your MySQL server is remote.

 

Set a strong password and confirm it.

 

Note: The user name and password you choose will get entered into plugin settings or configuration files for Procon. Because of this, don't use a user name/password combination that is used with anything else that is important, like an email account or an online banking account. Other admins in your community or technicians working for your layer hosting provider will see this user name/password, so treat them accordingly.

 

Press Apply at the bottom.

 

Making sure your new account is selected, switch to the Administrative Roles tab.

 

For Role, select "DBDesigner". This will automatically check some of the options under Global Privileges.

 

In addition to those options, also check: DROP, INSERT, REFERENCES, SELECT and UPDATE.

 

Press Apply at the bottom of the tab.

 

Making sure your new account is selected, switch to the Account Limits tab.

 

For a developer system, leave all the settings 0 except for Concurrent Connections. Set this to the smallest number you think you will need. Each tool (command window, GUI, Procon, plugins) needs a connection, so make sure you give enough. I usually have a command window, the GUI and Procon connected, so I set the number to 4 to have one extra just in case. If you get a connection error later, increase the number. Press Apply.

 

For a server system, leave 0, which means unlimited.

 

Quit from the GUI Workbench.

 

Grant access to your database

 

Before you create a database, you need to be granted access to it.

 

Choose a name you want to use for the database. It might be as simple as "mydb" or something named after your community like "lgndb" (my clan tag is [LGN]). You will need this name later in the "Create a database" section as well.

 

Using the MySQL Command Line Client from the Start menu of your MySQL Host, logon as root.

 

The pattern of the command you are going to use is:

 

Code:

GRANT ALL ON [i]your_db_name[/i].* TO '[i]your_mysql_name[/i]'@'[i]your_client_host[/i]';

Where:

• your_db_name is the name that you are going to give to the database you will create
• your_mysql_name is the user name you defined
• your_client_host is the name of the host you defined (Procon Remote and/or Procon Client host)

The use of single-quotes is critically important. Make sure you put them in exactly as shown.

 

For the purpose of this example, we will assume the database is called "mydb", the user to be granted access is the user name you created above, e.g., "papacharlie9", and the host name is the one you defined above, e.g., "localhost". The command for this example would be:

 

Code:

GRANT ALL ON mydb.* TO 'papacharlie9'@'localhost';

Type quit to exit the command window.

 

Create a database

---

If you don't already have a database to use, you can create one.

 

The MySQL Tutorial (English) is an excellent resource for setting up and using a database. Sections 3.1 through 3.4 are highly recommended. Tutorials in other languages are also available.

 

On your Procon remote host, Procon client host, or on any host that you have direct GUI access to and that has network connectivity to your MySQL server, launch a normal command window -- not the MySQL Command Line Client, since that defaults to root and you don't want to use root for this!. You can do this by opening the Start menu, type "cmd" into the search field, click on cmd.exe. Alternatively, you can use your favorite shell. I use the MinGWin Bash shell that comes with Git.

 

Start mysql like this:

 

Code:

mysql -h [i]host [/i]-u [i]user [/i]-p

Where:

• host is the name of the host you defined
• user is the user name you defined

So continuing with the example of my own developer setup, I type:

 

Code:

mysql -h localhost -u papachalie9 -p

To test to make sure you are connected to the server, type:

 

Code:

SELECT user(),version();

You should get something that looks like this:

 

Code:

+------------------------+-----------+
| user()                 | version() |
+------------------------+-----------+
| papacharlie9@localhost | 5.5.28    |
+------------------------+-----------+
1 row in set (0.00 sec)

If you don't or you get an error, check that you are typing the command correctly. If you are, go back to the configuration steps above and check your configuration.

 

Now you will create the database you granted access to.

 

The pattern of the command you will use is:

 

Code:

CREATE DATABASE [i]your_db_name[/i];

Replace your_db_name with the name of the database you granted access to above. For my example, I type:

 

Code:

CREATE DATABASE mydb;

Note: Best practice is to use the same letter case everywhere. So if you start with 'mydb', don't use 'MyDb' or 'MYDB' in other places or configurations. Type it the same way every time.

 

To see a list of databases on the server, type:

 

Code:

SHOW DATABASES;

You are now ready to create tables and use the database. The plugin you are developing or using will have instructions for how to create or configure the table to be used.

 

Other resources and notes

---

 

NOT NEEDED ADO.NET Connector:

 

 

Download & Install The Ado.Net Connector

---

This is installed on your Procon remote and Procon client hosts that require access to MySQL, which may not be all of them. If you have five different hosts, one for your layer and four for your clients, you need to install this connector only on the hosts that need access, most likely just your layer host. If you are doing plugin development on one of your clients and it needs database access, you will also need to install it on that host.

 

Make sure you are logged in with Administrator privileges (whatever account access level can install and modify the system).

 

Determine the operating system your Procon instance or layer runs on. For example, I use Win64. Choose the appropriate connector download from this page:

 

http://dev.mysql.com/downloads/connector/net/

 

Even though I have Win64, the only option is: "Windows (x86, 32-bit), MSI Installer , 6.6.4, 12.7M"

 

Note: The rest of these instructions are specific to the Win32 installer. Other OS installers will differ in details.

 

Follow the instructions of the installer

 

Click Next to the welcome.

 

Choose a Typical setup and Install.

 

Click Yes for the User Account Control (if you have it enabled).

 

Click Finished.

 

 

 

 

 

Optional ODBC Connector:

 

 

Download & Install The ODBC Connector

---

Older plugins may require the ODBC connector or you may want it for other purposes besides Procon. Here are the optional installation instructions.

 

Make sure you are logged in with Administrator privileges (whatever account access level can install and modify the system).

 

Determine the operating system your Procon instance or layer runs on. For example, I use Win64. Choose the appropriate connector download from this page:

 

http://dev.mysql.com/downloads/connector/odbc/

 

Procon is 32-bit on Windows so it requires Win32 : "Windows (x86, 32-bit), MSI Installer, Connector-ODBC, 5.2.2, 2.7M"

 

Note: The rest of these instructions are specific to the Win64 installer. Other OS installers will differ in details.

 

Follow the instructions of the installer

 

Click Next to the welcome.

 

Accept the license agreement.

 

Choose a Typical setup and Install.

 

Click Yes for the User Account Control (if you have it enabled).

 

Click Finished.

 

 

 

 

[ImportBot]

Originally Posted by PapaCharlie9*:

 

Please to sticky?

[ImportBot]

Originally Posted by XpKiller*:

 

Download & Install The Ado.Net Connector

 

The ado.net connector dont needed to be installed on the layer because procon already delivers his own binarys and will only use those.

 

The default charset should always be UTF-8

[ImportBot]

Originally Posted by PapaCharlie9*:

 

Download & Install The Ado.Net Connector

 

The ado.net connector dont needed to be installed on the layer because procon already delivers his own binarys and will only use those.

 

The default charset should always be UTF-8

Okay, I'll move that part to the bottom and make clear it's not needed.

[ImportBot]

Originally Posted by Pallywhacker*:

 

Papa,

 

Thank you for the time you spent writing up these instructions. For someone like me who knows nothing about MySQL I was able to follow these instructions and get my MySQL server setup. I did not receive any errors so I guess I'm good to go. Thank you again.

[ImportBot]

Originally Posted by PapaCharlie9*:

 

Papa,

 

Thank you for the time you spent writing up these instructions. For someone like me who knows nothing about MySQL I was able to follow these instructions and get my MySQL server setup. I did not receive any errors so I guess I'm good to go. Thank you again.

Good to hear. It's pretty complicated, that's for sure.

[ImportBot]

Originally Posted by dyn*:

 

Doh! Total fail. I have the write up on my VDS.

 

 

Yes -- thank you for this!

[ImportBot]

Originally Posted by PapaCharlie9*:

 

Doh! Total fail. I have the write up on my VDS.

Er, people who don't know the context might think you are saying that my instructions post is a total fail. I'm just saying ...

[ImportBot]

Originally Posted by ronin*:

 

hey all

 

this rly should be pinned .

 

one quick question i followed everything and it all worked like a charm i set up a db ect and it was all working fine then i went back later to set up another one and now i cant use workbench properly i get errors when clicking on server admin and local mysql ive attached some pics . i have not done anything else and followed your guide to the letter .

 

wberror1.jpgwberror2.jpg

 

Any help would be awsome thanks .

[ImportBot]

Originally Posted by PapaCharlie9*:

 

hey all

 

this rly should be pinned .

 

one quick question i followed everything and it all worked like a charm i set up a db ect and it was all working fine then i went back later to set up another one and now i cant use workbench properly i get errors when clicking on server admin and local mysql ive attached some pics . i have not done anything else and followed your guide to the letter .

 

wberror1.jpgwberror2.jpg

 

Any help would be awsome thanks .

Since I don't have to ask anyone to Sticky anymore, I only have myself to blame for not doing it yet!

 

The first error I have no clue what that's about.

 

The second one I've seen when I've entered the IP/hostname or port for the server incorrectly in the Configuration Wizard.

 

You say you set up "another one". Explain in detail which machine has what for both instances, workbenches (one or two_), using the boxes roadmap convention I described in the Roadmap section. For example, you can't have two database instances in the same MySQL Host and refer to both as localhost ... well, not with the same port anyway.

[ImportBot]

Originally Posted by MorpheusX(AUT)*:

 

Is that guide fixed/ready to be moved to the "Procon Support Threads" section? We're trying to move away from stickies to a forum with help-threads and howtos.

[ImportBot]

Originally Posted by PapaCharlie9*:

 

Is that guide fixed/ready to be moved to the "Procon Support Threads" section? We're trying to move away from stickies to a forum with help-threads and howtos.

As ready as it will ever be. It really needs a more expert MySQL admin to review and comment on it, but so far, none have stepped forward. I'm sure there are some places where a note (like don't use localhost under such-and-such circumstances, or gotchas when using a public Internet facing MySQL versus a private subnet MySQL service, etc.) would be helpful.

[ImportBot]

Originally Posted by XpKiller*:

 

i see not general fault i the setup. Except that 15 conection maybe to low.

An other point would be the innodb configuration. The default config in 5.5 delivers a low performance on bigger databases.