Reference Information › Appliance Catalog Reference Guide › System Catalog › Database Appliances › MYSQLR, MYSQLR64 - MySQL Database Appliance Suitable for Replication

MYSQLR, MYSQLR64 - MySQL Database Appliance Suitable for Replication

Functional Overview

MYSQLR64 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 appliances can also be used in complex MYSQL replication scenarios. The appliances are based on MYSQL5 (CentOS 5.5/MySQL 5) but also handle 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 MYSQLR64 appliances 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. MYSQLR64 also provide 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. (MYSQLR64 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 simply 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. (MYSQLR64 does not automatically backup your database, you need to manage the backup yourself. )

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

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

Important! You should not mix 32-bit and 64-bit MYSQLR appliances when using replication as the database files are copied as is from the master to the slave. Also, data volumes from the 32-bit version of the appliance should not be used with a 64-bit version of the same appliance (and vice versa). To migrate a database between the 32-bit and 64-bit MYSQLR versions, dump the databases on one host and import them on the other as described here.

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.

Important! The rin and rout terminals are used for both SSH (tcp 22) and MYSQL (tcp 3306) data. When gateways / VPN is used to connect those terminals, the firewalls should be configured to allow both ports.

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 MYSQLR64 1.6.1 or later.

Important!

• 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.
• To migrate a database from an old appliance (MYSQL, MYSQL5, MYSQL64), a database from a physical server or a database from MYSQLR (if migrating from 32 bit to 64 bit version of the appliance), see the 'Migrate database from another appliance' procedure.

Properties

Important!

• As of MYSQLR64 1.6.8, the use_old_passwords property was removed. If you need to enable old_passwords, create a custom configuration as described below.
• The MYSQLR 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)

Custom Configuration

This feature is available in MYSQLR64 1.6.1 or later.

MYSQLR64 allows the use of a custom MYSQL configuration file which can provide additional configuration options or overwrite existing configuration specified in /etc/my.cnf.

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 in the MYSQL documentation.

For example, the following can be used to tune MYSQLR64 for better performance when using InnoDB? (the default MYSQLR64 configuration is optimized for MyISAM). The example is based on using 512M of memory (default for MYSQLR64).

[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

Important! When used in replication mode, MYSQLR64 will also synchronize the my.cnf file on the data volume whenever you fix/initiate the replication, so your slave will have the same configuration as the master.

Web Interface

MYSQLR64 provides a web interface which can be accessed on both its ui terminal and its default interface through the Login (web) option in the CA AppLogic® editor. 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
• 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 MYSQLR64 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 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. Help ensure 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 Database
  • browse and edit your databases using PhPMyAdmin

Add Master-Slave Replications to MYSQLR64 Appliances

CA AppLogic® lets you add master-slave replications to existing MYSQLR64 appliances without losing any data.

To add master-slave replications to MYSQLR64 appliances

1. Set rpl_mode to master on the existing appliance
2. Add a new MYSQLR64 appliance to your application with an empty data volume. Connect its rout terminal to the rin terminal of the current appliance.
3. (Optional) If you are going to use it in master-master replication, connect the eliminated terminal on the current appliance to the rin terminal of the new appliance.
4. Restart the application.
5. Log in to the web interface of the new appliance and select initiate/fix replication from the Manage Replication tab. Logging takes time depending on the size of the data on the master. If you use INSSLR for accessing MYSQLR64, help ensure to set the timeout property to a high value (36000) to avoid timeouts. Verify that there are no timeouts in any proxies on the client side (better not use proxy at all).
6. After replication is initiated, login to the web interface on both MYSQLR64 appliances and verify the replication status. Within 5 minutes or less, the replication should be running on both appliances.

Add MYSQLR64 Appliances to Master-Slave Replications

CA AppLogic® lets you add new MYSQLR64 appliances to existing master-master replications without losing any data.

To add MYSQLR64 appliances to master-slave replications

1. Add a new MYSQLR64 appliance to your application with an empty data volume. For example, dbN, presuming that you already have N-1 MYSQLR64 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).
2. Set rpl_mode to master_and_slave of dbN.
3. Connect the rout terminal of the dbN-1 appliance to the rin terminal of the dbN appliance.
4. Connect the rout terminal of the dbN appliance to the rin terminal of the db1 appliance.
5. 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.
6. Log in to the web interface of the dbN appliance and select initiate/fix replication from the ManageReplication tab. Logging takes time depending on the size of the data on the master. If you use INSSLR for accessing MYSQLR64, help ensure that the timeout property is set to a high value (36000) to avoid timeouts. Also, help ensure that there are no timeouts in any proxies on the client side (better not use proxy at all).
7. After replication is initiated, log in to the web interface of dbN-1 and select reset master log position. Logging makes the application read dbN-1 and read dbN`s binary logs from the beginning.
8. Log in on to web interface of all MYSQLR64 appliances and verify the replication status, in 5 minutes or less the replication should be running on all appliances.

Repair Replication in Master-Slave Configurations

CA AppLogic® lets you repair replication in a master-slave setup without losing any data.

To repair replication in master-slave configurations

1. Login to the web interface of the slave and select initiate/fix replication. Logging takes time depending on the size of the data on the master. During the operation, mysql service on the slave stops. No downtime is introduced on the master. This operation is equivalent to adding a new appliance to an existing MYSQLR64 appliance. If you use INSSLR for accessing MYSQLR64, verify that the timeout property is set to a high value (36000) to avoid timeouts. Also, verify that there are no timeouts in any proxies on the client side (better not use proxy at all).
2. After replication is initiated, log in to the web interface on the slave MYSQLR64 appliances and verify the replication status, within 5 minutes or less, the replication should be running.

Repair Replication in Master-Master Configurations

CA AppLogic® lets you repair replication in a master-master setup without losing any data.

To repair replication in master-master configurations

1. Log in to the web interface of the appliance that reports the replication as failed and select initiate/fix replication. Logging takes time depending on the size of the database on the master. During the operation, mysql service on the appliance stops. No downtime is introduced on the master. The database will not be in sync among all masters until the end of the repair. The database updates may not be replicated on all other MYSQLR64 appliances for the duration of the repair.

   Important! All data on this appliance is initialized from the master. If there are any updates to the database on the current appliance since the replication is broken, they will be lost. In such cases, resolve the conflicts manually.

2. Login to the web interface of the appliance that has its rout terminal connected to the appliance on which you run fix replication. Select reset master log position to ensure the application reads the binary logs of the fixed appliance from the beginning. If you use INSSLR for accessing MYSQLR64, verify that the timeout property is set to a high value (36000) to avoid timeouts. Also, verify that there are no timeouts in any proxies on the client side (better not use proxy at all).
3. Log in on to web interface of all MYSQLR64 appliances and review the replication status. Within 5 minutes or less, the replication should be running on all appliances.

Replication Monitoring

There is a cron job that monitors the replication between MYSQLR64 appliances. In case the replication is enabled, the cron job runs every two minutes and sends alerts to the grid dashboard in the following cases:

• 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 such cases, the user should solve the problem manually.

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

Custom Counters

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

The following counters belong to the MySql counter group:

Error Messages

The following messages may appear in either the appliance log file or the system log of the grid controller when the appliance fails to start:

Additionally, the following errors may appear on the grid dashboard while the appliance is running:

Simple Two-tier Application (No Replication)

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

Appliances in use:

• user - input gateway
• web - web server
• db - MYSQLR64 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 user gateway. The gateway forwards the requests to the web server, which serves the request. When scripts (for example, 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 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: 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 Grid Users Guide.

Scalable Two-tier Application (No Replication)

The following diagram shows a typical usage of the MYSQLR64 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 and 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 (for example, 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):

Notes:

• 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, and so on.

N-tier Application with Master-Slave Replication (Suitable for 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 MYSQLR64 appliance
• sw - split HTTP traffic to each MYSQLR64 appliance
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (for example, CGI scripts)
• master - MYSQLR64 configured as master
• slave - MYSQLR64 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

Notes:

• The data volume must be configured on logs, content and mon appliances.
• 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.