March 7, 2008
How to monitor MySQL using System Center Essentials 2007
I have been running System Center Essentials (SCE) since July of 2007, after hearing about it at Tech Ed. I have pretty much been running SCE with little customization.
Then last week I was told by our web developers that our MySQL server was down. It turned out that the service had stopped running. Now, if only they had been using MS SQL, I would have been notified by my trusty SCE server (notice my gentle nudging). So I began to wonder how I could get System Center Essentials to monitor MySQL.
Go to the Authoring space in SCE. Expand Management Pack Templates.
- Windows Service – Make a custom monitor that checks all the MySQL services are running OK.
- TCP Port – Watch for the particular ports used by MySQL to make sure they are live from outside the monitored box.
- OLEDB Data Source – Attach to the MySQL databases of interest using OLEDB, you can create a monitor for each one.
Now, steps 1 and 2 I found easy to follow. However, step 3 gave me a few problems. According to the MySQL.com web site, officially, OLE DB is not supported for MySQL. If you search hard enough, you will find an OLE DB driver for MySQL. It is officially not supported and carries a last modified date of Febraury 2001! Furthermore, although there is an official ODBC driver for MySQL, SCE does not allow ODBC drivers. So how can I monitor MySQL databases?
The answer is the Microsoft OLE DB Provider for ODBC. This way we can have OLE DB for MySQL using recent technology (No 2001 drivers, thank you).
To setup the Microsoft OLE DB provider for ODBC for this application, do the following:
- Install the MySQL ODBC driver
- From the SCE Authoring page, select OLE DB Data Source. Then click on Add Monitoring Wizard on the left side of the page.
- For Select the monitoring type, make sure that OLE DB Data Source is selected. Click Next.
- Give a name and an optional description to your monitor. Select or create a management pack for this monitor. Click Next.
- For the Connection String, make sure that Advanced Configuration is selected.
You now have two choices for the type of connection string that you would like to enter depending on whether you want to use an ODBC Data Source Name (DSN) or not. My recommendation is not to use a DSN. It is just one more thing to troubleshoot. I have provided sample connection strings for both methods.
Without a DSN (recommended):
Provider=MSDASQL;DRIVER=MySQL ODBC 3.51 Driver; SERVER=your-mysql-server; DATABASE=your-databasename; UID=mysql-user; PWD=password
(Note: If the name of the MySQL ODBC driver changes, the DRIVER parameter will have to be changed to match the new name. Currently, the name of the ODBC driver appears as MySQL ODBC 3.51 Driver.)
With a DSN
Provider=MSDASQL; DSN=name-of-you-dsn; DATABASE=your-databasename; UID=mysql-user; PWD=password
For further documentation on the Microsoft OLE DB provider for ODBC :