 | Complete walkthrough for setting up LANrev MySQL ODBC export |  |
 Posted: Wed Jan 17, 2007 3:35 am |
 |
|
| bgt |
| LANrev Team |
 |
| |
| Joined: 26 Jul 2006 |
| Posts: 727 |
| Location: USA |
|
|
|
 |
|
 |
|
Q: Are there detailed instructions for setting up MySQL and the MySQL ODBC export in LANrev for a Mac?
UPDATE: This article has been superseded by the LANrev ODBC Export Guide posted in the resource center. Instructions for both Windows and Mac OS X are included. |
|
Last edited by bgt on Fri Mar 12, 2010 6:39 am; edited 5 times in total |
|
|
|
| | |
| Posted: Wed Jan 17, 2007 3:37 am |
|
|
| bgt |
| LANrev Team |
|
| |
| Joined: 26 Jul 2006 |
| Posts: 727 |
| Location: USA |
|
|
|
|
|
|
|
A: Follow the instructions below for setting up MySQL and the MySQL ODBC export in LANrev on a Mac OS 10.4 system.
Installing MySQL 4.1 Server
1. Download the appropriate PKG installer for your PowerPC or Intel Mac from the MySQL web site:
http://dev.mysql.com/downloads/mysql/4.1.html
2. Mount the downloaded disk image and install the mysql-standard-4.1.22-apple-darwin PKG package.
3. Install the MySQLStartupItem.pkg package.
4. Install the MysQL.prefPane by double-clicking it.
5. When asked whether you want to install it for this user or all users pick an option and click the Install button.
6. In the MySQL preference pane check 'Automatically Start MySQL Server on Startup' and click the Start MySQL Server button.
Creating a Database
1. Launch Terminal and run the following commands:
| Code: |
sudo su -c mysql
/usr/local/mysql/bin/mysql -u root -p [Press return when prompted for a password] |
2. At the mysql> prompt run the following commands (there are 3 commands here besides quit and they each end with a semicolon):
| Code: |
SET PASSWORD = PASSWORD('password'); [Replace password with a new password you want to use for the root account]
create database LANrev;
GRANT ALL ON LANrev.* TO username@localhost IDENTIFIED BY "password"; [Replace username and password with your own values |
3. At the shell prompt run the following commands to verify your database got created (there are 1 shell and 1 MySQL command here besides quit):
| Code: |
/usr/local/mysql/bin/mysql -u username -p [Replace username and password with values set with above GRANT operation]
SHOW DATABASES;
quit |
Installing the MyODBC 3.5.1 Driver
1. Download the MyODBC driver from the MySQL web site:
http://dev.mysql.com/downloads/connector/odbc/3.51.html
Please note that the MyODBC driver on the MySQL web site is for PowerPC Macs only so you must either re-compile the MyODBC drivers on an Intel-based Mac or download the pre-compiled Intel Mac ODBC drivers. The folks from GraphicCARE have very generously provided a compiled binary of the MyODBC drivers for Intel-based Macs along with a tip that includes detailed instructions on how to manually compile it from source code if you prefer that option. They have graciously given us permission to link to this tip on the GraphicCARE site at:
http://www.graphiccare.ca/index.php?option=com_content&task=view&id=56&Itemid=37
2. Mount the disk image and install the appropriate MyODBC driver for your platform.
Adding the MyODBC Driver to ODBC Administrator
You may be able to skip this section if you installed the PowerPC version of the MyODBC driver. The PowerPC installer for the MyODBC driver should automatically add a driver entry for you to the Drivers pane of ODBC Administrator although this may not always happen.
1. Launch ODBC Administrator from the /Applications/Utilities folder.
2. Click the lock in the lower left hand corner and enter your admin credentials.
3. Pick the Drivers pane and click the Add button.
4. Enter a description for the driver (e.g. MyODBC 3.5.1).
5. Click the Choose button next to the Driver file field.
6. For Intel Macs, press Shift-Command-G, enter /usr/local into the field, and click the Go button. Browse to ./myodbc/lib, select the libmyodbc3.so file entry, and click the Choose button.
7. For PowerPC Macs, press Shift-Command-G, enter /usr/lib into the field, and click the Go button. Browse to and select the libmyodbc3.dylib file entry and then click the Choose button.
8. Click the OK button.
Creating a Data Source Name
1. Select the System DSN pane.
2. Select your ODBC driver from the list and click OK. Note that the driver may not show up correctly in the System DSN tab so click OK without selecting anything and it will pick the MyODBC driver anyways.
3. Enter a name for your DSN.
4. Add four keywords with the following values:
server - address of your MySQL server (usually 127.0.0.1 if it's installed on your LANrev server)
user - username with access to your database (set with the GRANT operation when you created the DB)
password - password for above account (set with the GRANT operation when you created the DB)
database - name of your database (LANrev in this example)
5. Click OK when you are done.
6. Click the Apply button and close ODBC Administrator.
If there is an error message to the effect that you cannot connect to the MySQL database, it might be because your MySQL server is hosted on a different system than the LANrev server. By default these instructions have only enabled access to the MySQL server from localhost. To resolve this you'll need to run the following command from within the mysql utility:
| Code: |
| GRANT ALL ON LANrev.* TO username@ipaddress IDENTIFIED BY "password"; [Replace username/password with the correct values and replace ipaddress with the IP address of your LANrev server] |
Configuring LANrev
1. Launch Terminal and run the following commands:
| Code: |
cd /Library/Logs
tail -f -n 25 LANrevODBCExport.log |
2. In the LANrev admin console go to Server > Server Settings > ODBC Export.
3. Check the Enable ODBC export checkbox.
4. Enter name of your DSN, SQL server address (usually 127.0.0.1 if your SQL server is hosted on the same machine as your LANrev server), and database username/password.
5. Set the Export Interval to 5 minutes temporarily. Change this value back to how often you want the export to occur after you are done testing.
6. Click OK and monitor the Terminal window for error messages. A message of "Autodetected RDBM system to be MySQL" means it worked.
Installing the iOBDC Libraries
Skip this section if you already have the iODBC libraries installed and are not receiving any error messages.
1. In the About pane of the ODBC Administrator make sure you have 5 files listed. If this is not the case you are likely missing some iODBC libraries, which will likely result in an error message in the LANrevODBCExport.log file about the iODBC driver manager not being able to find the default driver.
2. Download and install the iODBC 3.52.5 libaries from source code using the following URL. You want the libiodbc-3.52.5.tar.gz file.
http://www.iodbc.org/index.php?page=downloads/index
3. Extract the contents of the tarball using the tar command. Run 'man tar' if you need help on tar usage.
4. Follow the instructions in the README.MACOSX file to compile and install the iODBC libraries.
5. Reset up your System DSN with the correct driver.
Verify that the Export Occurred
1. Wait as least 5 minutes.
2. Launch Terminal and run the following command:
| Code: |
/usr/local/mysql/bin/mysql database -u username -p [LANrev is the DB name in this example. Replace username with your DB username and enter password for specified account when prompted]
|
3. At the mysql> prompt run the following commands (there are 3 commands besides quit and they each end with a semicolon):
| Code: |
SHOW TABLES;
DESCRIBE table; [Replace table with one of the tables listed in the previous SHOW TABLES command]
SELECT agent_info.AgentName, hardware_info.MachineModel, hardware_info.CPUName, hardware_info.CPUSpeed from LANrev.agent_info, LANrev.hardware_info WHERE LANrev.agent_info.heartbeat_record_id =
LANrev.hardware_info.agent_info_record_id LIMIT 10;
quit
|
If a table of values with computer names, machine types, processors, and processor speeds shows up it means your export was successful. |
|
|
|
|
| | |
| Posted: Tue Jan 22, 2008 9:23 pm |
|
|
| bgt |
| LANrev Team |
|
| |
| Joined: 26 Jul 2006 |
| Posts: 727 |
| Location: USA |
|
|
|
|
|
|
|
A: Follow the instructions below for setting up MySQL and the MySQL ODBC export in LANrev on a Mac OS 10.5 system.
Installing the MySQL 5.0 Server
1. Download the "Mac OS X (package format) download" for 10.4 DMG from http://dev.mysql.com/downloads/mysql/5.0.html#macosx-dmg
2. Mount the mysql-5.0.45-osx10.4-i686 disk image and run the MySQL PKG installer to install MySQL.
3. Double click the MySQL.prefPane file to install it and pick "Install for all users of this computer".
4. Download the following com.mysql.mysqld.plist file from the URL below and place it in the /Library/LaunchDaemons folder.The contents of the plist file are below in case you are unable to download it.
| Code: |
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
<key>KeepAlive</key>
<true/>
<key>Label</key>
<string>com.mysql.mysqld</string>
<key>Program</key>
<string>/usr/local/mysql/bin/mysqld_safe</string>
<key>RunAtLoad</key>
<true/>
</dict>
</plist> |
5. Change the file's owner to root.
| Code: |
| sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.plist |
6. Change your MySQL owner to mysql.
| Code: |
| sudo chown -R mysql /usr/local/mysql-5.0.45-osx10.4-architecture /usr/local/mysql |
7. Load the MySQL launchd configuration file and start the MySQL server.
| Code: |
| sudo launchctl load /Library/LaunchDaemons/com.mysql.mysqld.plist |
8. Check the MySQL preference pane to make sure it says the server is running. Note that the "Stop MySQL Server" and "Automatically Start MySQL Server on Startup" buttons are nonfunctional.
Creating a Database
1. Run the following commands at the Terminal.
| Code: |
| /usr/local/mysql/bin/mysql -u root -p [Press return when prompted for a password] |
2. At the mysql> prompt run the following commands (there are 3 commands here besides quit and they each end with a semicolon).
| Code: |
SET PASSWORD = PASSWORD('password'); [Replace password with a new password you want to use for the root account]
create database LANrev;
GRANT ALL ON LANrev.* TO username@localhost IDENTIFIED BY "password"; [Replace username and password with your own values] |
3. At the shell prompt run the following commands to verify your database got created (there are 1 shell and 1 MySQL command here besides quit).
| Code: |
/usr/local/mysql/bin/mysql -u username -p [Replace username and password with values set with above GRANT operation]
SHOW DATABASES;
quit |
Installing the MyODBC 3.5.1 Driver
1. Get the "Mac OS X (package format) download" for 10.4 from http://dev.mysql.com/downloads/connector/odbc/3.51.html#macosx-dmg
2. Run the PKG to install the MyODBC driver.
3. Launch ODBC Administrator and click the lock in the lower left hand so you can make changes and register the MySQL ODBC driver.
4. Switch to the Drivers tab, select your driver, and click the Configure button. Change it so that the "Setup file" field is empty and the "Define as" field is set to "System".
Creating a Data Source
1. Switch to the System DSN tab, click the Add button, select the MySQL ODBC 3.51 Driver, and fill out the fields as follows.
| Quote: |
Data Source Name (DSN): LANrev
Description: |
2. Add 2 keywords, click OK and then Apply, and then edit /Library/ODBC/odbc.ini. Edit the old text for the 2 keys to the new text below.
| Code: |
-=OLD=-
[LANrev]
Driver = /usr/local/lib/libmyodbc3-3.51.23.so
Description =
Key = Value
Key1 = Value
-=NEW=-
[LANrev]
Driver = /usr/local/lib/libmyodbc3-3.51.23.so
Description =
Server = 127.0.0.1
Database = LANrev |
Configuring LANrev
1. In the LANrev admin console go to Server > Server Settings > ODBC Export.
2. Check the "Enable ODBC export" tickbox.
3. Enter name of your DSN, SQL server address (usually 127.0.0.1 if your SQL server is hosted on the same machine as your LANrev server), and database username/password.
4. Set the Export Interval to 5 minutes temporarily. Change this value back to how often you want the export to occur after you are done testing.
5. Launch Terminal and run the following command.
| Code: |
| tail -f -n 25 /Library/Logs LANrevODBCExport.log |
6. Monitor the Terminal window for error messages. A message of "Autodetected RDBM system to be MySQL" means it worked.
Installing the iOBDC Libraries
Skip this section if you already have the iODBC libraries installed and are not receiving any error messages.
1. In the About pane of the ODBC Administrator make sure you have 5 files listed. If this is not the case you are likely missing some iODBC libraries, which will likely result in an error message in the LANrevODBCExport.log file about the iODBC driver manager not being able to find the default driver.
2. Download and install the iODBC 3.52.6 libaries from source code using the following URL. You want the libiodbc-3.52.6.tar.gz file.
3. Extract the contents of the tarball using the tar command. Run 'man tar' if you need help on tar usage.
4. Edit the ./libiodbc-3.52.6/mac/Makefile file and add a Mac OS X 10.5 section just below the one for Mac OS X 10.4.
| Code: |
# Mac OS X 10.4
#
ifeq (10.4, $(findstring 10.4, $(macosx_version)))
PROJBUILD := /usr/bin/xcodebuild -configuration $(MODEL)
endif
# Mac OS X 10.5
#
ifeq (10.5, $(findstring 10.5, $(macosx_version)))
PROJBUILD := /usr/bin/xcodebuild -configuration $(MODEL)
endif
#
# Build the libraries
# |
5. Then run the following commands. You'll be asked to authenticate so you can delete the old iODBC files.
6. Re-set up your System DSN with the correct driver.
Verify that the Export Occurred
1. Wait as least 5 minutes.
2. Run the following commands in Terminal.
| Code: |
| /usr/local/mysql/bin/mysql database -u username -p [LANrev is the DB name in this example. Replace username with your DB username and enter password for specified account when prompted] |
3. At the mysql> prompt run the following commands (there are 3 commands besides quit and they each end with a semicolon):
| Code: |
SHOW TABLES;
DESCRIBE table; [Replace table with one of the tables listed in the previous SHOW TABLES command]
SELECT agent_info.AgentName, hardware_info.MachineModel, hardware_info.CPUName, hardware_info.CPUSpeed from LANrev.agent_info, LANrev.hardware_info WHERE LANrev.agent_info.heartbeat_record_id =
LANrev.hardware_info.agent_info_record_id LIMIT 10;
quit |
If a table of values with computer names, machine types, processors, and processor speeds shows up it means your export was successful. |
|
|
|
|
| | |
| Posted: Thu Sep 17, 2009 4:00 pm |
|
|
| MandeBooks |
|
|
| |
| Joined: 08 Sep 2009 |
| Posts: 5 |
| Location: Orlando, FL |
|
|
|
|
|
|
|
| Why do we have to sill complete the "Installing the MySQL 5.0 Server" step on OS 10.5 Servers when MySQL is now included with OS 10.5 Server? Is the built-in version not compatible for some reason? |
|
|
|
|
| Posted: Thu Sep 17, 2009 8:51 pm |
|
|
| bgt |
| LANrev Team |
|
| |
| Joined: 26 Jul 2006 |
| Posts: 727 |
| Location: USA |
|
|
|
|
|
|
|
No, you can skip that section as long as you're able to do the other necessary instructions after that. This document has been around a long time and at the time it was written we tried it on a Mac OS X client and not Server system, which didn't have MySQL installed on it.
Bao |
|
|
|
|
| Posted: Thu Dec 17, 2009 10:06 pm |
|
|
| jwu |
| LANrev Team |
|
| |
| Joined: 09 Jun 2009 |
| Posts: 20 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
