7.9 MySQL

The MySQL channel allows the logging server to log events to a MySQL database. The logging server can use the MySQL channel to create the central data store or a filtered database.

The space you need for your database depends on a number of factors. These include, but are not limited to, how many events per second you are storing and how long you want to keep the data. The MySQL install, itself, is about 20 MB. (Keep in mind that the MySQL database does not need to be on the same volume as the MySQL binaries.) For the data store, a system that generates around 80 events per second with an average event size of 80 bytes consumes approximately 500 MB of disk space for the database table and 150 MB for the index in a 24-hour period.

NOTE:To enable the MySQL channel, the MySQL client library, libmysql, is installed with the Secure Logging Server.

For further information, see Section C.0, Using MySQL with Nsure Audit.

7.9.1 MySQL Channel Driver

When the MySQL Channel object configuration is loaded in the logging server’s memory, the MySQL channel driver, lgdmsql, automatically creates the following table structure for the MySQL data store:

Figure 7-2 MySQL Table Structure

Image displaying the MySQL table structure

The table name is defined in the MySQL Channel object configuration page. The default table name is NAUDITLOG.

The MySQL Channel uses MyIsam as its database engine; therefore, the default maximum table size using MySQL 4.1 is 4 GB. MySQL 5.0 limits table sizes to 65,536 TB. Table size can be further constrained by the maximum file size your operating system can manage.

NOTE:If you need larger tables, use the max_rows and avg_row_length commands in the MySQL Channel object’s Create Table Options property.

7.9.2 MySQL Channel Object

The MySQL Channel object stores the information the MySQL driver needs to write events to a MySQL database.

The following table provides a description of each Channel object attribute.

IMPORTANT:You must restart the logging server to effect any changes in Channel object configuration. For more information, see Section G.3, Secure Logging Server Startup Commands

Table 7-10 MySQL Channel Object Attributes

Attribute

Description

Configuration

 

Database

Host

The IP Address or host name of the database server.

If a host name is specified, only the first address associated with that name is used.

If the MySQL channel driver loses its connection with the database server, it tries to reconnect every second for 30 seconds. If it cannot reconnect, the driver stores its current events in memory, but it does not accept any new events until the connection is restored. Incoming events are either stored in the Platform Agents’ Disconnected Mode Cache (in the case of the central data store) or dropped (in the case of a Notification Filter database).

Name

The name of the database to which the logging server writes events.

IMPORTANT:This database must already exist. The SQL Server channel driver does not create the database. If no database name is specified, the logging server looks for NAudit. for information on creating the database, see Section C.3, Preparing the MySQL Database.

Table

The name of the database table to which the logging server writes events.

The MySQL driver, lgdmsql, automatically creates this table when the logging server first loads the current Channel object configuration in memory. For information on the table structure, see MySQL Channel Driver.

Do not use hyphens, spaces, or other special characters in the table name. The default table name is NAUDITLOG.

User

The user account the logging server uses to log in to the database.

On NetWare 6.5, MySQL installs in Secure Mode. The default username for the NetWare 6.5 data store is auditusr. (This default can be changed during the installation of Nsure Audit.) This account has all privileges to the default database (naudit) and can log in from any IP address.

In Secure Mode, the default MySQL administrative account, Root, only has rights to log in at the database server. Therefore, if MySQL is running in Secure Mode and you want the logging server to use the Root account to log in to the database, MySQL and the Secure Logging Server must be located on the same server and you must specify a loopback address (“127.0.0.1” or “localhost”) in the Address field.

Password

The password the logging server uses to authenticate with the database.

The default password for the NetWare 6.5 data store is auditpwd. (This default can be changed during the installation of Nsure Audit.)

Test Credentials

This option tests the MySQL channel configuration to verify the MySQL driver (lgdmsql) can connect to the database.

When you click the Test Credentials link, you are prompted for the JDBC Class. The JDBC Class is the package and name of the Java Class providing JDBC connectivity. Provide the required information, then click OK. The MySQL driver tests the MySQL Channel object configuration by attempting to make a connection to the MySQL database.

Advanced

 

CREATE TABLE Options

This property allows you to customize the default table structure using standard SQL Create Table commands.

For example, the max_rows and avg_row_length commands can be used to increase the maximum size of your table as follows:

max_rows=200000000 avg_row_length=76

NOTE:When all the rows available to MySQL are used, the logging channel starts logging error messages to the screen. If the database is full during startup, the logging channel does not load at all. If MySQL is the default logging channel, the logging server fails to load entirely. Use an expiration command to avoid this issue. For more information, see Section C.9, SQL Expiration Command Variables.

SQL Expiration Commands

This property enables you to use SQL Expiration commands to automate database maintenance.

For example, you can automate data archiving by configuring the MySQL channel to automatically save out the current table and create a new table at designated intervals.

For a listing of command variables and sample scripts, see Section C.9, SQL Expiration Command Variables.

Use a semicolon ( ; ) to separate multiple commands that must be executed in sequence. If the commands can be executed in any order, no semicolon is needed.

WARNING:If you choose the Autoconfigure for MySQL option in the NetWare 6.5 install, the installation program automatically creates the MySQL Channel object with a default Expiration script that runs every night at midnight and automatically deletes every record older than 12 hours. This is done because the default events logged by the NetWare and eDirectory Instrumentations quickly fill the database. To remove this setting, simply delete the script from the SQL Expiration Commands property and restart the Secure Logging Server. The script reads as follows:

DELETE FROM $l WHERE clienttimestamp<(unix_timestamp()-43200);

Expire at specified time or interval

The frequency at which the expiration command script is executed.

For daily regimens, select a time of day. (00 is midnight.)

For weekly regimens, select a day of the week. The expiration commands are executed at midnight on that day.

For monthly regimens, the expiration commands are executed at midnight on the first day of the month.

Status

Allows you to enable or disable the Channel object. By default, all Channel objects are enabled. This means that the logging server loads the Channel object’s configuration in memory at startup.

The Channel object must be located in a supported Channel container for the logging server to use it. For more information on the logging server’s Channel Container property, see Logging Server Objects .

If you select the Disabled option, you must restart the Secure Logging Server for the setting to become effective. Thereafter, the logging server cannot load the object’s configuration until you select Enabled.

For information on unloading the logging server, see Section G.3, Secure Logging Server Startup Commands.