Add a new MySQL service for Maxene Reporter

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 Maxene Reporter 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_mr service (use forward slashes in path)
#------------------------------------------------------------------
[mysql55_mr]
datadir = C:/maxene_reporter/MaxeneReporter/mysql/data
port = 3307
 

Copy the initial datadir

You have to copy the original installation data folder to the new path in the Maxene reporter mr_homefolder/mysql folder.  If the mr_homefolder/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 and MVS_ROOT path).  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:\maxene_reporter\MaxeneReporter\mysql
mkdir C:\maxene_reporter\MaxeneReporter\mysql\data
copy "C:\Program files\MySQL\MySQL SERVER 5.5\Data" C:\maxene_reporter\MaxeneReporter\mysql

within the mysql\data folder you should create an empty folder named mr for the Maxene Reporter database name.
 

If you are installing MySQL for the first time then the original MySQL installation created the mysql55_mr 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_mr 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_mr --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_mr

You should go into the Services application to start the mysql55_mr 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 Maxene Reporter can access the Maxene Reporter 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 Maxene Reporter 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 Maxene Reporter we use "MaxeneReporter_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: mrdb and password mrdb1. Pick a maxene reporter 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.

You'll need to create a Mysql connection string in notepad.exe for the Maxene Reporter client with the first two lines as follows: 

Anything after the 2nd line will be treated as comments.

Save this file as mr.dsn.connection.string.txt and put it in the maxene_reporter\MaxeneReporter installation folder.