MYSQL5, MYSQL64: MySQL Database Appliances

	At a Glance
	Catalog	System
	Category	Database Appliances
	User volumes	yes
	Min. memory	128 MB
	OS	Linux
	Constraints	no
	Questions/Comments	Ask Forum

Functional Overview

MYSQL64 is not available in 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.

See notes on adding database users for the MYSQL appliance.

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.

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

Properties

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

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 here.

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 can’t reclaim.
innodb_file_per_table=1

Custom Counters

The following counters belong to the MySql counter group:

Error Messages

• Failed to create database.
• Failed to start mysql.
• Insufficent permissions for root@% in the mysql database.

Typical Usage

Simple two-tier application

The following diagram shows a typical usage of the MYSQL appliance in a two-tier web 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 (e.g., 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 in order to view the mysql log files. The admin gateway forwards the requests to the logs NAS appliance.

Example property configuration:

Note that the data volume must also be configured on the db1 appliance as well as the logs appliance. To create application volumes that can be used here, see the Editor Manage Volumes help topic.

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 as well as 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 (e.g., 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 that 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, etc.

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, make sure 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 25MB or more - currently, the InnoDB engine is enabled and it occupies about 22MB 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 (just created) if auto_create is set to 1.
  • The volume contains only the database data. The data can be moved by copying all files to a new (e.g., 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 in order 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.

• References:
  • http://www.mysql.com -- MySQL's web site
  • http://dev.mysql.com/doc/refman/5.0/en/index.html -- MySQL 5.0 documentation

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.

To see the full list of open source packages used in this appliance, please see its Implementation Design.

Related Documents

• MYSQL5
  • Implementation Design
  • Build procedure
  • Test Plan
• MYSQL64
  • Implementation Design
  • Build procedure
  • Test Plan

Questions and Comments

To post a question or comment on this appliance, visit our forum.

-- PavelGeorgiev - 27 Jan 2010