Add a new MySQL service for ODAS

Add a MySQL service

As of November 2014 we install MySQL assuming that there will always be the possibility of adding multiple services of the mysqld deamon later because ODAS will also use a separate service of MySQL. We install just one version of MySql and create more than one instance (service) of the mysqld deamon, each service will have it's own datadir and port number.

This page assumes the original installation of  MySQL was completed earlier. It did not support multiple services.

If this machine has never supported multiple services of MySQL then you'll probably have to convert the installed "C:\Program files\MySQL\MySQL SERVER 5.5\my.ini" file to support multiple services now.  Here's how.

You do not have to stop any of the other old MySQL services when adding a new one. But make sure the new one you are adding now is stopped..

• If you didn't just convert the my.ini file to support multiple services for the first time above then go to the bottom of the "C:\Program files\MySQL\MySQL SERVER 5.5\my.ini" file and create a new service by copying/pasting the last live service section and changing the new service name, data path to match your customer's info. Use the next available port # for the new service.
• If you DID just convert my.ini file to support multiple services for the first time above then you don't have to copy/paste anything below because you created the first xxx service section when you converted the my.ini file. The first default port number should be 3306.

#------------------------------------------------------------------
# options for mysql55_exc service (use forward slashes in path)
#------------------------------------------------------------------
[mysql55_exc]
datadir = C:/u/mvs/exc/mysql/data
port = 3306

#------------------------------------------------------------------
# options for mysql55_odas service (use forward slashes in path)
#------------------------------------------------------------------
[mysql55_odas]
datadir = C:/MVSWEB/mysql/data
port = 3307
 

Copy the initial datadir

You have to copy the original installation data folder to the new path in the MVSWEB/mysql folder.  If the MVSWEB/mysql/data folder exists now, delete it so you can copy in the new one. Enter this command in a Dos prompt window.  (You might have to use a different Drive letter or path, depending on where ODAS is installed).  You can also do this using Windows Explorer (copy/paste).  Do not delete the original C:\Program files\MySQL\MySQL SERVER 5.5\Data folder! You will need it later when creating new mysql services.  

mkdir C:\MVSWEB\mysql
mkdir C:\MVSWEB\mysql\data
copy "C:\Program files\MySQL\MySQL SERVER 5.5\Data" C:\MVSWEB\mysql

within the mysql\data folder you should create an empty folder named odas for the ODAS database name.
 

If you are installing MySQL for the first time then the original MySQL installation created the mysql55_odas service, so you don't have to do this next step.  But if you are adding a new service to an existing installation you need to create an additional service mysql55_odas as a Windows service manually. Your login should probably have administrative privileges.  Execute this in a command line shell (You MUST specify the --defaults-file parameter even though it is the default defaults file). You should be able to just copy/paste the command below directly to a DOS prompt. But if your paths are different than the ones below you should paste it into notepad first, edit the pathnames and then copy/paste it to a DOS prompt.

"C:\Program files\MySQL\MySQL SERVER 5.5\bin\mysqld" --install mysql55_odas --defaults-file="C:\Program files\MySQL\MySQL SERVER 5.5\my.ini"

If you ever have to un-install this service use the dos command:  sc delete mysql55_odas

You should go into the Services application to start the mysql55_odas service and make it automatically launch at startup.
 

Grant privileges and set passwords
 

Update the Profits Plus win32 folder with the version 5.5 mysql client by copying
"C:\Program files\MySQL\MySQL SERVER 5.5\bin\mysql.exe" to "C:\u\mvs\new\win32\bin"
Logon to the MySql client as root in a dos prompt window by entering this command:

mysql.exe -uroot -pmysql1 -Pxxxx  (where -Pxxxx is the port # you assigned to this new instance of MySQL)

If the client prompts for a password use the password mysql1.  It will give you a MySQL command line so you can copy/paste the following commands directly into the mysql client prompt all at once.

Create a new  DSN for MySQL so ODAS can access the ODAS database

This DSN does NOT access the Profits Plus database tables.  It is meant only for the Maxene reporter database tables. 

The my.ini datadir could be the same or different for Profits Plus and Maxene Reporter database tables. 

Instructions below that are unique for this DSN  are highlighted.

As of 3/18/2015 Vivek says the ODAS client can't use the 64-bit ODBC connectors so you'll have to install 32-bit versions.  See Google Drive \My drive\Applications and tools\mysql for these choices:

• mysql-connector-odbc-5.3.4-win32.msi 
• mysql-connector-odbc-3.51.30-win32.msi (if the 5.3.4 version won't install)

These instructions were taken from the following web page.

http://fdo.osgeo.org/files/fdo/docs/FET_TheEssentialFDO/files/WS1a9193826455f5ffd30557110ffe778f8-7c7c.htm

Do the following:

1. If you are installing a 32-bit connector on 64-bit windows then you can't use the Data Source Administrator you get from the start menu because it won't show any of the 32-bit connectors you installed to choose from.  Instead you need to use the 32-bit [ODBC Data Source Administrator] which is in C:\Windows\SysWOW64\odbcad32.exe.  Just double click on the file to start the Administrator. Windows 2012 and later have integrated the Data Sources app so you don't have to use odbcad32.exe separately,  you choose 32-bit or 64-bit from the Data Source app.
2. But if you are installing a 64-bit connector on 64-bit Windows then you can use the Windows Start menu, click Settings, click Control Panel, click Administrative Tools, click Data Sources (ODBC). The ODBC Data Source Administrator dialog box is displayed.
3. In the ODBC Data Source Administrator dialog box, click the User DSN tab if you want the data source to be visible only to you on this machine, or click the System DSN tab if you want the data source to be visible to others on this machine and on the network. Click Add. The Create New Data Source dialog box is displayed.
   NoteYou may not use the File DSN tab, because Fdo does not support its use.
4. In the Create New Data Source dialog box, click MySQL ODBC 3.51 Driver, click Finish. The Connector/ODBC 3.51.12 dialog box is displayed.
5. In this dialog box, in the text entry box  labeled Data Source Name , type the name that you want to use as the value of the DataSourceName connection property.   For ODAS we use "ODAS_DB".  
6. In this dialog in the text entry box labeled Server, type the hostname/IP address of the machine where the MySQL database server is installed. The default is localhost.
7. Enter user: odas_db and password odas_db1. Pick an ODAS database using the drop-down combo box labeled Database and click the TEST button.   You should get a "Connection Successful" message in a popup window.
8. Click OK to close the Configuration dialog.
9. In the ODBC Data Source Administrator dialog box, you see the DSN that you just added listed. Click OK.

If MVSWEB\ODAS\odas.dsn.connection.string.txt doesn't exist you should to create one in notepad.exe for the ODAS client with the first line as follows: 

Anything after the 1st line will be treated as comments.

Save this file as odas.dsn.connection.string.txt and put it in the MVSWEB\ODAS installation folder.