HOW TO: Configure MySQL to work with HELM

Hey everyone,

The most common problem encountered when configuring MySQL with Helm is that you think all has gone well and then the database creation fails. 99% of the time this is because the root user is set-up to allow local access only “root@localhost” whereas it should allow global connections, therefore be set-up as”root@%”

What follows is a step by step guide to getting MySQL installed and the relevant settings in place to work with Helm.

First of all download and install MySQL for windows from here. Once downloaded you will need to unzip the file and launch set-up. Typical install is fine for what we need. Next you need to install the MyODBC drivers. HELM supports 2.5 and 3.51 version of the MyODBC drivers. However for MySQL 4.1 you must use 3.51 drivers. In addition there have been some rogue 3.51 drivers in circulation.

When both of the above have been installed we now need to configure MySQL.

First of all we need an options file. This is a file that gives MySQL information about how it is to run. The option file allows great control over MySQL however you need the following as a bear minimum to get MySQL working.

Launch NotePad enter the following:

[mysqld]
# set basedir to your installation path
basedir=C:/mysql
# set datadir to the location of your data directory
datadir=C:/mysql/data

Note the slashes are opposite to normal and this is intentional. The basedir is the directory you installed MySQL and the datadir is where you want the actual databases to be stored, this could be on a different drive or location if you wish.

Save this file as my.ini in the C:\WINDOWS directory. That is all we need to do for now.

Now on the server you need to launch your command line utility. This is normally done via Start > Run then type cmd and hit enter.

I will assume that MySQL has been installed using the default path. If not you will have to amend the commands as appropriate.

At the command prompt type

cd C:\mysql\bin

This will change the current directory to the MySQL directory (assuming you have installed MySQL in the default location)

First we need to ensure that MySQL is not running in the desktop session which can sometimes happen. At the command prompt type

C:\mysql\bin\mysqladmin -u root shutdown

If you receive an error about being unable to connect then this is fine as it means MySQL was not running.

Now we need to install MySQL as a Windows Service so it can run while logged out of the server. To do this we need to type the following at the command prompt.

C:\mysql\bin\mysqld –install mysql –defaults-file=C:\windows\my.ini

This creates a Windows Service which when running will use the details provided in the my.ini file we created. More details on installing MySQL as a service can be found here.

Next we need to start the MySQL service. At the command prompt type

net start mysql

MySQL is now running on the server. However we need to make a number of changes to the users that are setup by default, as in its present state both anonymous users and root users have no passwords allowing anyone to gain access.

First we need to connect to the MySQL database, this is done again via the command prompt. At the command prompt type

mysql -u root

*Add -p at the end if you have already specified a root user password press enter and then type your password.

You will now be connected to the MySQL server. Lets start by adding a new root user which allows connections from any host (required for Helm to function). At the command prompt type the following replacing password with the password you want to set for the root account.

GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘password’ WITH GRANT OPTION;

To learn more about adding users in MySQL via the command prompt go here

Now we need to make our server a little safer by removing all the anonymous accounts and root users with no password. Still at the command prompt type the following.

NOTE: The first line is using two single quotes next to each other not a double quote. Press enter at the end of each line.

DELETE FROM mysql.user WHERE User = ”;
DELETE FROM mysql.user WHERE User=’root’ AND Host’%’;
FLUSH PRIVILEGES;

Next simply Type Exit and you will be logged out. MySQL is now configured with just one user root@% and the password you have set.

Now we need to tell HELM about MySQL. Log in to HELM and go to System Settings > Servers > Servername then click Add New

You will now be at the Add Service screen. Here you can select the Service Type and provide a Friendly Name for the service.

Next we need to provide the MySQL connection information in the screen below

For the Admin Username and Admin Password you must supply the root password as set earlier in this article. The Server IP Address is the IP address that MySQL is to run on. The Server Port should be left as default unless you have changed the MySQL port settings. For the ODBC Driver Version select 3.51 (unless you are running an old version of MySQL with the 2.5 drivers). The Maximum Databases is used when you want to balance MySQL across multiple servers. This allows you to tell HELM if you only want this server to host a maximum number of databases.

Clicking Next will create the actual service.

Best Regards,
Shane Bewick