Database Configuration Guide

CHAPTER 5

Configuring MySQL Databases

This chapter describes how to configure a MySQL database for use with the Novell exteNd Application Server as a SilverMaster or deployment database. Topics include:

About MySQL support

When running on Windows, Linux, or NetWare, the Novell exteNd Application Server supports access to MySQL databases for the following uses:

As the application server's SilverMaster database
As a deployment database or connection pool

For more information To learn more about these uses, see the chapter on data source configuration in the Administrator's Guide.

DBMS and driver versions The application server accesses MySQL through the Connector/J driver. For details on the supported versions of this DBMS and driver, see the Novell exteNd Application Server Release Notes.

MySQL Web site You can visit www.mysql.com to get additional information about MySQL.

Preparing to access MySQL

Before the application server can start using MySQL databases, you need to make sure that:

A supported version of the MySQL DBMS is installed on one or more host machines accessible to the application server (often including the application server machine itself)
A supported version of the Connector/J driver is installed on the application server machine

For details, see the section below for your situation:

When MySQL is installed for you

On NetWare and for appropriate Novell exteNd configurations on Windows and Linux, the exteNd install provides all of the MySQL software (MySQL DBMS and Connector/J driver) you need for the application server. In these cases, the exteNd install also takes care of all necessary DBMS and driver configuration for you.

For more information, see Installing Novell exteNd.

When you install MySQL yourself

In some cases, you may want or need to obtain and install the MySQL software yourself:

MySQL DBMS

If you don't already have the MySQL servers you need, set them up and make sure they can be accessed by your application server machine. Follow these steps (note that the examples shown are for Linux):

Obtain the MySQL DBMS and install it on an appropriate host machine.
Once your MySQL server is installed, set up the configuration file for it by using one of the templates in /usr/share/mysql:
- my-huge.cnf
- my-large.cnf
- my-medium.cnf
- my-small.cnf
Choose the appropriate template (based on size of RAM) and copy it to /etc/my.cnf.
Edit the my.cnf file to change the max_allowed_packet setting from the default of 16M to 8M (required to run SilverMasterInit):
```
  set-variable = max_allowed_packet=8M
```

If you want the MySQL server to support distributed transactions, uncomment the following lines in the my.cnf file:

  # Uncomment the following if you are using InnoDB tables
  innodb_data_home_dir = /var/lib/mysql/
  innodb_data_file_path = ibdata1:10M:autoextend
  innodb_log_group_home_dir = /var/lib/mysql/
  innodb_log_arch_dir = /var/lib/mysql/

Note that when you later create tables for use in distributed transactions, they must be specified as type InnoDB. For example:

  CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), 
  species VARCHAR(20), sex CHAR(1), birth DATE, death DATE) 
  TYPE InnoDB;

Add the following lines to the my.cnf file:

  transaction-isolation = READ-COMMITTED
  innodb_flush_log_at_trx_commit = 1

To turn on logging (recommended), add this line to the my.cnf file:
```
  log-bin = filename.ext
```

Connector/J driver

You need to set up the Connector/J driver so the application server can use it when connecting to MySQL. Follow these steps:

Obtain Connector/J and install it on the application server machine.
Specify the location of the Connector/J driver's JAR file by setting the AGCLASSPATH environment variable (in the .agprofile file on Linux). The JAR file is named:
```
  mysql-connector-java-version-bin.jar
```
For more about setting AGCLASSPATH, see the chapter on data source configuration in the Administrator's Guide.

Creating MySQL databases

This section provides basic instructions for manually creating a MySQL database that you can then use as a SilverMaster, deployment database, or connection pool.

NOTE: On NetWare and for appropriate Novell exteNd configurations on Windows and Linux, the exteNd install can automatically create the MySQL database for your SilverMaster. In these cases, you aren't required to manually create the MySQL database ahead of time.

Procedure To manually create a MySQL database:

From the command prompt, start the MySQL monitor by typing:
```
  mysql
```

To create your database, type:

  create database databasename;

For example:

  create database salesdb;

You must grant access rights for this database to the MySQL user through which the application server will be connecting. Type:

  grant all on databasename.* to username@localhost identified by 'password';

For example:

  grant all on salesdb.* to appserver@localhost identified by 'secretpw';

To exit from the MySQL monitor, type:
```
  quit
```

Setting up a SilverMaster database

The SilverMaster is a database in which the application server maintains its system information. Because the SilverMaster is configured when you install the application server, this database must be available at that time.

You can use a MySQL database as your application server's SilverMaster. For details, see the section below for your situation:

When SilverMaster is set up for you

On NetWare and for appropriate Novell exteNd configurations on Windows and Linux, the exteNd install can automatically create and configure a MySQL database for the SilverMaster when installing your application server.

For more information, see Installing Novell exteNd.

When you set up SilverMaster yourself

In some cases, you may want or need to set up a MySQL database for the SilverMaster yourself:

Create the MySQL database ahead of time.

For the manual creation steps, see Creating MySQL databases.
Start the exteNd install and specify that database when you're prompted for SilverMaster settings.

For more information, see Installing Novell exteNd.

Setting up a deployment database

It's common to deploy your J2EE applications to the SilverMaster database, but you can also add other databases to the application server anytime and deploy to them instead. To set up a MySQL deployment database, you must:

Create the MySQL database.

For the manual creation steps, see Creating MySQL databases.
Add that database to the application server. Use either of the following:
- The Add Database dialog in the SMC (Server Management Console)
- The AddDatabase command of the SilverCmd command-line tool
You'll supply the database name, user name, and password (as specified when the database was created), and choose the MySQL database platform and driver set. For the JDBC URL, specify the following (replacing %HOST% and %DBNAME% with the appropriate MySQL host address and database name):
```
  jdbc:mysql://%HOST%/%DBNAME%
```
For example:
```
  jdbc:mysql://localhost/salesdb
```

If you want to add a connection pool to the application server (for accessing data from a MySQL database), the steps are similar.