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.

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.
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

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

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 3Tera 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

Replication Setup and Maintenance

Add Master-Slave Replications to MYSQLR64 Appliances

CA 3Tera 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).

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 3Tera 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.

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 3Tera 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).

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 3Tera AppLogic lets you repair replication in a master-master setup without losing any data.

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.

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.

To repair replication in master-master configurations

1. Login to the web interface of the appliance that has its rout terminal connected to the appliance that runs fix replication. Select reset master log position. Setting master log makes the application read 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).

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.

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:

Typical Usage

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.

N-tier Application with Master-Master Replication (Suitable for Load Balancing)

The following diagram shows a typical usage of the MYSQLR64 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 MYSQLR64 servers during operation for load-balancing. Also, in the case where one of the WEB/MYSQLR64 instances fails, the other WEB/MYSQLR64 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 MYSQLR64 appliance
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (for example, CGI scripts)
• db1 and db2 - MYSQLR64 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 to replicate the updates that the web servers do to the database. Each MYSQLR64 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

Notes:

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

The following diagram shows a typical usage of the MYSQLR64 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 MYSQLR64 servers during operation for load-balancing. Also, in the case where one of the WEB/MYSQLR64 instances fails, the other WEB/MYSQLR64 instances can be used to prevent application downtime(MYSQLR64 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 MYSQLR64 appliance
• web_lb - web load-balancer for user requests
• web1, web2, web3, web4 - web servers with active content (for example, CGI scripts)
• db1, db2, db3, db4 - MYSQLR64 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 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 MYSQLR64 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

Notes:

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

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

Master application

Slave application

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 master or slave
• vpn - input for the remote application to connect to the master=/=slave appliance to replicate the database
• web_lb - web load-balancer for user requests
• web1, web2 - web servers with active content (for example, CGI scripts)
• master - MYSQLR64 configured to be a master
• slave - MYSQLR64 configured to be a slave
• content - storage for database error log files, web content and web logs
• logs - storage for database error log files

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 master database. The master appliance connects to the remote (slave) application, the only difference being the server_id of slave and the network setup) to replicate the database. The remote application connects to the master appliance through the vpn gateway which is configured to allow connection only from the vpn gateway of the remote application. The master and slave 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 master and slave is available via admin gateway on port 8080.

master

master vpn

slave

slave vpn

The remote application is an exact copy, the only difference is the network setup of the user, admin, and vpn appliances, connection(s) between the vpn appliance and master=/=slave, and the server_id of the master=/=slave appliance (it should be unique).

Migrate database from another appliance

If you need to migrate from MYSQLR to MYSQLR64 (and vice versa), you should not only use the volumes from the 32-bit appliance on a 64-bit one (or vice versa) as this could cause data corruption. The recommended way to do this is by dumping the database on the old appliance, transferring the dumped file to the new appliance, and then import the database into the new appliance.

The same procedure can be used to migrate a database from an older database appliance (MYSQL, MYSQL5, MYSQL64) or a mysql database not running on a CA 3Tera AppLogic appliance.

To migrate the database:

• (optional) Prevent access to the old database - this is optional and it is only to help ensure that the database is not modified while it is being dumped. One way to do this would be to start the appliance with nothing connected on its input terminals.
• Dump all databases on the old appliance. Verify you have enough space on the data volume before doing this (you can always resize the data volume if there is not enough space). This is done by starting the appliance, log into the appliance and execute the following command:
  • mysqldump --all-databases --lock-all-tables | gzip > /mnt/data/my_dump.sql.gz
• Stop the application.
• Create a new application volume that is going to store the database for the new appliance (ext3-snapshot). Verify the volume is large enough to hold the database dump file (/mnt/data/my_dump.sql.gz) and the database itself.
• Transfer the database dump file to the newly created volume (below is only one way of copying the database using 2 filers setup with external internet access; if this is not suitable for your configuration, the database may also be copied using scp from the grid to an external server and then back to the destination volume):
  • Manage each volume separately using two filers (one for each volume) using the vol manage command. Be sure to pass the external IP information on the vol manage command line so each filer can access each other to copy the database between the two volumes. See the vol manage help for more information about how to specify the external network settings.
  • In the filer for the source volume, execute the following: cat /etc/ssh/ssh_host_rsa_key.pub >> /root/.ssh/authorized_keys
  • In the filer for the destination volume, execute the following: scp -pr -i /etc/ssh/ssh_host_rsa_key IP_address_to_source_filer:/mnt/vol/my_dump.sql.gz /mnt/vol/.
  • After the database is copied, you may exit from both filers.
• Edit the application and add a new instance of MYSQLR/MYSQLR64, parameterize it, connect it and assign the newly created volume to the new instance. At this point the old MYSQLR/MYSQLR64 instance may be deleted from the application (however you may want to keep it around for backup purposes until you can verify that the database was transferred correctly to the new instance).
• Import the database - Start the new appliance, log into it and execute the following command:
  • zcat /mnt/data/my_dump.sql.gz | mysql

The database is now transferred and the appliance is ready for use.

Notes

Be aware of the following:

• MYSQLR64 performance is optimized for the MyISAM storage engine which is used by default. If you want to enhance the performance for Innodb or simply fine tune the MYSQL service for your application, see the Custom Configuration section for details on how to add configuration parameters to the existing configuration. This feature is available in MYSQLR64 1.6.1 or later.
• MYSQLR64 uses only asynchronous replication (synchronous replication is not supported by MySQL).
• MYSQLR64 detects replication errors within 2 minutes and notifies the user by posting a message on the grid's dashboard. The user can then use the MYSQLR64 GUI to fix the issue (no automated recovery is provided by MYSQLR64).
• When MYSQLR64 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 MYSQLR64 appliance.
• The theoretical maximum number of MYSQLR64 appliances in a single replication is 10. As data is replicated in a circular way, increasing the number of MYSQLR64 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).

Important! When creating users for the database, verify 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 MYSQLR64 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 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 MYSQLR64 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 MYSQLR64's in input should have the MySQL client library 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 website

http://dev.mysql.com/doc/refman/5.0/en/index.html -- MySQL 5.0 documentation

Third Party Open Source Software Used in the Appliance

MYSQLR64 uses the following third party open source packages in addition to the 3rd party open source packages used by its base class LUX64.

MYSQLR Troubleshooting

MYSQLR Appliance Freezes Upon Restart

Starting with MYSQLR 1.6.2, you can set/change a password for the root database account. However, the MSYQLR appliance has a few root accounts with different hostnames. When setting/changing a password, you should always use the root@% account. The root@% account is the account that appliances connected to the in terminal of MYSQLR will authenticate against. The other root accounts are used only from local users and a password should never be set for them as doing so will cause the MYSQLR appliance to fail to start.

Note: If you leave the root@localhost account without a password, it is not a security issue because this account can only be used by local users on the appliance and anyone who has access to the appliance can change the password.

Recover a MYSQLR appliance that fails to start after changing the root database password

To recover an appliance that fails to start due to changing the database root password, perform the following steps:

1. Start the appliance or application in debug mode.

   Appliance Start Command

   comp start main.MYSQLR --debug 
   

   Application Start Command

   app start --debug 
   

   You should be able to login to the appliance a few seconds after you start the appliance. It is not necessary to wait for the appliance to time out.

2. Login over SSH to the appliance and perform the following commands:

   mysql -p -e "update mysql.user set Password='' where User='root'" 
   mysqladmin -p flush-privileges 
   mysql -e 'update mysql.user set password=PASSWORD("NEWPASSWORD") where User="root" and Host="%"' 
   mysqladmin flush-privileges
   

Restart the appliance to verify that it boots, and that mysql connections on the 'in' terminal for user root require a password.