r8 - 20 Dec 2007 - 20:14:03 - BeckyHYou are here: Wiki >

AppLogic2 Web > CatDatabaseAppliancesMYSQL5

AppLogic 2.1/2.2 Documentation The latest production release is AppLogic 2.9.9

MYSQL5: MySQL 5.0 Database Appliance

Latest version: 1.0.0

	At a Glance
	Catalog	Proto (AppLogic 2.1)
		System (AppLogic 2.2+)
	Category	Database Appliances
	User volumes	yes
	Min. memory	128 MB
	OS	Linux
	Constraints	no
	Questions/Comments	Ask Forum?

Functional Overview

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

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

See notes on adding database users for the MYSQL5 appliance.

Boundary

Resources

Resource	Minimum	Maximum	Default
CPU	0.10	4	0.40
Memory	128 MB	8G	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).

User Volumes

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

The data volume must exclusively dedicated to the MYSQL5 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 (with stopped MySQL daemon). 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/db.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`
`use_old_passwords`	Integer	Whether the MySQL server should use the old password hashing algorithm for user login. Possible values are: `1` to use the older password hashing algorithm so newly created users are able to connect through older pre-4.1 MySQL clients, `0` to use the newer password hashing algorithm (attempts to connect to the MYSQL server with a pre-4.1 MYSQL client will fail). Default is `1`.

The MYSQL5 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 MYSQL5 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 MYSQL5 appliance in a two-tier web application:

Appliances in use:

in - input gateway
srv - web server
db - MySQL5 database appliance
logs - NAS appliance for storing log files
admin - input gateway for viewing log files

Client request arrive on the in gateway. The gateway forwards the requests to the srv server, which serves the request and writes logs in log files on the logs appliance. When scripts (e.g., Perl or PHP) on srv need to access database data, they use the db appliance through the db terminal of the web server. The db appliance is configured to store its log files within the mysql directory of the share exposed by logs.

Using a browser, administrators connect to the admin gateway in order to view the web server and 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`	`/mysql/db.log`	Name of error log file that is to be stored on the `logs` data volume.
`error_log_level`	`error`	Error logging level
`user_old_passwords`	`1`	Allows MYSQL pre-4.1 clients on the `srv` appliance to connect to the database server.

Note that the data volume must also be configured on the db appliance as well as on 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 MYSQL5 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 through which an administrator can log in and view the MySQL and web servers log files.

Appliances in use:

in - input gateway for user requests
hlb - web load-balancer for user requests
srv1, srv2 - web servers with active content (e.g., CGI scripts)
db - database
logs - storage for database and web servers log files
content - storage for web servers content
admin - input gateway for viewing log files

Client request arrive on the in gateway. The gateway forwards the requests to the hlb load balancer, which directs the request to one of the web servers srv1 of srv2. The web servers share one and the same content files and write logs in files on the logs appliance. Both web servers have access to one database on the db appliance.

The db database writes its error log to the logs appliance through the log terminal.

An administrator can log in through the log gateway to the logs appliance and view the web servers and database log files.

Example property configuration:

Property name	Value	Notes
`auto_create`	`1`	Create the database if the volumes are empty.
`error_log_filename`	`/mysql/db.log`	Name of error log file that is to be stored on the `logs` data volume.
`error_log_level`	`error`	Error logging level
`user_old_passwords`	`1`	Allows MYSQL pre-4.1 clients on the `srv` appliance to connect to the database server.

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

Notes

When MYSQL5 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 expected and does not affect the operation of the MYSQL5 appliance.

When creating users for the database, make sure all users are created with no restrictions on the host they connect from. For example:

mysql5> 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 MYSQL5 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 MYSQL5'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.