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

Reference Information › Appliance Catalog Reference Guide › System Catalog › Database Appliances › MYSQLR, MYSQLR64 - MySQL Database Appliance Suitable for Replication › N-tier Application with Multi-node Master-Master Replication (Suitable for Load Balancing)

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

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

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

Property name	Value	Notes
auto_create	1	Create the database if the volumes are empty.
error_log_filename	db1.error	Name of error log file that is to be stored on the logs data volume.
error_log_level	error	Error logging level
server_id	1	Master server 1
rpl_mode	master_and_slave	master and slave

db2

Property name	Value	Notes
auto_create	1	Create the database if the volumes are empty.
error_log_filename	db2.error	Name of error log file that is to be stored on the logs data volume.
error_log_level	error	Error logging level
server_id	2	Master server 2
rpl_mode	master_and_slave	master and slave

db3

Property name	Value	Notes
auto_create	1	Create the database if the volumes are empty.
error_log_filename	db3.error	Name of error log file that is to be stored on the logs data volume.
error_log_level	error	Error logging level
server_id	3	Master server 3
rpl_mode	master_and_slave	master and slave

db4

Property name	Value	Notes
auto_create	1	Create the database if the volumes are empty.
error_log_filename	db4.error	Name of error log file that is to be stored on the logs data volume.
error_log_level	error	Error logging level
server_id	4	Master server 4
rpl_mode	master_and_slave	master and slave

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.