System Catalog › Database Appliances › MYSQL5 - MySQL Database Appliance

MYSQL5 - MySQL Database Appliance

Functional Overview

Important! MYSQL64 is not available in CA 3Tera AppLogic 2.8+; please use MYSQLR64 instead.

MYSQL is a database appliance based on the MySQL database engine (http://www.mysql.org). It provides an easy way to add a database to any application.

MYSQL stores the database on an application-defined volume that can be configured on each MYSQL instance. MYSQL automatically creates an empty database when it starts on an empty volume.

Boundary

Resources

Terminals

The default interface is enabled. It is intended for diagnostics and troubleshooting (over SSH). Future versions of this appliance may disable SSH access.

User Volumes

The data volume can optionally contain a my.cnf file in its top directory, which includes MYSQL configuration options. See the Custom Configuration section for more details. This feature is available in MYSQL5/64 1.3.3 or later.

Important! The data volume must exclusively dedicated to the MYSQL instance (cannot be shared with other appliances).

Properties

Important!

• As of MYSQL5 1.3.8, the use_old_passwords property is removed. If you need to enable old_passwords, create a custom configuration as described below.

The MYSQL appliance will fail to start if the error_log_filename is specified and the log terminal is not connected or the file system cannot be mounted.

Custom Configuration

This feature is available in MYSQLR 1.3.3 or later.

MYSQL5/64 allows the use of a custom MYSQL configuration file which can provide additional configuration options or overwrite existing configuration specified in /etc/my.cnf.

To use a custom configuration, create a file named my.cnf and place it in the top directory of the data volume. The format of the file should follow the MYSQL options file syntax as described on their website.

For example, the following can be used to tune MYSQL5/64 for better performance when using InnoDB (the default MYSQL5/64 configuration is optimized for MyISAM). The example is based on using 512M of memory (default for MYSQL5/64).

[mysqld]
# Shrink down MyISAM buffers
key_buffer = 512K
myisam_sort_buffer_size = 512K

# Make InnoDB the default storage engine (optional)
default-storage-engine = INNODB

# Set InnoDB buffer size
innodb_buffer_pool_size=350M
innodb_log_file_size=128M
innodb_log_buffer_size=4M
innodb_thread_concurrency=8

# If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you cannot reclaim.

innodb_file_per_table=1

Custom Counters

The MYSQL appliance reports the following custom counters through the mon terminal.

The following counters belong to the MySql counter group:

Error Messages

The following messages may appear in either the appliance log file or the system log of the grid controller when the appliance fails to start:

• Failed to create database.
• Failed to start mysql.

Insufficient permissions for root@% in the mysql database.

Typical Usage

Simple Two-tier Application

Appliances in use:

• in1 - input gateway
• web1 - web server
• db1 - MySQL database appliance
• logs - NAS appliance for storing log files
• admin - input gateway for viewing log files

Client request arrive on the in1 gateway. The gateway forwards the requests to the web1 server, which serves the request. When scripts (for example, Perl or PHP) on web1 need to access persistent data, they use the db1 appliance through the out terminal of the web1 server. The db1 appliance is configured to store its log files within the root directory of the share exposed by logs.

Using a browser, administrators connect to the admin gateway to view the mysql log files. The admin gateway forwards the requests to the logs NAS appliance.

Example property configuration:

Note: The data volume must also be configured on the db1 appliance and the logs appliance. To create application volumes that can be used here, see the Grid Users Guide.

Scalable Two-tier Application

The following diagram shows a typical usage of the mysql appliance in a two-tier web application in which the database is used to share state and data between multiple, load-balanced web servers. In addition, this example has a separate input for maintenance, through which an administrator can log in and access the database for maintenance and an input through which an administrator can log in and view the mysql error log.

Appliances in use:

• usr - input gateway for user requests
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (for example, CGI scripts)
• db - database
• logs - storage for database error log files
• maint - input gateway for maintenance access
• admin - a generic Linux server used by the administrator
• gway - a network gateway for administrator's access to the Internet
• log - input gateway for access to the database error log

Client request arrive on the user gateway. The gateway forwards the requests to the web_lb load balancer, which directs the request to one of the web servers web1 and web2. The web servers access the db database.

The db database writes its error log to the logs appliance through the log terminal. In addition, an administrator can log in through the log gateway to the logs appliance and view the database error log files.

Additionally, an administrator can log in over SSH through the maint gateway to the admin server. From the admin server, the administrator can access the db database for statistics or changing the database schema. The admin server can access the Internet through the gway gateway, for example, to download a newer version of libraries or the database schema.

Example property configuration:

Note: The data volume must also be configured on the db appliance and the data volume must also be configured on the logs appliance.

The maint, admin, gway, and log appliances are not required for the operation of the two-tier application. If present, the admin server may have cron jobs for database scrubbing, sending e-mail, and so on.

Notes

• When MYSQL is started, there is a message written to the appliance log file stating that chmod failed to modify the owner for the mysql log file. This is to be expected and does not affect the operation of the MYSQL appliance.
• When creating users for the database, help ensure all users are created with no restrictions on the host they connect from. For example:

  mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%'
      ->     IDENTIFIED BY 'some_pass' WITH GRANT OPTION;
  

• Volume requirements
  • The volume size should be 40MB or more - currently, the InnoDB engine is enabled and it occupies about 34MB on the data volume. In this version, there is no way to disable this, even if you don't use InnoDB for data storage.
  • You can leave the volume empty (formatted) if auto_create is set to 1.
  • The volume type for the MYSQL5 appliance may not be a raw volume type.
  • The volume contains only the database data. The data can be moved by copying all files to a new (for example, larger) volume.
• The appliance connected to MYSQL is responsible for creating its own database and tables if they do not exist. Alternatively, you can manually copy the initial database on the data volume.
• The appliance or server connected to MYSQL's in input should have the MySQL client library to access the MySQL server and submit requests. All virtual servers provided in the appliance catalog have the MySQL client library installed, including the generic LINUX server.

Open source and 3rd party software used inside of the appliance

MYSQL5 uses the following 3rd party open source packages in addition to the 3rd party open source packages used by its base class LUX5.