MYSQLR: MySQL Database Appliances suitable for replication.

	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

MYSQLR 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. The appliance can also be used in complex MYSQL replication scenarios. The appliance is based on MYSQL5? (CentOS 5/MySQL 5) but also handles database replication.

Database replication enables data from one MySQL database server (known as "master") to be replicated to one or more MySQL database servers (known as "slaves"). The MYSQLR appliance can be setup for master-slave as well as master-master replication and replication with more than two masters.

The replication setup, management and monitoring is done via a web interface. The web interface provides an easy way to start replication with close to zero downtime on the master. It can also be used to repair a replication in case of problems. The web interface can be used for copying databases from older database appliances like MYSQL and MYSQL5. MYSQLR also provides an easy way for managing and browsing your database (based on phpMyAdmin).

Replication is useful in several cases:

• Scale-out solutions - spreading the load among multiple nodes to improve performance ("master-master" configuration).
• Failover solutions - Using multiple nodes in master-master replication so each appliance can be used alone to serve the application in case the other(s) fail. ( MYSQLR does not automatically take care of failures, if a MYSQL server fails, your application should detect it and stop using it. )
• Backup solutions - two (or more) nodes, one running as master, the rest running as slaves. The application uses only the master, the slaves are used for doing backup (in which case the database can be stopped to achieve consistency of the backup) or just for doing heavy analysis of the data (reports, summaries or anything that does not change the data) without loading the master, thus not influencing the performance of the application. ( MYSQLR does not automatically backup your database, you need to manage the backup yourself. )

In its default configuration MYSQLR acts exactly as a MYSQL5 appliance with a web interface for management. In order to use it for replication scenarios, you need at least two MYSQLR appliances with an appropriate configuration (see Typical Usage).

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

See notes on adding database users for the MYSQLR 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 must be exclusively dedicated to the MYSQL instance (cannot be shared with other appliances).

The data volume should not be formatted with any file-system as it will be used to build an LVM volume. If you use a volume that is formatted with any file-system (possibly with files from an old MYSQL appliance), the appliance will fail to start to prevent you from deleting your database/files.

The old_data volume can be used only when the data volume is blank. In this case all data from old_data is transferred to the data volume by using the MYSQLR web interface. If you have supplied both data and old_data volumes and the data volume is not blank, the appliance will fail to start, preventing you from overwriting any data on the data volume. If you still want to transfer data from the old_data volume without using a blank data volume, start your application in maintenance mode, login to the appliance and copy the data from 'old_data' to 'data'.

Properties

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.

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 unique ids for all your servers that are part of the replication. Default: 1
rpl_mode	String	Replication mode. Possible values are none (no replication), master, slave, and master_and_slave (for multi-master replication scenarios where a server is a master and a slave at the same time). Default: none
web_pwd	String	Specify password for authentication to the web interface. This property is not mandatory, but should be set if the 'ui' terminal is connected, otherwise the appliance will fail to start.

Web Interface

MYSQLR provides a web interface which can be accessed on its ui terminal. Using the web interface requires http authentication. Leave the username blank and use the value of web_pwd as password. The interface has the following features:

• view server status
  • start/stop/restart mysql service
  • mysql server status and uptime
  • mysql replication status (for this appliance only)
  • latest mysql log entries
  • appliance uptime
  • appliance memory usage
  • appliance disk usage

• Migrate Content (visible only when old_data volume is present )
  • Migrate Content - copies the content of old_data volume to data volume. This is used to provide an easy upgrade from MYSQL and MYSQL5 appliances. All data on the data volume will be wiped.

• view/manage replication
  • mysql replication status (for this appliance only)
  • initiate/fix replication. In this case the database on the appliance is wiped and the replication is started from scratch from the master. This does not affect the database on the master (no data is deleted on the master) and it does not introduce any downtime of the master MYSQLR appliance. During the repair, the appliance will be offline and should not be used.
  • delete snapshot - deletes a database snapshot that was created on this appliance in order to initiate/fix replication on its slave. In normal operation no snapshots will be left on the appliance, if you see the 'delete snapshot' button, either a slave is currently initiating replication from this appliance or a previous attempt to initiate replication failed. Make sure no slave is using the snapshot before deleting it. This option will not be visible if there is no snapshot on the appliance.
  • reset master log position - when adding a new server in a multi-node master-master replication, after running replication initiate/fix on the current appliance you need to login to the web interface of the appliance which connects through its 'rout' terminal to this appliance and click on 'reset master log position'.

• Manage Databse
  • browse and edit your databases using PhPMyAdmin

Replication setup/maintenance!

To add a master-slave replication to an existing MYSQLR appliance without losing any data:

• Set rpl_mode to master on the existing appliance
• Add a new MYSQLR appliance to your application with an empty data volume. Connect its rout terminal to the rin terminal of the current appliance.
• If you are going to use it in master-master replication, connect the rout terminal on the current appliance to the rin terminal of the new appliance
• Restart the application so that the changes take effect.
• Login to the web interface of the new appliance and select "initiate/fix replication" from the 'Manage Replication' tab. This will take some time depending on the size of the data on the master. If you use INSSL for accessing MYSQLR, make sure the timeout property is set to a high value (36000) to avoid timeouts. Also make sure there are no timeouts in any proxies on the client side (better not use proxy at all).
• After replication is initiated, login to the web interface on both MYSQLR appliances and check the replication status, in 5 minutes or less the replication should be running on both appliances.

To add a new MYSQLR appliance to an existing master-master replication without losing any data:

• Add a new MYSQLR appliance to your application with an empty data volume. For example dbN, presuming that you already have N-1 MYSQLR appliances ( 3 <= N <= 10 ) and each appliance has its rout terminal connected to the rin terminal of the next appliance in a circular setup (db1 rout is connected to db2 rin and so on).
• Set rpl_mode to master_and_slave of dbN .
• Connect the rout terminal of the dbN-1 appliance to the rin terminal of the dbN appliance
• Connect the rout terminal of the dbN appliance to the rin terminal of the db1 appliance.
• Restart the application so that the changes take effect. After the restart, the replication will not be in sync until the end of the procedure, data written to one appliance may not be replicated on all other appliances for the time of the procedure.
• Login to the web interface of the dbN appliance and select "initiate/fix replication" from the 'ManageReplication'. This will take some time depending on the size of the data on the master. If you use INSSL for accessing MYSQLR, make sure the timeout property is set to a high value (36000) to avoid timeouts. Also make sure there are no timeouts in any proxies on the client side (better not use proxy at all).
• After replication is initiated, login to the web interface of dbN-1 and select "reset master log position". This will make it read dbN-1 read dbN`s binary logs from the beginning.
• Login on to web interface of all MYSQLR appliances and check the replication status, in 5 minutes or less the replication should be running on all appliances.

To fix a broken replication in a master-slave setup without losing any data:

• Login to the web interface of the slave and select "initiate/fix replication". This will take some time depending on the size of the data on the master. During the operation the mysql service on the slave will be stopped. No downtime will be introduced on the master. This operation is equivalent to adding a new appliance to an existing MYSQLR appliance. If you use INSSL for accessing MYSQLR, make sure the timeout property is set to a high value (36000) to avoid timeouts. Also make sure there are no timeouts in any proxies on the client side (better not use proxy at all).
• After replication is initiated, login to the web interface on the slave MYSQLR appliances and check the replication status, in 5 minutes or less the replication should be running.

To fix a broken replication in a master-master setup without losing any data:

• Login to the web interface of the appliance that reports the replication as failed and select "initiate/fix replication". This will take some time depending on the size of the database on the master. During the operation the mysql service on the appliance will be stopped. No downtime will be introduced on the master. The database will not be in sync among all masters until the end of the repair, database updates may not be replicated on all other MYSQLR appliances for the duration of the repair.

• Login to the web interface of the appliance that has its rout terminal connected to the appliance that we run "fix replication" on. Select "reset master log position". This will make it read the binary logs of the "fixed" appliance from the begining. If you use INSSL for accessing MYSQLR, make sure the timeout property is set to a high value (36000) to avoid timeouts. Also make sure there are no timeouts in any proxies on the client side (better not use proxy at all).
• Login on to web interface of all MYSQLR appliances and check the replication status, in 5 minutes or less the replication should be running on all appliances.

Replication monitoring

• Replication is not running (the following appears on the grid dashboard Replication of master server is not running, please check!)
• Replication is running but it is too much behind the master (the following appears on the grid dashboard Replication is too much behind master, please check!)
• Disk space on the data volume is low (< 20% free). This is checked regardless of whether the replication is enabled or not (the following appears on the grid dashboard Free space on the data volume is running low, please check!)
• Disk space on the binlogs volume is low (< 20% free). This is checked regardless of whether the replication is enabled or not (the following appears on the grid dashboard Free space on the binlogs volume is running low, please check!)

In case of failed replication, you can use the web interface to fix it as described in the section above.

Custom Counters

The following counters belong to the MySql counter group:

Error Messages

• Failed to setup replication.
• Failed to create database.
• Failed to start mysql.
• Insufficient permissions in the mysql database.

Typical Usage

Simple two-tier application (no replication)

The following diagram shows a typical usage of the MYSQLR appliance in a two-tier web application:

Appliances in use:

• in - input gateway
• web - web server
• db - MYSQLR database appliance
• logs - NAS appliance for storing log files
• content - NAS appliance for storing web server content
• admin - input gateway for viewing log files
• mon - MON appliance

Client request arrives on the in gateway. The gateway forwards the requests to the web server, which serves the request. When scripts (e.g., Perl or PHP) on web need to access persistent data, they use the db appliance through the out terminal of the web server. The db 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 or web server log files. The admin gateway forwards the requests to the logs NAS appliance.

Example property configuration (properties that are not listed should be left to their default values):

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

Scalable two-tier application (no replication)

The following diagram shows a typical usage of the MYSQLR 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:

• user - 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 and web servers error log files and access to them
• content - storage for web servers content
• maint - input gateway for maintenance access
• admin - a generic Linux5 server used by the administrator
• gway - a network gateway for administrator's access to the Internet
• mon - MON appliance

Client request arrives 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 and web1 and web2 servers write their log files to the logs appliance through the log terminals. In addition, an administrator can log in through the maint gateway to the logs appliance and view the log files.

Additionally, an administrator can log in over ssh through the maint gateway to the admin server (public-private keys need to be setup). 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 (properties that are not listed should be left to their default values):

Note that the data volume must also be configured on the db, logs, content and mon appliances.

The maint, admin, gway, mon 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.

N-tier application with master-slave replication (suitable for doing backups)

The following diagram shows a typical usage of the MYSQL appliance in a web application in which the database is replicated to a slave server. The slave server can be used to make consistent backups of the data without stopping the master server, thus introducing zero downtime of the application.

Appliances in use:

• user - input gateway for user requests
• admin - input gateway for log files access and web access to MYSQLR appliance
• sw - split http traffic to each MYSQLR appliance
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (e.g., CGI scripts)
• master - MYSQLR configured as master
• slave - MYSQLR configured as slave
• content - storage for web servers content
• logs - storage for databases and web servers log files
• mon - MON appliance

Client request arrives 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 master database.

The slave appliance is connected to the master appliance and replicates its data. slave can be stopped at any time for doing consistent backups of the SQL data or heavy analytics without interfering with the performance of the master appliance and the rest of the application.

Web access to master and slave is available via admin gateway on port 8080 and 8081.

The master, slave, web1 and web2 appliances are configured to store their log files within the root directory of the share exposed by logs. In addition, an administrator can view log files through the admin gateway.

Example property configuration (properties that are not listed should be left to their default values):

master

slave

Note that the data volume must be configured on logs, content and mon appliances.

Note that the data and binlogs volumes must be configured on both the master and the slave. They should have enough space to handle both the data and the logs.

Each appliance should use its own volume (it cannot be shared between the appliances).

The admin, mon and log appliances are not required for the operation of the replication.

N-tier application with master-master replication (suitable for doing load balancing)

The following diagram shows a typical usage of the MYSQLR appliance in a web application in which the database is replicated to two servers in a master-master replication scenario. In this use case, the application uses both WEB and MYSQLR servers during operation for load-balancing. Also, in the case where one of the WEB/MYSQLR instances fails, the other WEB/MYSQLR instance can be used to prevent application downtime.

Appliances in use:

• user - input gateway for user requests
• admin - input gateway for log files access
• sw - split http traffic to each MYSQLR appliance
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (e.g., CGI scripts)
• db1 and db2 - MYSQLR appliances configured to be both master and slave at the same time
• content - storage for web servers content
• logs - storage for databases and web servers log files
• mon - MON appliance

Client request arrives 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. web1 uses the db1 database appliance, web2 uses the db2 database appliance. db1 and db2 are connected in order to replicate the updates that the web servers do to the database. Each MYSQLR appliance uses an offset (equal to its server_id) for its auto_increment columns so that no duplicate entries occur.

Web access to db1 and db2 is available via admin gateway on port 8080 and 8081.

The db1, db2, web1 and web2 appliances are configured to store their log files within the root directory of the share exposed by logs. In addition, an administrator can view log files through the admin gateway.

Example property configuration (properties that are not listed should be left to their default values):

db1

db2

Note that the data volume must be configured on logs, content and mon appliances.

Note that the data and binlogs volumes must be configured on both db1 and db2. Each appliance should use its own volume (it cannot be shared between the appliances).

When doing master-master replication you need to start with empty data volumes.

The admin, mon and log appliances are not required for the operation of the replication.

N-tier application with multi-node master-master replication (suitable for doing load balancing)

The following diagram shows a typical usage of the MYSQLR appliance in a web application in which the database is replicated to four servers in a master-master replication scenario. In this use case, the application uses all of the WEB and MYSQLR servers during operation for load-balancing. Also, in the case where one of the WEB/MYSQLR instances fails, the other WEB/MYSQLR instances can be used to prevent application downtime(MYSQLR does not take care of failures).

Appliances in use:

• user - input gateway for user requests
• admin - input gateway for log files access
• sw - split http traffic to each MYSQLR appliance
• web_lb - web load-balancer for user requests
• web1, web2, web3, web4 - web servers with active content (e.g., CGI scripts)
• db1, db2, db3, db4 - MYSQLR configured to be both master and slave at the same time
• content - storage for database error log files, web content and web logs
• logs - storage for databases and web servers log files
• mon - MON appliance

Client request arrives 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, web2, web3 and web4. Each web server uses its own database appliance. All database appliances are connected in a circular way in order to replicate the updates that the web servers do to the database. Thus an update to db1 for example is replicated to db2, db3 and db4. Each MYSQLR appliance uses an offset (equal to its server_id) for its auto_increment columns so that no duplicate entries occur.

Web access to db1, db2, db3, db4 is available via admin gateway on port 8080, 8081, 8082 and 8083.

The db1, db2, web1 and web2 appliances are configured to store their log files within the root directory of the share exposed by logs. In addition, an administrator can view log files through the admin gateway.

Example property configuration (properties that are not listed should be left to their default values):

db1

db2

db3

db4

Note that the data volume must be configured on logs, content and mon appliances.

Note that the data and binlogs volume must also be configured on all db appliances. Each appliance should use its own volume (it cannot be shared between the appliances).

When doing multi-master replication you need to start with empty data volumes.

The admin, mon and log appliances are not required for the operation of the replication.

N-tier application running on different facilities (suitable for doing load balancing and failover)

The following diagram shows a typical usage of the MYSQLR appliance in a web application running in more than one facility. With this setup you can have two or more identical applications running in different facilities with the database being replicated to all applications in master-master setup. This is useful in two cases:

• geographical load balancing - setup 2 (or more) such applications so that users can use the application that is closest to their geographical region for better response times (load balancing is done with DNS)
• failover - setup 2 (or more) such applications so if the whole facility where one application is running goes down (or the application itself goes down), the other applications can be used to prevent downtime.

Appliances in use:

• user - input gateway for user requests
• admin - input gateway for log files access
• sw - redirect port 8080 from admin to ui on db
• repl_in - input for the remote application to connect to the db appliance in order to replicate the database
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (e.g., CGI scripts)
• db - MYSQLR configured to be both a master and a slave at the same time
• content - storage for database error log files, web content and web logs
• logs - storage for database error log files
• repl_out - output gateway for the db appliance to connect to the remote application in order to replicate the database
• mon - MON appliance

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 appliance connects to the remote application (which is an identical copy, the only difference being the server_id of db and the network setup) in order to replicate the database. The remote application connects to the db appliance via the repl_in gateway which is configured to allow connection only from the repl_out gateway of the remote application. The db appliances in the two applications are running in master-master setup so they always have identical data.

Example property configuration (properties that are not listed should be left to their default values):

Web access to db is available via admin gateway on port 8080.

db

The remote application is an exact copy, the only difference is the network setup of the user, admin, repl_in and repl_out appliances and the server_id of the db appliance (it should be unique).

Notes

• When MYSQLR 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 MYSQLR appliance.

• The theoretical maximum number of MYSQLR appliances in a single replication is 10. As data is replicated in a circular way, increasing the number of MYSQLR appliances will increase the time it takes for slaves to update their databases. The actual maximum of appliances in a replication may be lower depending on your application, load and needs (instant update of slaves may not be critical in some scenarios like data backup).

• 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 data volume size should be 100MB 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.
  • The data volume should not be formatted with any file-system as it will be used to build an LVM volume (the volume should be unformatted). If you use a volume that is formatted with any file-system (possibly with files from an old MYSQL appliance), the appliance will fail to start to prevent you from deleting your files.
  • 20% of the space of the data volume is allocated for initiating/fixing replication, this leaves 80% of the volume space for database storage.
  • When using MYSQLR in replication scenarios either as master, slave or master_and_slave, you must provide a binlogs volume. Binary logs are rotated on 100MB and kept for 2 days after rotating, after that they are deleted. If the appliance is also running as slave, relay logs are also stored to the binlogs volume .You will need at least 500MB binlogs space in order to run master-master replication. The actual size depends on the intensity of MySQL update/insert queries that are executed, you`ll need a few GB binlogs volume for a normal operation of an application.
• The appliance connected to MYSQLR 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 MYSQLR'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

---

Related Documents

• MYSQLR
  • Implementation Design
  • Build procedure
  • Test Plan

Questions and Comments

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

-- NetClime - 24 Oct 2007