MYSQLR: MySQL 5.0 Database Appliance with replication capabilities - Implementation Design

Base class

Any AppLogic appliance image on CentOS 5 can be used as the base class for MYSQLR. The simplest one to use is LUX5 - although the class volumes need to be resized.

Class volumes

The volume configuration for MYSQLR is determined by the base class image from which it is created. The setup chosen for the 1.0.0 release is as follows:

• boot volume, 120MB size, mounted as "/", writeable, instantiable
• usr volume, 180MB size, mounted as "/usr", read-only, common

In summary, MYSQLR's boot volume contains:

• CentOS 5, as base OS image (as installed on the LUX5 appliance)
• MySQL database engine version 5.0.22, for data storage and query processing
• Samba client version 3.0.23, for accessing files through the log terminal
• sshd, used for logging into the component through the default interface

In addition to the class volume, MYSQL requires a user-supplied data volume that is used to store the actual database and binary logs needed for replication:

• data volume, user-defined size, mounted as "/mnt/data", writeable, placeholder

Packages

Installation:

Main

• mysql-5.0.22-2.1.0.1.i386.rpm: MySQL database utilities
• mysql-server-5.0.22-2.1.0.1.i386.rpm: MySQL database engine

Dependencies - Samba Client (used for the MySQL error log), Perl DBD and Perl DBI (required by mysql-server)

• samba-common-3.0.23c-2.el5.2.0.2.i386.rpm: samba shared libraries
• samba-client-3.0.23c-2.el5.2.0.2.i386.rpm: samba client
• perl-DBD-MySQL-3.0007-1.fc6.i386.rpm: a MySQL interface for perl
• perl-DBI-1.52-1.fc6.i386.rpm: a database access API for perl

MYSQLR does not modify MySQL in any way; MYSQLR uses the MySQL binary RPM installation without modification

Theory of Operation

The MYSQLR appliance is based on mysql-server-5.0.22. The default storage engine used is MyISAM. The appliance also supports MEMORY, InnoDB, BerkeleyDB and MRG_MYISAM as alternatives.

Configuration

Configuration files

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
pid-file=/var/run/mysqld/mysqld.pid


[mysqld]

# Performance tuning
key_buffer = 256M
max_allowed_packet = 16M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 8

# write temp files to data volume
tmpdir=/mnt/data

log-error=/mnt/data/error.log
datadir=/mnt/data
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
#$$propN: 1:use_old_passwords
old_passwords=1

# REPLICATION
expire_logs_days                = 0
auto_increment_increment        = 10
replicate-same-server-id        = 0
master-user                     = replication_user
relay-log                       = relay-log
max-relay-log-size              = 512857600
max-binlog-size                 = 512857600


#$$propN: 1:server_id
auto_increment_offset           = 1

#$$propN: 1:server_id
server-id                       = 1

#$$propN: 0:enable_slave_updates
log-slave-updates               = 0

Any valid options that can be used for the start of mysql daemon/server can be added to the configuration file. For a detailed list of available options as well as sample configutrations refer to the MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/option-files.html.

Properties

Advanced Properties (used in replication scenarios)

Property name	Type	Description
server_id	Integer	Server id. Possible values are: from 1 to 10. This specifies the id of the server when doing replication. Make sure you setup different ids for all your servers that are part of the replication. Default: 1
enable_binlog	Integer	Enable writing of binary logs. Possible values are: 0 to disable the use of binary logs and 1 to use binary logs. Enable this if you will use the appliance in replication scenario. This will allow you to have slave servers that will read this appliance binary logs. Default: 0
enable_slave_updates	Integer	Enable writing of slave updates to binary logs. Possible values are: 0 to disable slave updates writing in binary logs and 1 to enable it. Enable this if you will use the appliance in a multi-node replication scenario where the appliance is both a slave for one server and a master for another server. You need to have enable_binlog set to 1 in order for this to work, otherwise the appliance will fail to start. Default: 0

For a more detailed description of the properties and sample configurations look at MYSQLR appliance datasheet.

Log Files

By default, the mysql error logs are disabled. To enable them, the error_log_filename property must be specified. See the properties section above for configuration details and information about the error_log_level property.

When using in replication scenarios, binary/relay logs are written to the data volume. In the default configuration no binary/relay logs are written.

Important Notes

This section is not relevant for the MYSQLR appliance.

-- NetClime - 07 Nov 2007