r22 - 18 Jan 2008 - 17:46:55 - OleksandrMazinYou are here: Wiki >

December 24 - AppLogic 2.4.7 is now available and is the latest production release!

MYSQL, 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

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.

Name	Latest Version	OS	MySQL	Notes
MYSQL	2.2.2	Fedora Core 3	4.1
MYSQL5	1.0.2	CentOS 5	5.0.22	AppLogic 2.1.1+ ONLY
MYSQL64	1.0.1	CentOS 5 (64 bit)	5.0.22	AppLogic 2.2 Beta ONLY

Boundary

Resources

Resource	Minimum	Maximum	Default
CPU	0.10	16	0.40
Memory	128 MB	16G	512 MB
Bandwidth	1 Mbps	500 Mbps	1 Mbps

Terminals

Name	Dir	Protocol	Description
`in`	in	mysql	Receives MySQL database requests
`log`	out	cifs	Network file system for storing error log. This terminal may be left unconnected if not used.
`mon`	out	cce	Sends performance and resource usage statistics. This terminal may be left unconnected.

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

Volume	Description
`data`	Volume for the database data storage.

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

Properties

Property name	Type	Description
`auto_create`	Integer	Whether to create the database if it doesn't exist. Possible values are `1` to create it and `0` to prevent auto creation (to avoid accidental overwrite in case of corrupted volumes). If set to `0` and a database doesn't exist on the user volume, the appliance starts in maintenance mode (MySQL deamon not started). Default is `1`.
`error_log_filename`	String	Fully qualified file name for the error log file, relative to the `log` file system (e.g., `/mysql_logs/my.log`). If empty, error logging is disabled. Default: (empty).
`error_log_level`	String	Error logging level. Possible values are: `error` logs only errors, `warn` logs both warnings and errors. This property is not case sensitive. Default: `error`

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 Counters

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

The following counters belong to the MySql counter group:

Counter name	Description
`Aborted Clients`	Number of clients aborted by the server
`Aborted Connections`	Number of connects aborted by the server
`Bytes Received`	Number of received bytes
`Bytes Sent`	Number of sent bytes
`Total Connections`	Number of connections
`Questions`	Total number of questions
`Slow Queries`	Number of slow queries
`Threads Created`	Number of threads created
`Threads Connected`	Number of threads connected
`Threads Running`	Number of threads running
`Max Used Connections`	Number of max connections used
`Open Files`	Number of open files
`Admin Commands`	Number of admin commands
`Alter Table Commands`	Number of alter table commands
`Analyze Commands`	Number of analyze commands
`Backup Table Commands`	Number of backup table commands
`Change DB Commands`	Number of change DB commands
`Change Master Commands`	Number of change master commands
`Check Commands`	Number of check commands
`Commit Commands`	Number of commit commands
`Create DB Commands`	Number of create DB commands
`Create Function Commands`	Number of create function commands
`Create Index Commands`	Number of create index commands
`Create Table Commands`	Number of create table commands
`Delete Commands`	Number of delete commands
`Drop DB Commands`	Number of drop DB commands
`Drop Function Commands`	Number of drop function commands
`Drop Index Commands`	Number of drop index commands
`Drop Table Commands`	Number of drop table commands
`Flush Commands`	Number of flush commands
`Grant Commands`	Number of grant commands
`Insert Commands`	Number of insert commands
`Insert Select Commands`	Number of insert select commands
`Kill Commands`	Number of kill commands
`Load Commands`	Number of load commands
`Load Master Table Commands`	Number of load master table commands
`Lock Tables Commands`	Number of lock tables commands
`Optimize Commands`	Number of optimize commands
`Purge Commands`	Number of purge commands
`Rename Table Commands`	Number of rename table commands
`Repair Commands`	Number of repair commands
`Replace Commands`	Number of replace commands
`Replace Select Commands`	Number of replace select commands
`Reset Commands`	Number of reset commands
`Restore Table Commands`	Number of restore table commands
`Revoke Commands`	Number of revoke commands
`Rollback Commands`	Number of rollback commands
`Select Commands`	Number of select commands
`Set Option Commands`	Number of set option commands
`Truncate Commands`	Number of truncate commands
`Unlock Tables Commands`	Number of unlock tables commands
`Update Commands`	Number of update commands

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:

Property name	Value	Notes
`auto_create`	`1`	Create the database if the volumes are empty.
`error_log_filename`	`/my.log`	Name of error log file that is to be stored on the `logs` data volume.
`error_log_level`	`error`	Error logging level

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:

Property name	Value	Notes
`auto_create`	`1`	Create the database if the volumes are empty.
`error_log_filename`	`/my.log`	Name of error log file that is to be stored on the `logs` data volume.
`error_log_level`	`error`	Error logging level

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.